Bug #96688 sql query 'where' condition

• Submitted: 29 Aug 2019 4:14
• Modified: 29 Aug 2019 11:31
• Reporter: s k
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.16, 8.0.17
• OS: Any
• CPU Architecture: Any

[29 Aug 2019 4:14] s k

Description:
when having a spacing behind the data on where condition, no record found

an example has a table call `Car`
column `name` Char(10)
column `color` Char(10)

Data
----
Nissan, red

--------------------------------------
select * from Car where color = 'red'
this working fine

select * from Car where color = 'red   '
no record found.

How to repeat:
NA

[29 Aug 2019 11:29] MySQL Verification Team

Hello!

Thank you for the report.
Quoting from https://dev.mysql.com/doc/refman/8.0/en/char.html
The pad attribute determines how trailing spaces are treated for comparison of nonbinary strings (CHAR, VARCHAR, and TEXT values). NO PAD collations treat spaces at the end of strings like any other character. For PAD SPACE collations, trailing spaces are insignificant in comparisons; strings are compared without regard to any trailing spaces. 
-
-
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE names (myname CHAR(10));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO names VALUES ('Jon');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT myname = 'Jon', myname = 'Jon  ' FROM names;
+----------------+------------------+
| myname = 'Jon' | myname = 'Jon  ' |
+----------------+------------------+
|              1 |                0 |
+----------------+------------------+
1 row in set (0.00 sec)

- 5.7.27

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE names (myname CHAR(10));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO names VALUES ('Jon');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT myname = 'Jon', myname = 'Jon  ' FROM names;
+----------------+------------------+
| myname = 'Jon' | myname = 'Jon  ' |
+----------------+------------------+
|              1 |                1 |
+----------------+------------------+
1 row in set (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

regards,
Umesh