# 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(?,?,?);}
} {
do_test $tnum [list sqlite3_normalize $sql] $norm
}
finish_test