[SOLVED]Strange behavior of ORDER BY regarding number of records shown

Scriptcase 8 Bugs
#1

Hi all,

when trying to set up a grouped grid view on a table I faced a problem that looks like a more general one: using different values for the number of records to show gives different results ending up with some records showing up twice while others aren’t shown at all. As this happens even in the SQL Builder, I guess that something is wrong with the handling of partial SQL results.

A short example of what I’m talking about:
I have a table with 23 records. While stepping through them without grouping (just a standard SELECT - FROM SQL statement) showing 10 records a time everything is fine and works as expected. Now if I add a simple ORDER BY statement, things start to get confused. I would expect to see the same results, no matter the value I choose for the number of record sets to show at a time, but that’s not the case:

In the first picture you can see the expected result when showing all records at once. In the next pictures you can see the result if I limit the number of records to show to 10 at a time. As you can see, the results from picture 3 and 4 are just as expected, but in picture 2 (the first of the limited ones) from record 4 on the output is different, leading to the situation that the last record shown here is shown again as the first one of the next partial output and the 10th record on picture 1 (where f_org_lis_id = 1) isn’t shown at all…

I played around a little bit with the number of records to show and the observed behavior gets worse the smaller the number gets.

Is this a known problem (I tried to search for something similar but didn’t found anything) or is there anything wrong with my expectations? Btw., I’m running the actual version (8.00.0047) on Win 8.1 with MySQL 5.6.25, if that matters…

Thanks for any help or hint,
Peter

Showing_all_records.jpg

Showing_records_1_to_10.PNG

Showing_records_11_to_20.PNG

Showing_records_21_to_23.PNG

#2

You are missing just a little clearer explanation.

You are sorting by the 2nd column, f_organization, not the first, and there is no other secondary sort column called for, unless mysql assigns itself one even though you did not.

If the order was exactly the same as these images show, so that each time you reset the search to 10 results per page you keep getting the same order then mysql would obviously be using 2 columns to produce the Results.

But it appears here that each time you restart a search then it is randomly choosing by the second column value how it will do small row exchanges.

I am interested in that case if you always get the same page 3 and 4 order over and over when a new search is started after a full table display.

#3

Thanks for your reply. Sorry, if my explanation was not clear, I’ll try it again.

I’m aware that I’m sorting by the 2nd column - as this was my intention. Please have a look at the screenshots above: The first picture shows the “good case” where the result is as I expected it to be. To get this result, I had to change the number of datasets to show (in the small field below the SQL editor and above the “Ausf?hren” (meaning execute) button) from the default “10” to “30”. I guess that any number bigger than the total number of datasets in the table will work as well. Now the second picture (and the other two) shows the same query, only the number of datasets to show is now set to “10” and my expectation is that the first 10 datasets shown should be exactly the same as the first 10 datasets shown in the first picture. To my understanding, the data deliverd from the database is always the same as there is no difference in the SQL code. The only difference is in the presentation that happens after the SQL is executed: either you see the complete result on one page or you got a result that is partitioned into chunks of size 10. No matter which way you choose - at the end of the day you should see the same data in the same order. But as you can see by comparing the first 10 rows of the result shown in picture one with picture two, the result looks different. On picture 3 you can see the next 10 datasets and the last picture shows the remaining 3 datasets. Maybe you already noticed that these results are exactly identical to the rows 11 to 20 and 21 to 23 from the “good case” shown in picture one, so they can be counted as “good cases” as well. The only unexpected and confusing result is shown in picture two, as stated above.

Hope it is clearer now what I wanted to point out. Thanks again for reading.

#4

Vielen Danke,

Now I guess my only question left:
Are your integers really int types in the database, or are they varchars?

#5

You are welcome :slight_smile:

[QUOTE=elMavericko;38514]Now I guess my only question left:
Are your integers really int types in the database, or are they varchars?[/QUOTE]

Yes of cause they are integers as they (f_organization and f_member) are used as foreign keys to other tables. To complete the information: f_org_list_id is an autoincremented unsigned integer as primary key for the table and the remaining two columns (f_main, f_hidden) are integers used as binary values…

#6

[SOLVED] Strange behavior of ORDER BY regarding number of records shown

After some investigations it looks like I found the reason for this behavior and yes, it seems that my expectations were wrong?

First of all, it?s not a Scripcase issue ? I can reproduce the observed behavior with any MySQL admin tool and an additional ?LIMIT? statement added to the end of the query.

Scriptcase just does the same to limit the number of datasets: adding a LIMIT statement to the query (I checked the MySQL General.log to be sure about this). My expectation was that Scriptcase is sending the plain query to the db, stores the complete result in the internal memory and then presenting only the selected number of rows. But of cause, with respect to big tables, this may not make that much sense. My other (obviously wrong) expectation was that there is always an implicit ?ORDER BY [PRIMARY KEY]? for every query, forced by the database engine. But as this is not the case and the column I used to sort the result is not unique, the database has no additional information which dataset to show so it picks up random rows as long as the order fits, resulting in doubled and missing entries…

To avoid this behavior, I only had to add the unique primary key to the ORDER BY statement and everything works just fine.