| 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 |