Debian Codegraph @

test/normalize.test

raw
1 # 2018-01-08
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 #
12 # Tests for the sqlite3_normalize() extension function.
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix normalize
18
19 foreach {tnum sql norm} {
20 100
21 {SELECT * FROM t1 WHERE a IN (1) AND b=51.42}
22 {select*from t1 where a in(?,?,?)and b=?;}
23
24 110
25 {SELECT a, b+15, c FROM t1 WHERE d NOT IN (SELECT x FROM t2);}
26 {select a,b+?,c from t1 where d not in(select x from t2);}
27
28 120
29 { SELECT NULL, b FROM t1 -- comment text
30 WHERE d IN (WITH t(a) AS (VALUES(5)) /* CTE */
31 SELECT a FROM t)
32 OR e='hello';
33 }
34 {select?,b from t1 where d in(with t(a)as(values(?))select a from t)or e=?;}
35
36 121
37 {/*Initial comment*/
38 -- another comment line
39 SELECT NULL /* comment */ , b FROM t1 -- comment text
40 WHERE d IN (WITH t(a) AS (VALUES(5)) /* CTE */
41 SELECT a FROM t)
42 OR e='hello';
43 }
44 {select?,b from t1 where d in(with t(a)as(values(?))select a from t)or e=?;}
45
46 130
47 {/* Query containing parameters */
48 SELECT x,$::abc(15),y,@abc,z,?99,w FROM t1 /* Trailing comment */}
49 {select x,?,y,?,z,?,w from t1;}
50
51 140
52 {/* Long list on the RHS of IN */
53 SELECT 15 IN (1,2,3,(SELECT * FROM t1),'xyz',x'abcd',22*(x+5),null);}
54 {select?in(?,?,?);}
55
56 150
57 {SELECT x'abc'; -- illegal token}
58 {}
59
60 160
61 {SELECT a,NULL,b FROM t1 WHERE c IS NOT NULL or D is null or e=5}
62 {select a,?,b from t1 where c is not null or d is null or e=?;}
63
64 170
65 {/* IN list exactly 5 bytes long */
66 SELECT * FROM t1 WHERE x IN (1,2,3);}
67 {select*from t1 where x in(?,?,?);}
68 180
69 { }
70 {}
71 } {
72 do_test $tnum [list sqlite3_normalize $sql] $norm
73 }
74
75 ifcapable normalize {
76 do_test 200 {
77 execsql {
78 CREATE TABLE t1(a,b);
79 }
80 } {}
81 do_test 201 {
82 set STMT [sqlite3_prepare_v3 $DB \
83 "SELECT a, b FROM t1 WHERE b = ? ORDER BY a;" -1 0 TAIL]
84
85 sqlite3_bind_null $STMT 1
86 } {}
87 do_test 202 {
88 sqlite3_normalized_sql $STMT
89 } {SELECT a,b FROM t1 WHERE b=?ORDER BY a;}
90 do_test 203 {
91 sqlite3_finalize $STMT
92 } {SQLITE_OK}
93
94 do_test 210 {
95 set STMT [sqlite3_prepare_v3 $DB \
96 "SELECT a, b FROM t1 WHERE b = ? ORDER BY a;" -1 2 TAIL]
97
98 sqlite3_bind_null $STMT 1
99 } {}
100 do_test 211 {
101 sqlite3_normalized_sql $STMT
102 } {SELECT a,b FROM t1 WHERE b=?ORDER BY a;}
103 do_test 212 {
104 sqlite3_finalize $STMT
105 } {SQLITE_OK}
106
107 do_test 220 {
108 set STMT [sqlite3_prepare_v3 $DB \
109 "SELECT a, b FROM t1 WHERE b = 'a' ORDER BY a;" -1 2 TAIL]
110 } {/^[0-9A-Fa-f]+$/}
111 do_test 221 {
112 sqlite3_normalized_sql $STMT
113 } {SELECT a,b FROM t1 WHERE b=?ORDER BY a;}
114 do_test 222 {
115 sqlite3_finalize $STMT
116 } {SQLITE_OK}
117
118 do_test 297 {
119 execsql {
120 DROP TABLE t1;
121 }
122 } {}
123 do_test 298 {
124 execsql {
125 CREATE TABLE t1(a,b,c,d,e,"col f",w,x,y,z);
126 CREATE TABLE t2(x,"col y");
127 }
128 } {}
129 do_test 299 {
130 sqlite3_create_function db
131 } {SQLITE_OK}
132
133 foreach {tnum sql flags norm} {
134 300
135 {SELECT * FROM t1 WHERE a IN (1) AND b=51.42}
136 0x2
137 {0 {SELECT*FROM t1 WHERE a IN(?,?,?)AND b=?;}}
138
139 310
140 {SELECT a, b+15, c FROM t1 WHERE d NOT IN (SELECT x FROM t2);}
141 0x2
142 {0 {SELECT a,b+?,c FROM t1 WHERE d NOT IN(SELECT x FROM t2);}}
143
144 320
145 { SELECT NULL, b FROM t1 -- comment text
146 WHERE d IN (WITH t(a) AS (VALUES(5)) /* CTE */
147 SELECT a FROM t)
148 OR e='hello';
149 }
150 0x2
151 {0 {SELECT?,b FROM t1 WHERE d IN(WITH t(a)AS(VALUES(?))SELECT a FROM t)OR e=?;}}
152
153 321
154 {/*Initial comment*/
155 -- another comment line
156 SELECT NULL /* comment */ , b FROM t1 -- comment text
157 WHERE d IN (WITH t(a) AS (VALUES(5)) /* CTE */
158 SELECT a FROM t)
159 OR e='hello';
160 }
161 0x2
162 {0 {SELECT?,b FROM t1 WHERE d IN(WITH t(a)AS(VALUES(?))SELECT a FROM t)OR e=?;}}
163
164 330
165 {/* Query containing parameters */
166 SELECT x,$::abc(15),y,@abc,z,?99,w FROM t1 /* Trailing comment */}
167 0x2
168 {0 {SELECT x,?,y,?,z,?,w FROM t1;}}
169
170 340
171 {/* Long list on the RHS of IN */
172 SELECT 15 IN (1,2,3,(SELECT * FROM t1),'xyz',x'abcd',22*(x+5),null);}
173 0x2
174 {1 {(1) no such column: x}}
175
176 350
177 {SELECT x'abc'; -- illegal token}
178 0x2
179 {1 {(1) unrecognized token: "x'abc'"}}
180
181 360
182 {SELECT a,NULL,b FROM t1 WHERE c IS NOT NULL or D is null or e=5}
183 0x2
184 {0 {SELECT a,?,b FROM t1 WHERE c IS NOT NULL OR d IS NULL OR e=?;}}
185
186 370
187 {/* IN list exactly 5 bytes long */
188 SELECT * FROM t1 WHERE x IN (1,2,3);}
189 0x2
190 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}}
191
192 400
193 {SELECT a FROM t1 WHERE x IN (1,2,3) AND sqlite_version();}
194 0x2
195 {0 {SELECT a FROM t1 WHERE x IN(?,?,?)AND sqlite_version();}}
196
197 410
198 {SELECT a FROM t1 WHERE x IN (1,2,3) AND hex8();}
199 0x2
200 {1 {(1) wrong number of arguments to function hex8()}}
201
202 420
203 {SELECT a FROM t1 WHERE x IN (1,2,3) AND hex8('abc');}
204 0x2
205 {0 {SELECT a FROM t1 WHERE x IN(?,?,?)AND hex8(?);}}
206
207 430
208 {SELECT "a" FROM t1 WHERE "x" IN ("1","2",'3');}
209 0x2
210 {0 {SELECT a FROM t1 WHERE x IN(?,?,?);}}
211
212 440
213 {SELECT 'a' FROM t1 WHERE 'x';}
214 0x2
215 {0 {SELECT?FROM t1 WHERE?;}}
216
217 450
218 {SELECT [a] FROM t1 WHERE [x];}
219 0x2
220 {0 {SELECT a FROM t1 WHERE x;}}
221
222 460
223 {SELECT * FROM t1 WHERE x IN (x);}
224 0x2
225 {0 {SELECT*FROM t1 WHERE x IN(x);}}
226
227 470
228 {SELECT * FROM t1 WHERE x IN (x,a);}
229 0x2
230 {0 {SELECT*FROM t1 WHERE x IN(x,a);}}
231
232 480
233 {SELECT * FROM t1 WHERE x IN ([x],"a");}
234 0x2
235 {0 {SELECT*FROM t1 WHERE x IN(x,a);}}
236
237 500
238 {SELECT * FROM t1 WHERE x IN ([x],"a",'b',sqlite_version());}
239 0x2
240 {0 {SELECT*FROM t1 WHERE x IN(x,a,?,sqlite_version());}}
241
242 520
243 {SELECT * FROM t1 WHERE x IN (SELECT x FROM t1);}
244 0x2
245 {0 {SELECT*FROM t1 WHERE x IN(SELECT x FROM t1);}}
246
247 540
248 {SELECT * FROM t1 WHERE x IN ((SELECT x FROM t1));}
249 0x2
250 {0 {SELECT*FROM t1 WHERE x IN((SELECT x FROM t1));}}
251
252 550
253 {SELECT a, a+1, a||'b', a+"b" FROM t1;}
254 0x2
255 {0 {SELECT a,a+?,a||?,a+b FROM t1;}}
256
257 570
258 {SELECT * FROM t1 WHERE x IN (1);}
259 0x2
260 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}}
261
262 580
263 {SELECT * FROM t1 WHERE x IN (1,2);}
264 0x2
265 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}}
266
267 590
268 {SELECT * FROM t1 WHERE x IN (1,2,3);}
269 0x2
270 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}}
271
272 600
273 {SELECT * FROM t1 WHERE x IN (1,2,3,4);}
274 0x2
275 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}}
276
277 610
278 {SELECT * FROM t1 WHERE x IN (SELECT x FROM t1);}
279 0x2
280 {0 {SELECT*FROM t1 WHERE x IN(SELECT x FROM t1);}}
281
282 620
283 {SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 WHERE x IN (1,2,3));}
284 0x2
285 {0 {SELECT*FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(?,?,?));}}
286
287 630
288 {SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 WHERE x IN (x));}
289 0x2
290 {0 {SELECT*FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(x));}}
291
292 640
293 {SELECT x FROM t1 WHERE x IN (SELECT x FROM t1 WHERE x IN (
294 SELECT x FROM t1 WHERE x IN (SELECT x FROM t1 WHERE x IN (
295 SELECT x FROM t1 WHERE x IN (x)))));}
296 0x2
297 {0 {SELECT x FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(x)))));}}
298
299 650
300 {SELECT x FROM t1 WHERE x IN (SELECT x FROM t1 WHERE x IN (
301 SELECT x FROM t1 WHERE x IN (SELECT x FROM t1 WHERE x IN (
302 SELECT x FROM t1 WHERE x IN (1)))));}
303 0x2
304 {0 {SELECT x FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(?,?,?)))));}}
305
306 660
307 {SELECT x FROM t1 WHERE x IN (1) UNION ALL SELECT x FROM t1 WHERE x IN (1);}
308 0x2
309 {0 {SELECT x FROM t1 WHERE x IN(?,?,?)UNION ALL SELECT x FROM t1 WHERE x IN(?,?,?);}}
310
311 670
312 {SELECT "col f", [col f] FROM t1;}
313 0x2
314 {0 {SELECT"col f","col f"FROM t1;}}
315
316 680
317 {SELECT a, "col f" FROM t1 LEFT OUTER JOIN t2 ON [t1].[col f] == [t2].[col y];}
318 0x2
319 {0 {SELECT a,"col f"FROM t1 LEFT OUTER JOIN t2 ON t1."col f"==t2."col y";}}
320
321 690
322 {SELECT * FROM ( WITH x AS ( SELECT * FROM t1 WHERE x IN ( 1)) SELECT 10);}
323 0x2
324 {0 {SELECT*FROM(WITH x AS(SELECT*FROM t1 WHERE x IN(?,?,?))SELECT?);}}
325
326 700
327 {SELECT rowid, oid, _rowid_ FROM t1;}
328 0x2
329 {0 {SELECT rowid,oid,_rowid_ FROM t1;}}
330
331 710
332 {SELECT x FROM t1 WHERE x IS NULL;}
333 0x2
334 {0 {SELECT x FROM t1 WHERE x IS NULL;}}
335
336 740
337 {SELECT x FROM t1 WHERE x IS NOT NULL;}
338 0x2
339 {0 {SELECT x FROM t1 WHERE x IS NOT NULL;}}
340
341 750
342 {SELECT x FROM t1 WHERE x = NULL;}
343 0x2
344 {0 {SELECT x FROM t1 WHERE x=?;}}
345
346 760
347 {SELECT x FROM t1 WHERE x IN ([x] IS NOT NULL, NULL, 1, 'a', "b", x'00');}
348 0x2
349 {0 {SELECT x FROM t1 WHERE x IN(x IS NOT NULL,?,?,?,b,?);}}
350
351 800
352 {ATTACH "normalize800.db" AS somefile;}
353 0x2
354 {0 {ATTACH"normalize800.db"AS somefile;}}
355
356 810
357 {ATTACH DATABASE "normalize810.db" AS somefile;}
358 0x2
359 {0 {ATTACH DATABASE"normalize810.db"AS somefile;}}
360
361 900
362 {INSERT INTO t1 (x) VALUES("sl1"), (1), ("sl2"), ('i');}
363 0x2
364 {0 {INSERT INTO t1(x)VALUES(?),(?),(?),(?);}}
365
366 910
367 {UPDATE t1 SET x = "sl1" WHERE x IN (1, "sl2", 'i');}
368 0x2
369 {0 {UPDATE t1 SET x=?WHERE x IN(?,?,?);}}
370
371 920
372 {UPDATE t1 SET x = "y" WHERE x IN (1, "sl1", 'i');}
373 0x2
374 {0 {UPDATE t1 SET x=y WHERE x IN(?,?,?);}}
375
376 930
377 {DELETE FROM t1 WHERE x IN (1, "sl1", 'i');}
378 0x2
379 {0 {DELETE FROM t1 WHERE x IN(?,?,?);}}
380 } {
381 do_test $tnum {
382 set code [catch {
383 set STMT [sqlite3_prepare_v3 $DB $sql -1 $flags TAIL]
384 sqlite3_normalized_sql $STMT
385 } res]
386 if {[info exists STMT]} {
387 sqlite3_finalize $STMT; unset STMT
388 }
389 list $code $res
390 } $norm
391 }
392 }
393
394 finish_test