Disallow the use of COLLATE clauses and the ASC and DESC keywords within

foreign key constraints and in the argument list to common table expressions.

FossilOrigin-Name: 83cbc4d8761498647794affffa961a4fca311be7

1 files changed, 58 insertions, 0 deletions

diff --git a/test/parser1.test b/test/parser1.test
new file mode 100644
index 000000000..f4c3227f8
--- /dev/null
+++ b/test/parser1.test
@@ -0,0 +1,58 @@
+# 2014-08-24
+#
+# 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.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.
+# The focus of this script is testing details of the SQL language parser.
+# 
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+do_catchsql_test parser1-1.1 {
+  CREATE TABLE t1(
+    a TEXT PRIMARY KEY,
+    b TEXT,
+    FOREIGN KEY(b COLLATE nocase DESC) REFERENCES t1(a COLLATE binary ASC)
+  );
+} {1 {syntax error after column name "a"}}
+do_execsql_test parser1-1.2 {
+  CREATE TABLE t1(
+    a TEXT PRIMARY KEY,
+    b TEXT,
+    FOREIGN KEY(b) REFERENCES t1(a)
+  );
+  INSERT INTO t1 VALUES('abc',NULL),('xyz','abc');
+  PRAGMA writable_schema=on;
+  UPDATE sqlite_master SET sql='CREATE TABLE t1(
+    a TEXT PRIMARY KEY,
+    b TEXT,
+    FOREIGN KEY(b COLLATE nocase) REFERENCES t1(a)
+  )' WHERE name='t1';
+  SELECT name FROM sqlite_master WHERE sql LIKE '%collate%';
+} {t1}
+sqlite3 db2 test.db
+do_test parser1-1.3 {
+  sqlite3 db2 test.db
+  db2 eval {SELECT * FROM t1 ORDER BY 1}
+} {abc {} xyz abc}
+
+
+do_catchsql_test parser1-2.1 {
+  WITH RECURSIVE
+    c(x COLLATE binary) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5)
+  SELECT x FROM c;
+} {1 {syntax error after column name "x"}}
+do_catchsql_test parser1-2.2 {
+  WITH RECURSIVE
+    c(x ASC) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5)
+  SELECT x FROM c;
+} {1 {syntax error after column name "x"}}
+
+finish_test