normalize.test

# 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((SELECT x FROM t1));}}

  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,?);}}

  800
  {ATTACH "normalize800.db" AS somefile;}
  0x2
  {0 {ATTACH"normalize800.db"AS somefile;}}

  810
  {ATTACH DATABASE "normalize810.db" AS somefile;}
  0x2
  {0 {ATTACH DATABASE"normalize810.db"AS somefile;}}

  900
  {INSERT INTO t1 (x) VALUES("sl1"), (1), ("sl2"), ('i');}
  0x2
  {0 {INSERT INTO t1(x)VALUES(?),(?),(?),(?);}}

  910
  {UPDATE t1 SET x = "sl1" WHERE x IN (1, "sl2", 'i');}
  0x2
  {0 {UPDATE t1 SET x=?WHERE x IN(?,?,?);}}

  920
  {UPDATE t1 SET x = "y" WHERE x IN (1, "sl1", 'i');}
  0x2
  {0 {UPDATE t1 SET x=y WHERE x IN(?,?,?);}}

  930
  {DELETE FROM t1 WHERE x IN (1, "sl1", 'i');}
  0x2
  {0 {DELETE FROM t1 WHERE x IN(?,?,?);}}
} {
  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