Index: src/math.c ================================================================== --- src/math.c +++ src/math.c @@ -350,11 +350,11 @@ } /* If the IGNORE_WHITESPACE flag is set, ignore any leading whitespace. */ i = 0; if( flags & SQLITE4_IGNORE_WHITESPACE ){ - while( sqlite4Isspace(zIn[i]) && i=0 && x.e+n<=25 ){ Index: src/vdbemem.c ================================================================== --- src/vdbemem.c +++ src/vdbemem.c @@ -333,11 +333,17 @@ assert( pMem->db==0 || sqlite4_mutex_held(pMem->db->mutex) ); assert( (pMem->flags & MEM_RowSet)==0 ); assert( EIGHT_BYTE_ALIGNMENT(pMem) ); if( (pMem->flags & MEM_Int)==0 ){ - pMem->u.num = sqlite4_num_from_int64(sqlite4VdbeIntValue(pMem)); + if( pMem->flags & (MEM_Real|MEM_Null) ){ + pMem->u.num = sqlite4_num_from_int64(sqlite4VdbeIntValue(pMem)); + }else{ + unsigned int flags = pMem->enc | + SQLITE4_INTEGER_ONLY|SQLITE4_PREFIX_ONLY|SQLITE4_IGNORE_WHITESPACE; + pMem->u.num = sqlite4_num_from_text(pMem->z, pMem->n, flags, 0); + } MemSetTypeFlag(pMem, MEM_Int); } return SQLITE4_OK; } @@ -354,11 +360,12 @@ int bReal = 0; int flags = (pMem->enc | SQLITE4_PREFIX_ONLY | SQLITE4_IGNORE_WHITESPACE); assert( (pMem->flags & (MEM_Blob|MEM_Str))!=0 ); assert( pMem->db==0 || sqlite4_mutex_held(pMem->db->mutex) ); - pMem->u.num = sqlite4_num_from_text(pMem->z, pMem->n, flags, &bReal); + pMem->u.num = sqlite4_num_from_text(pMem->z, pMem->n, flags, 0); + sqlite4_num_to_int64(pMem->u.num, &bReal); MemSetTypeFlag(pMem, (bReal ? MEM_Real : MEM_Int)); } assert( (pMem->flags & (MEM_Int|MEM_Real|MEM_Null))!=0 ); pMem->flags &= ~(MEM_Str|MEM_Blob); return SQLITE4_OK; Index: test/e_createtable.test ================================================================== --- test/e_createtable.test +++ test/e_createtable.test @@ -363,11 +363,11 @@ } { 1 "CREATE TABLE sqlite_abc(a, b, c)" sqlite_abc 2 "CREATE TABLE temp.sqlite_helloworld(x)" sqlite_helloworld 3 {CREATE TABLE auxa."sqlite__"(x, y)} sqlite__ 4 {CREATE TABLE auxb."sqlite_"(z)} sqlite_ - 5 {CREATE TABLE "SQLITE4_TBL"(z)} SQLITE4_TBL + 5 {CREATE TABLE "SQLITE_TBL"(z)} SQLITE_TBL } do_createtable_tests 1.1.2 { 1 "CREATE TABLE sqlit_abc(a, b, c)" {} 2 "CREATE TABLE temp.sqlitehelloworld(x)" {} 3 {CREATE TABLE auxa."sqlite"(x, y)} {} @@ -614,11 +614,11 @@ # The argument must be a table name. Return a list of column declared # types, from left to right, for the table. # proc sci {select cmd} { set res [list] - set STMT [sqlite4_prepare_v2 db $select -1 dummy] + set STMT [sqlite4_prepare db $select -1 dummy] for {set i 0} {$i < [sqlite4_column_count $STMT]} {incr i} { lappend res [$cmd $STMT $i] } sqlite4_finalize $STMT set res @@ -919,20 +919,20 @@ SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f), quote(g), quote(h), quote(i), quote(j), quote(k) FROM t4 ORDER BY rowid DESC LIMIT 1; } { 1 "INSERT INTO t4 DEFAULT VALUES" { - NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 + NULL {'string constant'} x'424c4f42' 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'} } 2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" { 1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'} } 3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" { - NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1 + NULL {'string constant'} x'424c4f42' 1 -1 3.14 -3.14 'acd' 3 2 1 } 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)" { 1 2 3 4 5 6 7 8 9 10 11 } @@ -952,11 +952,11 @@ ); } {} do_execsql_test e_createtable-3.6.2 { INSERT INTO t5 DEFAULT VALUES; SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5; -} {NULL {'text value'} X'424C4F42' -45678.6 394507} +} {NULL {'text value'} x'424c4f42' -45678.6 394507} # EVIDENCE-OF: R-60616-50251 If the default value of a column is an # expression in parentheses, then the expression is evaluated once for # each row inserted and the results used in the new row. # @@ -1087,11 +1087,11 @@ sqlite4_limit db SQLITE4_LIMIT_COLUMN [expr $::SQLITE4_MAX_COLUMN+2] do_catchsql_test e_createtable-3.11.3 [subst { CREATE TABLE t11([columns [expr $::SQLITE4_MAX_COLUMN+1]]); }] {1 {too many columns on t11}} -sqlite4_limit db SQLITE4_LIMIT_LENGTH 90010 +sqlite4_limit db SQLITE4_LIMIT_LENGTH 90013 do_execsql_test e_createtable-3.11.4 { CREATE TABLE t12(a, b, c); INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000)); } {} do_catchsql_test e_createtable-3.11.5 { @@ -1183,22 +1183,22 @@ INSERT INTO t2 VALUES(45.5, 'one'); INSERT INTO t2 VALUES('brambles', 'two'); INSERT INTO t2 VALUES(X'ABCDEF', 'three'); } {} -do_createtable_tests 4.3.1 -error { %s not unique } { - 1 "INSERT INTO t1 VALUES(0, 0)" {"column x is"} - 2 "INSERT INTO t1 VALUES(45.5, 'abc')" {"column x is"} - 3 "INSERT INTO t1 VALUES(0.0, 'abc')" {"column x is"} - 4 "INSERT INTO t1 VALUES('brambles', 'abc')" {"column x is"} - 5 "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')" {"column x is"} +do_createtable_tests 4.3.1 -error { PRIMARY KEY must be unique%s } { + 1 "INSERT INTO t1 VALUES(0, 0)" {""} + 2 "INSERT INTO t1 VALUES(45.5, 'abc')" {""} + 3 "INSERT INTO t1 VALUES(0.0, 'abc')" {""} + 4 "INSERT INTO t1 VALUES('brambles', 'abc')" {""} + 5 "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')" {""} - 6 "INSERT INTO t2 VALUES(0, 'zero')" {"columns x, y are"} - 7 "INSERT INTO t2 VALUES(45.5, 'one')" {"columns x, y are"} - 8 "INSERT INTO t2 VALUES(0.0, 'zero')" {"columns x, y are"} - 9 "INSERT INTO t2 VALUES('brambles', 'two')" {"columns x, y are"} - 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'three')" {"columns x, y are"} + 6 "INSERT INTO t2 VALUES(0, 'zero')" {""} + 7 "INSERT INTO t2 VALUES(45.5, 'one')" {""} + 8 "INSERT INTO t2 VALUES(0.0, 'zero')" {""} + 9 "INSERT INTO t2 VALUES('brambles', 'two')" {""} + 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'three')" {""} } do_createtable_tests 4.3.2 { 1 "INSERT INTO t1 VALUES(-1, 0)" {} 2 "INSERT INTO t1 VALUES(45.2, 'abc')" {} 3 "INSERT INTO t1 VALUES(0.01, 'abc')" {} @@ -1209,64 +1209,52 @@ 7 "INSERT INTO t2 VALUES(45.5, 'abc')" {} 8 "INSERT INTO t2 VALUES(0.0, 'abc')" {} 9 "INSERT INTO t2 VALUES('brambles', 'abc')" {} 10 "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')" {} } -do_createtable_tests 4.3.3 -error { %s not unique } { - 1 "UPDATE t1 SET x=0 WHERE y='two'" {"column x is"} - 2 "UPDATE t1 SET x='brambles' WHERE y='three'" {"column x is"} - 3 "UPDATE t1 SET x=45.5 WHERE y='zero'" {"column x is"} - 4 "UPDATE t1 SET x=X'ABCDEF' WHERE y='one'" {"column x is"} - 5 "UPDATE t1 SET x=0.0 WHERE y='three'" {"column x is"} - - 6 "UPDATE t2 SET x=0, y='zero' WHERE y='two'" {"columns x, y are"} - 7 "UPDATE t2 SET x='brambles', y='two' WHERE y='three'" - {"columns x, y are"} - 8 "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"} - 9 "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'" - {"columns x, y are"} - 10 "UPDATE t2 SET x=0.0, y='zero' WHERE y='three'" - {"columns x, y are"} -} - - -# EVIDENCE-OF: R-52572-02078 For the purposes of determining the -# uniqueness of primary key values, NULL values are considered distinct -# from all other values, including other NULLs. -# -do_createtable_tests 4.4 { - 1 "INSERT INTO t1 VALUES(NULL, 0)" {} - 2 "INSERT INTO t1 VALUES(NULL, 0)" {} - 3 "INSERT INTO t1 VALUES(NULL, 0)" {} - - 4 "INSERT INTO t2 VALUES(NULL, 'zero')" {} - 5 "INSERT INTO t2 VALUES(NULL, 'one')" {} - 6 "INSERT INTO t2 VALUES(NULL, 'two')" {} - 7 "INSERT INTO t2 VALUES(NULL, 'three')" {} - - 8 "INSERT INTO t2 VALUES(0, NULL)" {} - 9 "INSERT INTO t2 VALUES(45.5, NULL)" {} - 10 "INSERT INTO t2 VALUES(0.0, NULL)" {} - 11 "INSERT INTO t2 VALUES('brambles', NULL)" {} - 12 "INSERT INTO t2 VALUES(X'ABCDEF', NULL)" {} - - 13 "INSERT INTO t2 VALUES(NULL, NULL)" {} - 14 "INSERT INTO t2 VALUES(NULL, NULL)" {} -} - -# EVIDENCE-OF: R-61866-38053 Unless the column is an INTEGER PRIMARY KEY -# SQLite allows NULL values in a PRIMARY KEY column. -# -# If the column is an integer primary key, attempting to insert a NULL -# into the column triggers the auto-increment behaviour. Attempting -# to use UPDATE to set an ipk column to a NULL value is an error. -# +do_createtable_tests 4.3.3 -error { PRIMARY KEY must be unique%s } { + 1 "UPDATE t1 SET x=0 WHERE y='two'" {""} + 2 "UPDATE t1 SET x='brambles' WHERE y='three'" {""} + 3 "UPDATE t1 SET x=45.5 WHERE y='zero'" {""} + 4 "UPDATE t1 SET x=X'ABCDEF' WHERE y='one'" {""} + 5 "UPDATE t1 SET x=0.0 WHERE y='three'" {""} + + 6 "UPDATE t2 SET x=0, y='zero' WHERE y='two'" {""} + 7 "UPDATE t2 SET x='brambles', y='two' WHERE y='three'" {""} + 8 "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {""} + 9 "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'" {""} + 10 "UPDATE t2 SET x=0.0, y='zero' WHERE y='three'" {""} +} + + +# src4 does not allow NULL values in primary key columns. +# +do_createtable_tests 4.4 -error { %s may not be NULL } { + 1 "INSERT INTO t1 VALUES(NULL, 0)" {t1.x} + 2 "INSERT INTO t1 VALUES(NULL, 0)" {t1.x} + 3 "INSERT INTO t1 VALUES(NULL, 0)" {t1.x} + + 4 "INSERT INTO t2 VALUES(NULL, 'zero')" {t2.x} + 5 "INSERT INTO t2 VALUES(NULL, 'one')" {t2.x} + 6 "INSERT INTO t2 VALUES(NULL, 'two')" {t2.x} + 7 "INSERT INTO t2 VALUES(NULL, 'three')" {t2.x} + + 8 "INSERT INTO t2 VALUES(0, NULL)" {t2.y} + 9 "INSERT INTO t2 VALUES(45.5, NULL)" {t2.y} + 10 "INSERT INTO t2 VALUES(0.0, NULL)" {t2.y} + 11 "INSERT INTO t2 VALUES('brambles', NULL)" {t2.y} + 12 "INSERT INTO t2 VALUES(X'ABCDEF', NULL)" {t2.y} + + 13 "INSERT INTO t2 VALUES(NULL, NULL)" {t2.x} + 14 "INSERT INTO t2 VALUES(NULL, NULL)" {t2.x} +} + do_createtable_tests 4.5.1 { - 1 "SELECT count(*) FROM t1 WHERE x IS NULL" 3 - 2 "SELECT count(*) FROM t2 WHERE x IS NULL" 6 - 3 "SELECT count(*) FROM t2 WHERE y IS NULL" 7 - 4 "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL" 2 + 1 "SELECT count(*) FROM t1 WHERE x IS NULL" 0 + 2 "SELECT count(*) FROM t2 WHERE x IS NULL" 0 + 3 "SELECT count(*) FROM t2 WHERE y IS NULL" 0 + 4 "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL" 0 } do_execsql_test 4.5.2 { CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v); INSERT INTO t3 VALUES(1, NULL, 2); INSERT INTO t3 VALUES('x', NULL, 'y'); @@ -1273,11 +1261,11 @@ SELECT u FROM t3; } {1 2} do_catchsql_test 4.5.3 { INSERT INTO t3 VALUES(2, 5, 3); UPDATE t3 SET u = NULL WHERE s = 2; -} {1 {datatype mismatch}} +} {1 {t3.u may not be NULL}} # EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY # KEY constraint, except that a single table may have any number of # UNIQUE constraints. # @@ -1350,15 +1338,15 @@ # index in the database (in the same way as a "CREATE UNIQUE INDEX" # statement would). do_createtable_tests 4.9 -repair drop_all_tables -query { SELECT count(*) FROM sqlite_master WHERE type='index' } { - 1 "CREATE TABLE t1(a TEXT PRIMARY KEY, b)" 1 + 1 "CREATE TABLE t1(a TEXT PRIMARY KEY, b)" 0 2 "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)" 0 3 "CREATE TABLE t1(a TEXT UNIQUE, b)" 1 - 4 "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)" 2 - 5 "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))" 2 + 4 "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)" 1 + 5 "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))" 1 } # EVIDENCE-OF: R-02252-33116 Such an index is used like any other index # in the database to optimize queries. # @@ -1366,17 +1354,17 @@ CREATE TABLE t1(a, b PRIMARY KEY); CREATE TABLE t2(a, b, c, UNIQUE(b, c)); } do_createtable_tests 4.10 { 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" - {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}} + {0 0 0 {SEARCH TABLE t1 USING PRIMARY KEY (b=?) (~1 rows)}} 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c" - {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}} + {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_t2_unique1 (~1000000 rows)}} 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10" - {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}} + {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_t2_unique1 (b=? AND c>?) (~2 rows)}} } # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a # column definition or specified as a table constraint. In practice it # makes no difference. @@ -1538,61 +1526,61 @@ INSERT INTO t3_re SELECT * FROM t3_ab; INSERT INTO t3_xx SELECT * FROM t3_ab; } foreach {tn tbl res ac data} { - 1 t1_ab {1 {column a is not unique}} 0 {1 one 2 two 3 three} - 2 t1_ro {1 {column a is not unique}} 1 {1 one 2 two} - 3 t1_fa {1 {column a is not unique}} 0 {1 one 2 two 3 three 4 string} + 1 t1_ab {1 {PRIMARY KEY must be unique}} 0 {1 one 2 two 3 three} + 2 t1_ro {1 {PRIMARY KEY must be unique}} 1 {1 one 2 two} + 3 t1_fa {1 {PRIMARY KEY must be unique}} 0 {1 one 2 two 3 three 4 string} 4 t1_ig {0 {}} 0 {1 one 2 two 3 three 4 string 6 string} - 5 t1_re {0 {}} 0 {1 one 2 two 4 string 3 string 6 string} - 6 t1_xx {1 {column a is not unique}} 0 {1 one 2 two 3 three} + 5 t1_re {0 {}} 0 {1 one 2 two 3 string 4 string 6 string} + 6 t1_xx {1 {PRIMARY KEY must be unique}} 0 {1 one 2 two 3 three} } { catchsql COMMIT do_execsql_test 4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" do_catchsql_test 4.15.$tn.2 " INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl; " $res - do_test e_createtable-4.15.$tn.3 { sqlite4_get_autocommit db } $ac + do_test e_createtable-4.15.$tn.3 { sqlite4_db_transaction_status db } [expr !$ac] do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data } -foreach {tn tbl res ac data} { - 1 t2_ab {1 {t2_ab.b may not be NULL}} 0 {1 one 2 two 3 three} - 2 t2_ro {1 {t2_ro.b may not be NULL}} 1 {1 one 2 two} - 3 t2_fa {1 {t2_fa.b may not be NULL}} 0 {1 one 2 two 3 three 4 xx} - 4 t2_ig {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx} - 5 t2_re {1 {t2_re.b may not be NULL}} 0 {1 one 2 two 3 three} - 6 t2_xx {1 {t2_xx.b may not be NULL}} 0 {1 one 2 two 3 three} +foreach {tn tbl res trans data} { + 1 t2_ab {1 {t2_ab.b may not be NULL}} 1 {1 one 2 two 3 three} + 2 t2_ro {1 {t2_ro.b may not be NULL}} 0 {1 one 2 two} + 3 t2_fa {1 {t2_fa.b may not be NULL}} 1 {1 one 2 two 3 three 4 xx} + 4 t2_ig {0 {}} 1 {1 one 2 two 3 three 4 xx 6 xx} + 5 t2_re {1 {t2_re.b may not be NULL}} 1 {1 one 2 two 3 three} + 6 t2_xx {1 {t2_xx.b may not be NULL}} 1 {1 one 2 two 3 three} } { catchsql COMMIT do_execsql_test 4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" do_catchsql_test 4.16.$tn.2 " INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl " $res - do_test e_createtable-4.16.$tn.3 { sqlite4_get_autocommit db } $ac + do_test e_createtable-4.16.$tn.3 { sqlite4_db_transaction_status db } $trans do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data } -foreach {tn tbl res ac data} { - 1 t3_ab {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three} - 2 t3_ro {1 {columns a, b are not unique}} 1 {1 one 2 two} - 3 t3_fa {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three 4 three} - 4 t3_ig {0 {}} 0 {1 one 2 two 3 three 4 three 6 three} - 5 t3_re {0 {}} 0 {1 one 2 two 4 three 3 three 6 three} - 6 t3_xx {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three} +foreach {tn tbl res trans data} { + 1 t3_ab {1 {columns a, b are not unique}} 1 {1 one 2 two 3 three} + 2 t3_ro {1 {columns a, b are not unique}} 0 {1 one 2 two} + 3 t3_fa {1 {columns a, b are not unique}} 1 {1 one 2 two 3 three 4 three} + 4 t3_ig {0 {}} 1 {1 one 2 two 3 three 4 three 6 three} + 5 t3_re {0 {}} 1 {1 one 2 two 4 three 3 three 6 three} + 6 t3_xx {1 {columns a, b are not unique}} 1 {1 one 2 two 3 three} } { catchsql COMMIT do_execsql_test 4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')" do_catchsql_test 4.17.$tn.2 " INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl " $res - do_test e_createtable-4.17.$tn.3 { sqlite4_get_autocommit db } $ac + do_test e_createtable-4.17.$tn.3 { sqlite4_db_transaction_status db } $trans do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data } catchsql COMMIT # EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not @@ -1611,11 +1599,11 @@ } do_execsql_test 4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) } do_catchsql_test 4.18.3 { INSERT INTO t4 SELECT a+4, b+4 FROM t4 } {1 {constraint failed}} -do_test e_createtable-4.18.4 { sqlite4_get_autocommit db } 0 +do_test e_createtable-4.18.4 { sqlite4_db_transaction_status db } 1 do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6} # EVIDENCE-OF: R-19114-56113 Different constraints within the same table # may have different default conflict resolution algorithms. # @@ -1626,102 +1614,10 @@ do_execsql_test 4.19.2 { SELECT * FROM t5 } {} do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \ {1 {t5.b may not be NULL}} do_execsql_test 4.19.4 { SELECT * FROM t5 } {} -#------------------------------------------------------------------------ -# Tests for INTEGER PRIMARY KEY and rowid related statements. -# - -# EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one -# of the special case-independent names "rowid", "oid", or "_rowid_" in -# place of a column name. -# -drop_all_tables -do_execsql_test 5.1.0 { - CREATE TABLE t1(x, y); - INSERT INTO t1 VALUES('one', 'first'); - INSERT INTO t1 VALUES('two', 'second'); - INSERT INTO t1 VALUES('three', 'third'); -} -do_createtable_tests 5.1 { - 1 "SELECT rowid FROM t1" {1 2 3} - 2 "SELECT oid FROM t1" {1 2 3} - 3 "SELECT _rowid_ FROM t1" {1 2 3} - 4 "SELECT ROWID FROM t1" {1 2 3} - 5 "SELECT OID FROM t1" {1 2 3} - 6 "SELECT _ROWID_ FROM t1" {1 2 3} - 7 "SELECT RoWiD FROM t1" {1 2 3} - 8 "SELECT OiD FROM t1" {1 2 3} - 9 "SELECT _RoWiD_ FROM t1" {1 2 3} -} - -# EVIDENCE-OF: R-26501-17306 If a table contains a user defined column -# named "rowid", "oid" or "_rowid_", then that name always refers the -# explicitly declared column and cannot be used to retrieve the integer -# rowid value. -# -do_execsql_test 5.2.0 { - CREATE TABLE t2(oid, b); - CREATE TABLE t3(a, _rowid_); - CREATE TABLE t4(a, b, rowid); - - INSERT INTO t2 VALUES('one', 'two'); - INSERT INTO t2 VALUES('three', 'four'); - - INSERT INTO t3 VALUES('five', 'six'); - INSERT INTO t3 VALUES('seven', 'eight'); - - INSERT INTO t4 VALUES('nine', 'ten', 'eleven'); - INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen'); -} -do_createtable_tests 5.2 { - 1 "SELECT oid, rowid, _rowid_ FROM t2" {one 1 1 three 2 2} - 2 "SELECT oid, rowid, _rowid_ FROM t3" {1 1 six 2 2 eight} - 3 "SELECT oid, rowid, _rowid_ FROM t4" {1 eleven 1 2 fourteen 2} -} - - -# Argument $tbl is the name of a table in the database. Argument $col is -# the name of one of the tables columns. Return 1 if $col is an alias for -# the rowid, or 0 otherwise. -# -proc is_integer_primary_key {tbl col} { - lindex [db eval [subst { - DELETE FROM $tbl; - INSERT INTO $tbl ($col) VALUES(0); - SELECT (rowid==$col) FROM $tbl; - DELETE FROM $tbl; - }]] 0 -} - -# EVIDENCE-OF: R-53738-31673 With one exception, if a table has a -# primary key that consists of a single column, and the declared type of -# that column is "INTEGER" in any mixture of upper and lower case, then -# the column becomes an alias for the rowid. -# -# EVIDENCE-OF: R-45951-08347 if the declaration of a column with -# declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does -# not become an alias for the rowid and is not classified as an integer -# primary key. -# -do_createtable_tests 5.3 -tclquery { - is_integer_primary_key t5 pk -} -repair { - catchsql { DROP TABLE t5 } -} { - 1 "CREATE TABLE t5(pk integer primary key)" 1 - 2 "CREATE TABLE t5(pk integer, primary key(pk))" 1 - 3 "CREATE TABLE t5(pk integer, v integer, primary key(pk))" 1 - 4 "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))" 0 - 5 "CREATE TABLE t5(pk int, v integer, primary key(pk, v))" 0 - 6 "CREATE TABLE t5(pk int, v integer, primary key(pk))" 0 - 7 "CREATE TABLE t5(pk int primary key, v integer)" 0 - 8 "CREATE TABLE t5(pk inTEger primary key)" 1 - 9 "CREATE TABLE t5(pk inteGEr, primary key(pk))" 1 - 10 "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))" 1 -} # EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or # "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary # key column to behave as an ordinary table column with integer affinity # and a unique index, not as an alias for the rowid. @@ -1730,14 +1626,10 @@ CREATE TABLE t6(pk INT primary key); CREATE TABLE t7(pk BIGINT primary key); CREATE TABLE t8(pk SHORT INTEGER primary key); CREATE TABLE t9(pk UNSIGNED INTEGER primary key); } -do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0 -do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0 -do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0 -do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0 do_execsql_test 5.4.3 { INSERT INTO t6 VALUES('2.0'); INSERT INTO t7 VALUES('2.0'); INSERT INTO t8 VALUES('2.0'); @@ -1748,178 +1640,17 @@ SELECT typeof(pk), pk FROM t9; } {integer 2 integer 2 integer 2 integer 2} do_catchsql_test 5.4.4.1 { INSERT INTO t6 VALUES(2) -} {1 {column pk is not unique}} +} {1 {PRIMARY KEY must be unique}} do_catchsql_test 5.4.4.2 { INSERT INTO t7 VALUES(2) -} {1 {column pk is not unique}} +} {1 {PRIMARY KEY must be unique}} do_catchsql_test 5.4.4.3 { INSERT INTO t8 VALUES(2) -} {1 {column pk is not unique}} +} {1 {PRIMARY KEY must be unique}} do_catchsql_test 5.4.4.4 { INSERT INTO t9 VALUES(2) -} {1 {column pk is not unique}} - -# EVIDENCE-OF: R-56094-57830 the following three table declarations all -# cause the column "x" to be an alias for the rowid (an integer primary -# key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE -# t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y, -# z, PRIMARY KEY(x DESC)); -# -# EVIDENCE-OF: R-20149-25884 the following declaration does not result -# in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY -# KEY DESC, y, z); -# -do_createtable_tests 5 -tclquery { - is_integer_primary_key t x -} -repair { - catchsql { DROP TABLE t } -} { - 5.1 "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)" 1 - 5.2 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))" 1 - 5.3 "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1 - 6.1 "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)" 0 -} - -# EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an -# UPDATE statement in the same way as any other column value can, either -# using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by -# using an alias created by an integer primary key. -# -do_execsql_test 5.7.0 { - CREATE TABLE t10(a, b); - INSERT INTO t10 VALUES('ten', 10); - - CREATE TABLE t11(a, b INTEGER PRIMARY KEY); - INSERT INTO t11 VALUES('ten', 10); -} -do_createtable_tests 5.7.1 -query { - SELECT rowid, _rowid_, oid FROM t10; -} { - 1 "UPDATE t10 SET rowid = 5" {5 5 5} - 2 "UPDATE t10 SET _rowid_ = 6" {6 6 6} - 3 "UPDATE t10 SET oid = 7" {7 7 7} -} -do_createtable_tests 5.7.2 -query { - SELECT rowid, _rowid_, oid, b FROM t11; -} { - 1 "UPDATE t11 SET rowid = 5" {5 5 5 5} - 2 "UPDATE t11 SET _rowid_ = 6" {6 6 6 6} - 3 "UPDATE t11 SET oid = 7" {7 7 7 7} - 4 "UPDATE t11 SET b = 8" {8 8 8 8} -} - -# EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide -# a value to use as the rowid for each row inserted. -# -do_createtable_tests 5.8.1 -query { - SELECT rowid, _rowid_, oid FROM t10; -} -repair { - execsql { DELETE FROM t10 } -} { - 1 "INSERT INTO t10(oid) VALUES(15)" {15 15 15} - 2 "INSERT INTO t10(rowid) VALUES(16)" {16 16 16} - 3 "INSERT INTO t10(_rowid_) VALUES(17)" {17 17 17} - 4 "INSERT INTO t10(a, b, oid) VALUES(1,2,3)" {3 3 3} -} -do_createtable_tests 5.8.2 -query { - SELECT rowid, _rowid_, oid, b FROM t11; -} -repair { - execsql { DELETE FROM t11 } -} { - 1 "INSERT INTO t11(oid) VALUES(15)" {15 15 15 15} - 2 "INSERT INTO t11(rowid) VALUES(16)" {16 16 16 16} - 3 "INSERT INTO t11(_rowid_) VALUES(17)" {17 17 17 17} - 4 "INSERT INTO t11(a, b) VALUES(1,2)" {2 2 2 2} -} - -# EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer -# primary key or rowid column must contain integer values. Integer -# primary key or rowid columns are not able to hold floating point -# values, strings, BLOBs, or NULLs. -# -# This is considered by the tests for the following 3 statements, -# which show that: -# -# 1. Attempts to UPDATE a rowid column to a non-integer value fail, -# 2. Attempts to INSERT a real, string or blob value into a rowid -# column fail, and -# 3. Attempting to INSERT a NULL value into a rowid column causes the -# system to automatically select an integer value to use. -# - - -# EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an -# integer primary key or rowid column to a NULL or blob value, or to a -# string or real value that cannot be losslessly converted to an -# integer, a "datatype mismatch" error occurs and the statement is -# aborted. -# -drop_all_tables -do_execsql_test 5.9.0 { - CREATE TABLE t12(x INTEGER PRIMARY KEY, y); - INSERT INTO t12 VALUES(5, 'five'); -} -do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } { - 1 "UPDATE t12 SET x = 4" {integer 4} - 2 "UPDATE t12 SET x = 10.0" {integer 10} - 3 "UPDATE t12 SET x = '12.0'" {integer 12} - 4 "UPDATE t12 SET x = '-15.0'" {integer -15} -} -do_createtable_tests 5.9.2 -error { - datatype mismatch -} { - 1 "UPDATE t12 SET x = 4.1" {} - 2 "UPDATE t12 SET x = 'hello'" {} - 3 "UPDATE t12 SET x = NULL" {} - 4 "UPDATE t12 SET x = X'ABCD'" {} - 5 "UPDATE t12 SET x = X'3900'" {} - 6 "UPDATE t12 SET x = X'39'" {} -} - -# EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a -# blob value, or a string or real value that cannot be losslessly -# converted to an integer into an integer primary key or rowid column, a -# "datatype mismatch" error occurs and the statement is aborted. -# -do_execsql_test 5.10.0 { DELETE FROM t12 } -do_createtable_tests 5.10.1 -error { - datatype mismatch -} { - 1 "INSERT INTO t12(x) VALUES(4.1)" {} - 2 "INSERT INTO t12(x) VALUES('hello')" {} - 3 "INSERT INTO t12(x) VALUES(X'ABCD')" {} - 4 "INSERT INTO t12(x) VALUES(X'3900')" {} - 5 "INSERT INTO t12(x) VALUES(X'39')" {} -} -do_createtable_tests 5.10.2 -query { - SELECT typeof(x), x FROM t12 -} -repair { - execsql { DELETE FROM t12 } -} { - 1 "INSERT INTO t12(x) VALUES(4)" {integer 4} - 2 "INSERT INTO t12(x) VALUES(10.0)" {integer 10} - 3 "INSERT INTO t12(x) VALUES('12.0')" {integer 12} - 4 "INSERT INTO t12(x) VALUES('4e3')" {integer 4000} - 5 "INSERT INTO t12(x) VALUES('-14.0')" {integer -14} -} - -# EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a -# NULL value into a rowid or integer primary key column, the system -# chooses an integer value to use as the rowid automatically. -# -do_execsql_test 5.11.0 { DELETE FROM t12 } -do_createtable_tests 5.11 -query { - SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12) -} { - 1 "INSERT INTO t12 DEFAULT VALUES" {integer 1} - 2 "INSERT INTO t12(y) VALUES(5)" {integer 2} - 3 "INSERT INTO t12(x,y) VALUES(NULL, 10)" {integer 3} - 4 "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12" - {integer 4 integer 5 integer 6} - 5 "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3" - {integer 7 integer 8 integer 9} -} +} {1 {PRIMARY KEY must be unique}} finish_test Index: test/e_delete.test ================================================================== --- test/e_delete.test +++ test/e_delete.test @@ -204,12 +204,12 @@ # attached). # do_execsql_test e_delete-2.3.0 { DROP TRIGGER aux.tr1; DROP TRIGGER main.tr1; - DELETE FROM main.t8 WHERE oid>1; - DELETE FROM aux.t8 WHERE oid>1; + DELETE FROM main.t8 WHERE rowid>1; + DELETE FROM aux.t8 WHERE rowid>1; INSERT INTO aux.t9 VALUES(1, 2); INSERT INTO main.t7 VALUES(3, 4); } {} do_execsql_test e_delete-2.3.1 { SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL Index: test/e_expr.test ================================================================== --- test/e_expr.test +++ test/e_expr.test @@ -42,11 +42,11 @@ foreach {op opn} { || cat * mul / div % mod + add - sub << lshift >> rshift & bitand | bitor < less <= lesseq > more >= moreeq = eq1 == eq2 <> ne1 != ne2 IS is LIKE like - GLOB glob AND and OR or MATCH match REGEXP regexp + GLOB glob AND and OR or REGEXP regexp {IS NOT} isnt } { set ::opname($op) $opn } set oplist [list] @@ -54,11 +54,11 @@ 1 || 2 {* / %} 3 {+ -} 4 {<< >> & |} 5 {< <= > >=} - 6 {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP} + 6 {= == != <> IS {IS NOT} LIKE GLOB REGEXP} 7 AND 8 OR } { foreach op $opl { set ::opprec($op) $prec @@ -196,11 +196,11 @@ foreach {tn literal type} { 1 'helloworld' text 2 45 integer 3 45.2 real 4 45.0 real - 5 X'ABCDEF' blob + 5 x'abcdef' blob 6 NULL null } { set sql " SELECT quote( + $literal ), typeof( + $literal) " do_execsql_test e_expr-3.$tn $sql [list $literal $type] } @@ -735,12 +735,10 @@ 55 "EXPR1 LIKE EXPR2 ESCAPE EXPR" 56 "EXPR1 GLOB EXPR2" 57 "EXPR1 GLOB EXPR2 ESCAPE EXPR" 58 "EXPR1 REGEXP EXPR2" 59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR" - 60 "EXPR1 MATCH EXPR2" - 61 "EXPR1 MATCH EXPR2 ESCAPE EXPR" 62 "EXPR1 NOT LIKE EXPR2" 63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR" 64 "EXPR1 NOT GLOB EXPR2" 65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR" 66 "EXPR1 NOT REGEXP EXPR2" @@ -1092,39 +1090,10 @@ set ::regexpargs [list] do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0 do_test e_expr-18.2.4 { set regexpargs } {Y X} sqlite4 db test.db -# EVIDENCE-OF: R-42037-37826 The default match() function implementation -# raises an exception and is not really useful for anything. -# -do_catchsql_test e_expr-19.1.1 { - SELECT 'abc' MATCH 'def' -} {1 {unable to use function MATCH in the requested context}} -do_catchsql_test e_expr-19.1.2 { - SELECT match('abc', 'def') -} {1 {unable to use function MATCH in the requested context}} - -# EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for -# the match() application-defined function. -# -# EVIDENCE-OF: R-06021-09373 But extensions can override the match() -# function with more helpful logic. -# -proc matchfunc {args} { - eval lappend ::matchargs $args - return 1 -} -db func match -argcount 2 matchfunc -set ::matchargs [list] -do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1 -do_test e_expr-19.2.2 { set matchargs } {def abc} -set ::matchargs [list] -do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0 -do_test e_expr-19.2.4 { set matchargs } {Y X} -sqlite4 db test.db - #------------------------------------------------------------------------- # Test cases for the testable statements related to the CASE expression. # # EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE # expression: those with a base expression and those without. @@ -1311,11 +1280,11 @@ do_execsql_test e_expr-23.1.4 { SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1 } {B} do_execsql_test e_expr-23.1.5 { SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1 -} {A} +} {B} do_execsql_test e_expr-23.1.6 { SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END } {B} do_execsql_test e_expr-23.1.7 { SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1 @@ -1437,28 +1406,28 @@ # EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting # the value to TEXT in the encoding of the database connection, then # interpreting the resulting byte sequence as a BLOB instead of as TEXT. # -do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869' -do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) } X'343536' -do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) } X'312E3738' +do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } x'676869' +do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) } x'343536' +do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) } x'312e3738' rename db db2 sqlite4 db :memory: ifcapable {utf16} { db eval { PRAGMA encoding = 'utf-16le' } -do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900' -do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) } X'340035003600' -do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) } X'31002E0037003800' +do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } x'670068006900' +do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) } x'340035003600' +do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) } x'31002e0037003800' } db close sqlite4 db :memory: db eval { PRAGMA encoding = 'utf-16be' } ifcapable {utf16} { -do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069' -do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) } X'003400350036' -do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) } X'0031002E00370038' +do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } x'006700680069' +do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) } x'003400350036' +do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) } x'0031002e00370038' } db close rename db2 db # EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence @@ -1484,13 +1453,13 @@ do_expr_test e_expr-28.2.1 { CAST (1 AS text) } text 1 do_expr_test e_expr-28.2.2 { CAST (45 AS text) } text 45 do_expr_test e_expr-28.2.3 { CAST (-45 AS text) } text -45 do_expr_test e_expr-28.2.4 { CAST (8.8 AS text) } text 8.8 do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) } text 230000.0 -do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05 +do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -0.000023 do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) } text 0.0 -do_expr_test e_expr-28.2.7 { CAST (0 AS text) } text 0 +do_expr_test e_expr-28.2.8 { CAST (0 AS text) } text 0 # EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the # value is first converted to TEXT. # do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23 @@ -1600,18 +1569,18 @@ # EVIDENCE-OF: R-49503-28105 If a REAL is too large to be represented as # an INTEGER then the result of the cast is the largest negative # integer: -9223372036854775808. # -do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer -9223372036854775808 +do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807 do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808 do_expr_test e_expr-31.2.3 { CAST(-9223372036854775809.0 AS INT) } integer -9223372036854775808 do_expr_test e_expr-31.2.4 { CAST(9223372036854775809.0 AS INT) -} integer -9223372036854775808 +} integer 9223372036854775807 # EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC # first does a forced conversion into REAL but then further converts the # result into INTEGER if and only if the conversion from REAL to INTEGER Index: test/permutations.test ================================================================== --- test/permutations.test +++ test/permutations.test @@ -171,13 +171,13 @@ delete.test delete2.test delete3.test descidx1.test descidx2.test descidx3.test distinct.test distinctagg.test enc.test enc4.test exists.test - e_droptrigger.test e_dropview.test - e_resolve.test e_dropview.test - e_select2.test + e_createtable.test e_delete.test e_droptrigger.test e_dropview.test + e_expr.test + e_resolve.test e_select2.test fkey1.test fkey2.test fkey3.test fkey4.test func.test func2.test func3.test fuzz2.test in.test in4.test index2.test index3.test index4.test Index: test/simple.test ================================================================== --- test/simple.test +++ test/simple.test @@ -1560,28 +1560,43 @@ SELECT idx, count(*), sum(length(sample)) FROM t1 GROUP BY idx } {t1 2 4 t1i1 2 4 t1i2 2 4 t1i3 2 4} #------------------------------------------------------------------------- reset_db -do_test alter-81.1 { +do_test 81.1 { execsql { CREATE TABLE t1(a TEXT); INSERT INTO t1 VALUES(5.4e-08); SELECT a FROM t1; } } {5.4e-8} #------------------------------------------------------------------------- reset_db -do_test alter-82.1 { +do_test 82.1 { execsql { CREATE TABLE t1(a TEXT) } execsql { SELECT * FROM sqlite_master } } {table t1 t1 2 {CREATE TABLE t1(a TEXT)}} -do_test alter-82.2 { +do_test 82.2 { execsql { DROP TABLE t1 } execsql { CREATE TABLE t1(a TEXT) } execsql { SELECT * FROM sqlite_master } } {table t1 t1 2 {CREATE TABLE t1(a TEXT)}} + +#------------------------------------------------------------------------- +reset_db +do_test 83.1 { + execsql { SELECT CAST('2.12e-01ABC' AS INT) } +} {2} +do_test 83.2 { + execsql { SELECT CAST(' -2.12e-01ABC' AS INT) } +} {-2} +do_test 83.3 { + execsql { SELECT CAST('45.0' AS NUMERIC) } +} {45} +do_test 83.4 { + execsql { SELECT CAST(0.0 AS TEXT) } +} {0.0} finish_test