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

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

Hello Bang,

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

Thanks,
Umesh

Posted by developer:
 
Fixed in 5.7.29