xref: /sqlite-3.40.0/test/cursorhint2.test (revision 87f500ce) 
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 is on testing that cursor-hints are correct for queries
13# involving LEFT JOIN.
14#
15
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19set ::testprefix cursorhint2
20
21ifcapable !cursorhints {
22  finish_test
23  return
24}
25
26proc extract_hints {sql} {
27
28  db eval "SELECT tbl_name, rootpage FROM sqlite_master where rootpage" {
29    set lookup($rootpage) $tbl_name
30  }
31
32  set ret [list]
33  db eval "EXPLAIN $sql" a {
34    switch -- $a(opcode) {
35      OpenRead {
36        set csr($a(p1)) $lookup($a(p2))
37      }
38      CursorHint {
39        lappend ret $csr($a(p1)) $a(p4)
40      }
41    }
42  }
43
44  set ret
45}
46
47proc do_extract_hints_test {tn sql ret} {
48  uplevel [list do_test $tn [list extract_hints $sql] [list {*}$ret]]
49}
50
51do_execsql_test 1.0 {
52  PRAGMA automatic_index = 0;
53  CREATE TABLE t1(a, b);
54  CREATE TABLE t2(c, d);
55  CREATE TABLE t3(e, f);
56}
57
58do_extract_hints_test 1.1 {
59  SELECT * FROM t1 WHERE a=1;
60} {
61  t1 EQ(c0,1)
62}
63
64do_extract_hints_test 1.2 {
65  SELECT * FROM t1 CROSS JOIN t2 ON (a=c) WHERE d IS NULL;
66} {
67  t2 {AND(ISNULL(c1),EQ(r[1],c0))}
68}
69
70do_extract_hints_test 1.3 {
71  SELECT * FROM t1 LEFT JOIN t2 ON (a=c) WHERE d IS NULL;
72} {
73  t2 {EQ(r[2],c0)}
74}
75
76do_extract_hints_test 1.4 {
77  SELECT * FROM t1 LEFT JOIN t2 ON (a=c AND a=10) WHERE d IS NULL;
78} {
79  t2 {AND(EQ(r[2],c0),EQ(r[3],10))}
80}
81
82do_extract_hints_test 1.5 {
83  SELECT * FROM t1 CROSS JOIN t2 ON (a=c AND a=10) WHERE d IS NULL;
84} {
85  t1 EQ(c0,10) t2 {AND(ISNULL(c1),EQ(r[3],c0))}
86}
87
88do_extract_hints_test 1.6 {
89  SELECT * FROM t1 LEFT JOIN t2 ON (a=c) LEFT JOIN t3 ON (d=f);
90} {
91  t2 {EQ(r[2],c0)} t3 {EQ(r[6],c1)}
92}
93
94if 0 {
95  do_extract_hints_test 1.7 {
96    SELECT * FROM t1 LEFT JOIN t2 ON (a=c AND d=e) LEFT JOIN t3 ON (d=f);
97  } {
98    t2 {EQ(r[2],c0)} t3 {AND(EQ(r[6],c0),EQ(r[7],c1))}
99  }
100}
101
102#-------------------------------------------------------------------------
103#
104do_execsql_test 2.0 {
105  CREATE TABLE x1(x, y);
106  CREATE TABLE x2(a, b);
107}
108
109do_extract_hints_test 2.1 {
110  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NULL;
111} {
112  x2 {EQ(c0,r[2])}
113}
114
115do_extract_hints_test 2.2 {
116  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS +NULL;
117} {
118  x2 {EQ(c0,r[2])}
119}
120
121do_extract_hints_test 2.3 {
122  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = (b IS NULL)
123} {
124  x2 {EQ(c0,r[2])}
125}
126
127do_extract_hints_test 2.4 {
128  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
129} {
130  x2 {EQ(c0,r[2])}
131}
132
133do_extract_hints_test 2.5 {
134  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
135} {
136  x2 {EQ(c0,r[2])}
137}
138
139do_extract_hints_test 2.6 {
140  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT NULL)
141} {
142  x2 {EQ(c0,r[2])}
143}
144
145do_extract_hints_test 2.7 {
146  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT +NULL)
147} {
148  x2 {EQ(c0,r[2])}
149}
150
151do_extract_hints_test 2.8 {
152  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NOT +NULL
153} {
154  x2 {EQ(c0,r[2])}
155}
156
157do_extract_hints_test 2.9 {
158  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE CASE b WHEN 0 THEN 0 ELSE 1 END;
159} {
160  x2 {EQ(c0,r[2])}
161}
162
163do_extract_hints_test 2.10 {
164  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b = 32+32
165} {
166  x2 {AND(EQ(c1,ADD(32,32)),EQ(c0,r[2]))}
167}
168
169ifcapable !icu {
170  # This test only works using the built-in LIKE, not the ICU LIKE extension.
171  do_extract_hints_test 2.11 {
172    SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b LIKE 'abc%'
173  } {
174    x2 {AND(expr,EQ(c0,r[2]))}
175  }
176}
177
178do_extract_hints_test 2.12 {
179  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE coalesce(x2.b, 1)
180} {
181  x2 {EQ(c0,r[2])}
182}
183
184finish_test
185