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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix normalize 18 19foreach {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 75ifcapable normalize { 76do_test 200 { 77 execsql { 78 CREATE TABLE t1(a,b); 79 } 80} {} 81do_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} {} 87do_test 202 { 88 sqlite3_normalized_sql $STMT 89} {SELECT a,b FROM t1 WHERE b=?ORDER BY a;} 90do_test 203 { 91 sqlite3_finalize $STMT 92} {SQLITE_OK} 93 94do_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} {} 100do_test 211 { 101 sqlite3_normalized_sql $STMT 102} {SELECT a,b FROM t1 WHERE b=?ORDER BY a;} 103do_test 212 { 104 sqlite3_finalize $STMT 105} {SQLITE_OK} 106 107do_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]+$/} 111do_test 221 { 112 sqlite3_normalized_sql $STMT 113} {SELECT a,b FROM t1 WHERE b=?ORDER BY a;} 114do_test 222 { 115 sqlite3_finalize $STMT 116} {SQLITE_OK} 117 118do_test 297 { 119 execsql { 120 DROP TABLE t1; 121 } 122} {} 123do_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} {} 129do_test 299 { 130 sqlite3_create_function db 131} {SQLITE_OK} 132 133foreach {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 394finish_test 395