1 # 2004 Jun 27

     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 #

    13 # This file implements tests for miscellanous features that were

    14 # left out of other test files.

    15 #

    16 # $Id: misc4.test,v 1.23 2007/12/08 18:01:31 drh Exp $

    17 

    18 set testdir [file dirname $argv0]

    19 source $testdir/tester.tcl

    20 

    21 # Prepare a statement that will create a temporary table.  Then do

    22 # a rollback.  Then try to execute the prepared statement.

    23 #

    24 do_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 

    32 ifcapable 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 and

    45   # was rolled back, statement $stmt was marked as expired. Executing it

    46   # 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 TAIL

    53   } {}

    54 

    55   do_test misc4-1.3 {

    56     sqlite3_step $stmt

    57   } SQLITE_DONE

    58   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 to

    65   # recreate it again.  This recreates ticket #807.

    66   #

    67   do_test misc4-1.5 {

    68     execsql {DROP TABLE t2}

    69     sqlite3_reset $stmt

    70     sqlite3_step $stmt

    71   } {SQLITE_ERROR}

    72   do_test misc4-1.6 {

    73     sqlite3_finalize $stmt

    74   } {SQLITE_SCHEMA}

    75 }

    76 

    77 # Prepare but do not execute various CREATE statements.  Then before

    78 # those statements are executed, try to use the tables, indices, views,

    79 # are triggers that were created.

    80 #

    81 do_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}}

    87 do_test misc4-2.2 {

    88   sqlite3_step $stmt

    89 } SQLITE_DONE

    90 do_test misc4-2.3 {

    91   sqlite3_finalize $stmt

    92 } SQLITE_OK

    93 do_test misc4-2.4 {

    94   catchsql {

    95     INSERT INTO t3 VALUES(1);

    96   }

    97 } {0 {}}

    98 

    99 # Ticket #966

   100 #

   101 do_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}}

   113 ifcapable compound {

   114   do_test misc4-3.2 {

   115     execsql {

   116       SELECT ID, Value FROM Table1

   117          UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1

   118       ORDER BY 1, 2;

   119     }

   120   } {1 x 1 z}

   121   do_test misc4-3.3 {

   122     catchsql { 

   123       SELECT ID, Value FROM Table1

   124          UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2

   125       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, 2

   131          UNION SELECT ID, Value FROM Table1

   132       ORDER BY 1, 2;

   133     }

   134   } {1 {aggregate functions are not allowed in the GROUP BY clause}}

   135 } ;# ifcapable compound

   136 

   137 # Ticket #1047.  Make sure column types are preserved in subqueries.

   138 #

   139 ifcapable 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 x

   154         where a.key=x.key;

   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 is

   159   # 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 the

   173 # short names of columns.

   174 #

   175 ifcapable 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 #1086

   198 do_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 } {}

   205 do_test misc4-6.2 {

   206   execsql {

   207     SELECT a FROM abc LEFT JOIN def ON (abc.a=def.d);

   208   }

   209 } {1}

   210 

   211 finish_test