Bug #118298 "(t1.c4)<-(-1.7976931348623157E308)" and "(t1.c4)<(1.7976931348623157E308)" in where clause return different results

• Submitted: 29 May 2:46
• Modified: 29 May 5:03
• Reporter: SamonBing SamonBing
• Status: Analyzing
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[29 May 2:46] SamonBing SamonBing

Description:
"(t1.c4)<-(-1.7976931348623157E308)" and "(t1.c4)<(1.7976931348623157E308)" in where clause 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)