Bug #83383 incorrect validation for logical expression in mysql5.7

• Submitted: 14 Oct 2016 3:30
• Modified: 2 Jan 2020 23:18
• Reporter: 帅 Bang
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.15
• OS: Linux
• CPU Architecture: Any

[14 Oct 2016 3:30] 帅 Bang

Description:
mysql> create table sb(pk bigint(20) primary key, c1 bigint(20) unsigned DEFAULT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql>  insert into  sb values (1,223);
Query OK, 2 rows affected (0.00 sec)

mysql> select ! ( c1 BETWEEN LOCATE( SUBSTR( 'RWQEZ' , 1, LEAST( 87, 20 ) ), TRIM( SUBSTR( INSERT( 'mebz' , 148, 99 , SUBSTR( '晴空万里无云' , 1, LEAST( 87, 20 ) ) ) , 1 , LEAST( 87, 20 ) ) ) , 15385) AND COALESCE( -36 , 99999 , 10 ) ) from sb;

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ! ( c1 BETWEEN LOCATE( SUBSTR( 'RWQEZ' , 1, LEAST( 87, 20 ) ), TRIM( SUBSTR( INSERT( 'mebz' , 148, 99 , SUBSTR( '晴空万里无云' , 1, LEAST( 87, 20 ) ) ) , 1 , LEAST( 87, 20 ) ) ) , 15385) AND COALESCE( -36 , 99999 , 10 ) ) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                                                   0 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Actually, it should be 1

BTW, this bug does not exist in mysql 5.6.22. 

How to repeat:
drop table if exists sb;
create table sb(pk bigint(20) primary key, c1 bigint(20) unsigned DEFAULT NULL);
insert into  sb values (1,223);
 select ! ( c1 BETWEEN LOCATE( SUBSTR( 'RWQEZ' , 1, LEAST( 87, 20 ) ), TRIM( SUBSTR( INSERT( 'mebz' , 148, 99 , SUBSTR( '晴空万里无云' , 1, LEAST( 87, 20 ) ) ) , 1 , LEAST( 87, 20 ) ) ) , 15385) AND COALESCE( -36 , 99999 , 10 ) ) from sb;

Suggested fix:
1 rather than 0 returned

[14 Oct 2016 5:30] MySQL Verification Team

Hello Bang,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

[2 Jan 2020 23:18] Roy Lyseng

Posted by developer:
 
Fixed in 5.7.29