Unexpected results when using `LIKE` with boolean values

[suyZhong]

Consider the test case below. It is unexpected that, if the second query returns true, the third query returns an empty result, because the value of the WHERE predicate should be true as well. The last query should return the same result as the first query, i.e., a row with null and false.

CREATE TABLE t0(c1 INT);
CREATE TABLE t1(c1 BOOLEAN);
INSERT INTO t1(c1) VALUES ( false);

SELECT * FROM t0 RIGHT  JOIN t1 ON true; -- null false
SELECT (t1.c1 LIKE (-1 BETWEEN 2 AND t0.c1)) FROM t0 RIGHT  JOIN t1 ON true; -- true
SELECT * FROM t0 RIGHT  JOIN t1 ON true WHERE (t1.c1 LIKE (-1 BETWEEN 2 AND t0.c1));
-- Expected: null false
-- Actual: empty table

I found this in version LI-T6.0.0.362 where I built from source code ecc49e0

[mrotteveel]

The fact that the second query yields true is the actual error, given it's effectively t1.c1 LIKE (-1 BETWEEN 2 AND NULL) -> t1.c1 LIKE NULL, it should evaluate to NULL. Also, the fact LIKE works against a BOOLEAN at all seems an error to me as well.

[mrotteveel]

Never mind, I see that I made the same error I made before. X BETWEEN Y AND Z is equivalent to X >= Y and X <= Z, so -1 BETWEEN 2 AND NULL would be FALSE, and thus t1.c1 LIKE (-1 BETWEEN 2 AND t0.c1) is FALSE LIKE FALSE is TRUE.

My guess is that this is one of the case where the null-indicator is also set.

[mrotteveel]

That said, per 8.5 <like predicate> of SQL:2023, LIKE should only accept a character or binary value expression for the pattern, so the fact this works at all is questionable (but that ship has already sailed, with regards to Firebird flexibly converting between types without explicit casts even when the standard says not to).