Unexpected Results when Using BETWEEN AND after CREATE INDEX

[suyZhong]

Considering the test case below:

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT, c1 INT, PRIMARY KEY(c0));
CREATE INDEX t1i1 ON t1(c1 , c0 ); -- necessary to reproduce the issue
INSERT INTO t1 (c1, c0) VALUES (1, 1);
INSERT INTO t1 (c0) VALUES (0);
INSERT INTO t0 (c0) VALUES (true);

SELECT * FROM t0, t1; -- 2 rows: (1 0 NULL), (1 1 1)
SELECT (t0.c0 BETWEEN t1.c1 AND t1.c0) FROM t0, t1; -- 0, 1
SELECT * FROM t0, t1 WHERE (t0.c0 BETWEEN t1.c1 AND t1.c0);
-- Expected: 1 row (1 1 1)
-- Actual: 0 row

The third SELECT returns an empty result, which is surprising: If the result of second query is 0, 1, the value of the BETWEEN expression should be true for one row, and thus the third query should return at least one row in t0, t1.

This test case works well in MySQL, however not in dolt.

I originally find this by building dolt from source version 2d0a2ed. It could also be reproduced in 1.29.7

[max-hoffman]

This looks like a range heap join bug @jycor @nicktobey

tmp1> explain SELECT * FROM t0, t1 WHERE (t0.c0 BETWEEN t1.c1 AND t1.c0);
+---------------------------------------------+
| plan                                        |
+---------------------------------------------+
| RangeHeapJoin                               |
|  ├─ ((t0.c0 >= t1.c1) AND (t0.c0 <= t1.c0)) |
|  ├─ Sort(t0.c0 ASC)                         |
|  │   └─ Table                               |
|  │       ├─ name: t0                        |
|  │       └─ columns: [c0]                   |
|  └─ IndexedTableAccess(t1)                  |
|      ├─ index: [t1.c1,t1.c0]                |
|      ├─ filters: [{[NULL, ∞), [NULL, ∞)}]   |
|      └─ columns: [c0 c1]                    |
+---------------------------------------------+

[suyZhong]

Hi all! I'm wondering if there has been any update or assessment regarding this issue.

[max-hoffman]

@nicktobey would you take a pass at this today?

[nicktobey]

I'll take a look at this today.

[nicktobey]

It looks like there's an issue with how the Range Heap Join algorithm handles NULLS in a BETWEEN expression, when there's an index over the lower bound. The index treats NULLs as being less than non-NULL values, while the comparison function used to sort the heap treats NULLs as being greater than non-NULL values.

This discrepancy usually doesn't matter because comparisons involving NULL can never be true. But this is causing the heap to behave incorrectly and omit rows that should be included.

I have a fix but I'm writing additional tests to confirm its correctness. The exact interplay between the different ways that MySQL handles NULL can be somewhat tricky.

[nicktobey]

Related issue: dolthub/go-mysql-server#1903