xref: /sqlite-3.40.0/test/parser1.test (revision 108aa00a) 
1# 2014-08-24
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# 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.tcl
17
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 have
28# COLLATE, ASC, and DESC keywords on the id list of a FK constraint, and that
29# those keywords are silently ignored.
30#
31do_execsql_test parser1-1.2 {
32  CREATE TABLE t1(
33    a TEXT PRIMARY KEY,
34    b TEXT,
35    FOREIGN KEY(b) REFERENCES t1(a)
36  );
37  INSERT INTO t1 VALUES('abc',NULL),('xyz','abc');
38  PRAGMA writable_schema=on;
39  UPDATE sqlite_master SET sql='CREATE TABLE t1(
40    a TEXT PRIMARY KEY,
41    b TEXT,
42    FOREIGN KEY(b COLLATE nocase) REFERENCES t1(a)
43  )' WHERE name='t1';
44  SELECT name FROM sqlite_master WHERE sql LIKE '%collate%';
45} {t1}
46sqlite3 db2 test.db
47do_test parser1-1.3 {
48  sqlite3 db2 test.db
49  db2 eval {SELECT * FROM t1 ORDER BY 1}
50} {abc {} xyz abc}
51db2 close
52
53do_execsql_test parser1-1.4 {
54  UPDATE sqlite_master SET sql='CREATE TABLE t1(
55    a TEXT PRIMARY KEY,
56    b TEXT,
57    FOREIGN KEY(b ASC) REFERENCES t1(a)
58  )' WHERE name='t1';
59  SELECT name FROM sqlite_master WHERE sql LIKE '%ASC%';
60} {t1}
61sqlite3 db2 test.db
62do_test parser1-1.5 {
63  sqlite3 db2 test.db
64  db2 eval {SELECT * FROM t1 ORDER BY 1}
65} {abc {} xyz abc}
66db2 close
67
68do_catchsql_test parser1-2.1 {
69  WITH RECURSIVE
70    c(x COLLATE binary) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5)
71  SELECT x FROM c;
72} {1 {syntax error after column name "x"}}
73do_catchsql_test parser1-2.2 {
74  WITH RECURSIVE
75    c(x ASC) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5)
76  SELECT x FROM c;
77} {1 {syntax error after column name "x"}}
78
79finish_test
80