1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/misc4.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,211 @@
1.4 +# 2004 Jun 27
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +#
1.16 +# This file implements tests for miscellanous features that were
1.17 +# left out of other test files.
1.18 +#
1.19 +# $Id: misc4.test,v 1.23 2007/12/08 18:01:31 drh Exp $
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +# Prepare a statement that will create a temporary table. Then do
1.25 +# a rollback. Then try to execute the prepared statement.
1.26 +#
1.27 +do_test misc4-1.1 {
1.28 + set DB [sqlite3_connection_pointer db]
1.29 + execsql {
1.30 + CREATE TABLE t1(x);
1.31 + INSERT INTO t1 VALUES(1);
1.32 + }
1.33 +} {}
1.34 +
1.35 +ifcapable tempdb {
1.36 + do_test misc4-1.2 {
1.37 + set sql {CREATE TEMP TABLE t2 AS SELECT * FROM t1}
1.38 + set stmt [sqlite3_prepare $DB $sql -1 TAIL]
1.39 + execsql {
1.40 + BEGIN;
1.41 + CREATE TABLE t3(a,b,c);
1.42 + INSERT INTO t1 SELECT * FROM t1;
1.43 + ROLLBACK;
1.44 + }
1.45 + } {}
1.46 +
1.47 + # Because the previous transaction included a DDL statement and
1.48 + # was rolled back, statement $stmt was marked as expired. Executing it
1.49 + # now returns SQLITE_SCHEMA.
1.50 + do_test misc4-1.2.1 {
1.51 + list [sqlite3_step $stmt] [sqlite3_finalize $stmt]
1.52 + } {SQLITE_ERROR SQLITE_SCHEMA}
1.53 + do_test misc4-1.2.2 {
1.54 + set stmt [sqlite3_prepare $DB $sql -1 TAIL]
1.55 + set TAIL
1.56 + } {}
1.57 +
1.58 + do_test misc4-1.3 {
1.59 + sqlite3_step $stmt
1.60 + } SQLITE_DONE
1.61 + do_test misc4-1.4 {
1.62 + execsql {
1.63 + SELECT * FROM temp.t2;
1.64 + }
1.65 + } {1}
1.66 +
1.67 + # Drop the temporary table, then rerun the prepared statement to
1.68 + # recreate it again. This recreates ticket #807.
1.69 + #
1.70 + do_test misc4-1.5 {
1.71 + execsql {DROP TABLE t2}
1.72 + sqlite3_reset $stmt
1.73 + sqlite3_step $stmt
1.74 + } {SQLITE_ERROR}
1.75 + do_test misc4-1.6 {
1.76 + sqlite3_finalize $stmt
1.77 + } {SQLITE_SCHEMA}
1.78 +}
1.79 +
1.80 +# Prepare but do not execute various CREATE statements. Then before
1.81 +# those statements are executed, try to use the tables, indices, views,
1.82 +# are triggers that were created.
1.83 +#
1.84 +do_test misc4-2.1 {
1.85 + set stmt [sqlite3_prepare $DB {CREATE TABLE t3(x);} -1 TAIL]
1.86 + catchsql {
1.87 + INSERT INTO t3 VALUES(1);
1.88 + }
1.89 +} {1 {no such table: t3}}
1.90 +do_test misc4-2.2 {
1.91 + sqlite3_step $stmt
1.92 +} SQLITE_DONE
1.93 +do_test misc4-2.3 {
1.94 + sqlite3_finalize $stmt
1.95 +} SQLITE_OK
1.96 +do_test misc4-2.4 {
1.97 + catchsql {
1.98 + INSERT INTO t3 VALUES(1);
1.99 + }
1.100 +} {0 {}}
1.101 +
1.102 +# Ticket #966
1.103 +#
1.104 +do_test misc4-3.1 {
1.105 + execsql {
1.106 + CREATE TABLE Table1(ID integer primary key, Value TEXT);
1.107 + INSERT INTO Table1 VALUES(1, 'x');
1.108 + CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);
1.109 + INSERT INTO Table2 VALUES(1, 'z');
1.110 + INSERT INTO Table2 VALUES (1, 'a');
1.111 + }
1.112 + catchsql {
1.113 + SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2 ORDER BY 1, 2;
1.114 + }
1.115 +} {1 {aggregate functions are not allowed in the GROUP BY clause}}
1.116 +ifcapable compound {
1.117 + do_test misc4-3.2 {
1.118 + execsql {
1.119 + SELECT ID, Value FROM Table1
1.120 + UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1
1.121 + ORDER BY 1, 2;
1.122 + }
1.123 + } {1 x 1 z}
1.124 + do_test misc4-3.3 {
1.125 + catchsql {
1.126 + SELECT ID, Value FROM Table1
1.127 + UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
1.128 + ORDER BY 1, 2;
1.129 + }
1.130 + } {1 {aggregate functions are not allowed in the GROUP BY clause}}
1.131 + do_test misc4-3.4 {
1.132 + catchsql {
1.133 + SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
1.134 + UNION SELECT ID, Value FROM Table1
1.135 + ORDER BY 1, 2;
1.136 + }
1.137 + } {1 {aggregate functions are not allowed in the GROUP BY clause}}
1.138 +} ;# ifcapable compound
1.139 +
1.140 +# Ticket #1047. Make sure column types are preserved in subqueries.
1.141 +#
1.142 +ifcapable subquery {
1.143 + do_test misc4-4.1 {
1.144 + execsql {
1.145 + create table a(key varchar, data varchar);
1.146 + create table b(key varchar, period integer);
1.147 + insert into a values('01','data01');
1.148 + insert into a values('+1','data+1');
1.149 +
1.150 + insert into b values ('01',1);
1.151 + insert into b values ('01',2);
1.152 + insert into b values ('+1',3);
1.153 + insert into b values ('+1',4);
1.154 +
1.155 + select a.*, x.*
1.156 + from a, (select key,sum(period) from b group by key) as x
1.157 + where a.key=x.key;
1.158 + }
1.159 + } {01 data01 01 3 +1 data+1 +1 7}
1.160 +
1.161 + # This test case tests the same property as misc4-4.1, but it is
1.162 + # a bit smaller which makes it easier to work with while debugging.
1.163 + do_test misc4-4.2 {
1.164 + execsql {
1.165 + CREATE TABLE ab(a TEXT, b TEXT);
1.166 + INSERT INTO ab VALUES('01', '1');
1.167 + }
1.168 + execsql {
1.169 + select * from ab, (select b from ab) as x where x.b = ab.a;
1.170 + }
1.171 + } {}
1.172 +}
1.173 +
1.174 +
1.175 +# Ticket #1036. When creating tables from a SELECT on a view, use the
1.176 +# short names of columns.
1.177 +#
1.178 +ifcapable view {
1.179 + do_test misc4-5.1 {
1.180 + execsql {
1.181 + create table t4(a,b);
1.182 + create table t5(a,c);
1.183 + insert into t4 values (1,2);
1.184 + insert into t5 values (1,3);
1.185 + create view myview as select t4.a a from t4 inner join t5 on t4.a=t5.a;
1.186 + create table problem as select * from myview;
1.187 + }
1.188 + execsql2 {
1.189 + select * FROM problem;
1.190 + }
1.191 + } {a 1}
1.192 + do_test misc4-5.2 {
1.193 + execsql2 {
1.194 + create table t6 as select * from t4, t5;
1.195 + select * from t6;
1.196 + }
1.197 + } {a 1 b 2 a:1 1 c 3}
1.198 +}
1.199 +
1.200 +# Ticket #1086
1.201 +do_test misc4-6.1 {
1.202 + execsql {
1.203 + CREATE TABLE abc(a);
1.204 + INSERT INTO abc VALUES(1);
1.205 + CREATE TABLE def(d, e, f, PRIMARY KEY(d, e));
1.206 + }
1.207 +} {}
1.208 +do_test misc4-6.2 {
1.209 + execsql {
1.210 + SELECT a FROM abc LEFT JOIN def ON (abc.a=def.d);
1.211 + }
1.212 +} {1}
1.213 +
1.214 +finish_test