Re: About mismatch of database in Results and Scrambles in WCA database

[Sébastien Auroux]

Hi all,

after some bad tries I managed to come up with a fast SQL command showing all differences between Results and Scrambles in terms of roundIds:

SELECT MIN(TableName) AS TableName, competitionId, eventId, roundId

FROM 

(SELECT 'Scrambles' AS TableName, competitionId, eventId, roundId FROM Scrambles

GROUP BY competitionId, eventId, roundId

UNION ALL

SELECT 'Results' AS TableName, competitionId, eventId, roundId FROM Results

WHERE competitionId IN (SELECT competitionId FROM Scrambles)

GROUP BY competitionId, eventId, roundId) tmp

GROUP BY competitionId, eventId, roundId

HAVING COUNT(*) = 1

ORDER BY competitionId

LIMIT 500

The result contains 129 entries, in detail we have 64 wrong roundIds in either the Scrambles or the Results table and there is one 5x5x5 round from NaftusiaOpen2014 without Results.

I wonder how this is possible. Any suggestions on how to deal with this from someone else?

BR, Sébastien

2015-02-13 15:57 GMT+01:00 Sébastien Auroux <sebastien...@gmail.com>:

Dear Akihiro,

thanks for noticing us. I investigated a little bit and this seems to affect multiple competitions. We will discuss on how to solve this within the results team. :)

Best regards,

Sébastien

2015-02-13 3:20 GMT+01:00 m...@roudai.net <m...@roudai.net>:

To WCA Results Team

Hello, I'm Akihiro Ishida in Japan.

I found a mismatch of database in Results and Scrambles tables in WCA database.

For example, in competitionId = 'JapanContestAutumn2014', 

eventId = "333" in `Results` table has roundId '1', '2', and 'f',

but `Scrambles` table has roundId '1', '3' and 'f'.

So, I can't match Results and Scrambles in roundId = '2'.

I didn't research all table, but I think there is same problem in some competitions (at least, CubeCampinKanazawa2014 has).

Regards,

--
You received this message because you are subscribed to the Google Groups "WCA Results" group.
To unsubscribe from this group and stop receiving emails from it, send an email to wca-results...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jeremy Fleischman]

Jim wrote the script that uploads the WCA JSON, it should check for stuff like this. Perhaps there's a bug there?

Can we get the excel workbook, tnoodle scramble zip, and WCA JSON from a competition that is messed up? The first two might be hard to come by. This feels like a bug in the wca-workbook-assistant.

--
You received this message because you are subscribed to the Google Groups "WCA Scrambler Team" group.
To unsubscribe from this group and stop receiving emails from it, send an email to wca-scramble...@googlegroups.com.

[James Mertens]

The upload script seems to be correctly marking the missing results/rounds, eg:
https://www.worldcubeassociation.org/results/admin/upload_results.php?competitionId=NaftusiaOpen2014

The import script will happily import incorrect or incomplete data; it is up to results team members to verify rounds match scrambles using the table provided on the upload competitions page.

Let me know if there is a case where something is noted incorrectly on the upload results page.

[Sébastien Auroux]

True, this one is even more obvious: https://www.worldcubeassociation.org/results/admin/upload_results.php?competitionId=LosLomas2014

@Lars: Is there a problem in the JSON generation process? I think this problem would be prevented if the WA makes sure that the scrambles roundId always equals the assigned results roundId.

@all: seems that all of those issues come from sloppyness while posting, so let's sure to pay more attention on the results/scrambles table in the future.

@Jim: slightly off-topic, but still: could you please add two X (on top or bottom) to the table to remove all scrambles/results of a competition at once, as discussed some time ago?

[James Mertens]

Let me try to get a timeline from Stefan regarding the django implementation.  There are many features that have been on hold, as I was hoping that project would take over, but it may have stalled again.  If there is no timescale for adoption, I may go ahead and do this.

[Sébastien Auroux]

I guess adding those would take less time for you than currently for us removing scrambles/results of one competition, so it might make sense to just go ahead even if something has to be changed after the django implementation is done. :)

[Jeremy Fleischman]

On Fri, Feb 13, 2015 at 10:51 AM, James Mertens <jbc...@gmail.com> wrote:

Let me try to get a timeline from Stefan regarding the django implementation.  There are many features that have been on hold, as I was hoping that project would take over, but it may have stalled again.  If there is no timescale for adoption, I may go ahead and do this.

On Fri Feb 13 2015 at 1:48:14 PM Sébastien Auroux <sebastien...@gmail.com> wrote:

True, this one is even more obvious: https://www.worldcubeassociation.org/results/admin/upload_results.php?competitionId=LosLomas2014

@Lars: Is there a problem in the JSON generation process? I think this problem would be prevented if the WA makes sure that the scrambles roundId always equals the assigned results roundId.

This *is* what the workbook assistant does. There may be a bug here, which is why I asked for an example workbook and tnoodle scramble zip from a competition that has this problem.

[James Mertens]

On Fri Feb 13 2015 at 1:54:14 PM Sébastien Auroux <sebastien...@gmail.com> wrote:

I guess adding those would take less time for you than currently for us removing scrambles/results of one competition, so it might make sense to just go ahead even if something has to be changed after the django implementation is done. :)

Yeah, I'll attempt to get to that this weekend.

 

[Sébastien Auroux]

Thanks Jim!

Jeremy: Fortunately the delegate of LasLomas sent both workbook and scrambles with the results. I just forwarded you the mail.

[Jeremy Fleischman]

Awesome! That was exactly what I needed. Here's what happened:

The Las Lomas competition had 3 rounds of 2x2: First Round, Second Round, and Semi Final. The wca-workbook-assistant doesn't complain when this happens (see screenshot). I've created an issue for this.

Presumably the wca upload scripts have tools to detect missing final rounds and change the last round of an event to a final round? If such a thing exists, it clearly doesn't handle updating the corresponding scrambles for that round. IMO, the wca upload scripts should refuse to upload a competition with bad data, and the organizers should be required to fix such mistakes.

-Jeremy

[Lars Vandenbergh]

There was already an issue logged about a year ago about enforcing round names in the WA according to same guidelines as used on the WCA website.
https://github.com/cubing/wca-workbook-assistant/issues/68

As you can see from the conversation in the issue, the main motivation was exactly the problem we are experiencing now with the mismatched scrambles.

I've closed the issue Jeremy made as it is a duplicate of this one.

Regards,
Lars