AryaWu/sqlite

1# 2024 June 142#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11#12 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15source $testdir/lock_common.tcl16set testprefix existsexpr217 18 19do_execsql_test 1.0 {20  CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;21  INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);22  CREATE INDEX x1b ON x1(b);23 24  CREATE TABLE x2(x, y);25  INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);26}27 28do_execsql_test 1.1 {29  SELECT * FROM x1 WHERE EXISTS (SELECT 1 FROM x2 WHERE a!=123)30} {1 2   3 4   5 6}31 32do_execsql_test 1.2 {33  CREATE TABLE x3(u, v);34  CREATE INDEX x3u ON x3(u);35  INSERT INTO x3 VALUES36    (1, 1), (1, 2), (1, 3),37    (2, 1), (2, 2), (2, 3);38}39 40do_execsql_test 1.3 {41  SELECT * FROM x1 WHERE EXISTS (42    SELECT 1 FROM x3 WHERE u IN (1, 2, 3, 4) AND v=b43  );44} {45  1 246}47 48#-------------------------------------------------------------------------49#50reset_db51do_execsql_test 2.0 {52  CREATE TABLE t1(a, b, c);53  CREATE INDEX t1ab ON t1(a,b);54 55  INSERT INTO t1 VALUES56      ('abc', 1, 1),57      ('abc', 2, 2),58      ('abc', 2, 3),59 60      ('def', 1, 1),61      ('def', 2, 2),62      ('def', 2, 3);63 64  CREATE TABLE t2(x, y);65  INSERT INTO t2 VALUES(1, 1), (2, 2), (3, 3);66 67  ANALYZE;68  DELETE FROM sqlite_stat1;69  INSERT INTO sqlite_stat1 VALUES('t1','t1ab','10000 5000 2');70  ANALYZE sqlite_master;71}72 73 74do_execsql_test 2.1 {75  SELECT a,b,c FROM t1 WHERE b=2 ORDER BY a76} {77  abc 2 278  abc 2 379  def 2 280  def 2 381}82 83do_execsql_test 2.2 {84  SELECT x, y FROM t2 WHERE EXISTS (85    SELECT 1 FROM t1 WHERE b=x86  )87} {88  1 189  2 290}91 92 93 94finish_test95 96 97