Description:
Step 1: Creating Tables and Inserting Data
mysql> CREATE TABLE `tb1` (
-> `double_col` double DEFAULT NULL,
-> `bit_col` bit(8) DEFAULT b'0'
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE `tb2` (
-> `double_col` double DEFAULT NULL,
-> `bit_col` bit(8) DEFAULT b'0'
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE `tb3` (
-> `double_col` double DEFAULT NULL,
-> `bit_col` bit(8) DEFAULT b'0'
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into tb1(bit_col) values(0x00),(0xFF),(0xF0),(0xAA),(0x99);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into tb2(bit_col) values(0x00),(0xFF),(0xF0),(0xAA),(0x99);
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into tb3(bit_col) values(0x00),(0xFF),(0xF0),(0xAA),(0x99);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
Step 2: Execute query (8.0.35), abnormal result.
mysql> SELECT B.bit_col, B.double_col, (B.double_col, B.bit_col) <=> (SELECT double_col, bit_col FROM tb3 D WHERE D.bit_col NOT BETWEEN true AND D.double_col OR B.bit_col > D.double_col ORDER BY 1,2 LIMIT 1) FROM tb1 A JOIN tb2 B GROUP BY 1,2 ORDER BY 1,2;
+------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bit_col | double_col | (B.double_col, B.bit_col) <=> (SELECT double_col, bit_col FROM tb3 D WHERE D.bit_col NOT BETWEEN true AND D.double_col OR B.bit_col > D.double_col ORDER BY 1,2 LIMIT 1) |
+------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0x00 | NULL | 0 |
| 0x99 | NULL | 0 |
| 0xAA | NULL | 0 |
| 0xF0 | NULL | 0 |
| 0xFF | NULL | 0 |
+------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)
The third column of the first row should be 1 instead of 0.
Step 3: Execute query (8.0.22), correct result.
mysql> SELECT B.bit_col, B.double_col, (B.double_col, B.bit_col) <=> (SELECT double_col, bit_col FROM tb3 D WHERE D.bit_col NOT BETWEEN true AND D.double_col OR B.bit_col > D.double_col ORDER BY 1,2 LIMIT 1) FROM tb1 A JOIN tb2 B GROUP BY 1,2 HAVING (B.double_col, B.bit_col) <=> (SELECT double_col, bit_col FROM tb3 D WHERE D.bit_col NOT BETWEEN true AND D.double_col OR B.bit_col > D.double_col ORDER BY 1,2 LIMIT 1) ORDER BY 1,2;
+------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bit_col | double_col | (B.double_col, B.bit_col) <=> (SELECT double_col, bit_col FROM tb3 D WHERE D.bit_col NOT BETWEEN true AND D.double_col OR B.bit_col > D.double_col ORDER BY 1,2 LIMIT 1) |
+------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0x00 | NULL | 1 |
| 0x99 | NULL | 0 |
| 0xAA | NULL | 0 |
| 0xF0 | NULL | 0 |
| 0xFF | NULL | 0 |
+------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.22 |
+-----------+
1 row in set (0.00 sec)
How to repeat:
Repeat steps 1 and 2.
Description: Step 1: Creating Tables and Inserting Data mysql> CREATE TABLE `tb1` ( -> `double_col` double DEFAULT NULL, -> `bit_col` bit(8) DEFAULT b'0' -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.03 sec) mysql> CREATE TABLE `tb2` ( -> `double_col` double DEFAULT NULL, -> `bit_col` bit(8) DEFAULT b'0' -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.07 sec) mysql> CREATE TABLE `tb3` ( -> `double_col` double DEFAULT NULL, -> `bit_col` bit(8) DEFAULT b'0' -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.05 sec) mysql> insert into tb1(bit_col) values(0x00),(0xFF),(0xF0),(0xAA),(0x99); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into tb2(bit_col) values(0x00),(0xFF),(0xF0),(0xAA),(0x99); Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into tb3(bit_col) values(0x00),(0xFF),(0xF0),(0xAA),(0x99); Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0 Step 2: Execute query (8.0.35), abnormal result. mysql> SELECT B.bit_col, B.double_col, (B.double_col, B.bit_col) <=> (SELECT double_col, bit_col FROM tb3 D WHERE D.bit_col NOT BETWEEN true AND D.double_col OR B.bit_col > D.double_col ORDER BY 1,2 LIMIT 1) FROM tb1 A JOIN tb2 B GROUP BY 1,2 ORDER BY 1,2; +------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bit_col | double_col | (B.double_col, B.bit_col) <=> (SELECT double_col, bit_col FROM tb3 D WHERE D.bit_col NOT BETWEEN true AND D.double_col OR B.bit_col > D.double_col ORDER BY 1,2 LIMIT 1) | +------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 0x00 | NULL | 0 | | 0x99 | NULL | 0 | | 0xAA | NULL | 0 | | 0xF0 | NULL | 0 | | 0xFF | NULL | 0 | +------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 5 rows in set (0.01 sec) The third column of the first row should be 1 instead of 0. Step 3: Execute query (8.0.22), correct result. mysql> SELECT B.bit_col, B.double_col, (B.double_col, B.bit_col) <=> (SELECT double_col, bit_col FROM tb3 D WHERE D.bit_col NOT BETWEEN true AND D.double_col OR B.bit_col > D.double_col ORDER BY 1,2 LIMIT 1) FROM tb1 A JOIN tb2 B GROUP BY 1,2 HAVING (B.double_col, B.bit_col) <=> (SELECT double_col, bit_col FROM tb3 D WHERE D.bit_col NOT BETWEEN true AND D.double_col OR B.bit_col > D.double_col ORDER BY 1,2 LIMIT 1) ORDER BY 1,2; +------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bit_col | double_col | (B.double_col, B.bit_col) <=> (SELECT double_col, bit_col FROM tb3 D WHERE D.bit_col NOT BETWEEN true AND D.double_col OR B.bit_col > D.double_col ORDER BY 1,2 LIMIT 1) | +------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 0x00 | NULL | 1 | | 0x99 | NULL | 0 | | 0xAA | NULL | 0 | | 0xF0 | NULL | 0 | | 0xFF | NULL | 0 | +------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.22 | +-----------+ 1 row in set (0.00 sec) How to repeat: Repeat steps 1 and 2.