/ Check-in [b34fa5bf]
Login

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

Overview
Comment:Take collating sequence into account when removing redundant columns from indexes on WITHOUT ROWID tables. This is the first proof-of-concept fix for ticket [3182d3879020ef3]. More testing needed.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | tkt-3182d38790
Files: files | file ages | folders
SHA3-256: b34fa5bff40d3d364bd8c80e7de55c606ef3caac47b14b5265ebcb38857eb85e
User & Date: drh 2019-04-28 19:27:02
Context
2019-04-29
13:30
Improved header comment and precondition checking for the new isDupColumn() function. Closed-Leaf check-in: 740d5ff6 user: drh tags: tkt-3182d38790
2019-04-28
19:27
Take collating sequence into account when removing redundant columns from indexes on WITHOUT ROWID tables. This is the first proof-of-concept fix for ticket [3182d3879020ef3]. More testing needed. check-in: b34fa5bf user: drh tags: tkt-3182d38790
2019-04-27
20:30
Add the ".recover" command to the shell tool. For recovering as much data as possible from corrupt databases. check-in: 50fe4845 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

1722
1723
1724
1725
1726
1727
1728
1729


1730
1731
1732


































1733
1734
1735
1736
1737
1738
1739
....
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840

1841
1842
1843
1844
1845
1846
1847
....
1863
1864
1865
1866
1867
1868
1869

1870


1871
1872
1873
1874
1875
1876
1877
1878

1879
1880
1881
1882
1883
1884
1885
1886
....
3388
3389
3390
3391
3392
3393
3394
3395
3396
3397

3398
3399
3400
3401
3402
3403
3404
    i16 x = pIdx->aiColumn[i];
    assert( x<pIdx->pTable->nCol );
    wIndex += x<0 ? 1 : aCol[pIdx->aiColumn[i]].szEst;
  }
  pIdx->szIdxRow = sqlite3LogEst(wIndex*4);
}

/* Return true if value x is found any of the first nCol entries of aiCol[]


*/
static int hasColumn(const i16 *aiCol, int nCol, int x){
  while( nCol-- > 0 ) if( x==*(aiCol++) ) return 1;


































  return 0;
}

/* Recompute the colNotIdxed field of the Index.
**
** colNotIdxed is a bitmask that has a 0 bit representing each indexed
** columns that are within the first 63 columns of the table.  The
................................................................................

    /*
    ** Remove all redundant columns from the PRIMARY KEY.  For example, change
    ** "PRIMARY KEY(a,b,a,b,c,b,c,d)" into just "PRIMARY KEY(a,b,c,d)".  Later
    ** code assumes the PRIMARY KEY contains no repeated columns.
    */
    for(i=j=1; i<pPk->nKeyCol; i++){
      if( hasColumn(pPk->aiColumn, j, pPk->aiColumn[i]) ){
        pPk->nColumn--;
      }else{

        pPk->aiColumn[j++] = pPk->aiColumn[i];
      }
    }
    pPk->nKeyCol = j;
  }
  assert( pPk!=0 );
  pPk->isCovering = 1;
................................................................................
  /* Update the in-memory representation of all UNIQUE indices by converting
  ** the final rowid column into one or more columns of the PRIMARY KEY.
  */
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    int n;
    if( IsPrimaryKeyIndex(pIdx) ) continue;
    for(i=n=0; i<nPk; i++){

      if( !hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) ) n++;


    }
    if( n==0 ){
      /* This index is a superset of the primary key */
      pIdx->nColumn = pIdx->nKeyCol;
      continue;
    }
    if( resizeIndexObject(db, pIdx, pIdx->nKeyCol+n) ) return;
    for(i=0, j=pIdx->nKeyCol; i<nPk; i++){

      if( !hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) ){
        pIdx->aiColumn[j] = pPk->aiColumn[i];
        pIdx->azColl[j] = pPk->azColl[i];
        j++;
      }
    }
    assert( pIdx->nColumn>=pIdx->nKeyCol+n );
    assert( pIdx->nColumn>=j );
................................................................................
  ** tables (when pPk!=0) this will be the declared PRIMARY KEY.  For
  ** normal tables (when pPk==0) this will be the rowid.
  */
  if( pPk ){
    for(j=0; j<pPk->nKeyCol; j++){
      int x = pPk->aiColumn[j];
      assert( x>=0 );
      if( hasColumn(pIndex->aiColumn, pIndex->nKeyCol, x) ){
        pIndex->nColumn--; 
      }else{

        pIndex->aiColumn[i] = x;
        pIndex->azColl[i] = pPk->azColl[j];
        pIndex->aSortOrder[i] = pPk->aSortOrder[j];
        i++;
      }
    }
    assert( i==pIndex->nColumn );







|
>
>


|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|


>







 







>
|
>
>








>
|







 







|


>







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
....
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
....
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
1926
1927
....
3429
3430
3431
3432
3433
3434
3435
3436
3437
3438
3439
3440
3441
3442
3443
3444
3445
3446
    i16 x = pIdx->aiColumn[i];
    assert( x<pIdx->pTable->nCol );
    wIndex += x<0 ? 1 : aCol[pIdx->aiColumn[i]].szEst;
  }
  pIdx->szIdxRow = sqlite3LogEst(wIndex*4);
}

/* Return true if column number x is any of the first nCol entries of aiCol[].
** This is used to determine if the column number x appears in any of the
** first nCol entries of an index.
*/
static int hasColumn(const i16 *aiCol, int nCol, int x){
  while( nCol-- > 0 ){
    assert( aiCol[0]>=0 );
    if( x==*(aiCol++) ){
      return 1;
    }
  }
  return 0;
}

/*
** Return true if any of the first nKey entries of index pIdx1 exactly
** match the iCol-th entry of pIdx2.
**
** The first nKey entries of pIdx1 are guaranteed to be ordinary columns,
** not a rowid or expression.
**
** This routine differs from hasColumn() in that both the column and the
** collating sequence must match for this routine, but for hasColumn() only
** the column name must match.
*/
static int isDupColumn(Index *pIdx1, int nKey, Index *pIdx2, int iCol){
  int i, j;
  assert( nKey<=pIdx1->nColumn );
  assert( iCol<MAX(pIdx2->nColumn,pIdx2->nKeyCol) );
  j = pIdx2->aiColumn[iCol];
  testcase( j==XN_EXPR );
  assert( j!=XN_ROWID );
  for(i=0; i<nKey; i++){
    assert( pIdx1->aiColumn[i]>=0 || j>=0 );
    if( pIdx1->aiColumn[i]==j 
     && sqlite3StrICmp(pIdx1->azColl[i],pIdx2->azColl[iCol])==0
    ){
      return 1;
    }
  }
  return 0;
}

/* Recompute the colNotIdxed field of the Index.
**
** colNotIdxed is a bitmask that has a 0 bit representing each indexed
** columns that are within the first 63 columns of the table.  The
................................................................................

    /*
    ** Remove all redundant columns from the PRIMARY KEY.  For example, change
    ** "PRIMARY KEY(a,b,a,b,c,b,c,d)" into just "PRIMARY KEY(a,b,c,d)".  Later
    ** code assumes the PRIMARY KEY contains no repeated columns.
    */
    for(i=j=1; i<pPk->nKeyCol; i++){
      if( isDupColumn(pPk, j, pPk, i) ){
        pPk->nColumn--;
      }else{
        testcase( hasColumn(pPk->aiColumn, j, pPk->aiColumn[i]) );
        pPk->aiColumn[j++] = pPk->aiColumn[i];
      }
    }
    pPk->nKeyCol = j;
  }
  assert( pPk!=0 );
  pPk->isCovering = 1;
................................................................................
  /* Update the in-memory representation of all UNIQUE indices by converting
  ** the final rowid column into one or more columns of the PRIMARY KEY.
  */
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    int n;
    if( IsPrimaryKeyIndex(pIdx) ) continue;
    for(i=n=0; i<nPk; i++){
      if( !isDupColumn(pIdx, pIdx->nKeyCol, pPk, i) ){
        testcase( hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) );
        n++;
      }
    }
    if( n==0 ){
      /* This index is a superset of the primary key */
      pIdx->nColumn = pIdx->nKeyCol;
      continue;
    }
    if( resizeIndexObject(db, pIdx, pIdx->nKeyCol+n) ) return;
    for(i=0, j=pIdx->nKeyCol; i<nPk; i++){
      if( !isDupColumn(pIdx, pIdx->nKeyCol, pPk, i) ){
        testcase( hasColumn(pIdx->aiColumn, pIdx->nKeyCol, pPk->aiColumn[i]) );
        pIdx->aiColumn[j] = pPk->aiColumn[i];
        pIdx->azColl[j] = pPk->azColl[i];
        j++;
      }
    }
    assert( pIdx->nColumn>=pIdx->nKeyCol+n );
    assert( pIdx->nColumn>=j );
................................................................................
  ** tables (when pPk!=0) this will be the declared PRIMARY KEY.  For
  ** normal tables (when pPk==0) this will be the rowid.
  */
  if( pPk ){
    for(j=0; j<pPk->nKeyCol; j++){
      int x = pPk->aiColumn[j];
      assert( x>=0 );
      if( isDupColumn(pIndex, pIndex->nKeyCol, pPk, j) ){
        pIndex->nColumn--; 
      }else{
        testcase( hasColumn(pIndex->aiColumn,pIndex->nKeyCol,x) );
        pIndex->aiColumn[i] = x;
        pIndex->azColl[i] = pPk->azColl[j];
        pIndex->aSortOrder[i] = pPk->aSortOrder[j];
        i++;
      }
    }
    assert( i==pIndex->nColumn );