[MDEV-2718] LP:793589 - Wrong result with double ORDER BY Created: 2011-06-06  Updated: 2015-02-02  Resolved: 2012-10-04

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

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug793589.xml    

 Description   

Reproducible on maria-5.2, maria-5.3.

If a query containing a double ORDER BY is used in a VIEW, selecting from that view returns only some of the rows of the original query.

test case:

CREATE TABLE t1 ( f1 int NOT NULL , f10 int, f11 varchar(32), PRIMARY KEY (f1)) ;
INSERT IGNORE INTO t1 VALUES (6,0,'j'),(7,0,'z'),(8,0,'c'),(9,0,'a'),(10,0,'q');

CREATE TABLE t2 ( f11 int) ;
INSERT IGNORE INTO t2 VALUES (0),(0);

CREATE OR REPLACE VIEW v1 AS
SELECT alias2.f11 AS field1
FROM t2 STRAIGHT_JOIN
( t1 AS alias2
 RIGHT JOIN t1 AS alias3 ON alias3.f10 = alias2.f10 )
 ON alias3.f1 = alias2.f1
 GROUP BY field1
ORDER BY alias3.f1 , alias2.f1 ;
SELECT * FROM v1;

should return 5 rows, returns only 1

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-07-13 ]

Re: Wrong result with double ORDER BY
Reproducible without views. Also reproducible with mysql-5.1 . So it is a legacy bug.

CREATE TABLE t1 ( b int) ;
INSERT INTO t1 VALUES (8),(9);

CREATE TABLE t2 ( a int, b int, PRIMARY KEY (a)) ;
INSERT INTO t2 VALUES (6,7),(7,7),(8,1),(9,7),(10,1),(11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5);

SELECT t2.b AS field1 FROM t1, t2 WHERE t1.b = t2.a GROUP BY field1 ORDER BY t1.b, field1;

explain:

1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1  
Comment by Oleksandr Byelkin [ 2011-09-08 ]

Re: Wrong result with double ORDER BY
I t is not a crash and it is edge case so we will not fix it in 5.1.

The smaller test suite is reproduceable on 5.2 and return 1 instead of two rows 1 and 7.

Comment by Oleksandr Byelkin [ 2011-09-09 ]

Re: Wrong result with double ORDER BY
repeatable on mysql 5.5

Comment by Philip Stoev (Inactive) [ 2011-09-09 ]

Re: Wrong result with double ORDER BY
Possible other instance of this bug , file /tmp/273881315580571.test, dir release-workdir-bkahash-sep09-doble1/trial12.log line 948 . I need to check if it is repeatable after the fix.

Comment by Oleksandr Byelkin [ 2011-12-28 ]

Re: Wrong result with double ORDER BY
Comparison of explain of this query and explain of query to which optimizer simplified it, shows that creation temporary table is skipped somehow:

explain extended
SELECT t2.b AS field1 FROM t1, t2 WHERE t1.b = t2.a GROUP BY field1 ORDER BY t1.b, field1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00
Warnings:
Note 1003 select `test`.`t2`.`b` AS `field1` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`b`) group by `test`.`t2`.`b` order by `test`.`t1`.`b`
explain extended
SELECT t2.b AS field1 FROM t1, t2 WHERE t1.b = t2.a GROUP BY field1 ORDER BY t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00
Warnings:
Note 1003 select `test`.`t2`.`b` AS `field1` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`b`) group by `test`.`t2`.`b` order by `test`.`t1`.`b`

Comment by Oleksandr Byelkin [ 2011-12-28 ]

Re: Wrong result with double ORDER BY
it does not use temporary table because simple_group set to TRUE.

Comment by Oleksandr Byelkin [ 2011-12-28 ]

Re: Wrong result with double ORDER BY
Here:
#0 eq_ref_table (join=0x1c04380, start_order=0x1bd1088, tab=0x1c05d50) at sql_select.cc:7270
#1 0x00000000007156a0 in only_eq_ref_tables (join=0x1c04380, order=0x1bd1088, tables=1) at sql_select.cc:7279
#2 0x0000000000715c92 in remove_const (join=0x1c04380, first_order=0x1bd1088, cond=0x1c06188, change_list=true, simple_order=0x1c05943) at sql_select.cc:7417
#3 0x0000000000704708 in JOIN::optimize (this=0x1c04380) at sql_select.cc:1290

For first table
if (tab->cached_eq_ref_table) // If cached
return tab->eq_ref_table;
is true, which should not be correct.

Comment by Oleksandr Byelkin [ 2011-12-28 ]

Re: Wrong result with double ORDER BY
cached_eq_ref_table set when we 'visited' eq_ref_table() with this table.
It looks like eq_ref_table() used for many purposes (like removing unneeded ORDER BY for example, and deciding if it is simple gropping) and the variable (cached_eq_ref_table) interfer between calls for different purposes.

Comment by Rasmus Johansson (Inactive) [ 2012-01-06 ]

Launchpad bug id: 793589

Generated at Thu Feb 08 06:43:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.