Wrong result with double ORDER BY

[Philip Stoev]

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

[Philip Stoev (pstoev-askmonty)]

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

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

repeatable on mysql 5.5

[Philip Stoev (pstoev-askmonty)]

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.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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`

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

it does not use temporary table because simple_group set to TRUE.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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.