Bug #119122 The query is normal without using the histogram, but an error occurs when using the histogram

• Submitted: 9 Oct 11:53
• Reporter: MIAO CHEN
• Status: Open
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any
• Tags: Query error

[9 Oct 11:53] MIAO CHEN

Description:
mysql> SELECT tinyint_col FROM t1  WHERE tinyint_col NOT IN (     SELECT tinyint_col      FROM t2      WHERE bit_col IN (-1.17549e-38, -3.40282e38) ) limit 1;
+-------------+
| tinyint_col |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

mysql> 
mysql> analyze table t2 update histogram on bit_col;
+---------+-----------+----------+----------------------------------------------------+
| Table   | Op        | Msg_type | Msg_text                                           |
+---------+-----------+----------+----------------------------------------------------+
| test.t2 | histogram | status   | Histogram statistics created for column 'bit_col'. |
+---------+-----------+----------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> analyze table t2 update histogram on tinyint_col;
+---------+-----------+----------+--------------------------------------------------------+
| Table   | Op        | Msg_type | Msg_text                                               |
+---------+-----------+----------+--------------------------------------------------------+
| test.t2 | histogram | status   | Histogram statistics created for column 'tinyint_col'. |
+---------+-----------+----------+--------------------------------------------------------+
1 row in set (0.01 sec)

mysql> 
mysql> SELECT tinyint_col FROM t1  WHERE tinyint_col NOT IN (     SELECT tinyint_col      FROM t2      WHERE bit_col IN (-1.17549e-38, -3.40282e38) ) limit 1;
ERROR 1690 (22003): BIGINT value is out of range in '-(3.40282e38)'

How to repeat:
set sql_mode='';
drop table  if EXISTS t1;
CREATE TABLE `t1` (
  `id_col` int NOT NULL AUTO_INCREMENT,
  `tinyint_col` tinyint DEFAULT NULL,
  `bit_col` bit(8) DEFAULT b'0',
  UNIQUE KEY `id_col` (`id_col` DESC),
   KEY `ndx_bit_col` (`bit_col` DESC)
) ENGINE=InnoDB AUTO_INCREMENT=114 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
drop table  if EXISTS t2;
CREATE TABLE `t2` (
  `id_col` int NOT NULL AUTO_INCREMENT,
  `tinyint_col` tinyint DEFAULT NULL,
  `bit_col` bit(8) DEFAULT b'0',
   PRIMARY KEY (`id_col` DESC),
   KEY `ndx_tinyint_col` (`tinyint_col` DESC),
   KEY `ndx_bit_col` (`bit_col` DESC)
) ENGINE=InnoDB AUTO_INCREMENT=114 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t2` VALUES (113,NULL,_binary '<99>'),(112,NULL,_binary 'ª'),(111,NULL,_binary '\ð');
INSERT INTO `t1` VALUES (113,NULL,_binary '<99>'),(112,NULL,_binary 'ª'),(111,NULL,_binary '\ð');

SELECT tinyint_col FROM t1  WHERE tinyint_col NOT IN (     SELECT tinyint_col      FROM t2      WHERE bit_col IN (-1.17549e-38, -3.40282e38) ) limit 1;

analyze table t2 update histogram on bit_col;
analyze table t2 update histogram on tinyint_col;

SELECT tinyint_col FROM t1  WHERE tinyint_col NOT IN (     SELECT tinyint_col      FROM t2      WHERE bit_col IN (-1.17549e-38, -3.40282e38) ) limit 1;

Suggested fix:
The query results using histograms are consistent with those without histograms.