summaryrefslogtreecommitdiff
path: root/test/subquery.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/subquery.test')
-rw-r--r--test/subquery.test499
1 files changed, 499 insertions, 0 deletions
diff --git a/test/subquery.test b/test/subquery.test
new file mode 100644
index 0000000..169ceda
--- /dev/null
+++ b/test/subquery.test
@@ -0,0 +1,499 @@
+# 2005 January 19
+#
+# 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.
+#
+#*************************************************************************
+# This file implements regression tests for SQLite library. The
+# focus of this script is testing correlated subqueries
+#
+# $Id: subquery.test,v 1.17 2009/01/09 01:12:28 drh Exp $
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+ifcapable !subquery {
+ finish_test
+ return
+}
+
+do_test subquery-1.1 {
+ execsql {
+ BEGIN;
+ CREATE TABLE t1(a,b);
+ INSERT INTO t1 VALUES(1,2);
+ INSERT INTO t1 VALUES(3,4);
+ INSERT INTO t1 VALUES(5,6);
+ INSERT INTO t1 VALUES(7,8);
+ CREATE TABLE t2(x,y);
+ INSERT INTO t2 VALUES(1,1);
+ INSERT INTO t2 VALUES(3,9);
+ INSERT INTO t2 VALUES(5,25);
+ INSERT INTO t2 VALUES(7,49);
+ COMMIT;
+ }
+ execsql {
+ SELECT a, (SELECT y FROM t2 WHERE x=a) FROM t1 WHERE b<8
+ }
+} {1 1 3 9 5 25}
+do_test subquery-1.2 {
+ execsql {
+ UPDATE t1 SET b=b+(SELECT y FROM t2 WHERE x=a);
+ SELECT * FROM t1;
+ }
+} {1 3 3 13 5 31 7 57}
+
+do_test subquery-1.3 {
+ execsql {
+ SELECT b FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE y=a)
+ }
+} {3}
+do_test subquery-1.4 {
+ execsql {
+ SELECT b FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE y=a)
+ }
+} {13 31 57}
+
+# Simple tests to make sure correlated subqueries in WHERE clauses
+# are used by the query optimizer correctly.
+do_test subquery-1.5 {
+ execsql {
+ SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
+ }
+} {1 1 3 3 5 5 7 7}
+do_test subquery-1.6 {
+ execsql {
+ CREATE INDEX i1 ON t1(a);
+ SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
+ }
+} {1 1 3 3 5 5 7 7}
+do_test subquery-1.7 {
+ execsql {
+ SELECT a, x FROM t2, t1 WHERE t1.a = (SELECT x);
+ }
+} {1 1 3 3 5 5 7 7}
+
+# Try an aggregate in both the subquery and the parent query.
+do_test subquery-1.8 {
+ execsql {
+ SELECT count(*) FROM t1 WHERE a > (SELECT count(*) FROM t2);
+ }
+} {2}
+
+# Test a correlated subquery disables the "only open the index" optimization.
+do_test subquery-1.9.1 {
+ execsql {
+ SELECT (y*2)>b FROM t1, t2 WHERE a=x;
+ }
+} {0 1 1 1}
+do_test subquery-1.9.2 {
+ execsql {
+ SELECT a FROM t1 WHERE (SELECT (y*2)>b FROM t2 WHERE a=x);
+ }
+} {3 5 7}
+
+# Test that the flattening optimization works with subquery expressions.
+do_test subquery-1.10.1 {
+ execsql {
+ SELECT (SELECT a), b FROM t1;
+ }
+} {1 3 3 13 5 31 7 57}
+do_test subquery-1.10.2 {
+ execsql {
+ SELECT * FROM (SELECT (SELECT a), b FROM t1);
+ }
+} {1 3 3 13 5 31 7 57}
+do_test subquery-1.10.3 {
+ execsql {
+ SELECT * FROM (SELECT (SELECT sum(a) FROM t1));
+ }
+} {16}
+do_test subquery-1.10.4 {
+ execsql {
+ CREATE TABLE t5 (val int, period text PRIMARY KEY);
+ INSERT INTO t5 VALUES(5, '2001-3');
+ INSERT INTO t5 VALUES(10, '2001-4');
+ INSERT INTO t5 VALUES(15, '2002-1');
+ INSERT INTO t5 VALUES(5, '2002-2');
+ INSERT INTO t5 VALUES(10, '2002-3');
+ INSERT INTO t5 VALUES(15, '2002-4');
+ INSERT INTO t5 VALUES(10, '2003-1');
+ INSERT INTO t5 VALUES(5, '2003-2');
+ INSERT INTO t5 VALUES(25, '2003-3');
+ INSERT INTO t5 VALUES(5, '2003-4');
+
+ SELECT period, vsum
+ FROM (SELECT
+ a.period,
+ (select sum(val) from t5 where period between a.period and '2002-4') vsum
+ FROM t5 a where a.period between '2002-1' and '2002-4')
+ WHERE vsum < 45 ;
+ }
+} {2002-2 30 2002-3 25 2002-4 15}
+do_test subquery-1.10.5 {
+ execsql {
+ SELECT period, vsum from
+ (select a.period,
+ (select sum(val) from t5 where period between a.period and '2002-4') vsum
+ FROM t5 a where a.period between '2002-1' and '2002-4')
+ WHERE vsum < 45 ;
+ }
+} {2002-2 30 2002-3 25 2002-4 15}
+do_test subquery-1.10.6 {
+ execsql {
+ DROP TABLE t5;
+ }
+} {}
+
+
+
+#------------------------------------------------------------------
+# The following test cases - subquery-2.* - are not logically
+# organized. They're here largely because they were failing during
+# one stage of development of sub-queries.
+#
+do_test subquery-2.1 {
+ execsql {
+ SELECT (SELECT 10);
+ }
+} {10}
+do_test subquery-2.2.1 {
+ execsql {
+ CREATE TABLE t3(a PRIMARY KEY, b);
+ INSERT INTO t3 VALUES(1, 2);
+ INSERT INTO t3 VALUES(3, 1);
+ }
+} {}
+do_test subquery-2.2.2 {
+ execsql {
+ SELECT * FROM t3 WHERE a IN (SELECT b FROM t3);
+ }
+} {1 2}
+do_test subquery-2.2.3 {
+ execsql {
+ DROP TABLE t3;
+ }
+} {}
+do_test subquery-2.3.1 {
+ execsql {
+ CREATE TABLE t3(a TEXT);
+ INSERT INTO t3 VALUES('10');
+ }
+} {}
+do_test subquery-2.3.2 {
+ execsql {
+ SELECT a IN (10.0, 20) FROM t3;
+ }
+} {0}
+do_test subquery-2.3.3 {
+ execsql {
+ DROP TABLE t3;
+ }
+} {}
+do_test subquery-2.4.1 {
+ execsql {
+ CREATE TABLE t3(a TEXT);
+ INSERT INTO t3 VALUES('XX');
+ }
+} {}
+do_test subquery-2.4.2 {
+ execsql {
+ SELECT count(*) FROM t3 WHERE a IN (SELECT 'XX')
+ }
+} {1}
+do_test subquery-2.4.3 {
+ execsql {
+ DROP TABLE t3;
+ }
+} {}
+do_test subquery-2.5.1 {
+ execsql {
+ CREATE TABLE t3(a INTEGER);
+ INSERT INTO t3 VALUES(10);
+
+ CREATE TABLE t4(x TEXT);
+ INSERT INTO t4 VALUES('10.0');
+ }
+} {}
+do_test subquery-2.5.2 {
+ # In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator
+ # has text affinity and the LHS has integer affinity. The rule is
+ # that we try to convert both sides to an integer before doing the
+ # comparision. Hence, the integer value 10 in t3 will compare equal
+ # to the string value '10.0' in t4 because the t4 value will be
+ # converted into an integer.
+ execsql {
+ SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
+ }
+} {10.0}
+do_test subquery-2.5.3.1 {
+ # The t4i index cannot be used to resolve the "x IN (...)" constraint
+ # because the constraint has integer affinity but t4i has text affinity.
+ execsql {
+ CREATE INDEX t4i ON t4(x);
+ SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
+ }
+} {10.0}
+do_test subquery-2.5.3.2 {
+ # Verify that the t4i index was not used in the previous query
+ set ::sqlite_query_plan
+} {t4 {}}
+do_test subquery-2.5.4 {
+ execsql {
+ DROP TABLE t3;
+ DROP TABLE t4;
+ }
+} {}
+
+#------------------------------------------------------------------
+# The following test cases - subquery-3.* - test tickets that
+# were raised during development of correlated subqueries.
+#
+
+# Ticket 1083
+ifcapable view {
+ do_test subquery-3.1 {
+ catchsql { DROP TABLE t1; }
+ catchsql { DROP TABLE t2; }
+ execsql {
+ CREATE TABLE t1(a,b);
+ INSERT INTO t1 VALUES(1,2);
+ CREATE VIEW v1 AS SELECT b FROM t1 WHERE a>0;
+ CREATE TABLE t2(p,q);
+ INSERT INTO t2 VALUES(2,9);
+ SELECT * FROM v1 WHERE EXISTS(SELECT * FROM t2 WHERE p=v1.b);
+ }
+ } {2}
+ do_test subquery-3.1.1 {
+ execsql {
+ SELECT * FROM v1 WHERE EXISTS(SELECT 1);
+ }
+ } {2}
+} else {
+ catchsql { DROP TABLE t1; }
+ catchsql { DROP TABLE t2; }
+ execsql {
+ CREATE TABLE t1(a,b);
+ INSERT INTO t1 VALUES(1,2);
+ CREATE TABLE t2(p,q);
+ INSERT INTO t2 VALUES(2,9);
+ }
+}
+
+# Ticket 1084
+do_test subquery-3.2 {
+ catchsql {
+ CREATE TABLE t1(a,b);
+ INSERT INTO t1 VALUES(1,2);
+ }
+ execsql {
+ SELECT (SELECT t1.a) FROM t1;
+ }
+} {1}
+
+# Test Cases subquery-3.3.* test correlated subqueries where the
+# parent query is an aggregate query. Ticket #1105 is an example
+# of such a query.
+#
+do_test subquery-3.3.1 {
+ execsql {
+ SELECT a, (SELECT b) FROM t1 GROUP BY a;
+ }
+} {1 2}
+do_test subquery-3.3.2 {
+ catchsql {DROP TABLE t2}
+ execsql {
+ CREATE TABLE t2(c, d);
+ INSERT INTO t2 VALUES(1, 'one');
+ INSERT INTO t2 VALUES(2, 'two');
+ SELECT a, (SELECT d FROM t2 WHERE a=c) FROM t1 GROUP BY a;
+ }
+} {1 one}
+do_test subquery-3.3.3 {
+ execsql {
+ INSERT INTO t1 VALUES(2, 4);
+ SELECT max(a), (SELECT d FROM t2 WHERE a=c) FROM t1;
+ }
+} {2 two}
+do_test subquery-3.3.4 {
+ execsql {
+ SELECT a, (SELECT (SELECT d FROM t2 WHERE a=c)) FROM t1 GROUP BY a;
+ }
+} {1 one 2 two}
+do_test subquery-3.3.5 {
+ execsql {
+ SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1;
+ }
+} {1 1 2 1}
+
+#------------------------------------------------------------------
+# These tests - subquery-4.* - use the TCL statement cache to try
+# and expose bugs to do with re-using statements that have been
+# passed to sqlite3_reset().
+#
+# One problem was that VDBE memory cells were not being initialised
+# to NULL on the second and subsequent executions.
+#
+do_test subquery-4.1.1 {
+ execsql {
+ SELECT (SELECT a FROM t1);
+ }
+} {1}
+do_test subquery-4.2 {
+ execsql {
+ DELETE FROM t1;
+ SELECT (SELECT a FROM t1);
+ }
+} {{}}
+do_test subquery-4.2.1 {
+ execsql {
+ CREATE TABLE t3(a PRIMARY KEY);
+ INSERT INTO t3 VALUES(10);
+ }
+ execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
+} {}
+do_test subquery-4.2.2 {
+ execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
+} {}
+
+#------------------------------------------------------------------
+# The subquery-5.* tests make sure string literals in double-quotes
+# are handled efficiently. Double-quote literals are first checked
+# to see if they match any column names. If there is not column name
+# match then those literals are used a string constants. When a
+# double-quoted string appears, we want to make sure that the search
+# for a matching column name did not cause an otherwise static subquery
+# to become a dynamic (correlated) subquery.
+#
+do_test subquery-5.1 {
+ proc callcntproc {n} {
+ incr ::callcnt
+ return $n
+ }
+ set callcnt 0
+ db function callcnt callcntproc
+ execsql {
+ CREATE TABLE t4(x,y);
+ INSERT INTO t4 VALUES('one',1);
+ INSERT INTO t4 VALUES('two',2);
+ INSERT INTO t4 VALUES('three',3);
+ INSERT INTO t4 VALUES('four',4);
+ CREATE TABLE t5(a,b);
+ INSERT INTO t5 VALUES(1,11);
+ INSERT INTO t5 VALUES(2,22);
+ INSERT INTO t5 VALUES(3,33);
+ INSERT INTO t5 VALUES(4,44);
+ SELECT b FROM t5 WHERE a IN
+ (SELECT callcnt(y)+0 FROM t4 WHERE x="two")
+ }
+} {22}
+do_test subquery-5.2 {
+ # This is the key test. The subquery should have only run once. If
+ # The double-quoted identifier "two" were causing the subquery to be
+ # processed as a correlated subquery, then it would have run 4 times.
+ set callcnt
+} {1}
+
+
+# Ticket #1380. Make sure correlated subqueries on an IN clause work
+# correctly when the left-hand side of the IN operator is constant.
+#
+do_test subquery-6.1 {
+ set callcnt 0
+ execsql {
+ SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=y)
+ }
+} {one two three four}
+do_test subquery-6.2 {
+ set callcnt
+} {4}
+do_test subquery-6.3 {
+ set callcnt 0
+ execsql {
+ SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=1)
+ }
+} {one two three four}
+do_test subquery-6.4 {
+ set callcnt
+} {1}
+
+if 0 { ############# disable until we get #2652 fixed
+# Ticket #2652. Allow aggregate functions of outer queries inside
+# a non-aggregate subquery.
+#
+do_test subquery-7.1 {
+ execsql {
+ CREATE TABLE t7(c7);
+ INSERT INTO t7 VALUES(1);
+ INSERT INTO t7 VALUES(2);
+ INSERT INTO t7 VALUES(3);
+ CREATE TABLE t8(c8);
+ INSERT INTO t8 VALUES(100);
+ INSERT INTO t8 VALUES(200);
+ INSERT INTO t8 VALUES(300);
+ CREATE TABLE t9(c9);
+ INSERT INTO t9 VALUES(10000);
+ INSERT INTO t9 VALUES(20000);
+ INSERT INTO t9 VALUES(30000);
+
+ SELECT (SELECT c7+c8 FROM t7) FROM t8;
+ }
+} {101 201 301}
+do_test subquery-7.2 {
+ execsql {
+ SELECT (SELECT max(c7)+c8 FROM t7) FROM t8;
+ }
+} {103 203 303}
+do_test subquery-7.3 {
+ execsql {
+ SELECT (SELECT c7+max(c8) FROM t8) FROM t7
+ }
+} {301}
+do_test subquery-7.4 {
+ execsql {
+ SELECT (SELECT max(c7)+max(c8) FROM t8) FROM t7
+ }
+} {303}
+do_test subquery-7.5 {
+ execsql {
+ SELECT (SELECT c8 FROM t8 WHERE rowid=max(c7)) FROM t7
+ }
+} {300}
+do_test subquery-7.6 {
+ execsql {
+ SELECT (SELECT (SELECT max(c7+c8+c9) FROM t9) FROM t8) FROM t7
+ }
+} {30101 30102 30103}
+do_test subquery-7.7 {
+ execsql {
+ SELECT (SELECT (SELECT c7+max(c8+c9) FROM t9) FROM t8) FROM t7
+ }
+} {30101 30102 30103}
+do_test subquery-7.8 {
+ execsql {
+ SELECT (SELECT (SELECT max(c7)+c8+c9 FROM t9) FROM t8) FROM t7
+ }
+} {10103}
+do_test subquery-7.9 {
+ execsql {
+ SELECT (SELECT (SELECT c7+max(c8)+c9 FROM t9) FROM t8) FROM t7
+ }
+} {10301 10302 10303}
+do_test subquery-7.10 {
+ execsql {
+ SELECT (SELECT (SELECT c7+c8+max(c9) FROM t9) FROM t8) FROM t7
+ }
+} {30101 30102 30103}
+do_test subquery-7.11 {
+ execsql {
+ SELECT (SELECT (SELECT max(c7)+max(c8)+max(c9) FROM t9) FROM t8) FROM t7
+ }
+} {30303}
+} ;############# Disabled
+
+finish_test
generated by cgit v1.2.3 (git 2.25.1) at 2024-06-01 21:05:48 +0000