Bug #114406 Discrepancy in SQL Results due to HAVING Clause Constraints.
Submitted: 19 Mar 12:57 Modified: 19 Mar 14:34
Reporter: shan he Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.36 OS:Any (20.04)
Assigned to: CPU Architecture:Any

[19 Mar 12:57] shan he
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;
[19 Mar 14:34] MySQL Verification Team
Hi Mr. he,

Thank you for your bug report.

However, this is not a bug.

Use of HAVING clause is very strictly documented in our Reference Manual as :

" HAVING clause must come after any GROUP BY clause and before any ORDER BY clause".

We do not see any GROUP BY in your query.

The fact that HAVING 1 is returning the correct result is because that clause is optimised away, since it always returns true.

Not a bug.