SQLite4
Check-in [d6d0e21ad2]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Run some more legacy tests. Fix some issues with converting between text and numeric values.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d6d0e21ad2770739d086a759f6a8133142835974
User & Date: dan 2013-06-27 20:23:03
Context
2013-06-29
18:46
Run further legacy tests. Fixes for bugs brought to light by the same. check-in: cb7a8b464e user: dan tags: trunk
2013-06-27
20:23
Run some more legacy tests. Fix some issues with converting between text and numeric values. check-in: d6d0e21ad2 user: dan tags: trunk
17:40
Run some more legacy test cases. check-in: 731841907a user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/math.c.

   348    348         nIn -= 1;
   349    349       }
   350    350     }
   351    351     
   352    352     /* If the IGNORE_WHITESPACE flag is set, ignore any leading whitespace. */
   353    353     i = 0;
   354    354     if( flags & SQLITE4_IGNORE_WHITESPACE ){
   355         -    while( sqlite4Isspace(zIn[i]) && i<nIn ) i+=incr;
          355  +    while( i<nIn && sqlite4Isspace(zIn[i]) ) i+=incr;
   356    356     }
   357    357     if( nIn<=i ) return error_value;
   358    358   
   359    359     /* Check for a leading '+' or '-' symbol. */
   360    360     if( zIn[i]=='-' ){
   361    361       r.sign = 1;
   362    362       i += incr;
................................................................................
   632    632         memcpy(z, "NaN", 4);
   633    633       }else{
   634    634         memcpy(z, "inf", 4);
   635    635       }
   636    636       return (z - zOut)+3;
   637    637     }
   638    638     if( x.m==0 ){
   639         -    memcpy(z, "0", 2);
          639  +    if( bReal ){
          640  +      memcpy(z, "0.0", 4);
          641  +    }else{
          642  +      memcpy(z, "0", 2);
          643  +    }
   640    644       return 1+(z-zOut);
   641    645     }
   642    646     zNum = renderInt(x.m, zBuf, sizeof(zBuf));
   643    647     n = &zBuf[sizeof(zBuf)-1] - zNum;
   644    648     if( x.e>=0 && x.e+n<=25 ){
   645    649       /* Integer values with up to 25 digits */
   646    650       memcpy(z, zNum, n+1);

Changes to src/vdbemem.c.

   331    331   */
   332    332   int sqlite4VdbeMemIntegerify(Mem *pMem){
   333    333     assert( pMem->db==0 || sqlite4_mutex_held(pMem->db->mutex) );
   334    334     assert( (pMem->flags & MEM_RowSet)==0 );
   335    335     assert( EIGHT_BYTE_ALIGNMENT(pMem) );
   336    336   
   337    337     if( (pMem->flags & MEM_Int)==0 ){
   338         -    pMem->u.num = sqlite4_num_from_int64(sqlite4VdbeIntValue(pMem));
          338  +    if( pMem->flags & (MEM_Real|MEM_Null) ){
          339  +      pMem->u.num = sqlite4_num_from_int64(sqlite4VdbeIntValue(pMem));
          340  +    }else{
          341  +      unsigned int flags = pMem->enc |
          342  +          SQLITE4_INTEGER_ONLY|SQLITE4_PREFIX_ONLY|SQLITE4_IGNORE_WHITESPACE;
          343  +      pMem->u.num = sqlite4_num_from_text(pMem->z, pMem->n, flags, 0);
          344  +    }
   339    345       MemSetTypeFlag(pMem, MEM_Int);
   340    346     }
   341    347     return SQLITE4_OK;
   342    348   }
   343    349   
   344    350   /*
   345    351   ** Convert pMem so that it has types MEM_Real or MEM_Int or both.
................................................................................
   352    358   int sqlite4VdbeMemNumerify(Mem *pMem){
   353    359     if( (pMem->flags & (MEM_Int|MEM_Real|MEM_Null))==0 ){
   354    360       int bReal = 0;
   355    361       int flags = (pMem->enc | SQLITE4_PREFIX_ONLY | SQLITE4_IGNORE_WHITESPACE);
   356    362   
   357    363       assert( (pMem->flags & (MEM_Blob|MEM_Str))!=0 );
   358    364       assert( pMem->db==0 || sqlite4_mutex_held(pMem->db->mutex) );
   359         -    pMem->u.num = sqlite4_num_from_text(pMem->z, pMem->n, flags, &bReal);
          365  +    pMem->u.num = sqlite4_num_from_text(pMem->z, pMem->n, flags, 0);
          366  +    sqlite4_num_to_int64(pMem->u.num, &bReal);
   360    367       MemSetTypeFlag(pMem, (bReal ? MEM_Real : MEM_Int));
   361    368     }
   362    369     assert( (pMem->flags & (MEM_Int|MEM_Real|MEM_Null))!=0 );
   363    370     pMem->flags &= ~(MEM_Str|MEM_Blob);
   364    371     return SQLITE4_OK;
   365    372   }
   366    373   

Changes to test/e_createtable.test.

   361    361   do_createtable_tests 1.1.1 -error {
   362    362     object name reserved for internal use: %s
   363    363   } {
   364    364     1    "CREATE TABLE sqlite_abc(a, b, c)"        sqlite_abc
   365    365     2    "CREATE TABLE temp.sqlite_helloworld(x)"  sqlite_helloworld
   366    366     3    {CREATE TABLE auxa."sqlite__"(x, y)}      sqlite__
   367    367     4    {CREATE TABLE auxb."sqlite_"(z)}          sqlite_
   368         -  5    {CREATE TABLE "SQLITE4_TBL"(z)}            SQLITE4_TBL
          368  +  5    {CREATE TABLE "SQLITE_TBL"(z)}            SQLITE_TBL
   369    369   }
   370    370   do_createtable_tests 1.1.2 {
   371    371     1    "CREATE TABLE sqlit_abc(a, b, c)"         {}
   372    372     2    "CREATE TABLE temp.sqlitehelloworld(x)"   {}
   373    373     3    {CREATE TABLE auxa."sqlite"(x, y)}        {}
   374    374     4    {CREATE TABLE auxb."sqlite-"(z)}          {}
   375    375     5    {CREATE TABLE "SQLITE-TBL"(z)}            {}
................................................................................
   612    612   #
   613    613   #   table_column_decltypes TBL
   614    614   #     The argument must be a table name. Return a list of column declared
   615    615   #     types, from left to right, for the table.
   616    616   #
   617    617   proc sci {select cmd} {
   618    618     set res [list]
   619         -  set STMT [sqlite4_prepare_v2 db $select -1 dummy]
          619  +  set STMT [sqlite4_prepare db $select -1 dummy]
   620    620     for {set i 0} {$i < [sqlite4_column_count $STMT]} {incr i} {
   621    621       lappend res [$cmd $STMT $i]
   622    622     }
   623    623     sqlite4_finalize $STMT
   624    624     set res
   625    625   }
   626    626   proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }
................................................................................
   917    917   set sqlite_current_time 1000000000
   918    918   do_createtable_tests 3.5 -query {
   919    919     SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f), 
   920    920            quote(g), quote(h), quote(i), quote(j), quote(k)
   921    921     FROM t4 ORDER BY rowid DESC LIMIT 1;
   922    922   } {
   923    923     1 "INSERT INTO t4 DEFAULT VALUES" {
   924         -    NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 
          924  +    NULL {'string constant'} x'424c4f42' 1 -1 3.14 -3.14 
   925    925       'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
   926    926     }
   927    927   
   928    928     2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" {
   929    929       1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
   930    930     }
   931    931   
   932    932     3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" {
   933         -    NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1
          933  +    NULL {'string constant'} x'424c4f42' 1 -1 3.14 -3.14 'acd' 3 2 1
   934    934     }
   935    935   
   936    936     4 "INSERT INTO t4(a,b,c,d,e,f,g,h,i,j,k) VALUES(1,2,3,4,5,6,7,8,9,10,11)" {
   937    937       1 2 3 4 5 6 7 8 9 10 11
   938    938     }
   939    939   }
   940    940   
................................................................................
   950    950       d DEFAULT -45678.6,
   951    951       e DEFAULT 394507
   952    952     );
   953    953   } {}
   954    954   do_execsql_test e_createtable-3.6.2 {
   955    955     INSERT INTO t5 DEFAULT VALUES;
   956    956     SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5;
   957         -} {NULL {'text value'} X'424C4F42' -45678.6 394507}
          957  +} {NULL {'text value'} x'424c4f42' -45678.6 394507}
   958    958   
   959    959   # EVIDENCE-OF: R-60616-50251 If the default value of a column is an
   960    960   # expression in parentheses, then the expression is evaluated once for
   961    961   # each row inserted and the results used in the new row.
   962    962   #
   963    963   #   Test case 3.6.4 demonstrates that the expression is evaluated 
   964    964   #   separately for each row if the INSERT is an "INSERT INTO ... SELECT ..."
................................................................................
  1085   1085   # default compile time value.
  1086   1086   #
  1087   1087   sqlite4_limit db SQLITE4_LIMIT_COLUMN [expr $::SQLITE4_MAX_COLUMN+2]
  1088   1088   do_catchsql_test e_createtable-3.11.3 [subst {
  1089   1089     CREATE TABLE t11([columns [expr $::SQLITE4_MAX_COLUMN+1]]);
  1090   1090   }] {1 {too many columns on t11}}
  1091   1091   
  1092         -sqlite4_limit db SQLITE4_LIMIT_LENGTH 90010
         1092  +sqlite4_limit db SQLITE4_LIMIT_LENGTH 90013
  1093   1093   do_execsql_test e_createtable-3.11.4 {
  1094   1094     CREATE TABLE t12(a, b, c);
  1095   1095     INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000));
  1096   1096   } {}
  1097   1097   do_catchsql_test e_createtable-3.11.5 {
  1098   1098     INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));
  1099   1099   } {1 {string or blob too big}}
................................................................................
  1181   1181     CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
  1182   1182     INSERT INTO t2 VALUES(0,          'zero');
  1183   1183     INSERT INTO t2 VALUES(45.5,       'one');
  1184   1184     INSERT INTO t2 VALUES('brambles', 'two');
  1185   1185     INSERT INTO t2 VALUES(X'ABCDEF',  'three');
  1186   1186   } {}
  1187   1187   
  1188         -do_createtable_tests 4.3.1 -error { %s not unique } {
  1189         -  1    "INSERT INTO t1 VALUES(0, 0)"                 {"column x is"}
  1190         -  2    "INSERT INTO t1 VALUES(45.5, 'abc')"          {"column x is"}
  1191         -  3    "INSERT INTO t1 VALUES(0.0, 'abc')"           {"column x is"}
  1192         -  4    "INSERT INTO t1 VALUES('brambles', 'abc')"    {"column x is"}
  1193         -  5    "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')"     {"column x is"}
         1188  +do_createtable_tests 4.3.1 -error { PRIMARY KEY must be unique%s } {
         1189  +  1    "INSERT INTO t1 VALUES(0, 0)"                 {""}
         1190  +  2    "INSERT INTO t1 VALUES(45.5, 'abc')"          {""}
         1191  +  3    "INSERT INTO t1 VALUES(0.0, 'abc')"           {""}
         1192  +  4    "INSERT INTO t1 VALUES('brambles', 'abc')"    {""}
         1193  +  5    "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')"     {""}
  1194   1194   
  1195         -  6    "INSERT INTO t2 VALUES(0, 'zero')"            {"columns x, y are"}
  1196         -  7    "INSERT INTO t2 VALUES(45.5, 'one')"          {"columns x, y are"}
  1197         -  8    "INSERT INTO t2 VALUES(0.0, 'zero')"          {"columns x, y are"}
  1198         -  9    "INSERT INTO t2 VALUES('brambles', 'two')"    {"columns x, y are"}
  1199         -  10   "INSERT INTO t2 VALUES(X'ABCDEF', 'three')"   {"columns x, y are"}
         1195  +  6    "INSERT INTO t2 VALUES(0, 'zero')"            {""}
         1196  +  7    "INSERT INTO t2 VALUES(45.5, 'one')"          {""}
         1197  +  8    "INSERT INTO t2 VALUES(0.0, 'zero')"          {""}
         1198  +  9    "INSERT INTO t2 VALUES('brambles', 'two')"    {""}
         1199  +  10   "INSERT INTO t2 VALUES(X'ABCDEF', 'three')"   {""}
  1200   1200   }
  1201   1201   do_createtable_tests 4.3.2 {
  1202   1202     1    "INSERT INTO t1 VALUES(-1, 0)"                {}
  1203   1203     2    "INSERT INTO t1 VALUES(45.2, 'abc')"          {}
  1204   1204     3    "INSERT INTO t1 VALUES(0.01, 'abc')"          {}
  1205   1205     4    "INSERT INTO t1 VALUES('bramble', 'abc')"     {}
  1206   1206     5    "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')"     {}
................................................................................
  1207   1207   
  1208   1208     6    "INSERT INTO t2 VALUES(0, 0)"                 {}
  1209   1209     7    "INSERT INTO t2 VALUES(45.5, 'abc')"          {}
  1210   1210     8    "INSERT INTO t2 VALUES(0.0, 'abc')"           {}
  1211   1211     9    "INSERT INTO t2 VALUES('brambles', 'abc')"    {}
  1212   1212     10   "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')"     {}
  1213   1213   }
  1214         -do_createtable_tests 4.3.3 -error { %s not unique } {
  1215         -  1    "UPDATE t1 SET x=0           WHERE y='two'"    {"column x is"}
  1216         -  2    "UPDATE t1 SET x='brambles'  WHERE y='three'"  {"column x is"}
  1217         -  3    "UPDATE t1 SET x=45.5        WHERE y='zero'"   {"column x is"}
  1218         -  4    "UPDATE t1 SET x=X'ABCDEF'   WHERE y='one'"    {"column x is"}
  1219         -  5    "UPDATE t1 SET x=0.0         WHERE y='three'"  {"column x is"}
  1220         -
  1221         -  6    "UPDATE t2 SET x=0, y='zero' WHERE y='two'"    {"columns x, y are"}
  1222         -  7    "UPDATE t2 SET x='brambles', y='two' WHERE y='three'"  
  1223         -       {"columns x, y are"}
  1224         -  8    "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"}
  1225         -  9    "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'" 
  1226         -       {"columns x, y are"}
  1227         -  10   "UPDATE t2 SET x=0.0, y='zero'        WHERE y='three'"  
  1228         -       {"columns x, y are"}
  1229         -}
  1230         -
  1231         -
  1232         -# EVIDENCE-OF: R-52572-02078 For the purposes of determining the
  1233         -# uniqueness of primary key values, NULL values are considered distinct
  1234         -# from all other values, including other NULLs.
  1235         -#
  1236         -do_createtable_tests 4.4 {
  1237         -  1    "INSERT INTO t1 VALUES(NULL, 0)"              {}
  1238         -  2    "INSERT INTO t1 VALUES(NULL, 0)"              {}
  1239         -  3    "INSERT INTO t1 VALUES(NULL, 0)"              {}
  1240         -
  1241         -  4    "INSERT INTO t2 VALUES(NULL, 'zero')"         {}
  1242         -  5    "INSERT INTO t2 VALUES(NULL, 'one')"          {}
  1243         -  6    "INSERT INTO t2 VALUES(NULL, 'two')"          {}
  1244         -  7    "INSERT INTO t2 VALUES(NULL, 'three')"        {}
  1245         -
  1246         -  8    "INSERT INTO t2 VALUES(0, NULL)"              {}
  1247         -  9    "INSERT INTO t2 VALUES(45.5, NULL)"           {}
  1248         -  10   "INSERT INTO t2 VALUES(0.0, NULL)"            {}
  1249         -  11   "INSERT INTO t2 VALUES('brambles', NULL)"     {}
  1250         -  12   "INSERT INTO t2 VALUES(X'ABCDEF', NULL)"      {}
  1251         -
  1252         -  13   "INSERT INTO t2 VALUES(NULL, NULL)"           {}
  1253         -  14   "INSERT INTO t2 VALUES(NULL, NULL)"           {}
  1254         -}
  1255         -
  1256         -# EVIDENCE-OF: R-61866-38053 Unless the column is an INTEGER PRIMARY KEY
  1257         -# SQLite allows NULL values in a PRIMARY KEY column.
  1258         -#
  1259         -#     If the column is an integer primary key, attempting to insert a NULL
  1260         -#     into the column triggers the auto-increment behaviour. Attempting
  1261         -#     to use UPDATE to set an ipk column to a NULL value is an error.
  1262         -#
         1214  +do_createtable_tests 4.3.3 -error { PRIMARY KEY must be unique%s } {
         1215  +  1    "UPDATE t1 SET x=0           WHERE y='two'"    {""}
         1216  +  2    "UPDATE t1 SET x='brambles'  WHERE y='three'"  {""}
         1217  +  3    "UPDATE t1 SET x=45.5        WHERE y='zero'"   {""}
         1218  +  4    "UPDATE t1 SET x=X'ABCDEF'   WHERE y='one'"    {""}
         1219  +  5    "UPDATE t1 SET x=0.0         WHERE y='three'"  {""}
         1220  +
         1221  +  6    "UPDATE t2 SET x=0, y='zero' WHERE y='two'"    {""}
         1222  +  7    "UPDATE t2 SET x='brambles', y='two' WHERE y='three'"  {""}
         1223  +  8    "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {""}
         1224  +  9    "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'" {""}
         1225  +  10   "UPDATE t2 SET x=0.0, y='zero'        WHERE y='three'"  {""}
         1226  +}
         1227  +
         1228  +
         1229  +# src4 does not allow NULL values in primary key columns.
         1230  +#
         1231  +do_createtable_tests 4.4 -error { %s may not be NULL } {
         1232  +  1    "INSERT INTO t1 VALUES(NULL, 0)"              {t1.x}
         1233  +  2    "INSERT INTO t1 VALUES(NULL, 0)"              {t1.x}
         1234  +  3    "INSERT INTO t1 VALUES(NULL, 0)"              {t1.x}
         1235  +
         1236  +  4    "INSERT INTO t2 VALUES(NULL, 'zero')"         {t2.x}
         1237  +  5    "INSERT INTO t2 VALUES(NULL, 'one')"          {t2.x}
         1238  +  6    "INSERT INTO t2 VALUES(NULL, 'two')"          {t2.x}
         1239  +  7    "INSERT INTO t2 VALUES(NULL, 'three')"        {t2.x}
         1240  +
         1241  +  8    "INSERT INTO t2 VALUES(0, NULL)"              {t2.y}
         1242  +  9    "INSERT INTO t2 VALUES(45.5, NULL)"           {t2.y}
         1243  +  10   "INSERT INTO t2 VALUES(0.0, NULL)"            {t2.y}
         1244  +  11   "INSERT INTO t2 VALUES('brambles', NULL)"     {t2.y}
         1245  +  12   "INSERT INTO t2 VALUES(X'ABCDEF', NULL)"      {t2.y}
         1246  +
         1247  +  13   "INSERT INTO t2 VALUES(NULL, NULL)"           {t2.x}
         1248  +  14   "INSERT INTO t2 VALUES(NULL, NULL)"           {t2.x}
         1249  +}
         1250  +
  1263   1251   do_createtable_tests 4.5.1 {
  1264         -  1    "SELECT count(*) FROM t1 WHERE x IS NULL"                   3
  1265         -  2    "SELECT count(*) FROM t2 WHERE x IS NULL"                   6
  1266         -  3    "SELECT count(*) FROM t2 WHERE y IS NULL"                   7
  1267         -  4    "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL"     2
         1252  +  1    "SELECT count(*) FROM t1 WHERE x IS NULL"                   0
         1253  +  2    "SELECT count(*) FROM t2 WHERE x IS NULL"                   0
         1254  +  3    "SELECT count(*) FROM t2 WHERE y IS NULL"                   0
         1255  +  4    "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL"     0
  1268   1256   }
  1269   1257   do_execsql_test 4.5.2 {
  1270   1258     CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v);
  1271   1259     INSERT INTO t3 VALUES(1, NULL, 2);
  1272   1260     INSERT INTO t3 VALUES('x', NULL, 'y');
  1273   1261     SELECT u FROM t3;
  1274   1262   } {1 2}
  1275   1263   do_catchsql_test 4.5.3 {
  1276   1264     INSERT INTO t3 VALUES(2, 5, 3);
  1277   1265     UPDATE t3 SET u = NULL WHERE s = 2;
  1278         -} {1 {datatype mismatch}}
         1266  +} {1 {t3.u may not be NULL}}
  1279   1267   
  1280   1268   # EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY
  1281   1269   # KEY constraint, except that a single table may have any number of
  1282   1270   # UNIQUE constraints.
  1283   1271   #
  1284   1272   drop_all_tables
  1285   1273   do_createtable_tests 4.6 {
................................................................................
  1348   1336   # EVIDENCE-OF: R-26983-26377 INTEGER PRIMARY KEY columns aside, both
  1349   1337   # UNIQUE and PRIMARY KEY constraints are implemented by creating an
  1350   1338   # index in the database (in the same way as a "CREATE UNIQUE INDEX"
  1351   1339   # statement would).
  1352   1340   do_createtable_tests 4.9 -repair drop_all_tables -query {
  1353   1341     SELECT count(*) FROM sqlite_master WHERE type='index'
  1354   1342   } {
  1355         -  1    "CREATE TABLE t1(a TEXT PRIMARY KEY, b)"              1
         1343  +  1    "CREATE TABLE t1(a TEXT PRIMARY KEY, b)"              0
  1356   1344     2    "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)"           0
  1357   1345     3    "CREATE TABLE t1(a TEXT UNIQUE, b)"                   1
  1358         -  4    "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)"       2
  1359         -  5    "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))"  2
         1346  +  4    "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)"       1
         1347  +  5    "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))"  1
  1360   1348   }
  1361   1349   
  1362   1350   # EVIDENCE-OF: R-02252-33116 Such an index is used like any other index
  1363   1351   # in the database to optimize queries.
  1364   1352   #
  1365   1353   do_execsql_test 4.10.0 {
  1366   1354     CREATE TABLE t1(a, b PRIMARY KEY);
  1367   1355     CREATE TABLE t2(a, b, c, UNIQUE(b, c));
  1368   1356   }
  1369   1357   do_createtable_tests 4.10 {
  1370   1358     1    "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" 
  1371         -       {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}}
         1359  +       {0 0 0 {SEARCH TABLE t1 USING PRIMARY KEY (b=?) (~1 rows)}}
  1372   1360   
  1373   1361     2    "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
  1374         -       {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}}
         1362  +       {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_t2_unique1 (~1000000 rows)}}
  1375   1363   
  1376   1364     3    "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
  1377         -       {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}}
         1365  +       {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_t2_unique1 (b=? AND c>?) (~2 rows)}}
  1378   1366   }
  1379   1367   
  1380   1368   # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a
  1381   1369   # column definition or specified as a table constraint. In practice it
  1382   1370   # makes no difference.
  1383   1371   #
  1384   1372   #   All the tests that deal with CHECK constraints below (4.11.* and 
................................................................................
  1536   1524     INSERT INTO t3_ig SELECT * FROM t3_ab;
  1537   1525     INSERT INTO t3_fa SELECT * FROM t3_ab;
  1538   1526     INSERT INTO t3_re SELECT * FROM t3_ab;
  1539   1527     INSERT INTO t3_xx SELECT * FROM t3_ab;
  1540   1528   }
  1541   1529   
  1542   1530   foreach {tn tbl res ac data} {
  1543         -  1   t1_ab    {1 {column a is not unique}} 0 {1 one 2 two 3 three}
  1544         -  2   t1_ro    {1 {column a is not unique}} 1 {1 one 2 two}
  1545         -  3   t1_fa    {1 {column a is not unique}} 0 {1 one 2 two 3 three 4 string}
         1531  +  1   t1_ab    {1 {PRIMARY KEY must be unique}} 0 {1 one 2 two 3 three}
         1532  +  2   t1_ro    {1 {PRIMARY KEY must be unique}} 1 {1 one 2 two}
         1533  +  3   t1_fa    {1 {PRIMARY KEY must be unique}} 0 {1 one 2 two 3 three 4 string}
  1546   1534     4   t1_ig    {0 {}} 0 {1 one 2 two 3 three 4 string 6 string}
  1547         -  5   t1_re    {0 {}} 0 {1 one 2 two 4 string 3 string 6 string}
  1548         -  6   t1_xx    {1 {column a is not unique}} 0 {1 one 2 two 3 three}
         1535  +  5   t1_re    {0 {}} 0 {1 one 2 two 3 string 4 string 6 string}
         1536  +  6   t1_xx    {1 {PRIMARY KEY must be unique}} 0 {1 one 2 two 3 three}
  1549   1537   } {
  1550   1538     catchsql COMMIT
  1551   1539     do_execsql_test  4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
  1552   1540   
  1553   1541     do_catchsql_test 4.15.$tn.2 " 
  1554   1542       INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl;
  1555   1543     " $res
  1556   1544   
  1557         -  do_test e_createtable-4.15.$tn.3 { sqlite4_get_autocommit db } $ac
         1545  +  do_test e_createtable-4.15.$tn.3 { sqlite4_db_transaction_status db } [expr !$ac]
  1558   1546     do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data
  1559   1547   }
  1560         -foreach {tn tbl res ac data} {
  1561         -  1   t2_ab    {1 {t2_ab.b may not be NULL}} 0 {1 one 2 two 3 three}
  1562         -  2   t2_ro    {1 {t2_ro.b may not be NULL}} 1 {1 one 2 two}
  1563         -  3   t2_fa    {1 {t2_fa.b may not be NULL}} 0 {1 one 2 two 3 three 4 xx}
  1564         -  4   t2_ig    {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx}
  1565         -  5   t2_re    {1 {t2_re.b may not be NULL}} 0 {1 one 2 two 3 three}
  1566         -  6   t2_xx    {1 {t2_xx.b may not be NULL}} 0 {1 one 2 two 3 three}
         1548  +foreach {tn tbl res trans data} {
         1549  +  1   t2_ab    {1 {t2_ab.b may not be NULL}} 1 {1 one 2 two 3 three}
         1550  +  2   t2_ro    {1 {t2_ro.b may not be NULL}} 0 {1 one 2 two}
         1551  +  3   t2_fa    {1 {t2_fa.b may not be NULL}} 1 {1 one 2 two 3 three 4 xx}
         1552  +  4   t2_ig    {0 {}} 1 {1 one 2 two 3 three 4 xx 6 xx}
         1553  +  5   t2_re    {1 {t2_re.b may not be NULL}} 1 {1 one 2 two 3 three}
         1554  +  6   t2_xx    {1 {t2_xx.b may not be NULL}} 1 {1 one 2 two 3 three}
  1567   1555   } {
  1568   1556     catchsql COMMIT
  1569   1557     do_execsql_test  4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
  1570   1558   
  1571   1559     do_catchsql_test 4.16.$tn.2 " 
  1572   1560       INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl
  1573   1561     " $res
  1574   1562   
  1575         -  do_test e_createtable-4.16.$tn.3 { sqlite4_get_autocommit db } $ac
         1563  +  do_test e_createtable-4.16.$tn.3 { sqlite4_db_transaction_status db } $trans
  1576   1564     do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data
  1577   1565   }
  1578         -foreach {tn tbl res ac data} {
  1579         -  1   t3_ab    {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three}
  1580         -  2   t3_ro    {1 {columns a, b are not unique}} 1 {1 one 2 two}
  1581         -  3   t3_fa    {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three 4 three}
  1582         -  4   t3_ig    {0 {}} 0 {1 one 2 two 3 three 4 three 6 three}
  1583         -  5   t3_re    {0 {}} 0 {1 one 2 two 4 three 3 three 6 three}
  1584         -  6   t3_xx    {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three}
         1566  +foreach {tn tbl res trans data} {
         1567  +  1   t3_ab    {1 {columns a, b are not unique}} 1 {1 one 2 two 3 three}
         1568  +  2   t3_ro    {1 {columns a, b are not unique}} 0 {1 one 2 two}
         1569  +  3   t3_fa    {1 {columns a, b are not unique}} 1 {1 one 2 two 3 three 4 three}
         1570  +  4   t3_ig    {0 {}} 1 {1 one 2 two 3 three 4 three 6 three}
         1571  +  5   t3_re    {0 {}} 1 {1 one 2 two 4 three 3 three 6 three}
         1572  +  6   t3_xx    {1 {columns a, b are not unique}} 1 {1 one 2 two 3 three}
  1585   1573   } {
  1586   1574     catchsql COMMIT
  1587   1575     do_execsql_test  4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
  1588   1576   
  1589   1577     do_catchsql_test 4.17.$tn.2 " 
  1590   1578       INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl
  1591   1579     " $res
  1592   1580   
  1593         -  do_test e_createtable-4.17.$tn.3 { sqlite4_get_autocommit db } $ac
         1581  +  do_test e_createtable-4.17.$tn.3 { sqlite4_db_transaction_status db } $trans
  1594   1582     do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data
  1595   1583   }
  1596   1584   catchsql COMMIT
  1597   1585   
  1598   1586   # EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not
  1599   1587   # include a conflict-clause or it is a CHECK constraint, the default
  1600   1588   # conflict resolution algorithm is ABORT.
................................................................................
  1609   1597     INSERT INTO t4 VALUES(1, 2);
  1610   1598     INSERT INTO t4 VALUES(3, 4);
  1611   1599   }
  1612   1600   do_execsql_test  4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) }
  1613   1601   do_catchsql_test 4.18.3 { 
  1614   1602     INSERT INTO t4 SELECT a+4, b+4 FROM t4
  1615   1603   } {1 {constraint failed}}
  1616         -do_test e_createtable-4.18.4 { sqlite4_get_autocommit db } 0
         1604  +do_test e_createtable-4.18.4 { sqlite4_db_transaction_status db } 1
  1617   1605   do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6}
  1618   1606   
  1619   1607   # EVIDENCE-OF: R-19114-56113 Different constraints within the same table
  1620   1608   # may have different default conflict resolution algorithms.
  1621   1609   #
  1622   1610   do_execsql_test 4.19.0 {
  1623   1611     CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);
................................................................................
  1624   1612   }
  1625   1613   do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}}
  1626   1614   do_execsql_test  4.19.2 { SELECT * FROM t5 } {}
  1627   1615   do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \
  1628   1616     {1 {t5.b may not be NULL}}
  1629   1617   do_execsql_test  4.19.4 { SELECT * FROM t5 } {}
  1630   1618   
  1631         -#------------------------------------------------------------------------
  1632         -# Tests for INTEGER PRIMARY KEY and rowid related statements.
  1633         -#
  1634         -
  1635         -# EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one
  1636         -# of the special case-independent names "rowid", "oid", or "_rowid_" in
  1637         -# place of a column name.
  1638         -#
  1639         -drop_all_tables
  1640         -do_execsql_test 5.1.0 {
  1641         -  CREATE TABLE t1(x, y);
  1642         -  INSERT INTO t1 VALUES('one', 'first');
  1643         -  INSERT INTO t1 VALUES('two', 'second');
  1644         -  INSERT INTO t1 VALUES('three', 'third');
  1645         -}
  1646         -do_createtable_tests 5.1 {
  1647         -  1   "SELECT rowid FROM t1"        {1 2 3}
  1648         -  2   "SELECT oid FROM t1"          {1 2 3}
  1649         -  3   "SELECT _rowid_ FROM t1"      {1 2 3}
  1650         -  4   "SELECT ROWID FROM t1"        {1 2 3}
  1651         -  5   "SELECT OID FROM t1"          {1 2 3}
  1652         -  6   "SELECT _ROWID_ FROM t1"      {1 2 3}
  1653         -  7   "SELECT RoWiD FROM t1"        {1 2 3}
  1654         -  8   "SELECT OiD FROM t1"          {1 2 3}
  1655         -  9   "SELECT _RoWiD_ FROM t1"      {1 2 3}
  1656         -}
  1657         -
  1658         -# EVIDENCE-OF: R-26501-17306 If a table contains a user defined column
  1659         -# named "rowid", "oid" or "_rowid_", then that name always refers the
  1660         -# explicitly declared column and cannot be used to retrieve the integer
  1661         -# rowid value.
  1662         -#
  1663         -do_execsql_test 5.2.0 {
  1664         -  CREATE TABLE t2(oid, b);
  1665         -  CREATE TABLE t3(a, _rowid_);
  1666         -  CREATE TABLE t4(a, b, rowid);
  1667         -
  1668         -  INSERT INTO t2 VALUES('one', 'two');
  1669         -  INSERT INTO t2 VALUES('three', 'four');
  1670         -
  1671         -  INSERT INTO t3 VALUES('five', 'six');
  1672         -  INSERT INTO t3 VALUES('seven', 'eight');
  1673         -
  1674         -  INSERT INTO t4 VALUES('nine', 'ten', 'eleven');
  1675         -  INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen');
  1676         -}
  1677         -do_createtable_tests 5.2 {
  1678         -  1   "SELECT oid, rowid, _rowid_ FROM t2"   {one 1 1      three 2 2}
  1679         -  2   "SELECT oid, rowid, _rowid_ FROM t3"   {1 1 six      2 2 eight} 
  1680         -  3   "SELECT oid, rowid, _rowid_ FROM t4"   {1 eleven 1   2 fourteen 2}
  1681         -}
  1682         -
  1683         -
  1684         -# Argument $tbl is the name of a table in the database. Argument $col is
  1685         -# the name of one of the tables columns. Return 1 if $col is an alias for
  1686         -# the rowid, or 0 otherwise.
  1687         -#
  1688         -proc is_integer_primary_key {tbl col} {
  1689         -  lindex [db eval [subst {
  1690         -    DELETE FROM $tbl;
  1691         -    INSERT INTO $tbl ($col) VALUES(0);
  1692         -    SELECT (rowid==$col) FROM $tbl;
  1693         -    DELETE FROM $tbl;
  1694         -  }]] 0
  1695         -}
  1696         -
  1697         -# EVIDENCE-OF: R-53738-31673 With one exception, if a table has a
  1698         -# primary key that consists of a single column, and the declared type of
  1699         -# that column is "INTEGER" in any mixture of upper and lower case, then
  1700         -# the column becomes an alias for the rowid.
  1701         -#
  1702         -# EVIDENCE-OF: R-45951-08347 if the declaration of a column with
  1703         -# declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does
  1704         -# not become an alias for the rowid and is not classified as an integer
  1705         -# primary key.
  1706         -#
  1707         -do_createtable_tests 5.3 -tclquery { 
  1708         -  is_integer_primary_key t5 pk
  1709         -} -repair {
  1710         -  catchsql { DROP TABLE t5 }
  1711         -} {
  1712         -  1   "CREATE TABLE t5(pk integer primary key)"                         1
  1713         -  2   "CREATE TABLE t5(pk integer, primary key(pk))"                    1
  1714         -  3   "CREATE TABLE t5(pk integer, v integer, primary key(pk))"         1
  1715         -  4   "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))"      0
  1716         -  5   "CREATE TABLE t5(pk int, v integer, primary key(pk, v))"          0
  1717         -  6   "CREATE TABLE t5(pk int, v integer, primary key(pk))"             0
  1718         -  7   "CREATE TABLE t5(pk int primary key, v integer)"                  0
  1719         -  8   "CREATE TABLE t5(pk inTEger primary key)"                         1
  1720         -  9   "CREATE TABLE t5(pk inteGEr, primary key(pk))"                    1
  1721         -  10  "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))"         1
  1722         -}
  1723   1619   
  1724   1620   # EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or
  1725   1621   # "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary
  1726   1622   # key column to behave as an ordinary table column with integer affinity
  1727   1623   # and a unique index, not as an alias for the rowid.
  1728   1624   #
  1729   1625   do_execsql_test 5.4.1 {
  1730   1626     CREATE TABLE t6(pk INT primary key);
  1731   1627     CREATE TABLE t7(pk BIGINT primary key);
  1732   1628     CREATE TABLE t8(pk SHORT INTEGER primary key);
  1733   1629     CREATE TABLE t9(pk UNSIGNED INTEGER primary key);
  1734   1630   } 
  1735         -do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0
  1736         -do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0
  1737         -do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0
  1738         -do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0
  1739   1631   
  1740   1632   do_execsql_test 5.4.3 {
  1741   1633     INSERT INTO t6 VALUES('2.0');
  1742   1634     INSERT INTO t7 VALUES('2.0');
  1743   1635     INSERT INTO t8 VALUES('2.0');
  1744   1636     INSERT INTO t9 VALUES('2.0');
  1745   1637     SELECT typeof(pk), pk FROM t6;
................................................................................
  1746   1638     SELECT typeof(pk), pk FROM t7;
  1747   1639     SELECT typeof(pk), pk FROM t8;
  1748   1640     SELECT typeof(pk), pk FROM t9;
  1749   1641   } {integer 2 integer 2 integer 2 integer 2}
  1750   1642   
  1751   1643   do_catchsql_test 5.4.4.1 { 
  1752   1644     INSERT INTO t6 VALUES(2) 
  1753         -} {1 {column pk is not unique}}
         1645  +} {1 {PRIMARY KEY must be unique}}
  1754   1646   do_catchsql_test 5.4.4.2 { 
  1755   1647     INSERT INTO t7 VALUES(2) 
  1756         -} {1 {column pk is not unique}}
         1648  +} {1 {PRIMARY KEY must be unique}}
  1757   1649   do_catchsql_test 5.4.4.3 { 
  1758   1650     INSERT INTO t8 VALUES(2) 
  1759         -} {1 {column pk is not unique}}
         1651  +} {1 {PRIMARY KEY must be unique}}
  1760   1652   do_catchsql_test 5.4.4.4 { 
  1761   1653     INSERT INTO t9 VALUES(2) 
  1762         -} {1 {column pk is not unique}}
  1763         -
  1764         -# EVIDENCE-OF: R-56094-57830 the following three table declarations all
  1765         -# cause the column "x" to be an alias for the rowid (an integer primary
  1766         -# key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE
  1767         -# t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y,
  1768         -# z, PRIMARY KEY(x DESC));
  1769         -#
  1770         -# EVIDENCE-OF: R-20149-25884 the following declaration does not result
  1771         -# in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY
  1772         -# KEY DESC, y, z);
  1773         -#
  1774         -do_createtable_tests 5 -tclquery { 
  1775         -  is_integer_primary_key t x
  1776         -} -repair {
  1777         -  catchsql { DROP TABLE t }
  1778         -} {
  1779         -  5.1    "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)"      1
  1780         -  5.2    "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))"  1
  1781         -  5.3    "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1
  1782         -  6.1    "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)"     0
  1783         -}
  1784         -
  1785         -# EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an
  1786         -# UPDATE statement in the same way as any other column value can, either
  1787         -# using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by
  1788         -# using an alias created by an integer primary key.
  1789         -#
  1790         -do_execsql_test 5.7.0 {
  1791         -  CREATE TABLE t10(a, b);
  1792         -  INSERT INTO t10 VALUES('ten', 10);
  1793         -
  1794         -  CREATE TABLE t11(a, b INTEGER PRIMARY KEY);
  1795         -  INSERT INTO t11 VALUES('ten', 10);
  1796         -}
  1797         -do_createtable_tests 5.7.1 -query { 
  1798         -  SELECT rowid, _rowid_, oid FROM t10;
  1799         -} {
  1800         -  1    "UPDATE t10 SET rowid = 5"   {5 5 5}
  1801         -  2    "UPDATE t10 SET _rowid_ = 6" {6 6 6}
  1802         -  3    "UPDATE t10 SET oid = 7"     {7 7 7}
  1803         -}
  1804         -do_createtable_tests 5.7.2 -query { 
  1805         -  SELECT rowid, _rowid_, oid, b FROM t11;
  1806         -} {
  1807         -  1    "UPDATE t11 SET rowid = 5"   {5 5 5 5}
  1808         -  2    "UPDATE t11 SET _rowid_ = 6" {6 6 6 6}
  1809         -  3    "UPDATE t11 SET oid = 7"     {7 7 7 7}
  1810         -  4    "UPDATE t11 SET b = 8"       {8 8 8 8}
  1811         -}
  1812         -
  1813         -# EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide
  1814         -# a value to use as the rowid for each row inserted.
  1815         -#
  1816         -do_createtable_tests 5.8.1 -query { 
  1817         -  SELECT rowid, _rowid_, oid FROM t10;
  1818         -} -repair { 
  1819         -  execsql { DELETE FROM t10 } 
  1820         -} {
  1821         -  1    "INSERT INTO t10(oid) VALUES(15)"           {15 15 15}
  1822         -  2    "INSERT INTO t10(rowid) VALUES(16)"         {16 16 16}
  1823         -  3    "INSERT INTO t10(_rowid_) VALUES(17)"       {17 17 17}
  1824         -  4    "INSERT INTO t10(a, b, oid) VALUES(1,2,3)"  {3 3 3}
  1825         -}
  1826         -do_createtable_tests 5.8.2 -query { 
  1827         -  SELECT rowid, _rowid_, oid, b FROM t11;
  1828         -} -repair { 
  1829         -  execsql { DELETE FROM t11 } 
  1830         -} {
  1831         -  1    "INSERT INTO t11(oid) VALUES(15)"           {15 15 15 15}
  1832         -  2    "INSERT INTO t11(rowid) VALUES(16)"         {16 16 16 16}
  1833         -  3    "INSERT INTO t11(_rowid_) VALUES(17)"       {17 17 17 17}
  1834         -  4    "INSERT INTO t11(a, b) VALUES(1,2)"         {2 2 2 2}
  1835         -}
  1836         -
  1837         -# EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer
  1838         -# primary key or rowid column must contain integer values. Integer
  1839         -# primary key or rowid columns are not able to hold floating point
  1840         -# values, strings, BLOBs, or NULLs.
  1841         -#
  1842         -#     This is considered by the tests for the following 3 statements,
  1843         -#     which show that:
  1844         -#
  1845         -#       1. Attempts to UPDATE a rowid column to a non-integer value fail,
  1846         -#       2. Attempts to INSERT a real, string or blob value into a rowid 
  1847         -#          column fail, and
  1848         -#       3. Attempting to INSERT a NULL value into a rowid column causes the
  1849         -#          system to automatically select an integer value to use.
  1850         -#
  1851         -
  1852         -
  1853         -# EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an
  1854         -# integer primary key or rowid column to a NULL or blob value, or to a
  1855         -# string or real value that cannot be losslessly converted to an
  1856         -# integer, a "datatype mismatch" error occurs and the statement is
  1857         -# aborted.
  1858         -#
  1859         -drop_all_tables
  1860         -do_execsql_test 5.9.0 {
  1861         -  CREATE TABLE t12(x INTEGER PRIMARY KEY, y);
  1862         -  INSERT INTO t12 VALUES(5, 'five');
  1863         -}
  1864         -do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } {
  1865         -  1   "UPDATE t12 SET x = 4"       {integer 4}
  1866         -  2   "UPDATE t12 SET x = 10.0"    {integer 10}
  1867         -  3   "UPDATE t12 SET x = '12.0'"  {integer 12}
  1868         -  4   "UPDATE t12 SET x = '-15.0'" {integer -15}
  1869         -}
  1870         -do_createtable_tests 5.9.2 -error {
  1871         -  datatype mismatch
  1872         -} {
  1873         -  1   "UPDATE t12 SET x = 4.1"         {}
  1874         -  2   "UPDATE t12 SET x = 'hello'"     {}
  1875         -  3   "UPDATE t12 SET x = NULL"        {}
  1876         -  4   "UPDATE t12 SET x = X'ABCD'"     {}
  1877         -  5   "UPDATE t12 SET x = X'3900'"     {}
  1878         -  6   "UPDATE t12 SET x = X'39'"       {}
  1879         -}
  1880         -
  1881         -# EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a
  1882         -# blob value, or a string or real value that cannot be losslessly
  1883         -# converted to an integer into an integer primary key or rowid column, a
  1884         -# "datatype mismatch" error occurs and the statement is aborted.
  1885         -#
  1886         -do_execsql_test 5.10.0 { DELETE FROM t12 }
  1887         -do_createtable_tests 5.10.1 -error { 
  1888         -  datatype mismatch
  1889         -} {
  1890         -  1   "INSERT INTO t12(x) VALUES(4.1)"     {}
  1891         -  2   "INSERT INTO t12(x) VALUES('hello')" {}
  1892         -  3   "INSERT INTO t12(x) VALUES(X'ABCD')" {}
  1893         -  4   "INSERT INTO t12(x) VALUES(X'3900')" {}
  1894         -  5   "INSERT INTO t12(x) VALUES(X'39')"   {}
  1895         -}
  1896         -do_createtable_tests 5.10.2 -query { 
  1897         -  SELECT typeof(x), x FROM t12 
  1898         -} -repair {
  1899         -  execsql { DELETE FROM t12 }
  1900         -} {
  1901         -  1   "INSERT INTO t12(x) VALUES(4)"       {integer 4}
  1902         -  2   "INSERT INTO t12(x) VALUES(10.0)"    {integer 10}
  1903         -  3   "INSERT INTO t12(x) VALUES('12.0')"  {integer 12}
  1904         -  4   "INSERT INTO t12(x) VALUES('4e3')"   {integer 4000}
  1905         -  5   "INSERT INTO t12(x) VALUES('-14.0')" {integer -14}
  1906         -}
  1907         -
  1908         -# EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a
  1909         -# NULL value into a rowid or integer primary key column, the system
  1910         -# chooses an integer value to use as the rowid automatically.
  1911         -#
  1912         -do_execsql_test 5.11.0 { DELETE FROM t12 }
  1913         -do_createtable_tests 5.11 -query { 
  1914         -  SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12)
  1915         -} {
  1916         -  1   "INSERT INTO t12 DEFAULT VALUES"                {integer 1}
  1917         -  2   "INSERT INTO t12(y)   VALUES(5)"                {integer 2}
  1918         -  3   "INSERT INTO t12(x,y) VALUES(NULL, 10)"         {integer 3}
  1919         -  4   "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12" 
  1920         -      {integer 4 integer 5 integer 6}
  1921         -  5   "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3"
  1922         -      {integer 7 integer 8 integer 9}
  1923         -}
         1654  +} {1 {PRIMARY KEY must be unique}}
  1924   1655   
  1925   1656   finish_test

Changes to test/e_delete.test.

   202    202   # top-level statement (by searching first the TEMP database, then the
   203    203   # main database, then any other databases in the order they were
   204    204   # attached).
   205    205   #
   206    206   do_execsql_test e_delete-2.3.0 {
   207    207     DROP TRIGGER aux.tr1;
   208    208     DROP TRIGGER main.tr1;
   209         -  DELETE FROM main.t8 WHERE oid>1;
   210         -  DELETE FROM aux.t8 WHERE oid>1;
          209  +  DELETE FROM main.t8 WHERE rowid>1;
          210  +  DELETE FROM aux.t8 WHERE rowid>1;
   211    211     INSERT INTO aux.t9 VALUES(1, 2);
   212    212     INSERT INTO main.t7 VALUES(3, 4);
   213    213   } {}
   214    214   do_execsql_test e_delete-2.3.1 {
   215    215     SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL
   216    216     SELECT count(*) FROM aux.t7  UNION ALL SELECT count(*) FROM aux2.t7;
   217    217   

Changes to test/e_expr.test.

    40     40   #   ::oplist         A list of all SQL operators supported by SQLite.
    41     41   #
    42     42   foreach {op opn} {
    43     43         ||   cat     *   mul       /  div       %     mod       +      add
    44     44         -    sub     <<  lshift    >> rshift    &     bitand    |      bitor
    45     45         <    less    <=  lesseq    >  more      >=    moreeq    =      eq1
    46     46         ==   eq2     <>  ne1       != ne2       IS    is        LIKE   like
    47         -      GLOB glob    AND and       OR or        MATCH match     REGEXP regexp
           47  +      GLOB glob    AND and       OR or        REGEXP regexp
    48     48         {IS NOT} isnt
    49     49   } {
    50     50     set ::opname($op) $opn
    51     51   }
    52     52   set oplist [list]
    53     53   foreach {prec opl} {
    54     54     1   ||
    55     55     2   {* / %}
    56     56     3   {+ -}
    57     57     4   {<< >> & |}
    58     58     5   {< <= > >=}
    59         -  6   {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP}
           59  +  6   {= == != <> IS {IS NOT} LIKE GLOB REGEXP}
    60     60     7   AND
    61     61     8   OR
    62     62   } {
    63     63     foreach op $opl { 
    64     64       set ::opprec($op) $prec 
    65     65       lappend oplist $op
    66     66     }
................................................................................
   194    194   # the operand.
   195    195   #
   196    196   foreach {tn literal type} {
   197    197     1     'helloworld'   text
   198    198     2     45             integer
   199    199     3     45.2           real
   200    200     4     45.0           real
   201         -  5     X'ABCDEF'      blob
          201  +  5     x'abcdef'      blob
   202    202     6     NULL           null
   203    203   } {
   204    204     set sql " SELECT quote( + $literal ), typeof( + $literal) "
   205    205     do_execsql_test e_expr-3.$tn $sql [list $literal $type]
   206    206   }
   207    207   
   208    208   #-------------------------------------------------------------------------
................................................................................
   733    733    
   734    734     54 "EXPR1 LIKE EXPR2"
   735    735     55 "EXPR1 LIKE EXPR2 ESCAPE EXPR"
   736    736     56 "EXPR1 GLOB EXPR2"
   737    737     57 "EXPR1 GLOB EXPR2 ESCAPE EXPR"
   738    738     58 "EXPR1 REGEXP EXPR2"
   739    739     59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR"
   740         -  60 "EXPR1 MATCH EXPR2"
   741         -  61 "EXPR1 MATCH EXPR2 ESCAPE EXPR"
   742    740     62 "EXPR1 NOT LIKE EXPR2"
   743    741     63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"
   744    742     64 "EXPR1 NOT GLOB EXPR2"
   745    743     65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"
   746    744     66 "EXPR1 NOT REGEXP EXPR2"
   747    745     67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"
   748    746     68 "EXPR1 NOT MATCH EXPR2"
................................................................................
  1090   1088   do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1
  1091   1089   do_test         e_expr-18.2.2 { set regexpargs } {def abc}
  1092   1090   set ::regexpargs [list]
  1093   1091   do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0
  1094   1092   do_test         e_expr-18.2.4 { set regexpargs } {Y X}
  1095   1093   sqlite4 db test.db
  1096   1094   
  1097         -# EVIDENCE-OF: R-42037-37826 The default match() function implementation
  1098         -# raises an exception and is not really useful for anything.
  1099         -#
  1100         -do_catchsql_test e_expr-19.1.1 { 
  1101         -  SELECT 'abc' MATCH 'def' 
  1102         -} {1 {unable to use function MATCH in the requested context}}
  1103         -do_catchsql_test e_expr-19.1.2 { 
  1104         -  SELECT match('abc', 'def')
  1105         -} {1 {unable to use function MATCH in the requested context}}
  1106         -
  1107         -# EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for
  1108         -# the match() application-defined function.
  1109         -#
  1110         -# EVIDENCE-OF: R-06021-09373 But extensions can override the match()
  1111         -# function with more helpful logic.
  1112         -#
  1113         -proc matchfunc {args} {
  1114         -  eval lappend ::matchargs $args
  1115         -  return 1
  1116         -}
  1117         -db func match -argcount 2 matchfunc
  1118         -set ::matchargs [list]
  1119         -do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1
  1120         -do_test         e_expr-19.2.2 { set matchargs } {def abc}
  1121         -set ::matchargs [list]
  1122         -do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0
  1123         -do_test         e_expr-19.2.4 { set matchargs } {Y X}
  1124         -sqlite4 db test.db
  1125         -
  1126   1095   #-------------------------------------------------------------------------
  1127   1096   # Test cases for the testable statements related to the CASE expression.
  1128   1097   #
  1129   1098   # EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE
  1130   1099   # expression: those with a base expression and those without.
  1131   1100   #
  1132   1101   do_execsql_test e_expr-20.1 {
................................................................................
  1309   1278     SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
  1310   1279   } {B}
  1311   1280   do_execsql_test e_expr-23.1.4 {
  1312   1281     SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1
  1313   1282   } {B}
  1314   1283   do_execsql_test e_expr-23.1.5 {
  1315   1284     SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1
  1316         -} {A}
         1285  +} {B}
  1317   1286   do_execsql_test e_expr-23.1.6 {
  1318   1287     SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END
  1319   1288   } {B}
  1320   1289   do_execsql_test e_expr-23.1.7 {
  1321   1290     SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1
  1322   1291   } {A}
  1323   1292   do_execsql_test e_expr-23.1.8 {
................................................................................
  1435   1404   do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
  1436   1405   do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10)   } blob ghi
  1437   1406   
  1438   1407   # EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting
  1439   1408   # the value to TEXT in the encoding of the database connection, then
  1440   1409   # interpreting the resulting byte sequence as a BLOB instead of as TEXT.
  1441   1410   #
  1442         -do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869'
  1443         -do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) }   X'343536'
  1444         -do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) }  X'312E3738'
         1411  +do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } x'676869'
         1412  +do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) }   x'343536'
         1413  +do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) }  x'312e3738'
  1445   1414   rename db db2
  1446   1415   sqlite4 db :memory:
  1447   1416   ifcapable {utf16} {
  1448   1417   db eval { PRAGMA encoding = 'utf-16le' }
  1449         -do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900'
  1450         -do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) }   X'340035003600'
  1451         -do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) }  X'31002E0037003800'
         1418  +do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } x'670068006900'
         1419  +do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) }   x'340035003600'
         1420  +do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) }  x'31002e0037003800'
  1452   1421   }
  1453   1422   db close
  1454   1423   sqlite4 db :memory:
  1455   1424   db eval { PRAGMA encoding = 'utf-16be' }
  1456   1425   ifcapable {utf16} {
  1457         -do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069'
  1458         -do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) }   X'003400350036'
  1459         -do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) }  X'0031002E00370038'
         1426  +do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } x'006700680069'
         1427  +do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) }   x'003400350036'
         1428  +do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) }  x'0031002e00370038'
  1460   1429   }
  1461   1430   db close
  1462   1431   rename db2 db
  1463   1432   
  1464   1433   # EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
  1465   1434   # of bytes that make up the BLOB is interpreted as text encoded using
  1466   1435   # the database encoding.
................................................................................
  1482   1451   # resulting TEXT uses the encoding of the database connection.
  1483   1452   #
  1484   1453   do_expr_test e_expr-28.2.1 { CAST (1 AS text)   }     text 1
  1485   1454   do_expr_test e_expr-28.2.2 { CAST (45 AS text)  }     text 45
  1486   1455   do_expr_test e_expr-28.2.3 { CAST (-45 AS text) }     text -45
  1487   1456   do_expr_test e_expr-28.2.4 { CAST (8.8 AS text)    }  text 8.8
  1488   1457   do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) }  text 230000.0
  1489         -do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05
         1458  +do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -0.000023
  1490   1459   do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) }     text 0.0
  1491         -do_expr_test e_expr-28.2.7 { CAST (0 AS text) }       text 0
         1460  +do_expr_test e_expr-28.2.8 { CAST (0 AS text) }       text 0
  1492   1461   
  1493   1462   # EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the
  1494   1463   # value is first converted to TEXT.
  1495   1464   #
  1496   1465   do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23
  1497   1466   do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
  1498   1467   do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87
................................................................................
  1598   1567   do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
  1599   1568   do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0
  1600   1569   
  1601   1570   # EVIDENCE-OF: R-49503-28105 If a REAL is too large to be represented as
  1602   1571   # an INTEGER then the result of the cast is the largest negative
  1603   1572   # integer: -9223372036854775808.
  1604   1573   #
  1605         -do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer -9223372036854775808
         1574  +do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807
  1606   1575   do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808
  1607   1576   do_expr_test e_expr-31.2.3 { 
  1608   1577     CAST(-9223372036854775809.0 AS INT)
  1609   1578   } integer -9223372036854775808
  1610   1579   do_expr_test e_expr-31.2.4 { 
  1611   1580     CAST(9223372036854775809.0 AS INT)
  1612         -} integer -9223372036854775808
         1581  +} integer 9223372036854775807
  1613   1582   
  1614   1583   
  1615   1584   # EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC
  1616   1585   # first does a forced conversion into REAL but then further converts the
  1617   1586   # result into INTEGER if and only if the conversion from REAL to INTEGER
  1618   1587   # is lossless and reversible.
  1619   1588   #

Changes to test/permutations.test.

   169    169     date.test
   170    170     default.test
   171    171     delete.test delete2.test delete3.test
   172    172     descidx1.test descidx2.test descidx3.test 
   173    173     distinct.test distinctagg.test
   174    174     enc.test enc4.test
   175    175     exists.test
   176         -  e_droptrigger.test e_dropview.test
   177         -  e_resolve.test e_dropview.test
   178         -  e_select2.test
          176  +  e_createtable.test e_delete.test e_droptrigger.test e_dropview.test
          177  +  e_expr.test
          178  +  e_resolve.test e_select2.test
   179    179     fkey1.test fkey2.test fkey3.test fkey4.test
   180    180     func.test func2.test func3.test 
   181    181     fuzz2.test 
   182    182     in.test in4.test
   183    183     index2.test index3.test index4.test 
   184    184     insert.test insert2.test insert3.test insert5.test
   185    185     join.test join2.test join3.test join4.test join5.test join6.test

Changes to test/simple.test.

  1558   1558   
  1559   1559   do_execsql_test 80.2 {
  1560   1560     SELECT idx, count(*), sum(length(sample)) FROM t1 GROUP BY idx
  1561   1561   } {t1 2 4 t1i1 2 4 t1i2 2 4 t1i3 2 4}
  1562   1562   
  1563   1563   #-------------------------------------------------------------------------
  1564   1564   reset_db
  1565         -do_test alter-81.1 {
         1565  +do_test 81.1 {
  1566   1566     execsql {
  1567   1567       CREATE TABLE t1(a TEXT);
  1568   1568       INSERT INTO t1 VALUES(5.4e-08);
  1569   1569       SELECT a FROM t1;
  1570   1570     }
  1571   1571   } {5.4e-8}
  1572   1572   
  1573   1573   #-------------------------------------------------------------------------
  1574   1574   reset_db
  1575         -do_test alter-82.1 {
         1575  +do_test 82.1 {
  1576   1576     execsql { CREATE TABLE t1(a TEXT) }
  1577   1577     execsql { SELECT * FROM sqlite_master }
  1578   1578   } {table t1 t1 2 {CREATE TABLE t1(a TEXT)}}
  1579   1579   
  1580         -do_test alter-82.2 {
         1580  +do_test 82.2 {
  1581   1581     execsql { DROP TABLE t1 }
  1582   1582     execsql { CREATE TABLE t1(a TEXT) }
  1583   1583     execsql { SELECT * FROM sqlite_master }
  1584   1584   } {table t1 t1 2 {CREATE TABLE t1(a TEXT)}}
         1585  +
         1586  +#-------------------------------------------------------------------------
         1587  +reset_db
         1588  +do_test 83.1 {
         1589  +  execsql { SELECT CAST('2.12e-01ABC' AS INT) }
         1590  +} {2}
         1591  +do_test 83.2 {
         1592  +  execsql { SELECT CAST('   -2.12e-01ABC' AS INT) }
         1593  +} {-2}
         1594  +do_test 83.3 {
         1595  +  execsql { SELECT CAST('45.0' AS NUMERIC) }
         1596  +} {45}
         1597  +do_test 83.4 {
         1598  +  execsql { SELECT CAST(0.0 AS TEXT) }
         1599  +} {0.0}
  1585   1600   
  1586   1601   finish_test
  1587   1602