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

Submitted: 5 Nov 2025 1:49 Modified: 5 Nov 2025 5:00
Reporter: david silva Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.41 OS:Any
Assigned to: CPU Architecture:Any
Tags: Query error histogram

[5 Nov 2025 1:49] david silva
Description:
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.
[5 Nov 2025 5:00] Chaithra Marsur Gopala Reddy
Hi David silva,

Thank you for the test case. Verified as described.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.061 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.