[MDEV-7792] SQL Parsing Error - UNION AND ORDER BY WITH JOIN Created: 2015-03-17  Updated: 2015-06-29  Resolved: 2015-06-29

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.3
Fix Version/s: 10.1.6

Type: Bug Priority: Major
Reporter: m.rygiel Assignee: Sergey Vojtovich
Resolution: Fixed Votes: 0
Labels: verified
Environment:

centos 6

Sprint: 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".

 Comments   
Comment by Elena Stepanova [ 2015-03-17 ]

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.

Comment by Igor Babaev [ 2015-04-07 ]

Some time ago we removed ORDER BY from any subqueries.
ORDER BY in subqueries does not make any sense.

Comment by Sergey Vojtovich [ 2015-06-24 ]

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()?

Comment by Oleksandr Byelkin [ 2015-06-25 ]

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;

Comment by Sergey Vojtovich [ 2015-06-25 ]

The statement you're referring to was fixed in MySQL revision 5948561, which is worthy, but doesn't directly relate to this bug.

Generated at Thu Feb 08 07:22:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.