[MDEV-6230] Error (too many FOUND_ROWS) for query when using order by Created: 2014-05-12  Updated: 2014-11-27  Resolved: 2014-06-05

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.11
Fix Version/s: 10.0.12

Type: Bug Priority: Critical
Reporter: David-Julian BUCH (Inactive) Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Attachments: Zip Archive Dump20140512.sql.zip    
Issue Links:
Duplicate
is duplicated by MDEV-6221 SQL_CALC_FOUND_ROWS yields wrong resu... Closed
Relates
relates to MDEV-7219 SQL_CALC_FOUND_ROWS yields wrong result Closed

 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

 Comments   
Comment by Elena Stepanova [ 2014-05-12 ]

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.

Comment by David-Julian BUCH (Inactive) [ 2014-05-12 ]

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...

Comment by David-Julian BUCH (Inactive) [ 2014-05-12 ]

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.

Comment by Sergei Golubchik [ 2014-05-22 ]

The version is set to 10.0.10-galera — is that right? Doesn't seem to be a galera issue to me.

Comment by Elena Stepanova [ 2014-05-24 ]

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.

Comment by Sergei Golubchik [ 2014-06-05 ]

It is a duplicate, yes. The fix for MDEV-6221 made this bug to disappear too.

Generated at Thu Feb 08 07:10:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.