/ Check-in [1b1dd4d4]
Login

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

Overview
Comment:Do not de-duplicate columns index columns associated with a WITHOUT ROWID table if the columns have different collating sequences. This is the fix for ticket [3182d3879020ef3b2]. There is one test case added, but most of the tests are done in TH3.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 1b1dd4d48cd79a585e1fa7ee79128e9f2a9ee9846339dc56bbd67b75112dcad5
User & Date: drh 2019-04-29 13:48:45
References
2019-07-17
01:27 New ticket [fd3aec0c] Valgrind warning if PK has more columns than the table.. artifact: 6e4769b4 user: drh
Context
2019-04-29
16:44
Changes to oserror.test so that it works even on systems that allow an unusually large number of file descriptors. check-in: a27b0b88 user: drh tags: trunk
13:48
Do not de-duplicate columns index columns associated with a WITHOUT ROWID table if the columns have different collating sequences. This is the fix for ticket [3182d3879020ef3b2]. There is one test case added, but most of the tests are done in TH3. check-in: 1b1dd4d4 user: drh tags: trunk
13:30
Improved header comment and precondition checking for the new isDupColumn() function. Closed-Leaf check-in: 740d5ff6 user: drh tags: tkt-3182d38790
11:41
Fix a buffer overwrite in shell.c.in (part of the new .recover code). check-in: 92facbc7 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1722   1722       i16 x = pIdx->aiColumn[i];
  1723   1723       assert( x<pIdx->pTable->nCol );
  1724   1724       wIndex += x<0 ? 1 : aCol[pIdx->aiColumn[i]].szEst;
  1725   1725     }
  1726   1726     pIdx->szIdxRow = sqlite3LogEst(wIndex*4);
  1727   1727   }
  1728   1728   
  1729         -/* Return true if value x is found any of the first nCol entries of aiCol[]
         1729  +/* Return true if column number x is any of the first nCol entries of aiCol[].
         1730  +** This is used to determine if the column number x appears in any of the
         1731  +** first nCol entries of an index.
  1730   1732   */
  1731   1733   static int hasColumn(const i16 *aiCol, int nCol, int x){
  1732         -  while( nCol-- > 0 ) if( x==*(aiCol++) ) return 1;
         1734  +  while( nCol-- > 0 ){
         1735  +    assert( aiCol[0]>=0 );
         1736  +    if( x==*(aiCol++) ){
         1737  +      return 1;
         1738  +    }
         1739  +  }
         1740  +  return 0;
         1741  +}
         1742  +
         1743  +/*
         1744  +** Return true if any of the first nKey entries of index pIdx exactly
         1745  +** match the iCol-th entry of pPk.  pPk is always a WITHOUT ROWID
         1746  +** PRIMARY KEY index.  pIdx is an index on the same table.  pIdx may
         1747  +** or may not be the same index as pPk.
         1748  +**
         1749  +** The first nKey entries of pIdx are guaranteed to be ordinary columns,
         1750  +** not a rowid or expression.
         1751  +**
         1752  +** This routine differs from hasColumn() in that both the column and the
         1753  +** collating sequence must match for this routine, but for hasColumn() only
         1754  +** the column name must match.
         1755  +*/
         1756  +static int isDupColumn(Index *pIdx, int nKey, Index *pPk, int iCol){
         1757  +  int i, j;
         1758  +  assert( nKey<=pIdx->nColumn );
         1759  +  assert( iCol<MAX(pPk->nColumn,pPk->nKeyCol) );
         1760  +  assert( pPk->idxType==SQLITE_IDXTYPE_PRIMARYKEY );
         1761  +  assert( pPk->pTable->tabFlags & TF_WithoutRowid );
         1762  +  assert( pPk->pTable==pIdx->pTable );
         1763  +  testcase( pPk==pIdx );
         1764  +  j = pPk->aiColumn[iCol];
         1765  +  assert( j!=XN_ROWID && j!=XN_EXPR );
         1766  +  for(i=0; i<nKey; i++){
         1767  +    assert( pIdx->aiColumn[i]>=0 || j>=0 );
         1768  +    if( pIdx->aiColumn[i]==j 
         1769  +     && sqlite3StrICmp(pIdx->azColl[i], pPk->azColl[iCol])==0
         1770  +    ){
         1771  +      return 1;
         1772  +    }
         1773  +  }
  1733   1774     return 0;
  1734   1775   }
  1735   1776   
  1736   1777   /* Recompute the colNotIdxed field of the Index.
  1737   1778   **
  1738   1779   ** colNotIdxed is a bitmask that has a 0 bit representing each indexed
  1739   1780   ** columns that are within the first 63 columns of the table.  The
................................................................................
  1831   1872   
  1832   1873       /*
  1833   1874       ** Remove all redundant columns from the PRIMARY KEY.  For example, change
  1834   1875       ** "PRIMARY KEY(a,b,a,b,c,b,c,d)" into just "PRIMARY KEY(a,b,c,d)".  Later
  1835   1876       ** code assumes the PRIMARY KEY contains no repeated columns.
  1836   1877       */
  1837   1878       for(i=j=1; i<pPk->nKeyCol; i++){
  1838         -      if( hasColumn(pPk->aiColumn, j, pPk->aiColumn[i]) ){
         1879  +      if( isDupColumn(pPk, j, pPk, i) ){
  1839   1880           pPk->nColumn--;
  1840   1881         }else{
         1882  +        testcase( hasColumn(pPk->aiColumn, j, pPk->aiColumn[i]) );
  1841   1883           pPk->aiColumn[j++] = pPk->aiColumn[i];
  1842   1884         }
  1843   1885       }
  1844   1886       pPk->nKeyCol = j;
  1845   1887     }
  1846   1888     assert( pPk!=0 );
  1847   1889     pPk->isCovering = 1;
................................................................................
  1863   1905     /* Update the in-memory representation of all UNIQUE indices by converting
  1864   1906     ** the final rowid column into one or more columns of the PRIMARY KEY.
  1865   1907     */
  1866   1908     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  1867   1909       int n;
  1868   1910       if( IsPrimaryKeyIndex(pIdx) ) continue;
  1869   1911       for(i=n=0; i<nPk; i++){
  1870         -      if( !hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) ) n++;
         1912  +      if( !isDupColumn(pIdx, pIdx->nKeyCol, pPk, i) ){
         1913  +        testcase( hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) );
         1914  +        n++;
         1915  +      }
  1871   1916       }
  1872   1917       if( n==0 ){
  1873   1918         /* This index is a superset of the primary key */
  1874   1919         pIdx->nColumn = pIdx->nKeyCol;
  1875   1920         continue;
  1876   1921       }
  1877   1922       if( resizeIndexObject(db, pIdx, pIdx->nKeyCol+n) ) return;
  1878   1923       for(i=0, j=pIdx->nKeyCol; i<nPk; i++){
  1879         -      if( !hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) ){
         1924  +      if( !isDupColumn(pIdx, pIdx->nKeyCol, pPk, i) ){
         1925  +        testcase( hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) );
  1880   1926           pIdx->aiColumn[j] = pPk->aiColumn[i];
  1881   1927           pIdx->azColl[j] = pPk->azColl[i];
  1882   1928           j++;
  1883   1929         }
  1884   1930       }
  1885   1931       assert( pIdx->nColumn>=pIdx->nKeyCol+n );
  1886   1932       assert( pIdx->nColumn>=j );
................................................................................
  3388   3434     ** tables (when pPk!=0) this will be the declared PRIMARY KEY.  For
  3389   3435     ** normal tables (when pPk==0) this will be the rowid.
  3390   3436     */
  3391   3437     if( pPk ){
  3392   3438       for(j=0; j<pPk->nKeyCol; j++){
  3393   3439         int x = pPk->aiColumn[j];
  3394   3440         assert( x>=0 );
  3395         -      if( hasColumn(pIndex->aiColumn, pIndex->nKeyCol, x) ){
         3441  +      if( isDupColumn(pIndex, pIndex->nKeyCol, pPk, j) ){
  3396   3442           pIndex->nColumn--; 
  3397   3443         }else{
         3444  +        testcase( hasColumn(pIndex->aiColumn,pIndex->nKeyCol,x) );
  3398   3445           pIndex->aiColumn[i] = x;
  3399   3446           pIndex->azColl[i] = pPk->azColl[j];
  3400   3447           pIndex->aSortOrder[i] = pPk->aSortOrder[j];
  3401   3448           i++;
  3402   3449         }
  3403   3450       }
  3404   3451       assert( i==pIndex->nColumn );

Changes to test/without_rowid1.test.

   387    387     CREATE TRIGGER t1_tr BEFORE UPDATE ON t1 BEGIN
   388    388       DELETE FROM t1 WHERE a = new.a;
   389    389     END;
   390    390     UPDATE t1 SET c = c+1 WHERE a = 'a';
   391    391     SELECT * FROM t1;
   392    392   } {b a 3  b b 4}
   393    393   
          394  +# 2019-04-29 ticket https://www.sqlite.org/src/info/3182d3879020ef3
          395  +do_execsql_test 11.1 {
          396  +  CREATE TABLE t11(a TEXT PRIMARY KEY, b INT) WITHOUT ROWID;
          397  +  CREATE INDEX t11a ON t11(a COLLATE NOCASE);
          398  +  INSERT INTO t11(a,b) VALUES ('A',1),('a',2);
          399  +  PRAGMA integrity_check;
          400  +  SELECT a FROM t11 ORDER BY a COLLATE binary;
          401  +} {ok A a}
   394    402     
   395    403   finish_test