AryaWu/sqlite
0
1# 2018-01-082#3# The author disclaims copyright to this source code. In place of4# 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.tcl17set testprefix normalize18 19foreach {tnum sql norm} {20 10021 {SELECT * FROM t1 WHERE a IN (1) AND b=51.42}22 {select*from t1 where a in(?,?,?)and b=?;}23 24 11025 {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 12029 { SELECT NULL, b FROM t1 -- comment text30 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 12137 {/*Initial comment*/38 -- another comment line39 SELECT NULL /* comment */ , b FROM t1 -- comment text40 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 13047 {/* 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 14052 {/* 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 15057 {SELECT x'abc'; -- illegal token}58 {}59 60 16061 {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 17065 {/* IN list exactly 5 bytes long */66 SELECT * FROM t1 WHERE x IN (1,2,3);}67 {select*from t1 where x in(?,?,?);}68 18069 { }70 {}71} {72 do_test $tnum [list sqlite3_normalize $sql] $norm73}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 186} {}87do_test 202 {88 sqlite3_normalized_sql $STMT89} {SELECT a,b FROM t1 WHERE b=?ORDER BY a;}90do_test 203 {91 sqlite3_finalize $STMT92} {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 199} {}100do_test 211 {101 sqlite3_normalized_sql $STMT102} {SELECT a,b FROM t1 WHERE b=?ORDER BY a;}103do_test 212 {104 sqlite3_finalize $STMT105} {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 $STMT113} {SELECT a,b FROM t1 WHERE b=?ORDER BY a;}114do_test 222 {115 sqlite3_finalize $STMT116} {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 db131} {SQLITE_OK}132 133foreach {tnum sql flags norm} {134 300135 {SELECT * FROM t1 WHERE a IN (1) AND b=51.42}136 0x2137 {0 {SELECT*FROM t1 WHERE a IN(?,?,?)AND b=?;}}138 139 310140 {SELECT a, b+15, c FROM t1 WHERE d NOT IN (SELECT x FROM t2);}141 0x2142 {0 {SELECT a,b+?,c FROM t1 WHERE d NOT IN(SELECT x FROM t2);}}143 144 320145 { SELECT NULL, b FROM t1 -- comment text146 WHERE d IN (WITH t(a) AS (VALUES(5)) /* CTE */147 SELECT a FROM t)148 OR e='hello';149 }150 0x2151 {0 {SELECT?,b FROM t1 WHERE d IN(WITH t(a)AS(VALUES(?))SELECT a FROM t)OR e=?;}}152 153 321154 {/*Initial comment*/155 -- another comment line156 SELECT NULL /* comment */ , b FROM t1 -- comment text157 WHERE d IN (WITH t(a) AS (VALUES(5)) /* CTE */158 SELECT a FROM t)159 OR e='hello';160 }161 0x2162 {0 {SELECT?,b FROM t1 WHERE d IN(WITH t(a)AS(VALUES(?))SELECT a FROM t)OR e=?;}}163 164 330165 {/* Query containing parameters */166 SELECT x,$::abc(15),y,@abc,z,?99,w FROM t1 /* Trailing comment */}167 0x2168 {0 {SELECT x,?,y,?,z,?,w FROM t1;}}169 170 340171 {/* 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 0x2174 {1 {(1) no such column: x}}175 176 350177 {SELECT x'abc'; -- illegal token}178 0x2179 {1 {(1) unrecognized token: "x'abc'"}}180 181 360182 {SELECT a,NULL,b FROM t1 WHERE c IS NOT NULL or D is null or e=5}183 0x2184 {0 {SELECT a,?,b FROM t1 WHERE c IS NOT NULL OR d IS NULL OR e=?;}}185 186 370187 {/* IN list exactly 5 bytes long */188 SELECT * FROM t1 WHERE x IN (1,2,3);}189 0x2190 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}}191 192 400193 {SELECT a FROM t1 WHERE x IN (1,2,3) AND sqlite_version();}194 0x2195 {0 {SELECT a FROM t1 WHERE x IN(?,?,?)AND sqlite_version();}}196 197 410198 {SELECT a FROM t1 WHERE x IN (1,2,3) AND hex8();}199 0x2200 {1 {(1) wrong number of arguments to function hex8()}}201 202 420203 {SELECT a FROM t1 WHERE x IN (1,2,3) AND hex8('abc');}204 0x2205 {0 {SELECT a FROM t1 WHERE x IN(?,?,?)AND hex8(?);}}206 207 430208 {SELECT "a" FROM t1 WHERE "x" IN ("1","2",'3');}209 0x2210 {0 {SELECT a FROM t1 WHERE x IN(?,?,?);}}211 212 440213 {SELECT 'a' FROM t1 WHERE 'x';}214 0x2215 {0 {SELECT?FROM t1 WHERE?;}}216 217 450218 {SELECT [a] FROM t1 WHERE [x];}219 0x2220 {0 {SELECT a FROM t1 WHERE x;}}221 222 460223 {SELECT * FROM t1 WHERE x IN (x);}224 0x2225 {0 {SELECT*FROM t1 WHERE x IN(x);}}226 227 470228 {SELECT * FROM t1 WHERE x IN (x,a);}229 0x2230 {0 {SELECT*FROM t1 WHERE x IN(x,a);}}231 232 480233 {SELECT * FROM t1 WHERE x IN ([x],"a");}234 0x2235 {0 {SELECT*FROM t1 WHERE x IN(x,a);}}236 237 500238 {SELECT * FROM t1 WHERE x IN ([x],"a",'b',sqlite_version());}239 0x2240 {0 {SELECT*FROM t1 WHERE x IN(x,a,?,sqlite_version());}}241 242 520243 {SELECT * FROM t1 WHERE x IN (SELECT x FROM t1);}244 0x2245 {0 {SELECT*FROM t1 WHERE x IN(SELECT x FROM t1);}}246 247 540248 {SELECT * FROM t1 WHERE x IN ((SELECT x FROM t1));}249 0x2250 {0 {SELECT*FROM t1 WHERE x IN((SELECT x FROM t1));}}251 252 550253 {SELECT a, a+1, a||'b', a+"b" FROM t1;}254 0x2255 {0 {SELECT a,a+?,a||?,a+b FROM t1;}}256 257 570258 {SELECT * FROM t1 WHERE x IN (1);}259 0x2260 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}}261 262 580263 {SELECT * FROM t1 WHERE x IN (1,2);}264 0x2265 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}}266 267 590268 {SELECT * FROM t1 WHERE x IN (1,2,3);}269 0x2270 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}}271 272 600273 {SELECT * FROM t1 WHERE x IN (1,2,3,4);}274 0x2275 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}}276 277 610278 {SELECT * FROM t1 WHERE x IN (SELECT x FROM t1);}279 0x2280 {0 {SELECT*FROM t1 WHERE x IN(SELECT x FROM t1);}}281 282 620283 {SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 WHERE x IN (1,2,3));}284 0x2285 {0 {SELECT*FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(?,?,?));}}286 287 630288 {SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 WHERE x IN (x));}289 0x2290 {0 {SELECT*FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(x));}}291 292 640293 {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 0x2297 {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 650300 {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 0x2304 {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 660307 {SELECT x FROM t1 WHERE x IN (1) UNION ALL SELECT x FROM t1 WHERE x IN (1);}308 0x2309 {0 {SELECT x FROM t1 WHERE x IN(?,?,?)UNION ALL SELECT x FROM t1 WHERE x IN(?,?,?);}}310 311 670312 {SELECT "col f", [col f] FROM t1;}313 0x2314 {0 {SELECT"col f","col f"FROM t1;}}315 316 680317 {SELECT a, "col f" FROM t1 LEFT OUTER JOIN t2 ON [t1].[col f] == [t2].[col y];}318 0x2319 {0 {SELECT a,"col f"FROM t1 LEFT OUTER JOIN t2 ON t1."col f"==t2."col y";}}320 321 690322 {SELECT * FROM ( WITH x AS ( SELECT * FROM t1 WHERE x IN ( 1)) SELECT 10);}323 0x2324 {0 {SELECT*FROM(WITH x AS(SELECT*FROM t1 WHERE x IN(?,?,?))SELECT?);}}325 326 700327 {SELECT rowid, oid, _rowid_ FROM t1;}328 0x2329 {0 {SELECT rowid,oid,_rowid_ FROM t1;}}330 331 710332 {SELECT x FROM t1 WHERE x IS NULL;}333 0x2334 {0 {SELECT x FROM t1 WHERE x IS NULL;}}335 336 740337 {SELECT x FROM t1 WHERE x IS NOT NULL;}338 0x2339 {0 {SELECT x FROM t1 WHERE x IS NOT NULL;}}340 341 750342 {SELECT x FROM t1 WHERE x = NULL;}343 0x2344 {0 {SELECT x FROM t1 WHERE x=?;}}345 346 760347 {SELECT x FROM t1 WHERE x IN ([x] IS NOT NULL, NULL, 1, 'a', "b", x'00');}348 0x2349 {0 {SELECT x FROM t1 WHERE x IN(x IS NOT NULL,?,?,?,b,?);}}350 351 800352 {ATTACH "normalize800.db" AS somefile;}353 0x2354 {0 {ATTACH"normalize800.db"AS somefile;}}355 356 810357 {ATTACH DATABASE "normalize810.db" AS somefile;}358 0x2359 {0 {ATTACH DATABASE"normalize810.db"AS somefile;}}360 361 900362 {INSERT INTO t1 (x) VALUES("sl1"), (1), ("sl2"), ('i');}363 0x2364 {0 {INSERT INTO t1(x)VALUES(?),(?),(?),(?);}}365 366 910367 {UPDATE t1 SET x = "sl1" WHERE x IN (1, "sl2", 'i');}368 0x2369 {0 {UPDATE t1 SET x=?WHERE x IN(?,?,?);}}370 371 920372 {UPDATE t1 SET x = "y" WHERE x IN (1, "sl1", 'i');}373 0x2374 {0 {UPDATE t1 SET x=y WHERE x IN(?,?,?);}}375 376 930377 {DELETE FROM t1 WHERE x IN (1, "sl1", 'i');}378 0x2379 {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 $STMT385 } res]386 if {[info exists STMT]} {387 sqlite3_finalize $STMT; unset STMT388 }389 list $code $res390 } $norm391}392}393 394finish_test395