You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When handling range queries unbounded from above or below, Postgres and MySQL do not consider NULL values to be greater than or less than every other value. Consider the following:
testdb=# CREATE TABLE t (x int);
CREATE TABLE
testdb=# INSERT INTO t (x) VALUES (1), (NULL), (2);
INSERT 0 3
testdb=# SELECT * FROM t WHERE x >= 1;
x
---
1
2
(2 rows)
testdb=# SELECT * FROM t WHERE x <= 2;
x
---
1
2
(2 rows)
In this example, t has values 1, 2, and NULL, but when querying for all the values >= 1 or <= 2, neither result set includes NULL.
With support for range query autoparameterization, Readyset currently exhibits different behavior. Continuing from the above example:
testdb=# CREATE CACHE FROM SELECT * FROM t WHERE x >= 1;
testdb=# SELECT * FROM t WHERE x >= 1;
x
---
1
2
(2 rows)
testdb=# CREATE CACHE FROM SELECT * FROM t WHERE x <= 2;
testdb=# SELECT * FROM t WHERE x <= 2;
x
---
1
2
(3 rows)
Because we consider NULL to be the minimum possible value, any range query that is unbounded from below will incorrectly include NULL values in the result set. The fix here is to ensure that we never actually construct range keys that are unbounded from above or below. Instead, "unbounded" range queries like the ones above should have range keys with exclusive bounds on the minimum and maximum possible DfValues. For example, the key for the query SELECT * FROM t WHERE x <= 2 would be Range((Bound::Excluded(DfValue::MIN), Bound::Included(2))).
We should make sure our fix for this leverages the type system to make it impossible to construct range queries with Bound::Unbounded to reduce the possibility of bugs being introduced in the future.
Change in user-visible behavior
Yes
Requires documentation change
No
The text was updated successfully, but these errors were encountered:
Description
When handling range queries unbounded from above or below, Postgres and MySQL do not consider
NULL
values to be greater than or less than every other value. Consider the following:In this example,
t
has values 1, 2, andNULL
, but when querying for all the values >= 1 or <= 2, neither result set includesNULL
.With support for range query autoparameterization, Readyset currently exhibits different behavior. Continuing from the above example:
Because we consider
NULL
to be the minimum possible value, any range query that is unbounded from below will incorrectly includeNULL
values in the result set. The fix here is to ensure that we never actually construct range keys that are unbounded from above or below. Instead, "unbounded" range queries like the ones above should have range keys with exclusive bounds on the minimum and maximum possibleDfValue
s. For example, the key for the querySELECT * FROM t WHERE x <= 2
would beRange((Bound::Excluded(DfValue::MIN), Bound::Included(2)))
.We should make sure our fix for this leverages the type system to make it impossible to construct range queries with
Bound::Unbounded
to reduce the possibility of bugs being introduced in the future.Change in user-visible behavior
Yes
Requires documentation change
No
The text was updated successfully, but these errors were encountered: