xref: /sqlite-3.40.0/test/quote.test (revision 7d44b22d) 
1# 2001 September 15
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.  The
12# focus of this file is the ability to specify table and column names
13# as quoted strings.
14#
15# $Id: quote.test,v 1.7 2007/04/25 11:32:30 drh Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19set testprefix quote
20
21# Create a table with a strange name and with strange column names.
22#
23do_test quote-1.0 {
24  catchsql {CREATE TABLE '@abc' ( '#xyz' int, '!pqr' text );}
25} {0 {}}
26
27# Insert, update and query the table.
28#
29do_test quote-1.1 {
30  catchsql {INSERT INTO '@abc' VALUES(5,'hello')}
31} {0 {}}
32do_test quote-1.2.1 {
33  catchsql {SELECT * FROM '@abc'}
34} {0 {5 hello}}
35do_test quote-1.2.2 {
36  catchsql {SELECT * FROM [@abc]}  ;# SqlServer compatibility
37} {0 {5 hello}}
38do_test quote-1.2.3 {
39  catchsql {SELECT * FROM `@abc`}  ;# MySQL compatibility
40} {0 {5 hello}}
41do_test quote-1.3 {
42  catchsql {
43    SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'
44  }
45} {0 {hello 10}}
46do_test quote-1.3.1 {
47  catchsql {
48    SELECT '!pqr', '#xyz'+5 FROM '@abc'
49  }
50} {0 {!pqr 5}}
51do_test quote-1.3.2 {
52  catchsql {
53    SELECT "!pqr", "#xyz"+5 FROM '@abc'
54  }
55} {0 {hello 10}}
56do_test quote-1.3.3 {
57  catchsql {
58    SELECT [!pqr], `#xyz`+5 FROM '@abc'
59  }
60} {0 {hello 10}}
61do_test quote-1.3.4 {
62  set r [catch {
63    execsql {SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'}
64  } msg ]
65  lappend r $msg
66} {0 {hello 10}}
67do_test quote-1.4 {
68  set r [catch {
69    execsql {UPDATE '@abc' SET '#xyz'=11}
70  } msg ]
71  lappend r $msg
72} {0 {}}
73do_test quote-1.5 {
74  set r [catch {
75    execsql {SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'}
76  } msg ]
77  lappend r $msg
78} {0 {hello 16}}
79
80# Drop the table with the strange name.
81#
82do_test quote-1.6 {
83  set r [catch {
84    execsql {DROP TABLE '@abc'}
85  } msg ]
86  lappend r $msg
87} {0 {}}
88
89#-------------------------------------------------------------------------
90# Check that it is not possible to use double-quotes for a string
91# constant in a CHECK constraint or CREATE INDEX statement. However,
92# SQLite can load such a schema from disk.
93#
94reset_db
95sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 0
96sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1
97do_execsql_test 2.0 {
98  CREATE TABLE t1(x, y, z);
99}
100foreach {tn sql errname} {
101  1 { CREATE TABLE xyz(a, b, c CHECK (c!="null") ) } null
102  2 { CREATE INDEX i2 ON t1(x, y, z||"abc") }        abc
103  3 { CREATE INDEX i3 ON t1("w") }                   w
104  4 { CREATE INDEX i4 ON t1(x) WHERE z="w" }         w
105} {
106  do_catchsql_test 2.1.$tn $sql [list 1 "no such column: $errname"]
107}
108
109do_execsql_test 2.2 {
110  PRAGMA writable_schema = 1;
111  CREATE TABLE xyz(a, b, c CHECK (c!="null") );
112  CREATE INDEX i2 ON t1(x, y, z||"abc");
113  CREATE INDEX i3 ON t1("w"||"");
114  CREATE INDEX i4 ON t1(x) WHERE z="w";
115}
116
117db close
118sqlite3 db test.db
119
120do_execsql_test 2.3.1 {
121  INSERT INTO xyz VALUES(1, 2, 3);
122}
123do_catchsql_test 2.3.2 {
124  INSERT INTO xyz VALUES(1, 2, 'null');
125} {1 {CHECK constraint failed: c!="null"}}
126
127do_execsql_test 2.4 {
128  INSERT INTO t1 VALUES(1, 2, 3);
129  INSERT INTO t1 VALUES(4, 5, 'w');
130  SELECT * FROM t1 WHERE z='w';
131} {4 5 w}
132do_execsql_test 2.5 {
133  SELECT sql FROM sqlite_master;
134} {
135  {CREATE TABLE t1(x, y, z)}
136  {CREATE TABLE xyz(a, b, c CHECK (c!="null") )}
137  {CREATE INDEX i2 ON t1(x, y, z||"abc")}
138  {CREATE INDEX i3 ON t1("w"||"")}
139  {CREATE INDEX i4 ON t1(x) WHERE z="w"}
140}
141
142# 2021-03-13
143# ticket 1c24a659e6d7f3a1
144ifcapable altertable {
145  reset_db
146    do_catchsql_test 3.0 {
147      CREATE TABLE t1(a,b);
148      CREATE INDEX x1 on t1("b");
149      ALTER TABLE t1 DROP COLUMN b;
150    } {1 {error in index x1 after drop column: no such column: b}}
151  do_catchsql_test 3.1 {
152    DROP TABLE t1;
153    CREATE TABLE t1(a,"b");
154    CREATE INDEX x1 on t1("b");
155    ALTER TABLE t1 DROP COLUMN b;
156  } {1 {error in index x1 after drop column: no such column: b}}
157  do_catchsql_test 3.2 {
158    DROP TABLE t1;
159    CREATE TABLE t1(a,'b');
160    CREATE INDEX x1 on t1("b");
161    ALTER TABLE t1 DROP COLUMN b;
162  } {1 {error in index x1 after drop column: no such column: b}}
163  do_catchsql_test 3.3 {
164    DROP TABLE t1;
165    CREATE TABLE t1(a,"b");
166    CREATE INDEX x1 on t1('b');
167    ALTER TABLE t1 DROP COLUMN b;
168  } {1 {error in index x1 after drop column: no such column: b}}
169  do_catchsql_test 3.4 {
170    DROP TABLE t1;
171    CREATE TABLE t1(a, b, c);
172    CREATE INDEX x1 ON t1("a"||"b");
173    INSERT INTO t1 VALUES(1,2,3),(1,4,5);
174    ALTER TABLE t1 DROP COLUMN b;
175  } {1 {error in index x1 after drop column: no such column: b}}
176  sqlite3_db_config db SQLITE_DBCONFIG_DQS_DDL 1
177  do_catchsql_test 3.5 {
178    DROP TABLE t1;
179    CREATE TABLE t1(a, b, c);
180    CREATE INDEX x1 ON t1("a"||"x");
181    INSERT INTO t1 VALUES(1,2,3),(1,4,5);
182    ALTER TABLE t1 DROP COLUMN b;
183  } {0 {}}
184}
185
186finish_test
187