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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.21, 5.7.31
• OS: Any
• 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