AryaWu/sqlite
0
1# 2004 Jun 272#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#13# This file implements tests for miscellanous features that were14# left out of other test files.15#16# $Id: misc4.test,v 1.23 2007/12/08 18:01:31 drh Exp $17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21# Prepare a statement that will create a temporary table. Then do22# a rollback. Then try to execute the prepared statement.23#24do_test misc4-1.1 {25 set DB [sqlite3_connection_pointer db]26 execsql {27 CREATE TABLE t1(x);28 INSERT INTO t1 VALUES(1);29 }30} {}31 32ifcapable tempdb {33 do_test misc4-1.2 {34 set sql {CREATE TEMP TABLE t2 AS SELECT * FROM t1}35 set stmt [sqlite3_prepare $DB $sql -1 TAIL]36 execsql {37 BEGIN;38 CREATE TABLE t3(a,b,c);39 INSERT INTO t1 SELECT * FROM t1;40 ROLLBACK;41 }42 } {}43 44 # Because the previous transaction included a DDL statement and45 # was rolled back, statement $stmt was marked as expired. Executing it46 # now returns SQLITE_SCHEMA.47 do_test misc4-1.2.1 {48 list [sqlite3_step $stmt] [sqlite3_finalize $stmt]49 } {SQLITE_ERROR SQLITE_SCHEMA}50 do_test misc4-1.2.2 {51 set stmt [sqlite3_prepare $DB $sql -1 TAIL]52 set TAIL53 } {}54 55 do_test misc4-1.3 {56 sqlite3_step $stmt57 } SQLITE_DONE58 do_test misc4-1.4 {59 execsql {60 SELECT * FROM temp.t2;61 }62 } {1}63 64 # Drop the temporary table, then rerun the prepared statement to65 # recreate it again. This recreates ticket #807.66 #67 do_test misc4-1.5 {68 execsql {DROP TABLE t2}69 sqlite3_reset $stmt70 sqlite3_step $stmt71 } {SQLITE_ERROR}72 do_test misc4-1.6 {73 sqlite3_finalize $stmt74 } {SQLITE_SCHEMA}75}76 77# Prepare but do not execute various CREATE statements. Then before78# those statements are executed, try to use the tables, indices, views,79# are triggers that were created.80#81do_test misc4-2.1 {82 set stmt [sqlite3_prepare $DB {CREATE TABLE t3(x);} -1 TAIL]83 catchsql {84 INSERT INTO t3 VALUES(1);85 }86} {1 {no such table: t3}}87do_test misc4-2.2 {88 sqlite3_step $stmt89} SQLITE_DONE90do_test misc4-2.3 {91 sqlite3_finalize $stmt92} SQLITE_OK93do_test misc4-2.4 {94 catchsql {95 INSERT INTO t3 VALUES(1);96 }97} {0 {}}98 99# Ticket #966100#101do_test misc4-3.1 {102 execsql { 103 CREATE TABLE Table1(ID integer primary key, Value TEXT);104 INSERT INTO Table1 VALUES(1, 'x');105 CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);106 INSERT INTO Table2 VALUES(1, 'z');107 INSERT INTO Table2 VALUES (1, 'a');108 }109 catchsql { 110 SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2 ORDER BY 1, 2;111 }112} {1 {aggregate functions are not allowed in the GROUP BY clause}}113ifcapable compound {114 do_test misc4-3.2 {115 execsql {116 SELECT ID, Value FROM Table1117 UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1118 ORDER BY 1, 2;119 }120 } {1 x 1 z}121 do_test misc4-3.3 {122 catchsql { 123 SELECT ID, Value FROM Table1124 UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2125 ORDER BY 1, 2;126 }127 } {1 {aggregate functions are not allowed in the GROUP BY clause}}128 do_test misc4-3.4 {129 catchsql { 130 SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2131 UNION SELECT ID, Value FROM Table1132 ORDER BY 1, 2;133 }134 } {1 {aggregate functions are not allowed in the GROUP BY clause}}135} ;# ifcapable compound136 137# Ticket #1047. Make sure column types are preserved in subqueries.138#139ifcapable subquery {140 do_test misc4-4.1 {141 execsql {142 create table a(key varchar, data varchar);143 create table b(key varchar, period integer);144 insert into a values('01','data01');145 insert into a values('+1','data+1');146 147 insert into b values ('01',1);148 insert into b values ('01',2);149 insert into b values ('+1',3);150 insert into b values ('+1',4);151 152 select a.*, x.*153 from a, (select key,sum(period) from b group by key) as x154 where a.key=x.key order by 1 desc;155 }156 } {01 data01 01 3 +1 data+1 +1 7}157 158 # This test case tests the same property as misc4-4.1, but it is159 # a bit smaller which makes it easier to work with while debugging.160 do_test misc4-4.2 {161 execsql {162 CREATE TABLE ab(a TEXT, b TEXT);163 INSERT INTO ab VALUES('01', '1');164 }165 execsql {166 select * from ab, (select b from ab) as x where x.b = ab.a;167 }168 } {}169}170 171 172# Ticket #1036. When creating tables from a SELECT on a view, use the173# short names of columns.174#175ifcapable view {176 do_test misc4-5.1 {177 execsql {178 create table t4(a,b);179 create table t5(a,c);180 insert into t4 values (1,2);181 insert into t5 values (1,3);182 create view myview as select t4.a a from t4 inner join t5 on t4.a=t5.a;183 create table problem as select * from myview; 184 }185 execsql2 {186 select * FROM problem;187 }188 } {a 1}189 do_test misc4-5.2 {190 execsql2 {191 create table t6 as select * from t4, t5;192 select * from t6;193 }194 } {a 1 b 2 a:1 1 c 3}195}196 197# Ticket #1086198do_test misc4-6.1 {199 execsql {200 CREATE TABLE abc(a);201 INSERT INTO abc VALUES(1);202 CREATE TABLE def(d, e, f, PRIMARY KEY(d, e));203 }204} {}205do_test misc4-6.2 {206 execsql {207 SELECT a FROM abc LEFT JOIN def ON (abc.a=def.d);208 }209} {1}210 211# 2015-05-15. Error message formatting problem.212#213db close214sqlite3 db :memory:215sqlite3_db_config db DEFENSIVE 0216do_catchsql_test misc4-7.1 {217 CREATE TABLE t7(x);218 PRAGMA writable_schema=ON;219 UPDATE sqlite_master SET sql='CREATE TABLE [M%s%s%s%s%s%s%s%s%s%s%s%s%s';220 VACUUM;221} {1 {unrecognized token: "[M%s%s%s%s%s%s%s%s%s%s%s%s%s"}}222 223# 2015-05-18. Use of ephermeral Mem content after the cursor that holds224# the canonical content has moved on.225#226do_execsql_test misc4-7.2 {227 CREATE TABLE t0(a,b);228 INSERT INTO t0 VALUES(1,0),(2,0);229 UPDATE t0 SET b=9 WHERE a AND (SELECT a FROM t0 WHERE a);230 SELECT * FROM t0 ORDER BY +a;231} {1 9 2 9}232 233finish_test234