MySQL Bugs: #113423: Unexpected result appeared

Bug #113423	Unexpected result appeared
Submitted:	14 Dec 2023 13:19	Modified:	14 Dec 2023 14:36
Reporter:	ru tu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Unexpected values appeared in select query statements that should logically imply each other

result1:
+---------------------+------+----------------------+
| f1                  | f2   | f3                   |
+---------------------+------+----------------------+
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
| 2003-11-21 13:16:10 |    0 | 18446723893189315655 |
+---------------------+------+----------------------+

result2:
+---------------------+------+----------------------+
| f1                  | f2   | f3                   |
+---------------------+------+----------------------+
| 2003-11-21 13:16:10 |    0 | 18446744073709549597 |
| 2003-11-21 13:16:10 |    0 | 18446744073709549597 |
| 2003-11-21 13:16:10 |    0 | 18446744073709549597 |
+---------------------+------+----------------------+

How to repeat:

create table table_3_utf8_undef (
`pk` int primary key,
`col_bigint_undef_signed` bigint  ,
`col_bigint_undef_unsigned` bigint unsigned ,
`col_bigint_key_signed` bigint  ,
`col_bigint_key_unsigned` bigint unsigned ,
`col_float_undef_signed` float  ,
`col_float_undef_unsigned` float unsigned ,
`col_float_key_signed` float  ,
`col_float_key_unsigned` float unsigned ,
`col_double_undef_signed` double  ,
`col_double_undef_unsigned` double unsigned ,
`col_double_key_signed` double  ,
`col_double_key_unsigned` double unsigned ,
`col_decimal(40, 20)_undef_signed` decimal(40, 20)  ,
`col_decimal(40, 20)_undef_unsigned` decimal(40, 20) unsigned ,
`col_decimal(40, 20)_key_signed` decimal(40, 20)  ,
`col_decimal(40, 20)_key_unsigned` decimal(40, 20) unsigned ,
`col_char(20)_undef_signed` char(20)  ,
`col_char(20)_key_signed` char(20)  ,
`col_varchar(20)_undef_signed` varchar(20)  ,
`col_varchar(20)_key_signed` varchar(20)  ,
key (`col_bigint_key_signed`),
key (`col_bigint_key_unsigned`),
key (`col_float_key_signed`),
key (`col_float_key_unsigned`),
key (`col_double_key_signed`),
key (`col_double_key_unsigned`),
key (`col_decimal(40, 20)_key_signed`),
key (`col_decimal(40, 20)_key_unsigned`),
key (`col_char(20)_key_signed`),
key (`col_varchar(20)_key_signed`)
) character set utf8 ;
insert into table_3_utf8_undef values (0,82.1847,1,39.0425,38.1089,-1,1,94.1106,1.009,12.991,19755,-13064,0,1,79.1429,-2,1,"well",'3
','-0','e'),(1,1,20.0078,-9.183,68.1957,1,2,1,0.0001,12.991,2,71.0510,1,-1,2,12.991,12.991,'3	','1','3	','-0'),(2,-2,1,-21247,1.009,2,1.009,0.0001,36.0002,-2,2,-0,0.0001,-2,0.1598,47.1515,1.009,'3	','w','-1','e');
create table table_7_utf8_undef (
`pk` int primary key,
`col_bigint_undef_signed` bigint  ,
`col_bigint_undef_unsigned` bigint unsigned ,
`col_bigint_key_signed` bigint  ,
`col_bigint_key_unsigned` bigint unsigned ,
`col_float_undef_signed` float  ,
`col_float_undef_unsigned` float unsigned ,
`col_float_key_signed` float  ,
`col_float_key_unsigned` float unsigned ,
`col_double_undef_signed` double  ,
`col_double_undef_unsigned` double unsigned ,
`col_double_key_signed` double  ,
`col_double_key_unsigned` double unsigned ,
`col_decimal(40, 20)_undef_signed` decimal(40, 20)  ,
`col_decimal(40, 20)_undef_unsigned` decimal(40, 20) unsigned ,
`col_decimal(40, 20)_key_signed` decimal(40, 20)  ,
`col_decimal(40, 20)_key_unsigned` decimal(40, 20) unsigned ,
`col_char(20)_undef_signed` char(20)  ,
`col_char(20)_key_signed` char(20)  ,
`col_varchar(20)_undef_signed` varchar(20)  ,
`col_varchar(20)_key_signed` varchar(20)  ,
key (`col_bigint_key_signed`),
key (`col_bigint_key_unsigned`),
key (`col_float_key_signed`),
key (`col_float_key_unsigned`),
key (`col_double_key_signed`),
key (`col_double_key_unsigned`),
key (`col_decimal(40, 20)_key_signed`),
key (`col_decimal(40, 20)_key_unsigned`),
key (`col_char(20)_key_signed`),
key (`col_varchar(20)_key_signed`)
) character set utf8 ;
insert into table_7_utf8_undef values (0,-9.183,1,1.1384,2,15.1271,12.991,-2,0.0001,36.1270,79.1819,0.0001,0.0001,3.1387,52.0818,-0,0.0001,'1','3	','0','0'),(1,79,12.991,107,2,-0.0001,0,1.009,1.009,34,1,-1,69.0208,1,2,120,12.991,'3	','-1',"if",'b'),(2,-2,1,-9.183,1,12.991,0.0001,53,12.991,1.009,12.991,12.991,0.0001,-0.0001,12.991,0.0001,2,'3
','p','0','3	'),(3,-0.0001,12.991,1.009,1.009,-9.183,2,0,1,-2,1,2,1,2,1.009,2,12.991,'3
','0','k','0'),(4,1.009,0.0001,-1,12.991,2,47,2,0,12.991,12.991,1.009,0,1.009,1.009,-0.0001,6949,'-1','	3','1','m'),(5,-0,1,0,0,0.0001,28.1237,12.991,0,12.991,12.991,-0,12.991,2,2,2,1.009,'0','	3','0','	3'),(6,45.0855,1,38.1166,1,1.009,80.0284,2,122,0.0001,0,-1,11130,0,1,1,0,"know",'-0','
3','3
');

WITH `MYWITH` AS (SELECT (ADDTIME(_UTF8MB4'2003-11-20 22:01:52', _UTF8MB4'15:14:18')) AS `f1`,(`f6`) AS `f2`,(~`f4`) AS `f3` FROM (SELECT (_UTF8MB4'2015-11-09'*`f11`) AS `f7`,
(CONCAT_WS(`f11`, `f9`, `f11`)*ATAN(8)) AS `f5`,(~`f10`<<`f9`) AS `f8` FROM (SELECT `col_char(20)_key_signed` AS `f9`,`col_varchar(20)_key_signed` AS `f10`,
`col_bigint_key_signed` AS `f11` FROM `table_3_utf8_undef`) AS `t1` ORDER BY `f11`) AS `t2` NATURAL JOIN (
    SELECT (SUBDATE(_UTF8MB4'2018-05-21', INTERVAL 1 DAY_MICROSECOND)) AS `f4`,(SIN(7) DIV BINARY RTRIM(8)) AS `f12`,(-`f14`<<_UTF8MB4'2017-10-10') AS `f6` 
    FROM (SELECT `col_double_undef_signed` AS `f13`,`col_decimal(40, 20)_key_signed` AS `f14`,`col_decimal(40, 20)_undef_signed` AS `f15` FROM `table_7_utf8_undef` 
    IGNORE INDEX (`col_bigint_key_unsigned`, `col_double_key_unsigned`)) AS `t3` WHERE ((((OCTET_LENGTH(1)) IN (SELECT `col_double_key_signed` 
    FROM `table_3_utf8_undef`)) IS TRUE) OR (((SUBTIME(_UTF8MB4'2001-06-19 19:44:23', _UTF8MB4'22:58:49')) NOT IN (SELECT `col_bigint_key_unsigned` FROM `table_7_utf8_undef` USE INDEX (`col_varchar(20)_key_signed`))) IS TRUE)) AND ((NOT ((COT(5)) NOT BETWEEN LN(0.4003331424722547) AND _UTF8MB4'v')) OR ((ROW(MINUTE(_UTF8MB4'2004-01-16 23:47:52'),1736655559147307272) IN (SELECT `col_decimal(40, 20)_undef_unsigned`,`col_bigint_undef_unsigned` FROM `table_7_utf8_undef` USE INDEX (`col_float_key_unsigned`))) IS FALSE))) AS `t4`) SELECT * FROM `MYWITH`;

WITH `MYWITH` AS (SELECT (ADDTIME(_UTF8MB4'2003-11-20 22:01:52', _UTF8MB4'15:14:18')) AS `f1`,(`f6`) AS `f2`,(~`f4`) AS `f3` FROM (SELECT (_UTF8MB4'2015-11-09'*`f11`) AS `f7`,
(CONCAT_WS(`f11`, `f9`, `f11`)*ATAN(8)) AS `f5`,(~`f10`<<`f9`) AS `f8` FROM (SELECT `col_char(20)_key_signed` AS `f9`,`col_varchar(20)_key_signed` AS `f10`,
`col_bigint_key_signed` AS `f11` FROM `table_3_utf8_undef`) AS `t1` ORDER BY `f11`) AS `t2` NATURAL JOIN (
    SELECT DISTINCT (SUBDATE(_UTF8MB4'2018-05-21', INTERVAL 1 DAY_MICROSECOND)) AS `f4`,(SIN(7) DIV BINARY RTRIM(8)) AS `f12`,(-`f14`<<_UTF8MB4'2017-10-10') AS `f6` 
    FROM (SELECT `col_double_undef_signed` AS `f13`,`col_decimal(40, 20)_key_signed` AS `f14`,`col_decimal(40, 20)_undef_signed` AS `f15` FROM `table_7_utf8_undef` 
    IGNORE INDEX (`col_bigint_key_unsigned`, `col_double_key_unsigned`)) AS `t3` WHERE ((((OCTET_LENGTH(1)) IN (SELECT `col_double_key_signed` 
    FROM `table_3_utf8_undef`)) IS TRUE) OR (((SUBTIME(_UTF8MB4'2001-06-19 19:44:23', _UTF8MB4'22:58:49')) NOT IN (SELECT `col_bigint_key_unsigned` FROM `table_7_utf8_undef` 
    USE INDEX (`col_varchar(20)_key_signed`))) IS TRUE)) AND ((NOT ((COT(5)) NOT BETWEEN LN(0.4003331424722547) AND _UTF8MB4'v')) OR ((ROW(MINUTE(_UTF8MB4'2004-01-16 23:47:52'),1736655559147307272) IN (SELECT `col_decimal(40, 20)_undef_unsigned`,`col_bigint_undef_unsigned` FROM `table_7_utf8_undef` USE INDEX (`col_float_key_unsigned`))) IS FALSE))) AS `t4`) SELECT * FROM `MYWITH`;

Could you please confirm whether the simplification process we applied has been helpful for developers in diagnosing and addressing this issue?

SELECT (~`f4`) AS `f3` FROM (SELECT (`f9`) FROM (
    SELECT `col_char(20)_key_signed` AS `f9` FROM `table_3_utf8_undef`) AS `t1` ) AS `t2` 
    NATURAL JOIN (SELECT (SUBDATE(_UTF8MB4'2018-05-21', INTERVAL 1 DAY_MICROSECOND)) AS `f4`FROM 
    `table_7_utf8_undef` )  AS `t4`;

result:
+----------------------+
| f3                   |
+----------------------+
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
| 18446723893189315655 |
+----------------------+

SELECT (~`f4`) AS `f3` FROM (SELECT (`f9`)  FROM (
    SELECT `col_char(20)_key_signed` AS `f9`FROM `table_3_utf8_undef`) AS `t1` ) AS `t2` 
    NATURAL JOIN (SELECT DISTINCT  (SUBDATE(_UTF8MB4'2018-05-21', INTERVAL 1 DAY_MICROSECOND)) AS `f4` FROM 
   `table_7_utf8_undef`  ) AS `t4`;

result:
+----------------------+
| f3                   |
+----------------------+
| 18446744073709549597 |
| 18446744073709549597 |
| 18446744073709549597 |
+----------------------+

Hi ,

Thank you for your bug report.

Sorry, but we do not see what is wrong with the values returned.

We ran your statements and we consider that the values returned to be correct.

The queries that you wrote return the correct results for the common table expressions.

Not a bug.