AryaWu/sqlite

1# 2014-08-242#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# This file implements regression tests for SQLite library.12# The focus of this script is testing details of the SQL language parser.13# 14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 18do_catchsql_test parser1-1.1 {19  CREATE TABLE t1(20    a TEXT PRIMARY KEY,21    b TEXT,22    FOREIGN KEY(b COLLATE nocase DESC) REFERENCES t1(a COLLATE binary ASC)23  );24} {1 {syntax error after column name "b"}}25 26 27# Verify that a legacy schema in the sqlite_master file is allowed to have28# COLLATE, ASC, and DESC keywords on the id list of a FK constraint, and that29# those keywords are silently ignored.30#31sqlite3_db_config db DEFENSIVE 032do_execsql_test parser1-1.2 {33  CREATE TABLE t1(34    a TEXT PRIMARY KEY,35    b TEXT,36    FOREIGN KEY(b) REFERENCES t1(a)37  );38  INSERT INTO t1 VALUES('abc',NULL),('xyz','abc');39  PRAGMA writable_schema=on;40  UPDATE sqlite_master SET sql='CREATE TABLE t1(41    a TEXT PRIMARY KEY,42    b TEXT,43    FOREIGN KEY(b COLLATE nocase) REFERENCES t1(a)44  )' WHERE name='t1';45  SELECT name FROM sqlite_master WHERE sql LIKE '%collate%';46} {t1}47sqlite3 db2 test.db48do_test parser1-1.3 {49  sqlite3 db2 test.db50  db2 eval {SELECT * FROM t1 ORDER BY 1}51} {abc {} xyz abc}52db2 close53 54do_execsql_test parser1-1.4 {55  UPDATE sqlite_master SET sql='CREATE TABLE t1(56    a TEXT PRIMARY KEY,57    b TEXT,58    FOREIGN KEY(b ASC) REFERENCES t1(a)59  )' WHERE name='t1';60  SELECT name FROM sqlite_master WHERE sql LIKE '%ASC%';61} {t1}62sqlite3 db2 test.db63do_test parser1-1.5 {64  sqlite3 db2 test.db65  db2 eval {SELECT * FROM t1 ORDER BY 1}66} {abc {} xyz abc}67db2 close68 69do_catchsql_test parser1-2.1 {70  WITH RECURSIVE71    c(x COLLATE binary) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5)72  SELECT x FROM c;73} {1 {syntax error after column name "x"}}74do_catchsql_test parser1-2.2 {75  WITH RECURSIVE76    c(x ASC) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5)77  SELECT x FROM c;78} {1 {syntax error after column name "x"}}79 80# Verify that the comma between multiple table constraints is81# optional.82#83# The missing comma is technically a syntax error.  But we have to support84# it because there might be legacy databases that omit the commas in their85# sqlite_master tables.86#87do_execsql_test parser1-3.1 {88  CREATE TABLE t300(id INTEGER PRIMARY KEY);89  CREATE TABLE t301(90    id INTEGER PRIMARY KEY,91    c1 INTEGER NOT NULL,92    c2 INTEGER NOT NULL,93    c3 BOOLEAN NOT NULL DEFAULT 0,94    FOREIGN KEY(c1) REFERENCES t300(id) ON DELETE CASCADE ON UPDATE RESTRICT95        /* no comma */96    FOREIGN KEY(c2) REFERENCES t300(id) ON DELETE CASCADE ON UPDATE RESTRICT97        /* no comma */98    UNIQUE(c1, c2)99  );100  PRAGMA foreign_key_list(t301);101} {0 0 t300 c2 id RESTRICT CASCADE NONE 1 0 t300 c1 id RESTRICT CASCADE NONE}102 103# 2025-07-01 https://sqlite.org/forum/forumpost/f4878de3e7dd4764104# Do not allow parse-time optimizations to omit aggregate functions,105# because doing so can change the meaning of the query.106#107unset -nocomplain zero108set zero [expr {0+0}]109do_execsql_test parser1-4.1 {110  DROP TABLE IF EXISTS t1;111  CREATE TABLE t1(x);112  SELECT max(x) AND $zero FROM t1;113} 0114do_execsql_test parser1-4.2 {115  SELECT max(x) AND 0 FROM t1;116} 0117do_execsql_test parser1-4.3 {118  SELECT max(x) IN () FROM t1;119} 0120do_execsql_test parser1-4.4 {121  SELECT max(x) NOT IN () FROM t1;122} 1123 124 125finish_test126