Wrong result for BETWEEN predicate involving bitwise negation and implicit type conversion

When a query uses a BETWEEN predicate involving a bitwise negation expression (~ TRUE) and mixed-type comparisons between string and numeric values, the predicate may evaluate inconsistently between WHERE filtering and SELECT expression evaluation.

In this case, the WHERE clause incorrectly returns an empty result set, while evaluating the same predicate as a SELECT expression indicates that one row satisfies the condition.

CREATE TABLE t448 (c1 VARCHAR (54));

INSERT INTO t448 (c1) VALUES ('jnbF3LETSTP');

SELECT t448.c1 FROM t448 JOIN t448 AS ta1 WHERE (('' BETWEEN (~ TRUE) AND t448.c1) IS FALSE);

--empty set

SELECT SUM(count) FROM (SELECT (('' BETWEEN (~ TRUE) AND t448.c1) IS FALSE) IS TRUE AS count FROM t448 JOIN t448 AS ta1) AS ta_norec;

--1