# 2018-01-08 # # 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. # #*********************************************************************** # # Tests for the sqlite3_normalize() extension function. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix normalize foreach {tnum sql norm} { 100 {SELECT * FROM t1 WHERE a IN (1) AND b=51.42} {select*from t1 where a in(?,?,?)and b=?;} 110 {SELECT a, b+15, c FROM t1 WHERE d NOT IN (SELECT x FROM t2);} {select a,b+?,c from t1 where d not in(select x from t2);} 120 { SELECT NULL, b FROM t1 -- comment text WHERE d IN (WITH t(a) AS (VALUES(5)) /* CTE */ SELECT a FROM t) OR e='hello'; } {select?,b from t1 where d in(with t(a)as(values(?))select a from t)or e=?;} 121 {/*Initial comment*/ -- another comment line SELECT NULL /* comment */ , b FROM t1 -- comment text WHERE d IN (WITH t(a) AS (VALUES(5)) /* CTE */ SELECT a FROM t) OR e='hello'; } {select?,b from t1 where d in(with t(a)as(values(?))select a from t)or e=?;} 130 {/* Query containing parameters */ SELECT x,$::abc(15),y,@abc,z,?99,w FROM t1 /* Trailing comment */} {select x,?,y,?,z,?,w from t1;} 140 {/* Long list on the RHS of IN */ SELECT 15 IN (1,2,3,(SELECT * FROM t1),'xyz',x'abcd',22*(x+5),null);} {select?in(?,?,?);} 150 {SELECT x'abc'; -- illegal token} {} 160 {SELECT a,NULL,b FROM t1 WHERE c IS NOT NULL or D is null or e=5} {select a,?,b from t1 where c is not null or d is null or e=?;} 170 {/* IN list exactly 5 bytes long */ SELECT * FROM t1 WHERE x IN (1,2,3);} {select*from t1 where x in(?,?,?);} 180 { } {} } { do_test $tnum [list sqlite3_normalize $sql] $norm } ifcapable normalize { do_test 200 { execsql { CREATE TABLE t1(a,b); } } {} do_test 201 { set STMT [sqlite3_prepare_v3 $DB \ "SELECT a, b FROM t1 WHERE b = ? ORDER BY a;" -1 0 TAIL] sqlite3_bind_null $STMT 1 } {} do_test 202 { sqlite3_normalized_sql $STMT } {SELECT a,b FROM t1 WHERE b=?ORDER BY a;} do_test 203 { sqlite3_finalize $STMT } {SQLITE_OK} do_test 210 { set STMT [sqlite3_prepare_v3 $DB \ "SELECT a, b FROM t1 WHERE b = ? ORDER BY a;" -1 2 TAIL] sqlite3_bind_null $STMT 1 } {} do_test 211 { sqlite3_normalized_sql $STMT } {SELECT a,b FROM t1 WHERE b=?ORDER BY a;} do_test 212 { sqlite3_finalize $STMT } {SQLITE_OK} do_test 220 { set STMT [sqlite3_prepare_v3 $DB \ "SELECT a, b FROM t1 WHERE b = 'a' ORDER BY a;" -1 2 TAIL] } {/^[0-9A-Fa-f]+$/} do_test 221 { sqlite3_normalized_sql $STMT } {SELECT a,b FROM t1 WHERE b=?ORDER BY a;} do_test 222 { sqlite3_finalize $STMT } {SQLITE_OK} do_test 297 { execsql { DROP TABLE t1; } } {} do_test 298 { execsql { CREATE TABLE t1(a,b,c,d,e,"col f",w,x,y,z); CREATE TABLE t2(x,"col y"); } } {} do_test 299 { sqlite3_create_function db } {SQLITE_OK} foreach {tnum sql flags norm} { 300 {SELECT * FROM t1 WHERE a IN (1) AND b=51.42} 0x2 {0 {SELECT*FROM t1 WHERE a IN(?,?,?)AND b=?;}} 310 {SELECT a, b+15, c FROM t1 WHERE d NOT IN (SELECT x FROM t2);} 0x2 {0 {SELECT a,b+?,c FROM t1 WHERE d NOT IN(SELECT x FROM t2);}} 320 { SELECT NULL, b FROM t1 -- comment text WHERE d IN (WITH t(a) AS (VALUES(5)) /* CTE */ SELECT a FROM t) OR e='hello'; } 0x2 {0 {SELECT?,b FROM t1 WHERE d IN(WITH t(a)AS(VALUES(?))SELECT a FROM t)OR e=?;}} 321 {/*Initial comment*/ -- another comment line SELECT NULL /* comment */ , b FROM t1 -- comment text WHERE d IN (WITH t(a) AS (VALUES(5)) /* CTE */ SELECT a FROM t) OR e='hello'; } 0x2 {0 {SELECT?,b FROM t1 WHERE d IN(WITH t(a)AS(VALUES(?))SELECT a FROM t)OR e=?;}} 330 {/* Query containing parameters */ SELECT x,$::abc(15),y,@abc,z,?99,w FROM t1 /* Trailing comment */} 0x2 {0 {SELECT x,?,y,?,z,?,w FROM t1;}} 340 {/* Long list on the RHS of IN */ SELECT 15 IN (1,2,3,(SELECT * FROM t1),'xyz',x'abcd',22*(x+5),null);} 0x2 {1 {(1) no such column: x}} 350 {SELECT x'abc'; -- illegal token} 0x2 {1 {(1) unrecognized token: "x'abc'"}} 360 {SELECT a,NULL,b FROM t1 WHERE c IS NOT NULL or D is null or e=5} 0x2 {0 {SELECT a,?,b FROM t1 WHERE c IS NOT NULL OR d IS NULL OR e=?;}} 370 {/* IN list exactly 5 bytes long */ SELECT * FROM t1 WHERE x IN (1,2,3);} 0x2 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}} 400 {SELECT a FROM t1 WHERE x IN (1,2,3) AND sqlite_version();} 0x2 {0 {SELECT a FROM t1 WHERE x IN(?,?,?)AND sqlite_version();}} 410 {SELECT a FROM t1 WHERE x IN (1,2,3) AND hex8();} 0x2 {1 {(1) wrong number of arguments to function hex8()}} 420 {SELECT a FROM t1 WHERE x IN (1,2,3) AND hex8('abc');} 0x2 {0 {SELECT a FROM t1 WHERE x IN(?,?,?)AND hex8(?);}} 430 {SELECT "a" FROM t1 WHERE "x" IN ("1","2",'3');} 0x2 {0 {SELECT"a"FROM t1 WHERE"x"IN(?,?,?);}} 440 {SELECT 'a' FROM t1 WHERE 'x';} 0x2 {0 {SELECT?FROM t1 WHERE?;}} 450 {SELECT [a] FROM t1 WHERE [x];} 0x2 {0 {SELECT"a"FROM t1 WHERE"x";}} 460 {SELECT * FROM t1 WHERE x IN (x);} 0x2 {0 {SELECT*FROM t1 WHERE x IN(x);}} 470 {SELECT * FROM t1 WHERE x IN (x,a);} 0x2 {0 {SELECT*FROM t1 WHERE x IN(x,a);}} 480 {SELECT * FROM t1 WHERE x IN ([x],"a");} 0x2 {0 {SELECT*FROM t1 WHERE x IN("x","a");}} 500 {SELECT * FROM t1 WHERE x IN ([x],"a",'b',sqlite_version());} 0x2 {0 {SELECT*FROM t1 WHERE x IN("x","a",?,sqlite_version());}} 520 {SELECT * FROM t1 WHERE x IN (SELECT x FROM t1);} 0x2 {0 {SELECT*FROM t1 WHERE x IN(SELECT x FROM t1);}} 540 {SELECT * FROM t1 WHERE x IN ((SELECT x FROM t1));} 0x2 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}} 550 {SELECT a, a+1, a||'b', a+"b" FROM t1;} 0x2 {0 {SELECT a,a+?,a||?,a+"b"FROM t1;}} 570 {SELECT * FROM t1 WHERE x IN (1);} 0x2 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}} 580 {SELECT * FROM t1 WHERE x IN (1,2);} 0x2 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}} 590 {SELECT * FROM t1 WHERE x IN (1,2,3);} 0x2 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}} 600 {SELECT * FROM t1 WHERE x IN (1,2,3,4);} 0x2 {0 {SELECT*FROM t1 WHERE x IN(?,?,?);}} 610 {SELECT * FROM t1 WHERE x IN (SELECT x FROM t1);} 0x2 {0 {SELECT*FROM t1 WHERE x IN(SELECT x FROM t1);}} 620 {SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 WHERE x IN (1,2,3));} 0x2 {0 {SELECT*FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(?,?,?));}} 630 {SELECT * FROM t1 WHERE x IN (SELECT x FROM t1 WHERE x IN (x));} 0x2 {0 {SELECT*FROM t1 WHERE x IN(SELECT x FROM t1 WHERE x IN(x));}} 640 {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)))));} 0x2 {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)))));}} 650 {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 (1)))));} 0x2 {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(?,?,?)))));}} 660 {SELECT x FROM t1 WHERE x IN (1) UNION ALL SELECT x FROM t1 WHERE x IN (1);} 0x2 {0 {SELECT x FROM t1 WHERE x IN(?,?,?)UNION ALL SELECT x FROM t1 WHERE x IN(?,?,?);}} 670 {SELECT "col f", [col f] FROM t1;} 0x2 {0 {SELECT"col f","col f"FROM t1;}} 680 {SELECT a, "col f" FROM t1 LEFT OUTER JOIN t2 ON [t1].[col f] == [t2].[col y];} 0x2 {0 {SELECT a,"col f"FROM t1 LEFT OUTER JOIN t2 ON"t1"."col f"=="t2"."col y";}} 690 {SELECT * FROM ( WITH x AS ( SELECT * FROM t1 WHERE x IN ( 1)) SELECT 10);} 0x2 {0 {SELECT*FROM(WITH x AS(SELECT*FROM t1 WHERE x IN(?,?,?))SELECT?);}} 700 {SELECT rowid, oid, _rowid_ FROM t1;} 0x2 {0 {SELECT rowid,oid,_rowid_ FROM t1;}} 710 {SELECT x FROM t1 WHERE x IS NULL;} 0x2 {0 {SELECT x FROM t1 WHERE x IS NULL;}} 740 {SELECT x FROM t1 WHERE x IS NOT NULL;} 0x2 {0 {SELECT x FROM t1 WHERE x IS NOT NULL;}} 750 {SELECT x FROM t1 WHERE x = NULL;} 0x2 {0 {SELECT x FROM t1 WHERE x=?;}} 760 {SELECT x FROM t1 WHERE x IN ([x] IS NOT NULL, NULL, 1, 'a', "b", x'00');} 0x2 {0 {SELECT x FROM t1 WHERE x IN("x"IS NOT NULL,?,?,?,"b",?);}} } { do_test $tnum { set code [catch { set STMT [sqlite3_prepare_v3 $DB $sql -1 $flags TAIL] sqlite3_normalized_sql $STMT } res] if {[info exists STMT]} { sqlite3_finalize $STMT; unset STMT } list $code $res } $norm } } finish_test