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:

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.

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

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.

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`

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

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.

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.

Launchpad bug id: 793589