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} { 69 do_test $tnum [list sqlite3_normalize $sql] $norm 70} 71 72finish_test 73