| Bug #118834 | Change a number in the query statement to -(-NUMBER), the query return different results. | ||
|---|---|---|---|
| Submitted: | 14 Aug 2:54 | Modified: | 19 Aug 0:16 |
| Reporter: | Alice Alice | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.41 | OS: | Any |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[19 Aug 0:16]
MySQL Verification Team
Hi,
please upgrade
mysql> CREATE TABLE `t1` (
-> `c0` decimal(10,0) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf',
-> `c1` varchar(500) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf',
-> `c2` mediumint /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf',
-> `c3` float /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf',
-> `c4` float /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf',
-> UNIQUE KEY `c0` (`c0`),
-> UNIQUE KEY `c2` (`c2`),
-> UNIQUE KEY `c4` (`c4`),
-> KEY `i1` (`c2` DESC,`c4`,`c0`,`c3` DESC,`c1`(4)) USING BTREE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.012 sec)
mysql>
mysql> INSERT INTO `t1` VALUES (NULL,'+hk\r',NULL,0.0190223,18783700),(NULL,'-119044867',NULL,0.0341699,366882000);
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> explain format=tree SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE (t1.c4)<-(-1.7976931348623157E308);
+-----------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t1.c4 < <cache>(-(-(1.7976931348623157E308)))) (cost=0.45 rows=1)
-> Table scan on t1 (cost=0.45 rows=2)
|
+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)
mysql> explain format=tree SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE (t1.c4)<(1.7976931348623157E308);
+---------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------+
| -> Index range scan on t1 using c4 over (NULL < c4), with index condition: (t1.c4 is not null) (cost=1.16 rows=2)
|
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
mysql> select -(-1.7976931348623157E308);
+----------------------------+
| -(-1.7976931348623157E308) |
+----------------------------+
| 1.7976931348623157e308 |
+----------------------------+
1 row in set (0.000 sec)
mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE (t1.c4)<-(-1.7976931348623157E308);
+------+------------+------+-----------+-----------+
| ref0 | ref1 | ref2 | ref3 | ref4 |
+------+------------+------+-----------+-----------+
| NULL | 0.0190223 | 18783700 |
| NULL | -119044867 | NULL | 0.0341699 | 366882000 |
+------+------------+------+-----------+-----------+
2 rows in set (0.001 sec)
mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE (t1.c4)<(1.7976931348623157E308);
+------+------------+------+-----------+-----------+
| ref0 | ref1 | ref2 | ref3 | ref4 |
+------+------------+------+-----------+-----------+
| NULL | 0.0190223 | 18783700 |
| NULL | -119044867 | NULL | 0.0341699 | 366882000 |
+------+------------+------+-----------+-----------+
2 rows in set (0.000 sec)
mysql>
Description: Change a number in the query statement to -(-NUMBER), the query return different results. mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE (t1.c4)<-(-1.7976931348623157E308); Empty set (0.00 sec) mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE (t1.c4)<(1.7976931348623157E308); +------+------------+------+-----------+-----------+ | ref0 | ref1 | ref2 | ref3 | ref4 | +------+------------+------+-----------+-----------+ | NULL | 0.0190223 | 18783700 | | NULL | -119044867 | NULL | 0.0341699 | 366882000 | +------+------------+------+-----------+-----------+ 2 rows in set (0.00 sec) How to repeat: CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tlp2025052847`; USE `tlp2025052847`; CREATE TABLE `t1` ( `c0` decimal(10,0) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf', `c1` varchar(500) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf', `c2` mediumint /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf', `c3` float /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf', `c4` float /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf', UNIQUE KEY `c0` (`c0`), UNIQUE KEY `c2` (`c2`), UNIQUE KEY `c4` (`c4`), KEY `i1` (`c2` DESC,`c4`,`c0`,`c3` DESC,`c1`(4)) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `t1` VALUES (NULL,'+h k\r',NULL,0.0190223,18783700),(NULL,'-119044867',NULL,0.0341699,366882000); mysql> explain format=tree SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE (t1.c4)<-(-1.7976931348623157E308); +--------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------+ | -> Zero rows (no matching row in const table) (cost=0..0 rows=0) | +--------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> explain format=tree SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE (t1.c4)<(1.7976931348623157E308); +---------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------+ | -> Index range scan on t1 using c4 over (NULL < c4), with index condition: (t1.c4 is not null) (cost=1.16 rows=2) | +---------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select -(-1.7976931348623157E308); +----------------------------+ | -(-1.7976931348623157E308) | +----------------------------+ | 1.7976931348623157e308 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE (t1.c4)<-(-1.7976931348623157E308); Empty set (0.00 sec) mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3, t1.c4 AS ref4 FROM t1 WHERE (t1.c4)<(1.7976931348623157E308); +------+------------+------+-----------+-----------+ | ref0 | ref1 | ref2 | ref3 | ref4 | +------+------------+------+-----------+-----------+ | NULL | 0.0190223 | 18783700 | | NULL | -119044867 | NULL | 0.0341699 | 366882000 | +------+------------+------+-----------+-----------+ 2 rows in set (0.00 sec)