mirror of
https://github.com/tursodatabase/limbo.git
synced 2025-12-23 08:21:09 +00:00
027ebe33fe
Take e.g. CREATE TABLE t(x); CREATE INDEX txdesc ON t(x desc); INSERT INTO t values (1),(2),(3); SELECT * FROM t WHERE x > NULL; -- Our plan, like Sqlite, was to start iterating the descending index from the beginning (Rewind) and stop once we hit a row where x is <= than NULL using `IdxGe` instruction (GE in descending indexes means LE). However, `IdxGe` and other similar instructions use a sort comparison where NULL is less than numbers/strings etc, so this would incorrectly not jump. Fix: we need to emit an explicit NULL check after rewinding.
111 lines
No EOL
3.2 KiB
Tcl
Executable file
111 lines
No EOL
3.2 KiB
Tcl
Executable file
#!/usr/bin/env tclsh
|
|
|
|
set testdir [file dirname $argv0]
|
|
source $testdir/tester.tcl
|
|
|
|
do_execsql_test isnull {
|
|
select null isnull, 'hi' isnull;
|
|
} {1|0}
|
|
|
|
do_execsql_test is-null {
|
|
select null is null, 'hi' is null;
|
|
} {1|0}
|
|
|
|
do_execsql_test notnull {
|
|
select null notnull, 'hi' notnull;
|
|
} {0|1}
|
|
|
|
do_execsql_test not-null {
|
|
select null not null, 'hi' not null;
|
|
} {0|1}
|
|
|
|
do_execsql_test sel-true {
|
|
select true;
|
|
} {1}
|
|
|
|
do_execsql_test sel-false {
|
|
select false;
|
|
} {0}
|
|
|
|
# https://github.com/tursodatabase/turso/issues/3966
|
|
do_execsql_test_on_specific_db {:memory:} not-null-just-cuz-unique {
|
|
create table t (a int, x int unique);
|
|
insert into t(a) values(1);
|
|
select * from t;
|
|
} {1|}
|
|
|
|
# Regression tests for NULL comparison with index scans
|
|
# Any comparison with NULL should return 0 rows because NULL comparisons
|
|
# always return NULL (unknown), which is falsy.
|
|
|
|
# Ascending index: x > NULL should return 0 rows
|
|
do_execsql_test_on_specific_db {:memory:} null-comparison-asc-gt {
|
|
CREATE TABLE t1(x);
|
|
CREATE INDEX t1_idx ON t1(x ASC);
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
SELECT * FROM t1 WHERE x > NULL;
|
|
} {}
|
|
|
|
# Descending index: x > NULL should return 0 rows
|
|
do_execsql_test_on_specific_db {:memory:} null-comparison-desc-gt {
|
|
CREATE TABLE t2(x);
|
|
CREATE INDEX t2_idx ON t2(x DESC);
|
|
INSERT INTO t2 VALUES (1), (2), (3);
|
|
SELECT * FROM t2 WHERE x > NULL;
|
|
} {}
|
|
|
|
# Ascending index: x < NULL should return 0 rows
|
|
do_execsql_test_on_specific_db {:memory:} null-comparison-asc-lt {
|
|
CREATE TABLE t3(x);
|
|
CREATE INDEX t3_idx ON t3(x ASC);
|
|
INSERT INTO t3 VALUES (1), (2), (3);
|
|
SELECT * FROM t3 WHERE x < NULL;
|
|
} {}
|
|
|
|
# Descending index: x < NULL should return 0 rows
|
|
do_execsql_test_on_specific_db {:memory:} null-comparison-desc-lt {
|
|
CREATE TABLE t4(x);
|
|
CREATE INDEX t4_idx ON t4(x DESC);
|
|
INSERT INTO t4 VALUES (1), (2), (3);
|
|
SELECT * FROM t4 WHERE x < NULL;
|
|
} {}
|
|
|
|
# Ascending index: x >= NULL should return 0 rows
|
|
do_execsql_test_on_specific_db {:memory:} null-comparison-asc-ge {
|
|
CREATE TABLE t5(x);
|
|
CREATE INDEX t5_idx ON t5(x ASC);
|
|
INSERT INTO t5 VALUES (1), (2), (3);
|
|
SELECT * FROM t5 WHERE x >= NULL;
|
|
} {}
|
|
|
|
# Descending index: x >= NULL should return 0 rows
|
|
do_execsql_test_on_specific_db {:memory:} null-comparison-desc-ge {
|
|
CREATE TABLE t6(x);
|
|
CREATE INDEX t6_idx ON t6(x DESC);
|
|
INSERT INTO t6 VALUES (1), (2), (3);
|
|
SELECT * FROM t6 WHERE x >= NULL;
|
|
} {}
|
|
|
|
# Ascending index: x <= NULL should return 0 rows
|
|
do_execsql_test_on_specific_db {:memory:} null-comparison-asc-le {
|
|
CREATE TABLE t7(x);
|
|
CREATE INDEX t7_idx ON t7(x ASC);
|
|
INSERT INTO t7 VALUES (1), (2), (3);
|
|
SELECT * FROM t7 WHERE x <= NULL;
|
|
} {}
|
|
|
|
# Descending index: x <= NULL should return 0 rows
|
|
do_execsql_test_on_specific_db {:memory:} null-comparison-desc-le {
|
|
CREATE TABLE t8(x);
|
|
CREATE INDEX t8_idx ON t8(x DESC);
|
|
INSERT INTO t8 VALUES (1), (2), (3);
|
|
SELECT * FROM t8 WHERE x <= NULL;
|
|
} {}
|
|
|
|
# Descending index with ORDER BY: x > NULL should return 0 rows
|
|
do_execsql_test_on_specific_db {:memory:} null-comparison-desc-order-by {
|
|
CREATE TABLE t9(x);
|
|
CREATE INDEX t9_idx ON t9(x DESC);
|
|
INSERT INTO t9 VALUES (1), (2), (3);
|
|
SELECT * FROM t9 WHERE x > NULL ORDER BY x DESC;
|
|
} {} |