create_db_test-order_2020-02-11.sql
order-problem-query.sql
[MDEV-21703] Gleicher Query mit ORDER liefert unterschiedlich sortierte Ergebnisse Created: 2020-02-10 Updated: 2020-02-13 Resolved: 2020-02-13 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Storage Engine - InnoDB |
| Affects Version/s: | 10.4.7, 10.4.12 |
| Fix Version/s: | N/A |
| Type: | Bug | Priority: | Critical |
| Reporter: | Maria User | Assignee: | Unassigned |
| Resolution: | Not a Bug | Votes: | 0 |
| Labels: | innodb, order | ||
| Environment: |
Win10 Pro 1909 |
||
| Attachments: |
create_db_test-order_2020-02-11.sql
order-problem-query.sql
|
| Description |
|
Die mehrfache, direkt aufeinander folgende Ausführung der gleichen Abfrage liefert eine unterschiedlich sortierte Ergebnismenge wenn in der Abfrage eine Order-By-Klausel verwendet wird. Im betreffenden Fall wird eine Order-By-Klausel auf eine über Left Join verknüpfte Spalte angewandt, in der mehrfach vorhandene, gleiche Werte sortiert werden. Die Sortierung der Ergebnismenge dürfte sich eigentlich nicht unterscheiden. Ein ähnlicher Bug: |
| Comments |
| Comment by Alice Sherepa [ 2020-02-11 ] |
|
Could you please add the test case to demonstrate the problem |
| Comment by Maria User [ 2020-02-11 ] |
|
Hi Alice, tried to use some simpler tables on that ... and found that it's working as expected, even using the Order clause. It has something to do with my query because if I remove some Left Joins the behaviour doesn't show. By now I'm not sure what the problem is, but please, could you remove my bug report, thank you. |
| Comment by Alice Sherepa [ 2020-02-11 ] |
|
Please do not hesitate to write back in case you figure out the bug. |
| Comment by Maria User [ 2020-02-11 ] |
|
Alice, I could reproduce the behaviour. I created a simple test db and exported it, also the query I use. I've no clue what the problem is ... if I execute the query multiple times, most times the order is the same as expected. But if I stop the MariaDB service, restart it and execute the query, it will result in a different order. I had this also by executing the query many times as fast as I can ... or when I wait an unspecified time and rerun the query. I'm now not sure if the problem is the query itself ... DB User = root |
| Comment by Alice Sherepa [ 2020-02-12 ] |
|
Does it mean that you get the wrong order? In your query, you do ORDER BY frist_art , |
| Comment by Maria User [ 2020-02-13 ] |
|
Hi Alice, thanks for your note ... coming from MySQL I thought it will behave the same. In MySQL I will allways get the same Order of IDs for both groups (2. Nachfrist + 3. Nachfrist) in the result, even when not considering using also a timestamp for sorting (which should do it using MariaDB). But it's ok for me now, as I changed what was the concern of firing the same query at least two times. Thank you for answering. |
| Comment by Maria User [ 2020-02-13 ] |
|
Sorry ... I didn't answer your question. No, the sorting of frist_art is fine ... but the sorting of the IDs varies within both sorted groups (2. Nachfrist + 3. Nachfrist). And this is randomly, if the Maria Service is running it is mostly resulting in the same order ... but if I restart Maria Service and fire the query on a fresh started MariaDB it's normally a differing result. I didn't understand why ... as said before, this isn't the expected behaviour when using MySQL. |