[MDEV-19841] ALL and DISTINCT don't work for EXCEPT and INTERSECT anymore Created: 2019-06-23  Updated: 2019-10-11  Resolved: 2019-10-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.6
Fix Version/s: 10.4.9

Type: Bug Priority: Major
Reporter: Markus Winand Assignee: Oleksandr Byelkin
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

linux, built from sources.


 Description   

EXCEPT DISTINCT and INTERSECT DISTINCT yield an error (but it worked in 10.3.7, see script below).

Also, the error message for EXCEPT ALL and INTERSECT ALL has changed (previously, it understood the syntax and gave a very specific error message, now it is a generic parser error message).

UNION is not affected.

To reproduced, run the following script in 10.3.7 and 10.4.6.

CREATE TABLE demo (id INTEGER);
 
 
 
INSERT INTO demo VALUES (1);
 
 
 
SELECT * FROM demo
 
EXCEPT
 
SELECT * FROM demo;
 
 
 
SELECT * FROM demo
 
EXCEPT DISTINCT
 
SELECT * FROM demo;
 
 
 
SELECT * FROM demo
 
EXCEPT ALL
 
SELECT * FROM demo;
 
 
 
 
 
 
 
SELECT * FROM demo
 
INTERSECT
 
SELECT * FROM demo;
 
 
 
SELECT * FROM demo
 
INTERSECT DISTINCT
 
SELECT * FROM demo;
 
 
 
SELECT * FROM demo
 
INTERSECT ALL
 
SELECT * FROM demo;
 
 
 
-- DROP TABLE demo;



 Comments   
Comment by Elena Stepanova [ 2019-06-23 ]

The change was made in the scope of MDEV-11953 (support of brackets in UNION/EXCEPT/INTERSECT operations), so sanja would be best to comment on it.

At least the part related to ALL appears to be intentional, as there is a corresponding change in the MTR test result. I don't see a similar test/change for DISTINCT, but I suppose it shares the same cause.

Comment by Oleksandr Byelkin [ 2019-10-11 ]

ALL was never working in 10.4 in EXCEPT and INTERSECT (only UNION), feature added in 10.5

Comment by Oleksandr Byelkin [ 2019-10-11 ]

if syntax allow it in 10.3, then it is a 10.3 bug

Generated at Thu Feb 08 08:54:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.