xref: /sqlite-3.40.0/test/pushdown.test (revision cb6acda9) 
1# 2017 April 29
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
12set testdir [file dirname $argv0]
13source $testdir/tester.tcl
14set testprefix pushdown
15
16do_execsql_test 1.0 {
17  CREATE TABLE t1(a, b, c);
18  INSERT INTO t1 VALUES(1, 'b1', 'c1');
19  INSERT INTO t1 VALUES(2, 'b2', 'c2');
20  INSERT INTO t1 VALUES(3, 'b3', 'c3');
21  INSERT INTO t1 VALUES(4, 'b4', 'c4');
22  CREATE INDEX i1 ON t1(a, c);
23}
24
25proc f {val} {
26  lappend ::L $val
27  return 0
28}
29db func f f
30
31do_test 1.1 {
32  set L [list]
33  execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }
34  set L
35} {c2}
36
37do_test 1.2 {
38  set L [list]
39  execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
40  set L
41} {c3}
42
43do_execsql_test 1.3 {
44  DROP INDEX i1;
45  CREATE INDEX i1 ON t1(a, b);
46}
47do_test 1.4 {
48  set L [list]
49  execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }
50  set L
51} {b2}
52
53do_test 1.5 {
54  set L [list]
55  execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
56  set L
57} {b3}
58
59#-----------------------------------------------
60
61do_execsql_test 2.0 {
62  CREATE TABLE u1(a, b, c);
63  CREATE TABLE u2(x, y, z);
64
65  INSERT INTO u1 VALUES('a1', 'b1', 'c1');
66  INSERT INTO u2 VALUES('a1', 'b1', 'c1');
67}
68
69do_test 2.1 {
70  set L [list]
71  execsql {
72    SELECT * FROM u1 WHERE f('one')=123 AND 123=(
73      SELECT x FROM u2 WHERE x=a AND f('two')
74    )
75  }
76  set L
77} {one}
78
79do_test 2.2 {
80  set L [list]
81  execsql {
82    SELECT * FROM u1 WHERE 123=(
83      SELECT x FROM u2 WHERE x=a AND f('two')
84    ) AND f('three')=123
85  }
86  set L
87} {three}
88
89
90
91finish_test
92