Bug #100375 Column name cannot be parsed inside COS function
Submitted: 30 Jul 6:16 Modified: 31 Jul 7:13
Reporter: Yushan ZHANG Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.7.31, 5.6.48 OS:Any
Assigned to: CPU Architecture:Any

[30 Jul 6:16] Yushan ZHANG
Description:
-- cannot parse the column in having
mysql> SELECT `col_tinyint_key_signed` FROM table_10_utf8_6 HAVING COS(`col_enum_undef_signed` );                                                                                                                                                                     ERROR 1054 (42S22): Unknown column 'col_enum_undef_signed' in 'having clause'                                                                                                                                                                                         

-- parse correct with WHERE, wrong result due to Bug #100301
mysql> SELECT `col_tinyint_key_signed` FROM table_10_utf8_6 WHERE COS(`col_enum_undef_signed` ); 
+------------------------+
| col_tinyint_key_signed |
+------------------------+
|                   NULL |
|                   NULL |
|                      0 |
|                   NULL |
|                      0 |
+------------------------+
5 rows in set (0.00 sec)
                                           

-- it cannot parse the column name with `(`  and `)`
mysql> SELECT `col_tinyint_key_signed` FROM table_10_utf8_6 HAVING COS( `col_varbinary(20)_key_signed` ); 
ERROR 1054 (42S22): Unknown column 'col_varbinary(20)_key_signed' in 'having clause'

-- parse correct with WHERE and result is correct
mysql> SELECT `col_tinyint_key_signed` FROM table_10_utf8_6 HAVING COS( `col_varbinary(20)_key_signed` ); 
+------------------------+
| col_tinyint_key_signed |
+------------------------+
|                      0 |
|                   NULL |
+------------------------+
2 rows in set, 2 warnings (0.00 sec)

How to repeat:
create table table_10_utf8_6 (
`pk` int primary key,
`col_bigint_undef_signed` bigint  ,
`col_bigint_key_signed` bigint  ,
`col_float_undef_signed` float  ,
`col_float_key_signed` float  ,
`col_double_undef_signed` double  ,
`col_double_key_signed` double  ,
`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)  ,
`col_enum_undef_signed` enum('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z')  ,
`col_enum_key_signed` enum('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z')  ,
`col_tinyint_undef_signed` tinyint  ,
`col_tinyint_key_signed` tinyint  ,
`col_smallint_undef_signed` smallint  ,
`col_smallint_key_signed` smallint  ,
`col_binary(10)_undef_signed` binary(10)  ,
`col_binary(10)_key_signed` binary(10)  ,
`col_varbinary(20)_undef_signed` varbinary(20)  ,
`col_varbinary(20)_key_signed` varbinary(20)  ,
key (`col_bigint_key_signed`),
key (`col_float_key_signed`),
key (`col_double_key_signed`),
key (`col_char(20)_key_signed`),
key (`col_varchar(20)_key_signed`),
key (`col_enum_key_signed`),
key (`col_tinyint_key_signed`),
key (`col_smallint_key_signed`),
key (`col_binary(10)_key_signed`),
key (`col_varbinary(20)_key_signed`)
) character set utf8 
partition by hash(pk)
partitions 6;

insert into table_10_utf8_6 values (0,null,-0,-18,12.991,100,20279,'y','r',null,null,'z','j','z',null,'a','j','k','v','w',null),(1,73.1276,3.0556,12.991,1.009,0,-9.183,"think","with","because",null,'g',null,null,null,null,null,'f','b','u',null),(2,-0,1,-16292,-0,null,6.1654,'o','j',"as",'t','c','o',null,null,null,null,null,'s',"back",'f'),(3,-9.183,31979,null,-9.183,19569,12.991,"her",'n',null,'r','c','p',null,"did",'j',"at",'y',null,null,null),(4,1,-9.183,11506,13587,4075,0,"the",null,'m','y',null,null,"we","be",null,'b',null,'g',null,null),(5,1.009,64.1466,12.991,0,null,46,"got",null,null,'z','t','r','u','m','g','s','y',null,"can",null),(6,-86,12.991,12597,0,70.1485,1.009,'k',null,"up",null,'e','t','d','j','y',null,'d','d','i',null),(7,-9.183,100,35.0346,95.1356,0,-9.183,'y','v',null,"now",'m','f',"all","okay",null,'p','w','g','m',"your"),(8,27,null,100,null,null,100,null,'s',null,"who",'k',null,'y',"they","yes","something",'b','n',null,null),(9,-10,-9.183,0,54.0798,56.1058,-28528,"could","look",null,'m',null,'x',null,'a',"come",null,'b','k','s',null);

SELECT `col_tinyint_key_signed` FROM table_10_utf8_6 HAVING COS(`col_enum_undef_signed` ); 

SELECT `col_tinyint_key_signed` FROM table_10_utf8_6 WHERE COS(`col_enum_undef_signed` );

SELECT `col_tinyint_key_signed` FROM table_10_utf8_6 HAVING COS( `col_varbinary(20)_key_signed` ); 

SELECT `col_tinyint_key_signed` FROM table_10_utf8_6 WHERE COS( `col_varbinary(20)_key_signed` ); 

Suggested fix:
Two possible fixe for the second case:

1. It should prohibit the user from using the illegal identifier with `(` and `)`;
2. It should allow the user to use the `(` and `)` in a query when the identifier is quoted.

The usage of the identifier when be consistent when creating the table and using in a query.
[30 Jul 7:05] Yushan ZHANG
Correct the version, it should be 5.7.31, the first one was a typo.
[30 Jul 12:55] MySQL Verification Team
Hello Yushan ZHANG,

Thank you for the report and test case.
Observed that 5.6.48, 5.7.31 are affected.

regards,
Umesh
[31 Jul 7:13] Yushan ZHANG
Another case that `HAVING` doesn't work:

-- error
mysql>  SELECT `col_varchar(20)_undef_signed` FROM table_10_utf8_6 HAVING ! `col_float_key_signed`;
ERROR 1054 (42S22): Unknown column 'col_float_key_signed' in 'having clause'

-- executable
mysql>  SELECT `col_varchar(20)_undef_signed` FROM table_10_utf8_6 WHERE ! `col_float_key_signed`;