[MDEV-3387] LP:780386 - Incorrect result of NULL <op> ALL (<empty set>) Created: 2011-05-10  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug780386.xml    

 Description   

The following query:

SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;

returns 1 row for which a1.f3 = NULL with join_cache_level = 0 and 2 rows with join_cache_level = 3 . maria-5.1 returns 2 rows. Notice that the subquery returns an empty result.

Note that the server reports that NULL < ALL ( SELECT f3 FROM t3 WHERE 0 ) is TRUE

test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( f11 int) ;
INSERT IGNORE INTO t1 VALUES (0),(0);

CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ;
INSERT IGNORE INTO t2 VALUES (0,0),(98,0),(6,0),(5,0),(0,0),(3,0),(1,0),(1,0),(147,0),(3,0),(3,0),(NULL,NULL),(2,0),(1,0),(8,0),(8,0),(8,0),(0,0),(1,0),(8,0),(5,0);

DROP TABLE IF EXISTS t3;
CREATE TABLE t3 ( f3 int) ;
INSERT INTO t3 VALUES (0),(0);

--let $query = SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;

SET SESSION join_cache_level = 0;
--eval CREATE TABLE r1 AS $query ;
SET SESSION join_cache_level = 3;
--eval CREATE TABLE r2 AS $query ;

SELECT COUNT FROM r1 WHERE r IS NULL;
SELECT COUNT FROM r2 WHERE r IS NULL;

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-06-14 ]

Re: Diverging results with join_cache_level=0, join_cache_level=3 in maria-5.3 with ALL() subquery + NULLs
Assigned to Sanja, as the bug seems related to MIN/MAX optimization.
If not, Sanja please reassign to Timour.

Comment by Oleksandr Byelkin [ 2011-07-19 ]

Re: Diverging results with join_cache_level=0, join_cache_level=3 in maria-5.3 with ALL() subquery + NULLs
join_cache_level == 0 return incorrect results (0-8 tested).

Comment by Oleksandr Byelkin [ 2011-07-20 ]

Re: Diverging results with join_cache_level=0, join_cache_level=3 in maria-5.3 with ALL() subquery + NULLs
If reduce number of rows in table t2 and have NULL first in the table wrong result will be independent of join_cache_level:
...
INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0);
...

+SET SESSION join_cache_level = 0;
+SELECT a1.f3 AS r, a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) FROM t2 AS a1 , t1;
+r a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 )
+NULL 1
+NULL 1
+5 1
+5 1
+SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;
+r
+5
+5
+SET SESSION join_cache_level = 3;
+SELECT a1.f3 AS r, a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) FROM t2 AS a1 , t1;
+r a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 )
+NULL 1
+5 1
+NULL 1
+5 1
+SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;
+r
+5
+5

Comment by Oleksandr Byelkin [ 2011-07-20 ]

Re: Diverging results with join_cache_level=0, join_cache_level=3 in maria-5.3 with ALL() subquery + NULLs
The problem is that if left argument is NULL (or could be other) comporison function do not check right argument (subquery) so subquery which was not executed return false on query of empty set, but ALL subquery with empty set should alway return TRUE independently on left part.

Comment by Oleksandr Byelkin [ 2011-07-20 ]

Re: Diverging results with join_cache_level=0, join_cache_level=3 in maria-5.3 with ALL() subquery + NULLs
The bug has no direct connection to join_cache_level and could be repeated on 5.1, here is simplified test suite (returns two 5 instead of two 5 and two NULL):

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( f11 int) ;
INSERT IGNORE INTO t1 VALUES (0),(0);

CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ;
INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0);

DROP TABLE IF EXISTS t3;
CREATE TABLE t3 ( f3 int) ;
INSERT INTO t3 VALUES (0),(0);

SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 780386

Generated at Thu Feb 08 06:48:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.