Description:
I previously performed reduction for bug #113424, but it was considered not a bug. The results of the two reduced SQL statements should be implication. This bug still occurs in 8.0.36.
--sql1--
SELECT (_UTF8MB4'2005'-ADDTIME(_UTF8MB4'2016-11-18 11:05:36', _UTF8MB4'19:02:18')%OCTET_LENGTH(`f6`)) AS `f3` FROM (SELECT `col_double_key_unsigned` AS `f6` FROM `table_7_utf8_undef`) AS `t1`
HAVING (( `f3`) IS TRUE) IS TRUE;
+------+
| f3 |
+------+
| 2005 |
| 2005 |
| 2005 |
| 2005 |
| 2005 |
| 2005 |
| 2004 |
+------+
--sql2--
SELECT (_UTF8MB4'2005'-ADDTIME(_UTF8MB4'2016-11-18 11:05:36', _UTF8MB4'19:02:18')%OCTET_LENGTH(`f6`)) AS `f3` FROM (SELECT `col_double_key_unsigned` AS `f6` FROM `table_7_utf8_undef`) AS `t1`
HAVING 1;
+------+
| f3 |
+------+
| 2005 |
| 2001 |
| 2001 |
| 2005 |
| 2001 |
| 2005 |
| 2001 |
+------+
The clause "HAVING (( `f3`) IS TRUE) IS TRUE" has stronger constraints compared to "HAVING 1".
With the rest of the query statement being the same, the result of sql1 should be a subset of the second query's result. However, the query result shows that SQL1 contains 2004, which is not part of SQL2.
Could you please confirm whether the simplification process we applied has been helpful for developers in diagnosing and addressing this issue?
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
');
SELECT (_UTF8MB4'2005'-ADDTIME(_UTF8MB4'2016-11-18 11:05:36', _UTF8MB4'19:02:18')%OCTET_LENGTH(`f6`)) AS `f3` FROM (SELECT `col_double_key_unsigned` AS `f6` FROM `table_7_utf8_undef`) AS `t1`
HAVING (( `f3`) IS TRUE) IS TRUE;
SELECT (_UTF8MB4'2005'-ADDTIME(_UTF8MB4'2016-11-18 11:05:36', _UTF8MB4'19:02:18')%OCTET_LENGTH(`f6`)) AS `f3` FROM (SELECT `col_double_key_unsigned` AS `f6` FROM `table_7_utf8_undef`) AS `t1`
HAVING 1;
