Bug #106924 Query result is incorrect if there is a condition compares char field with 0

• Submitted: 6 Apr 2022 8:12
• Modified: 6 Apr 2022 12:07
• Reporter: Brian Yue (OCA)
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.27
• OS: Any (rhel-7.4)
• CPU Architecture: Any (x86-64)

[6 Apr 2022 8:12] Brian Yue

Description:
Hello,
  There is a case that I think is not handled corretly. Firstly I create a table with field of type char(2), and insert records to the table. Then query from the table with a condition that compares the char(2) field with number 0, the resultset seems to be incorrect, which is expected to be `Empty set` but actually `3 rows in set` is gotten. 

  Reported by GoldenDB Team.

How to repeat:
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)

mysql> use testdb
Database changed
mysql>
mysql>
mysql> create table testdb.test(
    -> id int primary key,
    -> ch char(2) not null);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into testdb.test values (1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into testdb.test values (2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into testdb.test values (3,'c');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from testdb.test where ch = 1;
Empty set, 3 warnings (0.00 sec)

mysql> select * from testdb.test where ch = 0;
+----+----+
| id | ch |
+----+----+
|  1 | a  |
|  2 | b  |
|  3 | c  |
+----+----+
3 rows in set, 3 warnings (0.00 sec)

Suggested fix:
I suppose that the char(2) field value is converted to be 0 when comparing with an integer value, but actually it's not 0, I think it's not reasonable.

[6 Apr 2022 12:07] MySQL Verification Team

Hi Mr. Yue,

Thank you for your bug report.

However, it is not a bug.

SQL does not function like some other programming languages, where many data types always have their integer value. In this case, comparing a string to an integer, requires conversions of both to the floating point type. Since letters can not be converted properly, they are converted to 0.00. An integer like 0 (zero) is also converted to 0.00, so the comparison works.

This is explained in our Reference Manual, section on expressions.

Not a bug.