xref: /sqlite-3.40.0/test/whereL.test (revision e89feee5) 
1# 2018-07-26
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 the WHERE-clause constant propagation
13# optimization.
14#
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17set ::testprefix whereL
18
19do_execsql_test 100 {
20  CREATE TABLE t1(a INT PRIMARY KEY, b, c, d, e);
21  CREATE TABLE t2(a INT PRIMARY KEY, f, g, h, i);
22  CREATE TABLE t3(a INT PRIMARY KEY, j, k, l, m);
23  CREATE VIEW v4 AS SELECT * FROM t2 UNION ALL SELECT * FROM t3;
24}
25do_eqp_test 110 {
26  SELECT * FROM t1, v4 WHERE t1.a=?1 AND v4.a=t1.a;
27} {
28  QUERY PLAN
29  |--MATERIALIZE xxxxxx
30  |  `--COMPOUND QUERY
31  |     |--LEFT-MOST SUBQUERY
32  |     |  `--SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
33  |     `--UNION ALL
34  |        `--SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (a=?)
35  |--SCAN SUBQUERY xxxxxx
36  `--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
37}
38
39# The scan of the t1 table goes first since that enables the ORDER BY
40# sort to be omitted.  This would not be possible without constant
41# propagation because without it the t1 table would depend on t3.
42#
43do_eqp_test 120 {
44  SELECT * FROM t1, t2, t3
45   WHERE t1.a=t2.a AND t2.a=t3.j AND t3.j=5
46  ORDER BY t1.a;
47} {
48  QUERY PLAN
49  |--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
50  |--SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
51  `--SCAN TABLE t3
52}
53
54# Constant propagation in the face of collating sequences:
55#
56do_execsql_test 200 {
57  CREATE TABLE c3(x COLLATE binary, y COLLATE nocase, z COLLATE binary);
58  CREATE INDEX c3x ON c3(x);
59  INSERT INTO c3 VALUES('ABC', 'ABC', 'abc');
60  SELECT * FROM c3 WHERE x=y AND y=z AND z='abc';
61} {ABC ABC abc}
62
63# If the constants are blindly propagated, as shown in the following
64# query, the wrong answer results:
65#
66do_execsql_test 201 {
67  SELECT * FROM c3 WHERE x='abc' AND y='abc' AND z='abc';
68} {}
69
70# Constant propagation caused an incorrect answer in the following
71# query.  (Reported by Bentley system on 2018-08-09.)
72#
73do_execsql_test 300 {
74  CREATE TABLE A(id INTEGER PRIMARY KEY, label TEXT);
75  CREATE TABLE B(id INTEGER PRIMARY KEY, label TEXT, Aid INTEGER);
76  CREATE TABLE C(
77    id INTEGER PRIMARY KEY,
78    xx INTEGER NOT NULL,
79    yy INTEGER,
80    zz INTEGER
81  );
82  CREATE UNIQUE INDEX x2 ON C(yy);
83  CREATE UNIQUE INDEX x4 ON C(yy, zz);
84  INSERT INTO A(id) VALUES(1);
85  INSERT INTO B(id) VALUES(2);
86  INSERT INTO C(id,xx,yy,zz) VALUES(99,50,1,2);
87  SELECT 1
88    FROM A,
89         (SELECT id,xx,yy,zz FROM C) subq,
90         B
91   WHERE A.id='1'
92     AND A.id=subq.yy
93     AND B.id=subq.zz;
94} {1}
95do_execsql_test 301 {
96  SELECT 1
97    FROM A,
98         (SELECT id,xx,yy,zz FROM C) subq,
99         B
100   WHERE A.id=1
101     AND A.id=subq.yy
102     AND B.id=subq.zz;
103} {1}
104do_execsql_test 302 {
105  SELECT 1
106    FROM A,
107         (SELECT id,yy,zz FROM C) subq,
108         B
109   WHERE A.id='1'
110     AND A.id=subq.yy
111     AND B.id=subq.zz;
112} {1}
113
114# 2018-10-25: Ticket [cf5ed20f]
115# Incorrect join result with duplicate WHERE clause constraint.
116#
117do_execsql_test 400 {
118  CREATE TABLE x(a, b, c);
119  CREATE TABLE y(a, b);
120  INSERT INTO x VALUES (1, 0, 1);
121  INSERT INTO y VALUES (1, 2);
122  SELECT x.a FROM x JOIN y ON x.c = y.a WHERE x.b = 1 AND x.b = 1;
123} {}
124
125finish_test
126