Details
Major
Description
This test case is not working on MariaDB 10.1.3:
CREATE TABLE test ( id INT, type_id INT, PRIMARY KEY(id) ); INSERT INTO test ( id, type_id ) VALUES ( 1, 1 ), ( 2, 1 ), ( 3, 2 ), ( 4, 5 ); SELECT * FROM test T JOIN ( ( SELECT 1 AS some_another_type_id ) UNION ALL ( SELECT 2 AS some_another_type_id ) ORDER BY some_another_type_id DESC ) AAA WHERE T.type_id = 1;
Same query on clean Mysql 5.6 working with no problems.
Workaround: delete unneeded "order by".
Gliffy Diagrams
Attachments
Issue Links
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Some time ago we removed ORDER BY from any subqueries.
ORDER BY in subqueries does not make any sense.
Sergei Golubchik, please review fix for this bug. Note that I'm completely unsure about correctness of this fix, since it was done with assumption that the purpose of context change was intended for the duration of gathering field list for ORDER BY.
Or was it intended to be changed for different duration? In this case should we rather pop_context()?
CREATE TABLE t1 (
a INT
);
INSERT INTO t1 VALUES ( 2 );
SELECT *
FROM ( (SELECT a FROM t1 ORDER BY a) UNION (SELECT 1 as b ORDER BY b ) ) AS a1
WHERE a1.a = 1 OR a1.a = 2;
DROP TABLE t1;
The statement you're referring to was fixed in MySQL revision 5948561, which is worthy, but doesn't directly relate to this bug.
Thanks for the report and the test case.
The problem appeared along with UNION ALL optimization:
commit 3c4bb0e8720b84a14fe4822d1986d01290b9ab44 fec5ab5a56cb9a45c621207620cc85079cddf537 Author: Igor Babaev <igor@askmonty.org> Date: Tue Oct 14 09:36:50 2014 -0700 MDEV-334: Backport of UNION ALL optimization from mysql-5.7. Although the original code of mysql-5.7 was adjusted to the current MariaDB code the main ideas of the optimization were preserved.The same test case works all right on the current 5.7 tree.