MySQL Bugs: #110882: BETWEEN query with index wrong result

Bug #110882	BETWEEN query with index wrong result
Submitted:	2 May 2023 7:26	Modified:	3 May 2023 12:51
Reporter:	Pedro Ferreira	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any (x86_64)
Tags:	between, INDEX

Description:
This may be related to Bug #110310

Create the tables and insert into them:

CREATE TABLE t0 (c0 TEXT);
CREATE TABLE t1 (c0 INT, INDEX i1(c0));
INSERT INTO t0(c0) VALUES (CAST(X'4B1A' AS CHAR));
INSERT INTO t1(c0) VALUES (15312);

Then run these queries:

SELECT 1 FROM t0 JOIN t1 WHERE t1.c0 BETWEEN t0.c0 AND CAST(X'E555' AS CHAR);
SELECT 1 FROM t0 JOIN t1 WHERE (t1.c0 BETWEEN t0.c0 AND CAST(X'E555' AS CHAR)) IS NULL;

Both return a row, although the predicates are exclusive. If I do:

SELECT 15312 BETWEEN CAST(X'4B1A' AS CHAR) AND CAST(X'E555' AS CHAR); returns NULL, so I supposed the first one is wrong.

How to repeat:
Run the queries above.

Hi Mr. Ferreira,

Thank you for your bug report.

We did manage to repeat it, hence it is a bug.

However, this is a small and very insignificant bug. The only bug is in the case where WHERE returns NULL, but the row of the result set is still printed.

We repeated it on the several platforms.

This is a non-critical bug.

Hi,

We had a lot of discussions with our team and discovered that this is not a bug.

Here is a full analysis:

 The first query returns zero rows, since t he result of the predicate is 
 UNKNOWN.
 .
 The second query returns one row, since the result of NULL (UNKNOWN) IS NULL 
 is true.
 .
 The third query returns the value NULL.
 .
 Log from the run:
 .
 mysql> SELECT 1
 -> FROM t0 JOIN t1
 -> WHERE t1.c0 BETWEEN t0.c0 AND CAST(X'E555' AS CHAR);
 Empty set, 3 warnings (0.00 sec)
.
 mysql> SELECT 1
 -> FROM t0 JOIN t1
 -> WHERE (t1.c0 BETWEEN t0.c0 AND CAST(X'E555' AS CHAR)) IS NULL;
 +---+
 | 1 |
 +---+
 | 1 |
 +---+
 1 row in set, 1 warning (0.00 sec)
 .
 mysql> SELECT 15312 BETWEEN CAST(X'4B1A' AS CHAR) AND CAST(X'E555' AS CHAR); 
 +---------------------------------------------------------------+
 | 15312 BETWEEN CAST(X'4B1A' AS CHAR) AND CAST(X'E555' AS CHAR) |
 +---------------------------------------------------------------+
 | NULL |
 +---------------------------------------------------------------+
 1 row in set, 2 warnings (0.00 sec)