xref: /sqlite-3.40.0/test/rowvalue3.test (revision 8da209b1) 
1# 2016 June 17
2#
3# The author disclaims copyright to this source code.  In place of
4# 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# This file implements regression tests for SQLite library.  The
12# focus of this file is testing "(...) IN (SELECT ...)" expressions
13# where the SELECT statement returns more than one column.
14#
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18set ::testprefix rowvalue3
19
20
21do_execsql_test 1.0 {
22  CREATE TABLE t1(a, b, c);
23  CREATE INDEX i1 ON t1(a, b);
24  INSERT INTO t1 VALUES(1, 2, 3);
25  INSERT INTO t1 VALUES(4, 5, 6);
26  INSERT INTO t1 VALUES(7, 8, 9);
27}
28
29foreach {tn sql res} {
30  1  "SELECT 1 WHERE (4, 5) IN (SELECT a, b FROM t1)"  1
31  2  "SELECT 1 WHERE (5, 5) IN (SELECT a, b FROM t1)"  {}
32  3  "SELECT 1 WHERE (5, 4) IN (SELECT a, b FROM t1)"  {}
33  4  "SELECT 1 WHERE (5, 4) IN (SELECT b, a FROM t1)"  1
34  5  "SELECT 1 WHERE (SELECT a, b FROM t1 WHERE c=6) IN (SELECT a, b FROM t1)" 1
35  6  "SELECT (5, 4) IN (SELECT a, b FROM t1)" 0
36  7  "SELECT 1 WHERE (5, 4) IN (SELECT +b, +a FROM t1)"  1
37  8  "SELECT (5, 4) IN (SELECT +b, +a FROM t1)"  1
38  9  "SELECT (1, 2) IN (SELECT rowid, b FROM t1)"  1
39  10 "SELECT 1 WHERE (1, 2) IN (SELECT rowid, b FROM t1)"  1
40  11 "SELECT 1 WHERE (1, NULL) IN (SELECT rowid, b FROM t1)"  {}
41} {
42  do_execsql_test 1.$tn $sql $res
43}
44
45#-------------------------------------------------------------------------
46
47do_execsql_test 2.0 {
48  CREATE TABLE z1(x, y, z);
49  CREATE TABLE kk(a, b);
50
51  INSERT INTO z1 VALUES('a', 'b', 'c');
52  INSERT INTO z1 VALUES('d', 'e', 'f');
53  INSERT INTO z1 VALUES('g', 'h', 'i');
54
55  -- INSERT INTO kk VALUES('y', 'y');
56  INSERT INTO kk VALUES('d', 'e');
57  -- INSERT INTO kk VALUES('x', 'x');
58
59}
60
61foreach {tn idx} {
62  1 { }
63  2 {  CREATE INDEX z1idx ON z1(x, y) }
64  3 {  CREATE UNIQUE INDEX z1idx ON z1(x, y) }
65} {
66  execsql "DROP INDEX IF EXISTS z1idx"
67  execsql $idx
68
69  do_execsql_test 2.$tn.1 {
70    SELECT * FROM z1 WHERE x IN (SELECT a FROM kk)
71  } {d e f}
72
73  do_execsql_test 2.$tn.2 {
74    SELECT * FROM z1 WHERE (x,y) IN (SELECT a, b FROM kk)
75  } {d e f}
76
77  do_execsql_test 2.$tn.3 {
78    SELECT * FROM z1 WHERE (x, +y) IN (SELECT a, b FROM kk)
79  } {d e f}
80
81  do_execsql_test 2.$tn.4 {
82    SELECT * FROM z1 WHERE (x, +y) IN (SELECT a, b||'x' FROM kk)
83  } {}
84
85  do_execsql_test 2.$tn.5 {
86    SELECT * FROM z1 WHERE (+x, y) IN (SELECT a, b FROM kk)
87  } {d e f}
88}
89
90explain_i {
91  SELECT * FROM z1 WHERE (x, y) IN (SELECT a, b FROM kk)
92}
93
94finish_test
95
96
97
98