/ Changes On Branch wor-pk-dups
Login

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

Changes In Branch wor-pk-dups Excluding Merge-Ins

This is equivalent to a diff from fe014288ac to 340378c1e6

2019-07-17
12:49
Fix the WITHOUT ROWID table logic so that it generates a correct KeyInfo object for tables that have a PRIMARY KEY containing the same column used more than once with different collating sequences. Enhance the index_xinfo pragma to assist in testing the above. Fix for ticket [fd3aec0c7e3e2998]. (check-in: 84a51a755c user: drh tags: trunk)
12:42
New test cases for PRAGMA index_xinfo on a WITHOUT ROWID table. And new testcases using index_xinfo to verify that WITHOUT ROWID tables are constructed correctly. (Closed-Leaf check-in: 340378c1e6 user: drh tags: wor-pk-dups)
11:01
Enhance the "PRAGMA index_info()" and "PRAGMA index_xinfo()" statements so that they allow a WITHOUT ROWID table as their argument, and in that case show the structure of the underlying index used to implement the WITHOUT ROWID table. (check-in: 62274ff683 user: drh tags: wor-pk-dups)
09:18
Fix problems with duplicate fields in the PRIMARY KEYs of WITHOUT ROWID tables. (check-in: bd9a47a3a2 user: dan tags: wor-pk-dups)
07:23
Add casts to shell.c.in to avoid warnings on systems where size_t is 32 bits. (check-in: fe014288ac user: dan tags: trunk)
2019-07-16
19:44
Add new assert() statements in an attempt to help static analyzers avoid false-positives. (check-in: 9e66458592 user: drh tags: trunk)

Changes to src/build.c.

  1827   1827   **
  1828   1828   ** For virtual tables, only (1) is performed.
  1829   1829   */
  1830   1830   static void convertToWithoutRowidTable(Parse *pParse, Table *pTab){
  1831   1831     Index *pIdx;
  1832   1832     Index *pPk;
  1833   1833     int nPk;
         1834  +  int nExtra;
  1834   1835     int i, j;
  1835   1836     sqlite3 *db = pParse->db;
  1836   1837     Vdbe *v = pParse->pVdbe;
  1837   1838   
  1838   1839     /* Mark every PRIMARY KEY column as NOT NULL (except for imposter tables)
  1839   1840     */
  1840   1841     if( !db->init.imposterTable ){
................................................................................
  1869   1870       pList->a[0].sortOrder = pParse->iPkSortOrder;
  1870   1871       assert( pParse->pNewTable==pTab );
  1871   1872       pTab->iPKey = -1;
  1872   1873       sqlite3CreateIndex(pParse, 0, 0, 0, pList, pTab->keyConf, 0, 0, 0, 0,
  1873   1874                          SQLITE_IDXTYPE_PRIMARYKEY);
  1874   1875       if( db->mallocFailed || pParse->nErr ) return;
  1875   1876       pPk = sqlite3PrimaryKeyIndex(pTab);
         1877  +    assert( pPk->nKeyCol==1 );
  1876   1878     }else{
  1877   1879       pPk = sqlite3PrimaryKeyIndex(pTab);
  1878   1880       assert( pPk!=0 );
  1879   1881   
  1880   1882       /*
  1881   1883       ** Remove all redundant columns from the PRIMARY KEY.  For example, change
  1882   1884       ** "PRIMARY KEY(a,b,a,b,c,b,c,d)" into just "PRIMARY KEY(a,b,c,d)".  Later
................................................................................
  1883   1885       ** code assumes the PRIMARY KEY contains no repeated columns.
  1884   1886       */
  1885   1887       for(i=j=1; i<pPk->nKeyCol; i++){
  1886   1888         if( isDupColumn(pPk, j, pPk, i) ){
  1887   1889           pPk->nColumn--;
  1888   1890         }else{
  1889   1891           testcase( hasColumn(pPk->aiColumn, j, pPk->aiColumn[i]) );
         1892  +        pPk->azColl[j] = pPk->azColl[i];
         1893  +        pPk->aSortOrder[j] = pPk->aSortOrder[i];
  1890   1894           pPk->aiColumn[j++] = pPk->aiColumn[i];
  1891   1895         }
  1892   1896       }
  1893   1897       pPk->nKeyCol = j;
  1894   1898     }
  1895   1899     assert( pPk!=0 );
  1896   1900     pPk->isCovering = 1;
  1897   1901     if( !db->init.imposterTable ) pPk->uniqNotNull = 1;
  1898         -  nPk = pPk->nKeyCol;
         1902  +  nPk = pPk->nColumn = pPk->nKeyCol;
  1899   1903   
  1900   1904     /* Bypass the creation of the PRIMARY KEY btree and the sqlite_master
  1901   1905     ** table entry. This is only required if currently generating VDBE
  1902   1906     ** code for a CREATE TABLE (not when parsing one as part of reading
  1903   1907     ** a database schema).  */
  1904   1908     if( v && pPk->tnum>0 ){
  1905   1909       assert( db->init.busy==0 );
................................................................................
  1941   1945       }
  1942   1946       assert( pIdx->nColumn>=pIdx->nKeyCol+n );
  1943   1947       assert( pIdx->nColumn>=j );
  1944   1948     }
  1945   1949   
  1946   1950     /* Add all table columns to the PRIMARY KEY index
  1947   1951     */
  1948         -  if( nPk<pTab->nCol ){
  1949         -    if( resizeIndexObject(db, pPk, pTab->nCol) ) return;
  1950         -    for(i=0, j=nPk; i<pTab->nCol; i++){
  1951         -      if( !hasColumn(pPk->aiColumn, j, i) ){
  1952         -        assert( j<pPk->nColumn );
  1953         -        pPk->aiColumn[j] = i;
  1954         -        pPk->azColl[j] = sqlite3StrBINARY;
  1955         -        j++;
  1956         -      }
         1952  +  nExtra = 0;
         1953  +  for(i=0; i<pTab->nCol; i++){
         1954  +    if( !hasColumn(pPk->aiColumn, nPk, i) ) nExtra++;
         1955  +  }
         1956  +  if( resizeIndexObject(db, pPk, nPk+nExtra) ) return;
         1957  +  for(i=0, j=nPk; i<pTab->nCol; i++){
         1958  +    if( !hasColumn(pPk->aiColumn, j, i) ){
         1959  +      assert( j<pPk->nColumn );
         1960  +      pPk->aiColumn[j] = i;
         1961  +      pPk->azColl[j] = sqlite3StrBINARY;
         1962  +      j++;
  1957   1963       }
  1958         -    assert( pPk->nColumn==j );
  1959         -    assert( pTab->nCol==j );
  1960         -  }else{
  1961         -    pPk->nColumn = pTab->nCol;
  1962   1964     }
         1965  +  assert( pPk->nColumn==j );
         1966  +  assert( pTab->nCol<=j );
  1963   1967     recomputeColumnsNotIndexed(pPk);
  1964   1968   }
  1965   1969   
  1966   1970   #ifndef SQLITE_OMIT_VIRTUALTABLE
  1967   1971   /*
  1968   1972   ** Return true if zName is a shadow table name in the current database
  1969   1973   ** connection.

Changes to src/pragma.c.

  1153   1153     break;
  1154   1154   #endif
  1155   1155   
  1156   1156     case PragTyp_INDEX_INFO: if( zRight ){
  1157   1157       Index *pIdx;
  1158   1158       Table *pTab;
  1159   1159       pIdx = sqlite3FindIndex(db, zRight, zDb);
         1160  +    if( pIdx==0 ){
         1161  +      /* If there is no index named zRight, check to see if there is a
         1162  +      ** WITHOUT ROWID table named zRight, and if there is, show the
         1163  +      ** structure of the PRIMARY KEY index for that table. */
         1164  +      pTab = sqlite3LocateTable(pParse, LOCATE_NOERR, zRight, zDb);
         1165  +      if( pTab && !HasRowid(pTab) ){
         1166  +        pIdx = sqlite3PrimaryKeyIndex(pTab);
         1167  +      }
         1168  +    }
  1160   1169       if( pIdx ){
  1161   1170         int iIdxDb = sqlite3SchemaToIndex(db, pIdx->pSchema);
  1162   1171         int i;
  1163   1172         int mx;
  1164   1173         if( pPragma->iArg ){
  1165   1174           /* PRAGMA index_xinfo (newer version with more rows and columns) */
  1166   1175           mx = pIdx->nColumn;

Changes to test/vtab1.test.

   870    870     }
   871    871   } {31429}
   872    872   do_test vtab1.7-13 {
   873    873     execsql {
   874    874       SELECT rowid, a, b, c FROM real_abc
   875    875     }
   876    876   } {}
          877  +
          878  +# PRAGMA index_info and index_xinfo are no-ops on a virtual table
          879  +do_test vtab1.7-14 {
          880  +  execsql {
          881  +    PRAGMA index_info('echo_abc');
          882  +    PRAGMA index_xinfo('echo_abc');
          883  +  }
          884  +} {}
   877    885   
   878    886   ifcapable attach {
   879    887     do_test vtab1.8-1 {
   880    888       set echo_module ""
   881    889       execsql {
   882    890         ATTACH 'test2.db' AS aux;
   883    891         CREATE VIRTUAL TABLE aux.e2 USING echo(real_abc);

Changes to test/without_rowid1.test.

    26     26     INSERT INTO t1 VALUES('dynamic','juliet','flipper','command');
    27     27     INSERT INTO t1 VALUES('journal','sherman','gamma','patriot');
    28     28     INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena');
    29     29     SELECT *, '|' FROM t1 ORDER BY c, a;
    30     30   } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
    31     31   
    32     32   integrity_check without_rowid1-1.0ic
           33  +
           34  +do_execsql_test without_rowid1-1.0ixi {
           35  +  SELECT name, key FROM pragma_index_xinfo('t1');
           36  +} {c 1 a 1 b 0 d 0}
    33     37   
    34     38   do_execsql_test without_rowid1-1.1 {
    35     39     SELECT *, '|' FROM t1 ORDER BY +c, a;
    36     40   } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
    37     41   
    38     42   do_execsql_test without_rowid1-1.2 {
    39     43     SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC;
................................................................................
   116    120     INSERT INTO t4 VALUES('abc', 'def');
   117    121     SELECT * FROM t4;
   118    122   } {abc def}
   119    123   do_execsql_test 2.1.2 {
   120    124     UPDATE t4 SET a = 'ABC';
   121    125     SELECT * FROM t4;
   122    126   } {ABC def}
          127  +do_execsql_test 2.1.3 {
          128  +  SELECT name, coll, key FROM pragma_index_xinfo('t4');
          129  +} {a nocase 1 b BINARY 0}
   123    130   
   124    131   do_execsql_test 2.2.1 {
   125    132     DROP TABLE t4;
   126    133     CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID;
   127    134     INSERT INTO t4(a, b) VALUES('abc', 'def');
   128    135     SELECT * FROM t4;
   129    136   } {def abc}
   130    137   
   131    138   do_execsql_test 2.2.2 {
   132    139     UPDATE t4 SET a = 'ABC', b = 'xyz';
   133    140     SELECT * FROM t4;
   134    141   } {xyz ABC}
          142  +
          143  +do_execsql_test 2.2.3 {
          144  +  SELECT name, coll, key FROM pragma_index_xinfo('t4');
          145  +} {a nocase 1 b BINARY 0}
          146  +
   135    147   
   136    148   do_execsql_test 2.3.1 {
   137    149     CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID;
   138    150     INSERT INTO t5(a, b) VALUES('abc', 'def');
   139    151     UPDATE t5 SET a='abc', b='def';
   140    152   } {}
          153  +
          154  +do_execsql_test 2.3.2 {
          155  +  SELECT name, coll, key FROM pragma_index_xinfo('t5');
          156  +} {b BINARY 1 a BINARY 1}
          157  +
   141    158   
   142    159   do_execsql_test 2.4.1 {
   143    160     CREATE TABLE t6 (
   144    161       a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a)
   145    162     ) WITHOUT ROWID;
   146    163   
   147    164     INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi');
................................................................................
   148    165     UPDATE t6 SET a='ABC', c='ghi';
   149    166   } {}
   150    167   
   151    168   do_execsql_test 2.4.2 {
   152    169     SELECT * FROM t6 ORDER BY b, a;
   153    170     SELECT * FROM t6 ORDER BY c;
   154    171   } {ABC def ghi ABC def ghi}
          172  +
          173  +do_execsql_test 2.4.3 {
          174  +  SELECT name, coll, key FROM pragma_index_xinfo('t6');
          175  +} {b BINARY 1 a nocase 1 c BINARY 0}
          176  +
   155    177   
   156    178   #-------------------------------------------------------------------------
   157    179   # Unless the destination table is completely empty, the xfer optimization 
   158    180   # is disabled for WITHOUT ROWID tables. The following tests check for
   159    181   # some problems that might occur if this were not the case.
   160    182   #
   161    183   reset_db

Changes to test/without_rowid6.test.

    20     20     CREATE TABLE t1(a,b,c,d,e, PRIMARY KEY(a,b,c,a,b,c,d,a,b,c)) WITHOUT ROWID;
    21     21     CREATE INDEX t1a ON t1(b, b);
    22     22     WITH RECURSIVE
    23     23       c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<1000)
    24     24     INSERT INTO t1(a,b,c,d,e) SELECT i, i+1000, printf('x%dy',i), 0, 0 FROM c;
    25     25     ANALYZE;
    26     26   } {}
           27  +do_execsql_test without_rowid6-101 {
           28  +  SELECT name, key FROM pragma_index_xinfo('t1');
           29  +} {a 1 b 1 c 1 d 1 e 0}
    27     30   do_execsql_test without_rowid6-110 {
    28     31     SELECT c FROM t1 WHERE a=123;
    29     32   } {x123y}
    30     33   do_execsql_test without_rowid6-120 {
    31     34     SELECT c FROM t1 WHERE b=1123;
    32     35   } {x123y}
    33     36   do_execsql_test without_rowid6-130 {
................................................................................
    47     50       b UNIQUE,
    48     51       c UNIQUE,
    49     52       PRIMARY KEY(b)
    50     53     ) WITHOUT ROWID;
    51     54     INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
    52     55     SELECT a FROM t1 WHERE b>3 ORDER BY b;
    53     56   } {4 1}
           57  +do_execsql_test without_rowid6-201 {
           58  +  SELECT name, key FROM pragma_index_xinfo('t1');
           59  +} {b 1 a 0 c 0}
    54     60   do_execsql_test without_rowid6-210 {
    55     61     EXPLAIN QUERY PLAN
    56     62     SELECT a FROM t1 WHERE b>3 ORDER BY b;
    57     63   } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
    58     64   do_execsql_test without_rowid6-220 {
    59     65     PRAGMA index_list(t1);
    60     66   } {/sqlite_autoindex_t1_2 1 pk/}
................................................................................
   101    107     CREATE TABLE t1(a,b,c,
   102    108       UNIQUE(b,c),
   103    109       PRIMARY KEY(b,c)
   104    110     ) WITHOUT ROWID;
   105    111     INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
   106    112     SELECT a FROM t1 WHERE b>3 ORDER BY b;
   107    113   } {4 1}
          114  +do_execsql_test without_rowid6-501 {
          115  +  SELECT name, key FROM pragma_index_xinfo('t1');
          116  +} {b 1 c 1 a 0}
   108    117   do_execsql_test without_rowid6-510 {
   109    118     EXPLAIN QUERY PLAN
   110    119     SELECT a FROM t1 WHERE b>3 ORDER BY b;
   111    120   } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
   112    121   do_execsql_test without_rowid6-520 {
   113    122     PRAGMA index_list(t1);
   114    123   } {/sqlite_autoindex_t1_1 1 pk/}

Added test/without_rowid7.test.

            1  +# 2019 July 17
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +# This file implements regression tests for SQLite library.  
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix without_rowid7
           17  +
           18  +do_execsql_test 1.0 {
           19  +  CREATE TABLE t1(a, b COLLATE nocase, PRIMARY KEY(a, a, b)) WITHOUT ROWID;
           20  +}
           21  +
           22  +do_catchsql_test 1.1 {
           23  +  INSERT INTO t1 VALUES(1, 'one'), (1, 'ONE');
           24  +} {1 {UNIQUE constraint failed: t1.a, t1.b}}
           25  +
           26  +
           27  +do_execsql_test 2.0 {
           28  +  CREATE TABLE t2(a, b, PRIMARY KEY(a COLLATE nocase, a)) WITHOUT ROWID;
           29  +}
           30  +
           31  +do_execsql_test 2.1 {
           32  +  INSERT INTO t2 VALUES(1, 'one');
           33  +  SELECT b FROM t2;
           34  +} {one}
           35  +
           36  +do_execsql_test 2.2a {
           37  +  PRAGMA index_info(t2);
           38  +} {0 0 a 1 0 a}
           39  +do_execsql_test 2.2b {
           40  +  SELECT *, '|' FROM pragma_index_info('t2');
           41  +} {0 0 a | 1 0 a |}
           42  +do_execsql_test 2.3a {
           43  +  PRAGMA index_xinfo(t2);
           44  +} {0 0 a 0 nocase 1 1 0 a 0 BINARY 1 2 1 b 0 BINARY 0}
           45  +do_execsql_test 2.3b {
           46  +  SELECT *, '|' FROM pragma_index_xinfo('t2');
           47  +} {0 0 a 0 nocase 1 | 1 0 a 0 BINARY 1 | 2 1 b 0 BINARY 0 |}
           48  +
           49  +do_execsql_test 2.4 {
           50  +  CREATE TABLE t3(a, b, PRIMARY KEY(a COLLATE nocase, a));
           51  +  PRAGMA index_info(t3);
           52  +} {}
           53  +
           54  +
           55  +
           56  +finish_test