[MDEV-14346] incorrect result of intersect with ANY/ALL/IN subquery Created: 2017-11-10  Updated: 2017-11-13  Resolved: 2017-11-13

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Optimizer - CTE
Affects Version/s: 10.3.2
Fix Version/s: 10.3.3

Type: Bug Priority: Critical
Reporter: Oleksandr Byelkin Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-13723 Server crashes in ha_heap::find_uniqu... Closed

 Description   

Following should return empty set

CREATE TABLE t (i INT);
 
INSERT INTO t VALUES (1),(2);
 
SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT SELECT 3 );
 
i
 
2
 
drop table t;

test suite

CREATE TABLE t (i INT);
 
INSERT INTO t VALUES (1),(2);
 
SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT SELECT 3 );
 
drop table t;



 Comments   
Comment by Oleksandr Byelkin [ 2017-11-10 ] 

select i from t where
 
exists ((select 6 as r from dual having t.i <> 6)
 
intersect
 
(select 3 from dual having t.i <> 3));
 
i
 
2
 
(select 6 as r from dual having 1 <> 6)
 
intersect
 
(select 3 from dual having 1 <> 3);
 
r
 
(select 6 as r from dual having 2 <> 6)
 
intersect
 
(select 3 from dual having 2 <> 3);
 
r

Comment by Oleksandr Byelkin [ 2017-11-10 ]

Problem is lack of initialization of state of select_unit before second and so on restart

Comment by Oleksandr Byelkin [ 2017-11-10 ]

github tree is bb-10.2c-MDEV-14346

revision-id: a25aa2e47c2d65d733ae622c215ba5e99ba29468 (mariadb-10.2.2-706-ga25aa2e47c2)
parent(s): 0592d4d88a18bef33176444a07bc7fc1984d3f3c
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2017-11-10 17:57:26 +0100
message:

MDEV-14346: incorrect result of intersect with ANY/ALL/IN subquery

Reinit internal state of select_unit before using to correctly run it after first time.

—

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