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
77finish_test
78