xref: /sqlite-3.40.0/test/tkt-31338dca7e.test (revision 21172c4c) 
1# 2009 December 16
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.
12#
13# This file implements tests to verify that ticket [31338dca7e] has been
14# fixed.  Ticket [31338dca7e] demonstrates problems with the OR-clause
15# optimization in joins where the WHERE clause is of the form
16#
17#     (x AND y) OR z
18#
19# And the x and y subterms from from different tables of the join.
20#
21
22set testdir [file dirname $argv0]
23source $testdir/tester.tcl
24
25do_test tkt-31338-1.1 {
26  db eval {
27    CREATE TABLE t1(x);
28    CREATE TABLE t2(y);
29    INSERT INTO t1 VALUES(111);
30    INSERT INTO t1 VALUES(222);
31    INSERT INTO t2 VALUES(333);
32    INSERT INTO t2 VALUES(444);
33    SELECT * FROM t1, t2
34     WHERE (x=111 AND y!=444) OR x=222
35     ORDER BY x, y;
36  }
37} {111 333 222 333 222 444}
38
39do_test tkt-31338-1.2 {
40  db eval {
41    CREATE INDEX t1x ON t1(x);
42    SELECT * FROM t1, t2
43     WHERE (x=111 AND y!=444) OR x=222
44     ORDER BY x, y;
45  }
46} {111 333 222 333 222 444}
47
48do_test tkt-31338-2.1 {
49  db eval {
50    CREATE TABLE t3(v,w);
51    CREATE TABLE t4(x,y);
52    CREATE TABLE t5(z);
53    INSERT INTO t3 VALUES(111,222);
54    INSERT INTO t3 VALUES(333,444);
55    INSERT INTO t4 VALUES(222,333);
56    INSERT INTO t4 VALUES(444,555);
57    INSERT INTO t5 VALUES(888);
58    INSERT INTO t5 VALUES(999);
59
60    SELECT * FROM t3, t4, t5
61     WHERE (v=111 AND x=w AND z!=999) OR (v=333 AND x=444)
62     ORDER BY v, w, x, y, z;
63  }
64} {111 222 222 333 888 333 444 444 555 888 333 444 444 555 999}
65
66do_test tkt-31338-2.2 {
67  db eval {
68   CREATE INDEX t3v ON t3(v);
69   CREATE INDEX t4x ON t4(x);
70    SELECT * FROM t3, t4, t5
71     WHERE (v=111 AND x=w AND z!=999) OR (v=333 AND x=444)
72     ORDER BY v, w, x, y, z;
73  }
74} {111 222 222 333 888 333 444 444 555 888 333 444 444 555 999}
75
76
77# Ticket [2c2de252666662f5459904fc33a9f2956cbff23c]
78#
79do_test tkt-31338-3.1 {
80  foreach x [db eval {SELECT name FROM sqlite_master WHERE type='table'}] {
81     db eval "DROP TABLE $x"
82  }
83  db eval {
84    CREATE TABLE t1(a,b,c,d);
85    CREATE TABLE t2(e,f);
86    INSERT INTO t1 VALUES(1,2,3,4);
87    INSERT INTO t2 VALUES(10,-8);
88    CREATE INDEX t1a ON t1(a);
89    CREATE INDEX t1b ON t1(b);
90    CREATE TABLE t3(g);
91    INSERT INTO t3 VALUES(4);
92    CREATE TABLE t4(h);
93    INSERT INTO t4 VALUES(5);
94
95    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
96     WHERE (a=1 AND h=4)
97         OR (b IN (
98               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
99               GROUP BY e
100            ));
101  }
102} {4 1 2 3 4 {}}
103do_test tkt-31338-3.2 {
104  db eval {
105    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
106     WHERE (a=1 AND h=4)
107         OR (b=2 AND b NOT IN (
108               SELECT x+1 FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
109               GROUP BY e
110            ));
111  }
112} {4 1 2 3 4 {}}
113do_test tkt-31338-3.3 {
114  db eval {
115    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
116     WHERE (+a=1 AND h=4)
117         OR (b IN (
118               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
119               GROUP BY e
120            ));
121  }
122} {4 1 2 3 4 {}}
123do_test tkt-31338-3.4 {
124  db eval {
125    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
126     WHERE (a=1 AND h=4)
127         OR (+b IN (
128               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
129               GROUP BY e
130            ));
131  }
132} {4 1 2 3 4 {}}
133
134do_test tkt-31338-3.5 {
135  db eval {
136    CREATE TABLE t5(a,b,c,d,e,f);
137    CREATE TABLE t6(g,h);
138    CREATE TRIGGER t6r AFTER INSERT ON t6 BEGIN
139      INSERT INTO t5
140        SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
141         WHERE (a=1 AND h=4)
142            OR (b IN (
143               SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
144               GROUP BY e
145            ));
146    END;
147    INSERT INTO t6 VALUES(88,99);
148    SELECT * FROM t5;
149  }
150} {4 1 2 3 4 {}}
151
152do_test tkt-31338-3.6 {
153  db eval {
154    INSERT INTO t1 VALUES(2,4,3,4);
155    INSERT INTO t1 VALUES(99,101,3,4);
156    INSERT INTO t1 VALUES(98,97,3,4);
157    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
158     WHERE (a=1 AND h=4)
159         OR (b IN (
160               SELECT x+a FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2)
161               GROUP BY e
162            ));
163  }
164} {4 2 4 3 4 {} 4 99 101 3 4 {}}
165
166do_test tkt-31338-3.7 {
167  db eval {
168    SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h
169     WHERE (a=1 AND h=4)
170         OR (b IN (
171               SELECT x FROM (SELECT e+f+a AS x, e FROM t2 ORDER BY 1 LIMIT 2)
172               GROUP BY e
173            ));
174  }
175} {4 2 4 3 4 {} 4 99 101 3 4 {}}
176
177
178finish_test
179