Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.3
-
centos 6
-
10.1.6-1
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".
Attachments
Issue Links
- links to
Activity
Some time ago we removed ORDER BY from any subqueries.
ORDER BY in subqueries does not make any sense.
serg, 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 fec5ab5a56cb9a45c621207620cc85079cddf537Author: Igor Babaev <igor@askmonty.org>Date: Tue Oct 14 09:36:50 2014 -0700MDEV-334: Backport of UNION ALL optimization from mysql-5.7.Although the original code of mysql-5.7 was adjustedto the current MariaDB code the main ideas of the optimizationwere preserved.The same test case works all right on the current 5.7 tree.