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.
