Bug #100807 Explicit IS TRUE changes the result for BETWEEN when index is used
Submitted: 11 Sep 2020 5:39 Modified: 11 Sep 2020 7:40
Reporter: Yushan ZHANG Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.21, 5.7.31 OS:Any
Assigned to: CPU Architecture:Any

[11 Sep 2020 5:39] Yushan ZHANG
Description:
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE TABLE t1 (
    ->  mychar char(20) DEFAULT NULL,
    ->  KEY mykey (`mychar`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=binary;
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> insert into t1 values (1), (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT mychar FROM t1 WHERE ( ( mychar)  BETWEEN ( 'd' ) AND (7 ) );
Empty set (0.00 sec)

mysql> -- different
mysql> SELECT mychar FROM t1 WHERE ( ( mychar)  BETWEEN ( 'd' ) AND (7 ) ) IS TRUE;
+----------------------+
| mychar               |
+----------------------+
| 1                    |
| 2                    |
+----------------------+
2 rows in set, 4 warnings (0.00 sec)

How to repeat:
drop table if exists t1;

CREATE TABLE t1 (
 mychar char(20) DEFAULT NULL,
 KEY mykey (`mychar`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;

insert into t1 values (1), (2);

SELECT mychar FROM t1 WHERE ( ( mychar)  BETWEEN ( 'd' ) AND (7 ) );

SELECT mychar FROM t1 WHERE ( ( mychar)  BETWEEN ( 'd' ) AND (7 ) ) IS TRUE;
[11 Sep 2020 7:40] MySQL Verification Team
Hello Yushan ZHANG,

Thank you for the report and feedback.

regards,
Umesh