function BIT_AND: got an error in TiDB, but the error description is more detailed in MySQL 8.0, and no error is reported in MySQL 5.7.

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do? If possible, provide a recipe for reproducing the error.

CREATE TABLE `table20_int_autoinc` (
  `col_char_20_key` char(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col_decimal_10_4_unsigned` decimal(10,4) unsigned DEFAULT NULL,
  `col_double_signed` double DEFAULT NULL,
  `col_float_unsigned` float unsigned DEFAULT NULL,
  `col_bigint_unsigned_key` bigint(20) unsigned DEFAULT NULL,
  `col_double_unsigned_key` double unsigned DEFAULT NULL,
  `col_double_unsigned` double unsigned DEFAULT NULL,
  `col_bigint_unsigned` bigint(20) unsigned DEFAULT NULL,
  `col_bigint_signed` bigint(20) DEFAULT NULL,
  `col_varchar_20` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col_float_signed` float DEFAULT NULL,
  `col_decimal_10_4_unsigned_key` decimal(10,4) unsigned DEFAULT NULL,
  `col_bigint_signed_key` bigint(20) DEFAULT NULL,
  `col_decimal_40_20_unsigned` decimal(40,20) unsigned DEFAULT NULL,
  `col_decimal_10_4_signed_key` decimal(10,4) DEFAULT NULL,
  `col_decimal_6_3_unsigned_key` decimal(6,3) unsigned DEFAULT NULL,
  `col_decimal_10_4_signed` decimal(10,4) DEFAULT NULL,
  `col_float_unsigned_key` float unsigned DEFAULT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_varchar_20_key` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col_decimal_40_20_unsigned_key` decimal(40,20) unsigned DEFAULT NULL,
  `col_decimal_40_20_signed` decimal(40,20) DEFAULT NULL,
  `col_double_signed_key` double DEFAULT NULL,
  `col_float_signed_key` float DEFAULT NULL,
  `col_decimal_6_3_signed_key` decimal(6,3) DEFAULT NULL,
  `col_decimal_6_3_signed` decimal(6,3) DEFAULT NULL,
  `col_decimal_6_3_unsigned` decimal(6,3) unsigned DEFAULT NULL,
  `col_char_20` char(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col_decimal_40_20_signed_key` decimal(40,20) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_char_20_key` (`col_char_20_key`),
  KEY `col_bigint_unsigned_key` (`col_bigint_unsigned_key`),
  KEY `col_double_unsigned_key` (`col_double_unsigned_key`),
  KEY `col_decimal_10_4_unsigned_key` (`col_decimal_10_4_unsigned_key`),
  KEY `col_bigint_signed_key` (`col_bigint_signed_key`),
  KEY `col_decimal_10_4_signed_key` (`col_decimal_10_4_signed_key`),
  KEY `col_decimal_6_3_unsigned_key` (`col_decimal_6_3_unsigned_key`),
  KEY `col_float_unsigned_key` (`col_float_unsigned_key`),
  KEY `col_varchar_20_key` (`col_varchar_20_key`),
  KEY `col_decimal_40_20_unsigned_key` (`col_decimal_40_20_unsigned_key`),
  KEY `col_double_signed_key` (`col_double_signed_key`),
  KEY `col_float_signed_key` (`col_float_signed_key`),
  KEY `col_decimal_6_3_signed_key` (`col_decimal_6_3_signed_key`),
  KEY `col_decimal_40_20_signed_key` (`col_decimal_40_20_signed_key`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `table20_int_autoinc` VALUES ('lrzybvqnqnmfvv',0.0000,-9.1823,0,11400,1.009,0,0,0,'y',-111.121,12.9873,1,12.99100000000000000000,-111.1212,999.999,NULL,0,1,'her',0.00000000000000000000,0.00000001000000000000,-0.999999999,12.991,1.000,999.999,0.000,'v',-0.99999999900000000000),(NULL,0.0000,-59,1,1,0,0.999999999,82,1,NULL,86,NULL,NULL,0.99999999900000000000,-0.9871,1.000,0.1123,0,2,NULL,0.00000000000000000000,NULL,0,12.991,-999.999,-111.121,0.000,NULL,1.00900000000000000000),('i',1.0000,12.991,0.112345,0,0,0.00000001,13,1,'why',-1,73.0000,1,0.00000000000000000000,0.0000,12.987,0.1123,0,3,'no',0.00000000000000000000,0.11234500000000000000,-0.987103,0,12.991,-1.000,0.000,NULL,12.99100000000000000000),('rlrzybvqnqn',0.0000,0.999999999,12.991,1,0.112345,0,0,1,'o',12.991,0.0000,1,0.00000000000000000000,0.0000,0.000,1.0988,0.112345,4,NULL,12.98731000000000000000,0.11234500000000000000,-39,1.09878,-1.000,-1.000,999.999,'c',0.99999999900000000000),('c',0.0000,12.991,0.112345,13,0,0,0,-1,'yeah',12.9873,0.1123,-111,12.98731000000000000000,-9.1823,0.000,-0.9871,12.991,5,NULL,1.00900000000000000000,-9.18230000000000000000,1.009,12.991,0.000,12.987,0.000,'a',1.09878100000000000000),('yrlrzybvqnqn',0.0000,1.098781,0.112345,0,0.112345,12.991,0,1,NULL,12.9873,0.1123,0,0.00000000000000000000,-15133.0000,1.000,NULL,1.09878,6,'oyrlrzybvqnq',1.09878100000000000000,-18.00000000000000000000,-0.987103,-0.000000001,-111.121,-9.182,0.000,'mo',28281.00000000000000000000),('it',0.0000,12.98731,12.9873,1,12.991,0,13,1,'g',1.009,3356.0000,1,5.00000000000000000000,0.0000,12.987,-1.0000,12.9873,7,'w',12.99100000000000000000,0.11234500000000000000,4896,-9.1823,-0.987,0.000,0.000,'m',-111.12120000000000000000),('d',1.0090,1.098781,0.00000001,0,0,12.991,0,-111,'be',1.009,12.9873,-1,12.98731000000000000000,-111.1212,0.000,1.0988,0.00000001,8,NULL,0.00000001000000000000,-9.18230000000000000000,0.112345,0.112345,1.000,-1.000,0.000,'to',12.99100000000000000000),('were',1.0000,NULL,1.09878,33,1.098781,0,13,-1,'bmoyrlrz',0.112345,0.0000,0,0.99999999900000000000,1.0000,1.099,30.0000,12.991,9,'okay',0.00000000000000000000,12.99100000000000000000,12.98731,1,12.991,NULL,1.000,'p',-0.99999999900000000000),('sbmoyrlrzybv',0.0000,12.98731,0,1,27445,1.009,1,1,NULL,1.009,0.0000,-111,12.99100000000000000000,1.0988,0.112,12.9910,12.9873,10,'tsbmoyrlrz',0.00000000000000000000,-9.18230000000000000000,-0.000000001,1.009,12.987,0.112,1.099,'g',12.98731000000000000000),('his',12.9873,0.112345,NULL,NULL,25845,0.00000001,NULL,0,NULL,-1,12.9873,-1,0.00000001000000000000,12.9873,0.000,-9.1823,0,11,NULL,0.00000000000000000000,2.00000000000000000000,0.112345,0.00000001,-0.987,1.000,0.000,'we',12.99100000000000000000),(NULL,12.9873,101,0,0,1.098781,NULL,1,-111,'rtsbmoyrlrzybv',12.991,1.0000,-1,25.00000000000000000000,1.0988,1.099,-108.0000,12.9873,12,NULL,0.00000000000000000000,0.99999999900000000000,12.991,0.00000001,-1.000,-1.000,0.000,NULL,-0.98710300000000000000),('a',1.0000,-77,1,0,12.98731,0,0,13,'a',1,0.0000,NULL,53.00000000000000000000,10829.0000,12.987,1.0988,1.09878,13,'y',0.00000000000000000000,1.09878100000000000000,12.991,1,1.000,1.000,1.000,'he',1.00900000000000000000),(NULL,1.0090,0.999999999,0,0,0,0.999999999,0,1,'f',0.00000001,0.0000,1,12.99100000000000000000,-0.9871,0.000,12.9910,47,14,'go',0.11234500000000000000,-0.00000000100000000000,-0.000000001,0.112345,-999.999,12.991,1.000,'been',12.98731000000000000000),(NULL,0.0000,0.999999999,0.112345,0,12.991,0,1,13,'but',19645,0.0000,13,0.00000000000000000000,1.0090,0.000,1.0988,0,15,'p',0.11234500000000000000,0.99999999900000000000,NULL,-0.000000001,0.000,0.112,12.987,NULL,NULL),(NULL,0.0000,-15447,1.009,0,0.999999999,1.098781,0,0,NULL,-9.1823,18845.0000,0,12.98731000000000000000,81.0000,0.000,1.0988,12.9873,16,'not',12.98731000000000000000,1349.00000000000000000000,-111.1212,1.009,-1.000,-9.182,0.000,'w',0.00000001000000000000),(NULL,0.0000,12.991,0.00000001,0,123,12.991,0,13,'x',12.9873,1.0090,0,1.09878100000000000000,-111.1212,0.000,12.9873,0,17,'srtsb',0.99999999900000000000,0.00000000000000000000,0.00000001,-9.1823,-82.000,12.987,12.987,NULL,-111.12120000000000000000),('her',1.0988,-111.1212,0,0,0,NULL,0,NULL,'p',NULL,12.9910,0,0.00000000000000000000,1.0988,1.099,-13336.0000,0,18,NULL,0.00000000000000000000,0.11234500000000000000,-0.000000001,0.112345,1.099,-1.000,0.000,NULL,-1570.00000000000000000000),('is',0.0000,-65,0,0,0.999999999,1.098781,0,1,'she',7,1.0000,1,0.00000000000000000000,1.0988,1.099,69.0000,0,19,'m',0.00000001000000000000,12.98731000000000000000,0.112345,0,0.000,0.000,NULL,'will',1.09878100000000000000),(NULL,8523.0000,0.112345,0,0,NULL,0,13,0,'up',1.009,0.0000,21811,1.00900000000000000000,12.9873,1.009,0.0000,0,20,'q',0.00000000000000000000,0.00000001000000000000,1.098781,12.991,1.099,0.112,1.009,'asrts',-20774.00000000000000000000);

2. What did you expect to see?

In MySQL5.7

mysql> SELECT BIT_AND( ( POW( `col_float_unsigned_key`, `col_decimal_40_20_unsigned` ) ) ) AS field1  FROM `table20_int_autoinc` ;
+--------+
| field1 |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

In MySQL8.0

mysql> SELECT BIT_AND( ( POW( `col_float_unsigned_key`, `col_decimal_40_20_unsigned` ) ) ) AS field1  FROM `table20_int_autoinc` ;
ERROR 1690 (22003): BIGINT value is out of range in 'pow(`randgen_test`.`table20_int_autoinc`.`col_float_unsigned_key`,`randgen_test`.`table20_int_autoinc`.`col_decimal_40_20_unsigned`)'

3. What did you see instead?

In TiDB,

mysql> SELECT BIT_AND( ( POW( `col_float_unsigned_key`, `col_decimal_40_20_unsigned` ) ) ) AS field1  FROM `table20_int_autoinc` ;
ERROR 1690 (22003): constant 6.88621404964935e+27 overflows double

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)? 8c20289c7

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @qw4990