[MDEV-31951] Inconsistent query results Created: 2023-08-18  Updated: 2023-09-05

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 11.0.3
Fix Version/s: 11.0, 11.1, 11.2

Type: Bug Priority: Major
Reporter: John Jove Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 22.04LTS


 Description   

The following two cases return different results for the same query.
I construct case 2 by adding a not null constraint on case 1, which is expected to not change the query result.

Case 1:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (c0 double, c2 tinyint(1)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (-1, null);
 
SELECT 1 HAVING ((1, NULL) NOT IN (SELECT `c2`, `c0` FROM t1)); -- {}

Case 2:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (c0 double not null , c2 tinyint(1)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (-1, null);
 
SELECT 1 HAVING ((1, NULL) NOT IN (SELECT `c2`, `c0` FROM t1)); -- {1}


Generated at Thu Feb 08 10:27:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.