Bug #112111 Incorrect query results on comparing with SOME including some null value

• Submitted: 18 Aug 2023 8:54
• Modified: 18 Aug 2023 12:50
• Reporter: John Jove
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.1.0
• OS: Any
• CPU Architecture: Any

[18 Aug 2023 8:54] John Jove

Description:
Run the following statements, in which the SELECT statement returns an incorrect query result {-1}, {null}, which is expected to be empty.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 DECIMAL) ;
INSERT INTO t1 VALUES(-1);
INSERT INTO t1 VALUES(NULL);
SELECT c1 FROM t1 WHERE ((1 >= SOME (SELECT `c1` FROM t1)) IS TRUE) IS FALSE; -- actual: {-1}, {null}, expected: {}

[18 Aug 2023 12:50] MySQL Verification Team

Hi Mr. Jove,

Thank you for your bug report.

However, we can not repeat your behaviour, since we get an empty result:

With a test case exactly like yours, we get:

---------------
---------------

Empty result.

Here is a test case we used:

------------------

DROP TABLE IF EXISTS t1;

CREATE TABLE t1(c1 DECIMAL) ;

INSERT INTO t1 VALUES(-1);

INSERT INTO t1 VALUES(NULL);

SELECT '---------------';

SELECT c1 FROM t1 WHERE ((1 >= SOME (SELECT `c1` FROM t1)) IS TRUE) IS FALSE;

SELECT '---------------';

DROP TABLE IF EXISTS t1;

------------------

Then we changed your test case, to see what is happening when we break your query into the steps, one by one, like this:

------------------

DROP TABLE IF EXISTS t1;

CREATE TABLE t1(c1 DECIMAL) ;

INSERT INTO t1 VALUES(-1);

INSERT INTO t1 VALUES(NULL);

SELECT '---------------';

SELECT c1 FROM t1 WHERE (1 >= SOME (SELECT `c1` FROM t1));

SELECT '---------------';

SELECT c1 FROM t1 WHERE (1 >= SOME (SELECT `c1` FROM t1)) IS TRUE;

SELECT '---------------';

SELECT c1 FROM t1 WHERE ((1 >= SOME (SELECT `c1` FROM t1)) IS TRUE) IS FALSE;

DROP TABLE IF EXISTS t1;

-------------------------

Then we get these results, where again, the final query returns an empty result:

---------------
c1
-1
NULL
---------------
c1
-1
NULL
---------------
---------------

------------------

Hence, as you can see, all the results are as expected, but the final query returns zero rows.

Can't repeat.