[MDEV-31952] Incorrect query result Created: 2023-08-18  Updated: 2023-09-05  Resolved: 2023-09-05

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 11.0.3
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: John Jove Assignee: Sergei Golubchik
Resolution: Cannot Reproduce Votes: 0
Labels: None
Environment:

Ubuntu 22.04LTS


 Description   

In the following case, the SELECT statement returns an incorrect result {-1}, which is expected to be empty.

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (c0 double unsigned, c1 bigint,UNIQUE KEY c0 (c0));
 
INSERT INTO t1 VALUES (NULL, -1);
 
SELECT c1 FROM t1 WHERE c1 NOT IN (SELECT `c0` FROM t1); -- actual: {-1}, expected: {}



 Comments   
Comment by Sergei Golubchik [ 2023-09-05 ]

I get an empty result set, as expected

CREATE TABLE t1 (c0 double unsigned, c1 bigint,UNIQUE KEY c0 (c0));
 
INSERT INTO t1 VALUES (NULL, -1);
 
SELECT c1 FROM t1 WHERE c1 NOT IN (SELECT `c0` FROM t1);
 
c1

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