Bug #100231 hex string in query lead to wrong result

• Submitted: 16 Jul 2020 5:57
• Modified: 16 Jul 2020 13:55
• Reporter: SUMMER SHENG
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.20
• OS: Any
• CPU Architecture: Any

[16 Jul 2020 5:57] SUMMER SHENG

Description:
use hex string in a query lead to wrong answer.

How to repeat:
create table t1 as (select 0x70 a, 0x70 b);

select * from t1;

+------+------+
| a    | b    |
+------+------+
| 0x70 | 0x70 |
+------+------+

select a,b from t1 where a=b group by a,b having a=0x70;

+------+------+
| a    | b    |
+------+------+
| 0x70 | 0x70 |
+------+------+

select a,b from t1 where a=b group by a,b having a=b and a=0x70;

Empty set (0.00 sec)

explain the query and show warnings give me the optimized query like
"/* select#1 */ select `test`.`t10`.`a` AS `a`,`test`.`t10`.`b` AS `b` from `test`.`t10` where (`test`.`t10`.`b` = `test`.`t10`.`a`) group by `test`.`t10`.`a`,`test`.`t10`.`b` having ((`test`.`t10`.`b` = 0x19) and (`test`.`t10`.`a` = 0x70))"
The 0x19 comes from nowhere.

[16 Jul 2020 13:55] MySQL Verification Team

Hi Mr. SHENG,

Thank you for your bug report.

However, I can't repeat this behaviour. This is what I get with latest 8.0:

a	b
112	112
a	b
112	112
a	b
112	112

Problem is that the way you create a table does not make neither of the columns an INT.

Not a bug.