MySQL Bugs: #77631: filter using trim results in correct

Bug #77631	filter using trim results in correct
Submitted:	6 Jul 2015 19:00	Modified:	20 Jul 2015 14:49
Reporter:	Son Nguyen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.7	OS:	Linux
Assigned to:	Tor Didriksen	CPU Architecture:	Any

Description:
Hi,

I have simple queries below.  The queries return correct result 1 row

1) select `COUNTRY` from `COUNTRY` where trim(leading 'A' from  `COUNTRY`) = 'ustralia'

Get Data All:
"COUNTRY"
"Australia"

2) select  `COUNTRY`  from `COUNTRY` where  trim(trailing 'a' from `COUNTRY`) = 'Australi' 

Get Data All:
"COUNTRY"
"Australia"

But the query blow returns incorrectly no rows

select `COUNTRY` from `COUNTRY` where trim(leading 'A' from  `COUNTRY`) = 'ustralia'  and  trim(trailing 'a' from `COUNTRY`) = 'Australi'

Get Data All:
"COUNTRY"
0 rows fetched from 1 column.

Here are my database and driver versions.  The issue can be reproduced with MS ODBCTest tools on Windows

SQL_DBMS_NAME=17, 10, "MySQL"
SQL_DBMS_VER=18, 24, "5.7.7-rc-log"

SQL_DRIVER_NAME=6, 24, "myodbc5a.dll"
SQL_DRIVER_VER=7, 20, "05.02.0004"

How to repeat:

Using MS ODBCTest tools, the issue can be reproduced the issue.

Create a table COUNTRY with column COUNTRY that has string value "Australia", and run the query.

select `COUNTRY` from `COUNTRY` where trim(leading 'A' from  `COUNTRY`) = 'ustralia'  and  trim(trailing 'a' from `COUNTRY`) = 'Australi'

Hello Son Nguyen,

Thank you for the bug report.
Verified this behavior on MySql Server 5.7.7 as described.

Thanks,
Chiranjeevi.

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.7-rc-enterprise-commercial-advanced-log MySQL Enterprise Ser
ver - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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> use test;
Database changed
mysql> select `COUNTRY` from `COUNTRY` where trim(leading 'A' from  `COUNTRY`) = 'ustralia';
+-----------+
| COUNTRY   |
+-----------+
| Australia |
+-----------+
1 row in set (0.00 sec)

mysql> select  `COUNTRY`  from `COUNTRY` where  trim(trailing 'a' from `COUNTRY`) = 'Australi';
+-----------+
| COUNTRY   |
+-----------+
| Australia |
+-----------+
1 row in set (0.00 sec)

mysql> select `COUNTRY` from `COUNTRY` where trim(leading 'A' from  `COUNTRY`) = 'ustralia'  or  trim(trailing 'a' from `COUNTRY`) = 'Australi';
+-----------+
| COUNTRY   |
+-----------+
| Australia |
+-----------+
1 row in set (0.00 sec)

mysql> select `COUNTRY` from `COUNTRY` where trim(leading 'A' from  `COUNTRY`) = 'ustralia'  and  trim(trailing 'a' from `COUNTRY`) = 'Australi';
Empty set (0.00 sec)

mysql>

Noted in 5.7.9, 5.8.0 changelogs.

A WHERE predicate containing both TRIM(LEADING ..) and TRIM(TRAILING
...) could be incorrectly optimized away.