SQLite4
Check-in [d6d0e21ad2]
Not logged in

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

Overview
SHA1 Hash:d6d0e21ad2770739d086a759f6a8133142835974
Date: 2013-06-27 20:23:03
User: dan
Comment:Run some more legacy tests. Fix some issues with converting between text and numeric values.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/math.c

348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
...
632
633
634
635
636
637
638



639

640
641
642
643
644
645
646
      nIn -= 1;
    }
  }
  
  /* If the IGNORE_WHITESPACE flag is set, ignore any leading whitespace. */
  i = 0;
  if( flags & SQLITE4_IGNORE_WHITESPACE ){
    while( sqlite4Isspace(zIn[i]) && i<nIn ) i+=incr;
  }
  if( nIn<=i ) return error_value;

  /* Check for a leading '+' or '-' symbol. */
  if( zIn[i]=='-' ){
    r.sign = 1;
    i += incr;
................................................................................
      memcpy(z, "NaN", 4);
    }else{
      memcpy(z, "inf", 4);
    }
    return (z - zOut)+3;
  }
  if( x.m==0 ){



    memcpy(z, "0", 2);

    return 1+(z-zOut);
  }
  zNum = renderInt(x.m, zBuf, sizeof(zBuf));
  n = &zBuf[sizeof(zBuf)-1] - zNum;
  if( x.e>=0 && x.e+n<=25 ){
    /* Integer values with up to 25 digits */
    memcpy(z, zNum, n+1);







|







 







>
>
>
|
>







348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
...
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
      nIn -= 1;
    }
  }
  
  /* If the IGNORE_WHITESPACE flag is set, ignore any leading whitespace. */
  i = 0;
  if( flags & SQLITE4_IGNORE_WHITESPACE ){
    while( i<nIn && sqlite4Isspace(zIn[i]) ) i+=incr;
  }
  if( nIn<=i ) return error_value;

  /* Check for a leading '+' or '-' symbol. */
  if( zIn[i]=='-' ){
    r.sign = 1;
    i += incr;
................................................................................
      memcpy(z, "NaN", 4);
    }else{
      memcpy(z, "inf", 4);
    }
    return (z - zOut)+3;
  }
  if( x.m==0 ){
    if( bReal ){
      memcpy(z, "0.0", 4);
    }else{
      memcpy(z, "0", 2);
    }
    return 1+(z-zOut);
  }
  zNum = renderInt(x.m, zBuf, sizeof(zBuf));
  n = &zBuf[sizeof(zBuf)-1] - zNum;
  if( x.e>=0 && x.e+n<=25 ){
    /* Integer values with up to 25 digits */
    memcpy(z, zNum, n+1);

Changes to src/vdbemem.c

331
332
333
334
335
336
337

338





339
340
341
342
343
344
345
...
352
353
354
355
356
357
358
359

360
361
362
363
364
365
366
*/
int sqlite4VdbeMemIntegerify(Mem *pMem){
  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));





    MemSetTypeFlag(pMem, MEM_Int);
  }
  return SQLITE4_OK;
}

/*
** Convert pMem so that it has types MEM_Real or MEM_Int or both.
................................................................................
int sqlite4VdbeMemNumerify(Mem *pMem){
  if( (pMem->flags & (MEM_Int|MEM_Real|MEM_Null))==0 ){
    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);

    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;
}








>
|
>
>
>
>
>







 







|
>







331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
...
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
*/
int sqlite4VdbeMemIntegerify(Mem *pMem){
  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 ){
    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;
}

/*
** Convert pMem so that it has types MEM_Real or MEM_Int or both.
................................................................................
int sqlite4VdbeMemNumerify(Mem *pMem){
  if( (pMem->flags & (MEM_Int|MEM_Real|MEM_Null))==0 ){
    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, 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;
}

Changes to test/e_createtable.test

361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
...
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
...
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
...
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
....
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
....
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
....
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234

1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
....
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
....
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
....
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
....
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
....
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
do_createtable_tests 1.1.1 -error {
  object name reserved for internal use: %s
} {
  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
}
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)}        {}
  4    {CREATE TABLE auxb."sqlite-"(z)}          {}
  5    {CREATE TABLE "SQLITE-TBL"(z)}            {}
................................................................................
#
#   table_column_decltypes TBL
#     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]
  for {set i 0} {$i < [sqlite4_column_count $STMT]} {incr i} {
    lappend res [$cmd $STMT $i]
  }
  sqlite4_finalize $STMT
  set res
}
proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }
................................................................................
set sqlite_current_time 1000000000
do_createtable_tests 3.5 -query {
  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 
    '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
  }

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

................................................................................
    d DEFAULT -45678.6,
    e DEFAULT 394507
  );
} {}
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}

# 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.
#
#   Test case 3.6.4 demonstrates that the expression is evaluated 
#   separately for each row if the INSERT is an "INSERT INTO ... SELECT ..."
................................................................................
# default compile time value.
#
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
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 {
  INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));
} {1 {string or blob too big}}
................................................................................
  CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
  INSERT INTO t2 VALUES(0,          'zero');
  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"}

  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"}
}
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')"          {}
  4    "INSERT INTO t1 VALUES('bramble', 'abc')"     {}
  5    "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')"     {}
................................................................................

  6    "INSERT INTO t2 VALUES(0, 0)"                 {}
  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.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
}
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');
  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}}

# 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.
#
drop_all_tables
do_createtable_tests 4.6 {
................................................................................
# EVIDENCE-OF: R-26983-26377 INTEGER PRIMARY KEY columns aside, both
# UNIQUE and PRIMARY KEY constraints are implemented by creating an
# 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
  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
}

# EVIDENCE-OF: R-02252-33116 Such an index is used like any other index
# in the database to optimize queries.
#
do_execsql_test 4.10.0 {
  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)}}

  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)}}

  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)}}
}

# 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.
#
#   All the tests that deal with CHECK constraints below (4.11.* and 
................................................................................
  INSERT INTO t3_ig SELECT * FROM t3_ab;
  INSERT INTO t3_fa SELECT * FROM t3_ab;
  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}
  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}
} {
  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_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}
} {
  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_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}
} {
  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_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data
}
catchsql COMMIT

# EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not
# include a conflict-clause or it is a CHECK constraint, the default
# conflict resolution algorithm is ABORT.
................................................................................
  INSERT INTO t4 VALUES(1, 2);
  INSERT INTO t4 VALUES(3, 4);
}
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_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.
#
do_execsql_test 4.19.0 {
  CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);
................................................................................
}
do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}}
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.
#
do_execsql_test 5.4.1 {
  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');
  INSERT INTO t9 VALUES('2.0');
  SELECT typeof(pk), pk FROM t6;
................................................................................
  SELECT typeof(pk), pk FROM t7;
  SELECT typeof(pk), pk FROM t8;
  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}}
do_catchsql_test 5.4.4.2 { 
  INSERT INTO t7 VALUES(2) 
} {1 {column pk is not unique}}
do_catchsql_test 5.4.4.3 { 
  INSERT INTO t8 VALUES(2) 
} {1 {column pk is not 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}
}

finish_test







|







 







|







 







|








|







 







|







 







|







 







|
|
|
|
|
|

|
|
|
|
|







 







|
|
|
|
|
|

|
|
<
|
|
<
|
<



<
<
<
>

|
|
|
|

|
|
|
|

|
|
|
|
|

|
|


<
<
<
<
<
<
<

|
|
|
|










|







 







|


|
|











|


|


|







 







|
|
|

|
|








|


|
|
|
|
|
|
|








|


|
|
|
|
|
|
|








|







 







|







 







<
<
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<











<
<
<
<







 







|


|


|


<
<
<
<
<
<
|
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<

361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
...
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
...
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
...
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
....
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
....
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
....
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222

1223
1224

1225

1226
1227
1228



1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250







1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
....
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
....
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
....
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
....
1612
1613
1614
1615
1616
1617
1618


1619


























































































1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630




1631
1632
1633
1634
1635
1636
1637
....
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653






1654
1655



























































































































































1656
do_createtable_tests 1.1.1 -error {
  object name reserved for internal use: %s
} {
  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 "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)}        {}
  4    {CREATE TABLE auxb."sqlite-"(z)}          {}
  5    {CREATE TABLE "SQLITE-TBL"(z)}            {}
................................................................................
#
#   table_column_decltypes TBL
#     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 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
}
proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }
................................................................................
set sqlite_current_time 1000000000
do_createtable_tests 3.5 -query {
  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 
    '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
  }

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

................................................................................
    d DEFAULT -45678.6,
    e DEFAULT 394507
  );
} {}
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}

# 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.
#
#   Test case 3.6.4 demonstrates that the expression is evaluated 
#   separately for each row if the INSERT is an "INSERT INTO ... SELECT ..."
................................................................................
# default compile time value.
#
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 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 {
  INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));
} {1 {string or blob too big}}
................................................................................
  CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
  INSERT INTO t2 VALUES(0,          'zero');
  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 { 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')"            {""}
  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')"          {}
  4    "INSERT INTO t1 VALUES('bramble', 'abc')"     {}
  5    "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')"     {}
................................................................................

  6    "INSERT INTO t2 VALUES(0, 0)"                 {}
  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 { 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"                   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');
  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 {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.
#
drop_all_tables
do_createtable_tests 4.6 {
................................................................................
# EVIDENCE-OF: R-26983-26377 INTEGER PRIMARY KEY columns aside, both
# UNIQUE and PRIMARY KEY constraints are implemented by creating an
# 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)"              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)"       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.
#
do_execsql_test 4.10.0 {
  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 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_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_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.
#
#   All the tests that deal with CHECK constraints below (4.11.* and 
................................................................................
  INSERT INTO t3_ig SELECT * FROM t3_ab;
  INSERT INTO t3_fa SELECT * FROM t3_ab;
  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 {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 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_db_transaction_status db } [expr !$ac]
  do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data
}
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_db_transaction_status db } $trans
  do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data
}
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_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
# include a conflict-clause or it is a CHECK constraint, the default
# conflict resolution algorithm is ABORT.
................................................................................
  INSERT INTO t4 VALUES(1, 2);
  INSERT INTO t4 VALUES(3, 4);
}
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_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.
#
do_execsql_test 4.19.0 {
  CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);
................................................................................
}
do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}}
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 } {}






























































































# 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.
#
do_execsql_test 5.4.1 {
  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_execsql_test 5.4.3 {
  INSERT INTO t6 VALUES('2.0');
  INSERT INTO t7 VALUES('2.0');
  INSERT INTO t8 VALUES('2.0');
  INSERT INTO t9 VALUES('2.0');
  SELECT typeof(pk), pk FROM t6;
................................................................................
  SELECT typeof(pk), pk FROM t7;
  SELECT typeof(pk), pk FROM t8;
  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 {PRIMARY KEY must be unique}}
do_catchsql_test 5.4.4.2 { 
  INSERT INTO t7 VALUES(2) 
} {1 {PRIMARY KEY must be unique}}
do_catchsql_test 5.4.4.3 { 
  INSERT INTO t8 VALUES(2) 
} {1 {PRIMARY KEY must be unique}}
do_catchsql_test 5.4.4.4 { 
  INSERT INTO t9 VALUES(2) 






} {1 {PRIMARY KEY must be unique}}




























































































































































finish_test

Changes to test/e_delete.test

202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
# top-level statement (by searching first the TEMP database, then the
# main database, then any other databases in the order they were
# 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;
  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
  SELECT count(*) FROM aux.t7  UNION ALL SELECT count(*) FROM aux2.t7;








|
|







202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
# top-level statement (by searching first the TEMP database, then the
# main database, then any other databases in the order they were
# attached).
#
do_execsql_test e_delete-2.3.0 {
  DROP TRIGGER aux.tr1;
  DROP TRIGGER main.tr1;
  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
  SELECT count(*) FROM aux.t7  UNION ALL SELECT count(*) FROM aux2.t7;

Changes to test/e_expr.test

40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
...
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
...
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
....
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
....
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
....
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
....
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
....
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
#   ::oplist         A list of all SQL operators supported by SQLite.
#
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
      {IS NOT} isnt
} {
  set ::opname($op) $opn
}
set oplist [list]
foreach {prec opl} {
  1   ||
  2   {* / %}
  3   {+ -}
  4   {<< >> & |}
  5   {< <= > >=}
  6   {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP}
  7   AND
  8   OR
} {
  foreach op $opl { 
    set ::opprec($op) $prec 
    lappend oplist $op
  }
................................................................................
# the operand.
#
foreach {tn literal type} {
  1     'helloworld'   text
  2     45             integer
  3     45.2           real
  4     45.0           real
  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]
}

#-------------------------------------------------------------------------
................................................................................
 
  54 "EXPR1 LIKE EXPR2"
  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"
  67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"
  68 "EXPR1 NOT MATCH EXPR2"
................................................................................
do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1
do_test         e_expr-18.2.2 { set regexpargs } {def abc}
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.
#
do_execsql_test e_expr-20.1 {
................................................................................
  SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
} {B}
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}
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
} {A}
do_execsql_test e_expr-23.1.8 {
................................................................................
do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10)   } blob ghi

# 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'
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'
}
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'
}
db close
rename db2 db

# EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
# of bytes that make up the BLOB is interpreted as text encoded using
# the database encoding.
................................................................................
# resulting TEXT uses the encoding of the database connection.
#
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.7 { CAST (0.0 AS text) }     text 0.0
do_expr_test e_expr-28.2.7 { 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
do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87
................................................................................
do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0

# 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.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


# 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
# is lossless and reversible.
#







|











|







 







|







 







<
<







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







|







 







|
|
|




|
|
|





|
|
|







 







|

|







 







|






|







40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
...
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
...
733
734
735
736
737
738
739


740
741
742
743
744
745
746
....
1088
1089
1090
1091
1092
1093
1094





























1095
1096
1097
1098
1099
1100
1101
....
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
....
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
....
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
....
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
#   ::oplist         A list of all SQL operators supported by SQLite.
#
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        REGEXP regexp
      {IS NOT} isnt
} {
  set ::opname($op) $opn
}
set oplist [list]
foreach {prec opl} {
  1   ||
  2   {* / %}
  3   {+ -}
  4   {<< >> & |}
  5   {< <= > >=}
  6   {= == != <> IS {IS NOT} LIKE GLOB REGEXP}
  7   AND
  8   OR
} {
  foreach op $opl { 
    set ::opprec($op) $prec 
    lappend oplist $op
  }
................................................................................
# the operand.
#
foreach {tn literal type} {
  1     'helloworld'   text
  2     45             integer
  3     45.2           real
  4     45.0           real
  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]
}

#-------------------------------------------------------------------------
................................................................................
 
  54 "EXPR1 LIKE EXPR2"
  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"


  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"
  67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"
  68 "EXPR1 NOT MATCH EXPR2"
................................................................................
do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1
do_test         e_expr-18.2.2 { set regexpargs } {def abc}
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






























#-------------------------------------------------------------------------
# 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.
#
do_execsql_test e_expr-20.1 {
................................................................................
  SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
} {B}
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
} {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
} {A}
do_execsql_test e_expr-23.1.8 {
................................................................................
do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10)   } blob ghi

# 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'
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'
}
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'
}
db close
rename db2 db

# EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
# of bytes that make up the BLOB is interpreted as text encoded using
# the database encoding.
................................................................................
# resulting TEXT uses the encoding of the database connection.
#
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 -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.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
do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87
................................................................................
do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0

# 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 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 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
# is lossless and reversible.
#

Changes to test/permutations.test

169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
  date.test
  default.test
  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
  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 
  insert.test insert2.test insert3.test insert5.test
  join.test join2.test join3.test join4.test join5.test join6.test







|
|
|







169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
  date.test
  default.test
  delete.test delete2.test delete3.test
  descidx1.test descidx2.test descidx3.test 
  distinct.test distinctagg.test
  enc.test enc4.test
  exists.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 
  insert.test insert2.test insert3.test insert5.test
  join.test join2.test join3.test join4.test join5.test join6.test

Changes to test/simple.test

1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584















1585
1586
1587

do_execsql_test 80.2 {
  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 {
  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 {
  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 {
  execsql { DROP TABLE t1 }
  execsql { CREATE TABLE t1(a TEXT) }
  execsql { SELECT * FROM sqlite_master }
} {table t1 t1 2 {CREATE TABLE t1(a TEXT)}}
















finish_test








|









|




|




>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602

do_execsql_test 80.2 {
  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 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 82.1 {
  execsql { CREATE TABLE t1(a TEXT) }
  execsql { SELECT * FROM sqlite_master }
} {table t1 t1 2 {CREATE TABLE t1(a TEXT)}}

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