Return to misc4.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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 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 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