Bug #111429 Incorrect result when timestamp col apply <=> to the result of subquery

• Submitted: 15 Jun 2023 6:44
• Modified: 15 Jun 2023 7:00
• Reporter: Will Chen
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.22, 8.0.33
• OS: Any
• CPU Architecture: Any

[15 Jun 2023 6:44] Will Chen

Description:
as shown below, the first and second SQL statement should return same result, because the subquery of the second statement(which is the third one) is equals to the value set in first SQL statement.

mysql> SELECT * FROM bugtest;
+--------+----------------------------+--------------------------+
| id_col | timestamp_col              | binary_col               |
+--------+----------------------------+--------------------------+
|      1 | 1995-03-04 12:20:00.000000 | 0x6161610000000000000000 |
|      2 | 1996-03-04 12:20:00.000000 | 0x6262620000000000000000 |
|      3 | 1997-03-04 12:20:00.000000 | 0x                       |
|      4 | 1998-03-04 12:20:00.000000 | 0x                       |
|      5 | 1999-03-04 12:20:00.000000 | 0x                       |
+--------+----------------------------+--------------------------+
5 rows in set (0.00 sec)

mysql> SELECT binary_col,timestamp_col FROM bugtest WHERE (binary_col,timestamp_col) <=> (NULL,'1997-03-04 12:20:00.000000') ORDER BY 1 limit 10;
+------------------------+----------------------------+
| binary_col             | timestamp_col              |
+------------------------+----------------------------+
| 0x                     | 1997-03-04 12:20:00.000000 |
+------------------------+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT binary_col,timestamp_col FROM bugtest WHERE (binary_col,timestamp_col) <=> (SELECT binary_col,timestamp_col FROM bugtest ORDER BY 1,2 LIMIT 1) ORDER BY 1 limit 10;
+------------------------+----------------------------+
| binary_col             | timestamp_col              |
+------------------------+----------------------------+
| 0x                     | 1997-03-04 12:20:00.000000 |
| 0x                     | 1998-03-04 12:20:00.000000 |
| 0x                     | 1999-03-04 12:20:00.000000 |
+------------------------+----------------------------+
3 rows in set (0.00 sec)

mysql> SELECT binary_col,timestamp_col FROM bugtest ORDER BY 1,2 LIMIT 1;
+------------------------+----------------------------+
| binary_col             | timestamp_col              |
+------------------------+----------------------------+
| 0x                     | 1997-03-04 12:20:00.000000 |
+------------------------+----------------------------+
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE bugtest (
  `id_col` int NOT NULL AUTO_INCREMENT,
  `timestamp_col` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
   `binary_col` binary(11) DEFAULT NULL,
   UNIQUE KEY (`id_col`),
   KEY `ndx_timestamp_col` (`timestamp_col`),
   KEY `ndx_binary_col` (`binary_col`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO bugtest VALUES (1,'1995-03-04 12:20:00.000000',_binary 'aaa'), (2,'1996-03-04 12:20:00.000000',_binary 'bbb'), (3,'1997-03-04 12:20:00.000000',NULL), (4,'1998-03-04 12:20:00.000000',NULL), ((5,'1999-03-04 12:20:00.000000',NULL);

SELECT binary_col,timestamp_col FROM bugtest WHERE (binary_col,timestamp_col) <=> (NULL,'1997-03-04 12:20:00.000000') ORDER BY 1 limit 10;

SELECT binary_col,timestamp_col FROM bugtest WHERE (binary_col,timestamp_col) <=> (SELECT binary_col,timestamp_col FROM bugtest ORDER BY 1,2 LIMIT 1) ORDER BY 1 limit 10;

SELECT binary_col,timestamp_col FROM bugtest ORDER BY 1,2 LIMIT 1;

Suggested fix:
maybe the first and second statement should return same result?

[15 Jun 2023 7:00] MySQL Verification Team

Hello Will Chen,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

[21 Jun 2023 14:02] huahua xu

Hi all:

The issue is caused by the commit https://github.com/mysql/mysql-server/commit/dc80b26d9097bea487e58efa90a1a859ef99f2fc which sets abort_on_null to true about `Item_bool_func2`.

Above case would be fine in previous versions(~8.0.16).

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.4-rc-debug-log |
+--------------------+

mysql> SELECT binary_col,timestamp_col FROM bugtest;
+-------------+----------------------------+
| binary_col  | timestamp_col              |
+-------------+----------------------------+
| aaa         | 1995-03-04 12:20:00.000000 |
| bbb         | 1996-03-04 12:20:00.000000 |
| NULL        | 1997-03-04 12:20:00.000000 |
| NULL        | 1998-03-04 12:20:00.000000 |
| NULL        | 1999-03-04 12:20:00.000000 |
+-------------+----------------------------+

mysql> SELECT binary_col,timestamp_col FROM bugtest WHERE (binary_col,timestamp_col) <=> (NULL,'1997-03-04 12:20:00.000000') ORDER BY 1 limit 10;
+------------+----------------------------+
| binary_col | timestamp_col              |
+------------+----------------------------+
| NULL       | 1997-03-04 12:20:00.000000 |
+------------+----------------------------+

mysql> SELECT binary_col,timestamp_col FROM bugtest WHERE (binary_col,timestamp_col) <=> (SELECT binary_col,timestamp_col FROM bugtest ORDER BY 1,2 LIMIT 1) ORDER BY 1 limit 10;
+------------+----------------------------+
| binary_col | timestamp_col              |
+------------+----------------------------+
| NULL       | 1997-03-04 12:20:00.000000 |
+------------+----------------------------+

mysql> SELECT binary_col,timestamp_col FROM bugtest ORDER BY 1,2 LIMIT 1;
+------------+----------------------------+
| binary_col | timestamp_col              |
+------------+----------------------------+
| NULL       | 1997-03-04 12:20:00.000000 |
+------------+----------------------------+