Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 10.0.11
-
Fix Version/s: 10.0.12
-
Component/s: None
-
Labels:None
Description
If you have a condition on the join and then the where, the priority on the condition of the join is not respected if you have an order by clause.
Here is a real query :
SELECT SQL_CALC_FOUND_ROWS a.*, os.`color` FROM `ps_orders` a LEFT JOIN `ps_order_history` oh ON (oh.`id_order` = a.`id_order` AND (oh.`id_order_history` = (SELECT `id_order_history` FROM `ps_order_history` moh WHERE moh.`id_order` = a.id_order ORDER BY moh.date_add DESC LIMIT 1))) LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = oh.`id_order_state`) WHERE oh.`id_order_state`=3 ORDER BY a.`date_add` DESC LIMIT 50;
This query returns 43316 found rows.
If I remove the ORDER BY a.`date_add` DESC statement, I get 356 found rows.
If I use the following query, using a temp table it works :
SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT a.*, a.id_order AS id_pdf, os.`color`, os.id_order_state FROM `ps_orders` a LEFT JOIN `ps_order_history` oh ON (oh.`id_order` = a.`id_order` AND (oh.`id_order_history` = (SELECT `id_order_history` FROM `ps_order_history` moh WHERE moh.`id_order` = a.id_order ORDER BY moh.date_add DESC LIMIT 1))) LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = oh.`id_order_state`) WHERE 1 ORDER BY `date_add` desc) tmpTable WHERE 1 AND `id_order_state` = 3 LIMIT 0,50;
But the ORDER BY `date_add` desc is not applied... the result is not sorted.
My structure and data is in the attachement.
Thanks.
David
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
If I move the ORDER BY clause outside of the temp table, the query crashes again, this leading me to think that the big point is the problem of the ORDER BY clause...
Finaly searching a bit, I found out that the correct number of items is returned by the query if I remove the LIMIT clause I have 356 lines returned, it is only the number returned by SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() that is incorrect.
The version is set to 10.0.10-galera — is that right? Doesn't seem to be a galera issue to me.
It does look like the same issue as MDEV-6221, also reproducible on 10.0.10 but not on 10.0.9. However, it's worth re-checking the provided test case after the other bug is fixed, since the area seems to be sensitive.
It is a duplicate, yes. The fix for MDEV-6221 made this bug to disappear too.
Hi,
Please provide the complete example: CREATE TABLE statements, INSERT INTO .. (or SELECT * FROM... , if the data is already there), your SELECT, actual result, expected result.
Thanks.