aboutsummaryrefslogtreecommitdiff
path: root/test
diff options
context:
space:
mode:
Diffstat (limited to 'test')
-rw-r--r--test/eqp.test3
-rw-r--r--test/existsexpr.test426
-rw-r--r--test/existsexpr2.test96
-rw-r--r--test/existsfault.test49
-rw-r--r--test/incrblob4.test15
-rw-r--r--test/json101.test8
-rw-r--r--test/notnull2.test2
7 files changed, 588 insertions, 11 deletions
diff --git a/test/eqp.test b/test/eqp.test
index 5d2659be7..147b5ceaf 100644
--- a/test/eqp.test
+++ b/test/eqp.test
@@ -338,8 +338,7 @@ det 3.3.3 {
} {
QUERY PLAN
|--SCAN t1
- `--CORRELATED SCALAR SUBQUERY xxxxxx
- `--SCAN t2
+ `--SCAN t2 EXISTS
}
#-------------------------------------------------------------------------
diff --git a/test/existsexpr.test b/test/existsexpr.test
new file mode 100644
index 000000000..2bf2e8223
--- /dev/null
+++ b/test/existsexpr.test
@@ -0,0 +1,426 @@
+# 2024 May 25
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+source $testdir/lock_common.tcl
+set testprefix existsexpr
+
+
+do_execsql_test 1.0 {
+ CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
+ INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
+ CREATE INDEX x1b ON x1(b);
+
+ CREATE TABLE x2(x, y);
+ INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
+}
+
+do_execsql_test 1.1 {
+ SELECT 1 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=5)
+} {1}
+
+do_execsql_test 1.2 {
+ SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
+} {1 2 3 4 5 6}
+
+# With "a=x", the UNIQUE index means the EXIST can be transformed to a join.
+# So no "SUBQUERY". With "b=x", the index is not UNIQUE and so there is a
+# "SUBQUERY".
+do_execsql_test 1.3.1 {
+ EXPLAIN QUERY PLAN
+ SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
+} {~/SUBQUERY/}
+do_execsql_test 1.3.2 {
+ EXPLAIN QUERY PLAN
+ SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE b=x)
+} {~/SUBQUERY/}
+
+do_execsql_test 1.4.1 {
+ EXPLAIN QUERY PLAN
+ SELECT * FROM x2 WHERE x=1 AND EXISTS (SELECT 1 FROM x1 WHERE a=x)
+} {~/SUBQUERY/}
+do_execsql_test 1.4.2 {
+ EXPLAIN QUERY PLAN
+ SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) AND y=2
+} {~/SUBQUERY/}
+
+do_execsql_test 1.5 {
+ SELECT count(*) FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
+} {3}
+
+#-------------------------------------------------------------------------
+do_execsql_test 2.0 {
+ CREATE TABLE t1(a, b);
+ WITH s(i) AS (
+ SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000
+ ) INSERT INTO t1 SELECT i, i FROM s;
+
+ CREATE TABLE t2(c, d);
+ WITH s(i) AS (
+ SELECT 10 UNION ALL SELECT i+10 FROM s WHERE i<1000
+ ) INSERT INTO t2 SELECT i, i FROM s;
+}
+
+do_execsql_test 2.1 {
+ SELECT count(*) FROM t1;
+ SELECT count(*) FROM t2;
+} {1000 100}
+
+do_execsql_test 2.2 {
+ SELECT count(*) FROM t1, t2 WHERE a=c;
+} {100}
+
+do_execsql_test 2.3 {
+ SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a)
+} {100}
+do_eqp_test 2.4 {
+ SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a)
+} {SCAN t1}
+
+do_execsql_test 2.4.0 {
+ CREATE UNIQUE INDEX t2c ON t2(c);
+ CREATE UNIQUE INDEX t1a ON t1(a);
+}
+
+do_eqp_test 2.4.1 {
+ SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
+} {SCAN t1*t2 EXISTS}
+do_execsql_test 2.4.2 {
+ ANALYZE;
+}
+do_eqp_test 2.4.3 {
+ SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
+} {SCAN t1*t2 EXISTS}
+do_execsql_test 2.4.4 {
+ SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
+} {100}
+
+do_execsql_test 2.5.1 {
+ EXPLAIN QUERY PLAN
+ SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.rowid=a);
+} {~/SUBQUERY/}
+
+#-------------------------------------------------------------------------
+proc do_subquery_test {tn bSub sql res} {
+ set r1(0) ~/SUBQUERY/
+ set r1(1) /SUBQUERY/
+ do_execsql_test $tn.1 "explain query plan $sql" $r1($bSub)
+ do_execsql_test $tn.2 $sql $res
+}
+
+do_execsql_test 3.0 {
+ CREATE TABLE y1(a, b, c);
+ CREATE TABLE y2(x, y, z);
+ CREATE UNIQUE INDEX y2zy ON y2(z, y);
+
+ INSERT INTO y1 VALUES(1, 1, 1);
+ INSERT INTO y1 VALUES(2, 2, 2);
+ INSERT INTO y1 VALUES(3, 3, 3);
+ INSERT INTO y1 VALUES(4, 4, 4);
+
+ INSERT INTO y2 VALUES(1, 1, 1);
+ INSERT INTO y2 VALUES(3, 3, 3);
+}
+
+do_subquery_test 3.1 0 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 WHERE z=a AND y=b AND x=z
+ )
+} {
+ 1 1 1 3 3 3
+}
+
+do_subquery_test 3.2 0 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND x=z
+ )
+} {
+ 1 1 1 3 3 3
+}
+
+do_subquery_test 3.3 0 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND c!=3
+ )
+} {
+ 1 1 1
+}
+
+do_subquery_test 3.4 0 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 WHERE z=max(a,b) AND b=3
+ )
+} {
+ 3 3 3
+}
+
+do_subquery_test 3.5 0 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 WHERE z=a-1 AND y=a-1
+ )
+} {
+ 2 2 2
+ 4 4 4
+}
+
+do_subquery_test 3.6 0 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 WHERE z=a-1 AND y+1=a
+ )
+} {
+ 2 2 2
+ 4 4 4
+}
+
+do_subquery_test 3.7 1 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT count(*) FROM y2 WHERE z=a-1 AND y=a-1
+ )
+} {
+ 1 1 1
+ 2 2 2
+ 3 3 3
+ 4 4 4
+}
+
+do_subquery_test 3.8 0 {
+ SELECT * FROM y1 WHERE EXISTS ( SELECT a+1 FROM y2 )
+} {
+ 1 1 1
+ 2 2 2
+ 3 3 3
+ 4 4 4
+}
+
+do_subquery_test 3.9 1 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 one, y2 two WHERE one.z=a-1 AND one.y=a-1
+ )
+} {
+ 2 2 2
+ 4 4 4
+}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 4.0 {
+ CREATE TABLE tx1(a TEXT COLLATE nocase, b TEXT);
+ CREATE UNIQUE INDEX tx1ab ON tx1(a, b);
+
+ INSERT INTO tx1 VALUES('a', 'a');
+ INSERT INTO tx1 VALUES('B', 'b');
+ INSERT INTO tx1 VALUES('c', 'c');
+ INSERT INTO tx1 VALUES('D', 'd');
+ INSERT INTO tx1 VALUES('e', 'e');
+
+ CREATE TABLE tx2(x, y);
+ INSERT INTO tx2 VALUES('A', 'a');
+ INSERT INTO tx2 VALUES('b', 'b');
+ INSERT INTO tx2 VALUES('C', 'c');
+ INSERT INTO tx2 VALUES('D', 'd');
+}
+
+do_subquery_test 4.1 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE a=x AND b=y
+ )
+} {
+ A a
+ b b
+ C c
+ D d
+}
+
+do_subquery_test 4.1.1 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE (a COLLATE nocase)=x AND b=y
+ )
+} {
+ A a b b C c D d
+}
+do_subquery_test 4.1.2 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE a=x AND (b COLLATE binary)=y
+ )
+} {
+ A a b b C c D d
+}
+do_subquery_test 4.1.1 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE x=(a COLLATE nocase) AND b=y
+ )
+} {
+ A a b b C c D d
+}
+do_subquery_test 4.1.2 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE a=x AND y=(b COLLATE binary)
+ )
+} {
+ A a b b C c D d
+}
+
+do_subquery_test 4.2 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE a=x AND b=y COLLATE nocase
+ )
+} {
+ A a
+ b b
+ C c
+ D d
+}
+
+do_execsql_test 4.3 {
+ DROP INDEX tx1ab;
+ CREATE UNIQUE INDEX tx1ab ON tx1(a COLLATE binary, b);
+}
+
+do_subquery_test 4.4 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE a=x AND b=y
+ )
+} {
+ A a
+ b b
+ C c
+ D d
+}
+
+do_subquery_test 4.4 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE a=x COLLATE binary AND b=y
+ )
+} {
+ D d
+}
+
+do_subquery_test 4.4 1 {
+ SELECT EXISTS ( SELECT x FROM tx1 ) FROM tx2
+} {
+ 1 1 1 1
+}
+
+do_subquery_test 4.4 1 {
+ SELECT (SELECT EXISTS ( SELECT x FROM tx1 ) WHERE 1) FROM tx2
+} {
+ 1 1 1 1
+}
+
+#-------------------------------------------------------------------------
+proc cols {s f} {
+ set lCols [list]
+ for {set i $s} {$i<=$f} {incr i} {
+ lappend lCols [format "c%02d" $i]
+ }
+ join $lCols ", "
+}
+proc vals {n val} {
+ set lVal [list]
+ for {set i 0} {$i<$n} {incr i} {
+ lappend lVal $val
+ }
+ join $lVal ", "
+}
+proc exprs {s f} {
+ set lExpr [list]
+ for {set i $s} {$i<=$f} {incr i} {
+ lappend lExpr [format "c%02d = o" $i]
+ }
+ join $lExpr " AND "
+}
+
+
+do_execsql_test 5.0 "
+ CREATE TABLE a1( [cols 0 99] );
+"
+do_execsql_test 5.1 "
+ -- 63 column index
+ CREATE UNIQUE INDEX a1idx1 ON a1( [cols 0 62] );
+"
+do_execsql_test 5.2 "
+ -- 64 column index
+ CREATE UNIQUE INDEX a1idx2 ON a1( [cols 10 73] );
+"
+do_execsql_test 5.2 "
+ -- 65 column index
+ CREATE UNIQUE INDEX a1idx3 ON a1( [cols 20 84] );
+"
+
+do_test 5.3 {
+ foreach v {1 2 3 4 5 6} {
+ execsql "INSERT INTO a1 VALUES( [vals 100 $v] )"
+ }
+} {}
+
+do_execsql_test 5.4 {
+ CREATE TABLE a2(o);
+ INSERT INTO a2 VALUES(2), (5);
+}
+
+do_subquery_test 5.5 0 "
+ SELECT o FROM a2 WHERE EXISTS (
+ SELECT 1 FROM a1 WHERE [exprs 0 62]
+ )
+" {
+ 2 5
+}
+
+do_subquery_test 5.6 0 "
+ SELECT o FROM a2 WHERE EXISTS (
+ SELECT 1 FROM a1 WHERE [exprs 10 73]
+ )
+" {
+ 2 5
+}
+
+do_subquery_test 5.7 0 "
+ SELECT o FROM a2 WHERE EXISTS (
+ SELECT 1 FROM a1 WHERE [exprs 20 84]
+ )
+" {
+ 2 5
+}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 6.0 {
+ CREATE TABLE t1(a, b UNIQUE, c UNIQUE);
+ CREATE TABLE t2(a INfEGER PRIMARY KEY, b);
+ CREATE UNIQUE INDEX t2b ON t2(b);
+}
+
+do_catchsql_test 6.1 {
+ SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c COLLATE f = a)
+} {1 {no such collation sequence: f}}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 7.0 {
+ CREATE TABLE t1(x);
+ CREATE TABLE t2(y UNIQUE);
+
+ INSERT INTO t1 VALUES(1), (2);
+ INSERT INTO t2 VALUES(1), (3);
+
+ SELECT * FROM t1 one LEFT JOIN t1 two ON (one.x=two.x AND EXISTS (
+ SELECT 1 FROM t2 WHERE y=one.x
+ ));
+} {
+ 1 1
+ 2 {}
+}
+
+
+
+finish_test
diff --git a/test/existsexpr2.test b/test/existsexpr2.test
new file mode 100644
index 000000000..f7644bf80
--- /dev/null
+++ b/test/existsexpr2.test
@@ -0,0 +1,96 @@
+# 2024 June 14
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+source $testdir/lock_common.tcl
+set testprefix existsexpr2
+
+
+do_execsql_test 1.0 {
+ CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
+ INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
+ CREATE INDEX x1b ON x1(b);
+
+ CREATE TABLE x2(x, y);
+ INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
+}
+
+do_execsql_test 1.1 {
+ SELECT * FROM x1 WHERE EXISTS (SELECT 1 FROM x2 WHERE a!=123)
+} {1 2 3 4 5 6}
+
+do_execsql_test 1.2 {
+ CREATE TABLE x3(u, v);
+ CREATE INDEX x3u ON x3(u);
+ INSERT INTO x3 VALUES
+ (1, 1), (1, 2), (1, 3),
+ (2, 1), (2, 2), (2, 3);
+}
+
+do_execsql_test 1.3 {
+ SELECT * FROM x1 WHERE EXISTS (
+ SELECT 1 FROM x3 WHERE u IN (1, 2, 3, 4) AND v=b
+ );
+} {
+ 1 2
+}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 2.0 {
+ CREATE TABLE t1(a, b, c);
+ CREATE INDEX t1ab ON t1(a,b);
+
+ INSERT INTO t1 VALUES
+ ('abc', 1, 1),
+ ('abc', 2, 2),
+ ('abc', 2, 3),
+
+ ('def', 1, 1),
+ ('def', 2, 2),
+ ('def', 2, 3);
+
+ CREATE TABLE t2(x, y);
+ INSERT INTO t2 VALUES(1, 1), (2, 2), (3, 3);
+
+ ANALYZE;
+ DELETE FROM sqlite_stat1;
+ INSERT INTO sqlite_stat1 VALUES('t1','t1ab','10000 5000 2');
+ ANALYZE sqlite_master;
+}
+
+
+do_execsql_test 2.1 {
+ SELECT a,b,c FROM t1 WHERE b=2 ORDER BY a
+} {
+ abc 2 2
+ abc 2 3
+ def 2 2
+ def 2 3
+}
+
+do_execsql_test 2.2 {
+ SELECT x, y FROM t2 WHERE EXISTS (
+ SELECT 1 FROM t1 WHERE b=x
+ )
+} {
+ 1 1
+ 2 2
+}
+
+
+
+finish_test
+
+
diff --git a/test/existsfault.test b/test/existsfault.test
new file mode 100644
index 000000000..4b335d84c
--- /dev/null
+++ b/test/existsfault.test
@@ -0,0 +1,49 @@
+# 2024 May 25
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+source $testdir/lock_common.tcl
+source $testdir/malloc_common.tcl
+set testprefix existsfault
+
+db close
+sqlite3_shutdown
+sqlite3_config_lookaside 0 0
+sqlite3_initialize
+autoinstall_test_functions
+sqlite3 db test.db
+
+do_execsql_test 1.0 {
+ CREATE TABLE x1(a, b);
+ INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
+ CREATE UNIQUE INDEX x1a ON x1(a);
+ CREATE INDEX x1b ON x1(b);
+
+ CREATE TABLE x2(x, y);
+ INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
+}
+
+do_faultsim_test 1 -faults oom* -prep {
+ sqlite3 db test.db
+ execsql { SELECT * FROM sqlite_schema }
+} -body {
+ execsql {
+ SELECT count(*) FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) AND y!=11
+ }
+} -test {
+ faultsim_test_result {0 3}
+}
+
+finish_test
+
+
diff --git a/test/incrblob4.test b/test/incrblob4.test
index 31040e91b..c9bcee8a3 100644
--- a/test/incrblob4.test
+++ b/test/incrblob4.test
@@ -108,6 +108,8 @@ close $blob
#-------------------------------------------------------------------------
+ifcapable preupdate {
+
reset_db
do_execsql_test 5.1 {
CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
@@ -127,7 +129,8 @@ do_test 5.2.1 {
do_test 5.2.2 {
puts -nonewline $blob "world"
- list [catch { flush $blob } msg] $msg
+ set rc [catch { flush $blob } msg]
+ list $rc [regsub {input/output} $msg {I/O}]
} "1 {error flushing \"$blob\": I/O error}"
catch { close $blob }
@@ -147,7 +150,8 @@ do_test 5.3.1 {
do_test 5.3.2 {
puts -nonewline $blob "world"
- list [catch { flush $blob } msg] $msg
+ set rc [catch { flush $blob } msg]
+ list $rc [regsub {input/output} $msg {I/O}]
} "1 {error flushing \"$blob\": I/O error}"
catch { close $blob }
@@ -188,13 +192,16 @@ do_test 5.4.3 {
do_test 5.4.4 {
puts -nonewline $blob "world"
- list [catch { flush $blob } msg] $msg
+ set rc [catch { flush $blob } msg]
+ list $rc [regsub {input/output} $msg {I/O}]
} "1 {error flushing \"$blob\": I/O error}"
catch { close $blob }
catchsql { ROLLBACK }
-do_test 5.3.3 {
+do_test 5.4.5 {
set ::preupdate_count
} {2}
+}
+
finish_test
diff --git a/test/json101.test b/test/json101.test
index e22902f86..7582d14a6 100644
--- a/test/json101.test
+++ b/test/json101.test
@@ -892,15 +892,15 @@ do_execsql_test json101-13.100 {
INSERT INTO t2(id,json) VALUES(4,'{"value":4}');
INSERT INTO t2(id,json) VALUES(5,'{"value":5}');
INSERT INTO t2(id,json) VALUES(6,'{"value":6}');
- SELECT * FROM t1 CROSS JOIN t2
+ SELECT *, 'NL' FROM t1 CROSS JOIN t2
WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z
WHERE Z.value==t2.id);
-} {1 {{"items":[3,5]}} 3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}}}
+} {1 {{"items":[3,5]}} 3 {{"value":3}} NL 1 {{"items":[3,5]}} 5 {{"value":5}} NL}
do_execsql_test json101-13.110 {
- SELECT * FROM t2 CROSS JOIN t1
+ SELECT *, 'NL' FROM t2 CROSS JOIN t1
WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z
WHERE Z.value==t2.id);
-} {3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}} 1 {{"items":[3,5]}}}
+} {3 {{"value":3}} 1 {{"items":[3,5]}} NL 5 {{"value":5}} 1 {{"items":[3,5]}} NL}
# 2018-05-16
# Incorrect fullkey output from json_each()
diff --git a/test/notnull2.test b/test/notnull2.test
index 09161efbd..67d7c26a8 100644
--- a/test/notnull2.test
+++ b/test/notnull2.test
@@ -66,7 +66,7 @@ do_vmstep_test 1.5.2 {
SELECT count(*) FROM t2 WHERE EXISTS(
SELECT 1 FROM t1 WHERE t1.a=450 AND t2.c IS NULL
)
-} +8000 {0}
+} 4000 {0}
#-------------------------------------------------------------------------
reset_db
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-10 06:25:02 +0000