summaryrefslogtreecommitdiffstats
path: root/test/autoindex4.test
blob: 6af99f5e15622ce63b9da7d1bbb996ed85d1733f (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
# 2014-10-24
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
#
# This file implements regression tests for SQLite library.  The
# focus of this script is testing automatic index creation logic,
# and specifically creation of automatic partial indexes.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test autoindex4-1.0 {
  CREATE TABLE t1(a,b);
  INSERT INTO t1 VALUES(123,'abc'),(234,'def'),(234,'ghi'),(345,'jkl');
  CREATE TABLE t2(x,y);
  INSERT INTO t2 VALUES(987,'zyx'),(654,'wvu'),(987,'rqp');

  SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=987 ORDER BY +b;
} {234 def 987 rqp | 234 def 987 zyx | 234 ghi 987 rqp | 234 ghi 987 zyx |}
do_execsql_test autoindex4-1.1 {
  SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=555;
} {}

do_execsql_test autoindex4-1.2 {
  SELECT *, '|' FROM t1 LEFT JOIN t2 ON a=234 AND x=555;
} {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |}
do_execsql_test autoindex4-1.2-rj {
  SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 ON a=234 AND x=555;
} {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |}
do_execsql_test autoindex4-1.3 {
  SELECT *, '|' FROM t1 LEFT JOIN t2 ON x=555 WHERE a=234;
} {234 def {} {} | 234 ghi {} {} |}
do_execsql_test autoindex4-1.3-rj {
  SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 ON x=555 WHERE a=234;
} {234 def {} {} | 234 ghi {} {} |}
do_execsql_test autoindex4-1.4 {
  SELECT *, '|' FROM t1 LEFT JOIN t2 WHERE a=234 AND x=555;
} {}
do_execsql_test autoindex4-1.4-rj {
  SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 WHERE a=234 AND x=555;
} {}


do_execsql_test autoindex4-2.0 {
  CREATE TABLE t3(e,f);
  INSERT INTO t3 VALUES(123,654),(555,444),(234,987);

  SELECT (SELECT count(*) FROM t1, t2 WHERE a=e AND x=f), e, f, '|'
    FROM t3
   ORDER BY rowid;
} {1 123 654 | 0 555 444 | 4 234 987 |}

# Ticket [2326c258d02ead33d]
# Two joins, one with and the other without an ORDER BY clause.
# The one without ORDER BY correctly returns two rows of result.
# The one with ORDER BY returns no rows. 
#
do_execsql_test autoindex4-3.0 {
  CREATE TABLE A(Name text);
  CREATE TABLE Items(ItemName text , Name text);
  INSERT INTO Items VALUES('Item1','Parent');
  INSERT INTO Items VALUES('Item2','Parent');
  CREATE TABLE B(Name text);
  
  SELECT Items.ItemName
    FROM Items
      LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
      LEFT JOIN B ON (B.Name = Items.ItemName)
    WHERE Items.Name = 'Parent'
    ORDER BY Items.ItemName;
} {Item1 Item2}
do_execsql_test autoindex4-3.1 {
  SELECT Items.ItemName
    FROM A
      RIGHT JOIN Items ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
      LEFT JOIN B ON (B.Name = Items.ItemName)
    WHERE Items.Name = 'Parent'
    ORDER BY Items.ItemName;
} {Item1 Item2}
do_execsql_test autoindex4-3.10 {
  CREATE INDEX Items_x1 ON Items(ItemName,Name) WHERE ItemName = 'dummy';
  
  SELECT Items.ItemName
    FROM Items
      LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
      LEFT JOIN B ON (B.Name = Items.ItemName)
    WHERE Items.Name = 'Parent'
    ORDER BY Items.ItemName;
} {Item1 Item2}
do_execsql_test autoindex4-3.11 {
  SELECT Items.ItemName
    FROM A
      RIGHT JOIN Items ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
      LEFT JOIN B ON (B.Name = Items.ItemName)
    WHERE Items.Name = 'Parent'
    ORDER BY Items.ItemName;
} {Item1 Item2}

# 2021-11-30 - Enhancement to help the automatic index mechanism to
# create a partial index more often.
#
unset -nocomplain id data1 data2 jointype onclause whereclause answer
foreach {id data1 data2 jointype onclause whereclause answer} {
  1
  VALUES(1,2),(3,4)
  VALUES(1,2),(3,4)
  {LEFT JOIN}
  a=x
  {y=4 OR y IS NULL}
  {3 4 3 4}

  2
  VALUES(1,2),(3,4)
  VALUES(1,2),(3,4)
  {LEFT JOIN}
  {a=x AND y=4}
  {coalesce(y,4)==4}
  {1 2 {} {} 3 4 3 4}

  3
  VALUES(1,2),(3,4)
  VALUES(1,2),(3,4)
  {JOIN}
  {a=x}
  {y=4 OR y IS NULL}
  {3 4 3 4}

  4
  VALUES(1,2),(3,4)
  VALUES(1,2),(3,4)
  {JOIN}
  {a=x AND y=4}
  {coalesce(y,4)==4}
  {3 4 3 4}

  5.1
  VALUES(1,2),(3,4),(NULL,4)
  VALUES(1,2),(3,4)
  {LEFT JOIN}
  a=x
  {y=4 OR y IS NULL}
  {3 4 3 4 {} 4 {} {}}

  5.2
  VALUES(1,2),(3,4),(NULL,4)
  VALUES(1,2),(3,4)
  {LEFT JOIN}
  a=x
  {y NOT IN ()}
  {1 2 1 2 3 4 3 4 {} 4 {} {}}

  5.3
  VALUES(1,2),(3,4),(NULL,4)
  VALUES(1,2),(3,4)
  {LEFT JOIN}
  a=x
  {y NOT IN (SELECT 1 WHERE false)}
  {1 2 1 2 3 4 3 4 {} 4 {} {}}

  6
  VALUES(1,2),(3,4)
  VALUES(1,2),(3,4),(NULL,4)
  {LEFT JOIN}
  {a=x AND y=4}
  {coalesce(y,4)==4}
  {1 2 {} {} 3 4 3 4}

  7
  VALUES(1,2),(3,4),(NULL,4)
  VALUES(1,2),(3,4),(NULL,4)
  {JOIN}
  {a=x}
  {y=4 OR y IS NULL}
  {3 4 3 4}

  8
  VALUES(1,2),(3,4)
  VALUES(1,2),(3,4)
  {JOIN}
  {a=x AND y=4}
  {coalesce(y,4)==4}
  {3 4 3 4}
} {
  do_test autoindex4-4.$id.0 {
    db eval {
       DROP TABLE IF EXISTS t1;
       CREATE TABLE t1(a INT, b INT);
       DROP TABLE IF EXISTS t2;
       CREATE TABLE t2(x INT, y INT);
    }
    db eval "INSERT INTO t1(a,b) $data1;"
    db eval "INSERT INTO t2(x,y) $data2;"
  } {}
  set sql "SELECT * FROM t1 $jointype t2 ON $onclause WHERE $whereclause"
  # puts "sql = $sql"
  do_test autoindex4-4.$id.1 {
    db eval {PRAGMA automatic_index=ON;}
    db eval $sql
  } $answer
  do_test autoindex4-4.$id.2 {
    db eval {PRAGMA automatic_index=OFF;}
    db eval $sql
  } $answer
  do_test autoindex4-4.$id.3 {
    db eval {PRAGMA automatic_index=ON;}
    optimization_control db all 0
    db eval $sql
  } $answer
  optimization_control db all 1
}




finish_test