Description:
A query using a NOT BETWEEN predicate may evaluate inconsistently between WHERE filtering and direct SELECT expression evaluation when interval arithmetic and implicit type conversion are involved.
In this case, a LONGTEXT column participates in numeric arithmetic together with date/time interval expressions. The predicate evaluates to TRUE when computed directly as a SELECT expression, but the same predicate in the WHERE clause incorrectly filters out the row and returns an empty result set.
Both the left-hand expression and the BETWEEN boundaries evaluate to non-NULL values, indicating that the issue is not caused by NULL-boundary handling. The inconsistency appears to be related to optimizer processing of NOT BETWEEN predicates combined with interval arithmetic and implicit type conversion.
How to repeat:
-- CREATE TABLE t934 (c1 LONGTEXT);
-- INSERT INTO t934 (c1) VALUES ('CRykJwmFhMsHnU598YDWqTKDuxSl');
SELECT t934.c1 FROM t934 WHERE ((t934.c1 + ('16:10:11' + INTERVAL '14 22:48:01' DAY_SECOND)) NOT BETWEEN 0 AND ('16:10:11' - INTERVAL '18' DAY));
-- 0 rows
SELECT SUM(count) FROM (SELECT ((t934.c1 + ('16:10:11' + INTERVAL '14 22:48:01' DAY_SECOND)) NOT BETWEEN 0 AND ('16:10:11' - INTERVAL '18' DAY)) IS TRUE AS count FROM t934) AS ta_norec;
-- 1
Description: A query using a NOT BETWEEN predicate may evaluate inconsistently between WHERE filtering and direct SELECT expression evaluation when interval arithmetic and implicit type conversion are involved. In this case, a LONGTEXT column participates in numeric arithmetic together with date/time interval expressions. The predicate evaluates to TRUE when computed directly as a SELECT expression, but the same predicate in the WHERE clause incorrectly filters out the row and returns an empty result set. Both the left-hand expression and the BETWEEN boundaries evaluate to non-NULL values, indicating that the issue is not caused by NULL-boundary handling. The inconsistency appears to be related to optimizer processing of NOT BETWEEN predicates combined with interval arithmetic and implicit type conversion. How to repeat: -- CREATE TABLE t934 (c1 LONGTEXT); -- INSERT INTO t934 (c1) VALUES ('CRykJwmFhMsHnU598YDWqTKDuxSl'); SELECT t934.c1 FROM t934 WHERE ((t934.c1 + ('16:10:11' + INTERVAL '14 22:48:01' DAY_SECOND)) NOT BETWEEN 0 AND ('16:10:11' - INTERVAL '18' DAY)); -- 0 rows SELECT SUM(count) FROM (SELECT ((t934.c1 + ('16:10:11' + INTERVAL '14 22:48:01' DAY_SECOND)) NOT BETWEEN 0 AND ('16:10:11' - INTERVAL '18' DAY)) IS TRUE AS count FROM t934) AS ta_norec; -- 1