Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.11
-
None
-
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
