Bug #102655 results from SELECT are different when use WHERE or not

• Submitted: 19 Feb 2021 3:07
• Modified: 2 Dec 2021 11:19
• Reporter: CM M
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0, 8.0.23
• OS: Any
• CPU Architecture: Any

[19 Feb 2021 3:07] CM M

Description:
The results of the following two query statements are different:

SELECT t6.c0 FROM t6 WHERE (NOT ((t6.c0) IN ("", -510885935)));
SELECT t6.c0, (NOT ((t6.c0) IN ("", -510885935))) FROM t6;

Execution process:

mysql> DROP TABLE IF EXISTS t6;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `t6` (
    ->   `c0` float DEFAULT NULL,
    ->   UNIQUE KEY `i0` (`c0`)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t6(c0) VALUES (-510885935);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t6(c0) VALUES (-510889000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t6(c0) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t6(c0) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT t6.c0 FROM t6 WHERE (NOT ((t6.c0) IN ("", -510885935)));
+------------+
| c0         |
+------------+
| -510889000 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT t6.c0, (NOT ((t6.c0) IN ("", -510885935))) FROM t6;
+------------+-------------------------------------+
| c0         | (NOT ((t6.c0) IN ("", -510885935))) |
+------------+-------------------------------------+
|       NULL |                                NULL |
|       NULL |                                NULL |
| -510889000 |                                   1 |
| -510886000 |                                   1 |
+------------+-------------------------------------+
4 rows in set (0.00 sec)

The second query statement has two true results:
| -510889000 |                                   1 |
| -510886000 |                                   1 |

However, the first query statement has only one result:
| -510889000 |

How to repeat:
Run the following statements to obtain different results:

DROP TABLE IF EXISTS t6;
CREATE TABLE `t6` (
  `c0` float DEFAULT NULL,
  UNIQUE KEY `i0` (`c0`)
);
INSERT INTO t6(c0) VALUES (-510885935);
INSERT INTO t6(c0) VALUES (-510889000);
INSERT INTO t6(c0) VALUES (NULL);
INSERT INTO t6(c0) VALUES (NULL);

SELECT t6.c0 FROM t6 WHERE (NOT ((t6.c0) IN ("", -510885935)));
SELECT t6.c0, (NOT ((t6.c0) IN ("", -510885935))) FROM t6;

However, the results are the same when there is only one NULL value.

mysql> DROP TABLE IF EXISTS t6;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `t6` (
    ->   `c0` float DEFAULT NULL,
    ->   UNIQUE KEY `i0` (`c0`)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t6(c0) VALUES (-510885935);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t6(c0) VALUES (-510889000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t6(c0) VALUES (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT t6.c0 FROM t6 WHERE (NOT ((t6.c0) IN ("", -510885935)));
+------------+
| c0         |
+------------+
| -510889000 |
| -510886000 |
+------------+
2 rows in set (0.00 sec)

mysql> SELECT t6.c0, (NOT ((t6.c0) IN ("", -510885935))) FROM t6;
+------------+-------------------------------------+
| c0         | (NOT ((t6.c0) IN ("", -510885935))) |
+------------+-------------------------------------+
|       NULL |                                NULL |
| -510889000 |                                   1 |
| -510886000 |                                   1 |
+------------+-------------------------------------+
3 rows in set (0.00 sec)

[19 Feb 2021 6:32] MySQL Verification Team

Hello!

Thank you for the report and test case.

regards,
Umesh

[2 Dec 2021 11:19] Erlend Dahl

Duplicate of

Bug#100800 wrong result when select int column with range

fixed in 8.0.27.