/ Check-in [6ac0f82245]
Login

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

Overview
Comment:When using an index for both DISTINCT and ORDER BY, do not confuse the number of columns required for distinctness with the number required to get the correct sort order. Fix for [6749cb3c].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 6ac0f822450b26c7d67c33cdb2a90189cd3cf65a052af8497b795c3f71a23813
User & Date: dan 2019-07-29 15:32:01
Context
2019-07-29
16:53
Fix a crash in fts3 caused by corrupt database records. check-in: 11f7f94f1c user: dan tags: trunk
15:32
When using an index for both DISTINCT and ORDER BY, do not confuse the number of columns required for distinctness with the number required to get the correct sort order. Fix for [6749cb3c]. check-in: 6ac0f82245 user: dan tags: trunk
14:42
When using an index for both DISTINCT and ORDER BY, do not confuse the number of columns required for distinctness with the number required to get the correct sort order. Fix for [6749cb3c]. Closed-Leaf check-in: b47169319a user: dan tags: tkt-6749cb3c
06:06
Fix the OP_Affinity operator so that when applying REAL affinity, it only sets MEM_IntReal if the integer value will fit in 6 bytes or less. Fix for ticket [ba2f4585cf495231] check-in: 2b221bb15f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3691   3691         if( wctrlFlags & WHERE_ORDERBY_LIMIT ) continue;
  3692   3692       }else{
  3693   3693         pLoop = pLast;
  3694   3694       }
  3695   3695       if( pLoop->wsFlags & WHERE_VIRTUALTABLE ){
  3696   3696         if( pLoop->u.vtab.isOrdered ) obSat = obDone;
  3697   3697         break;
  3698         -    }else{
  3699         -      pLoop->u.btree.nIdxCol = 0;
         3698  +    }else if( wctrlFlags & WHERE_DISTINCTBY ){
         3699  +      pLoop->u.btree.nDistinctCol = 0;
  3700   3700       }
  3701   3701       iCur = pWInfo->pTabList->a[pLoop->iTab].iCursor;
  3702   3702   
  3703   3703       /* Mark off any ORDER BY term X that is a column in the table of
  3704   3704       ** the current loop for which there is term in the WHERE
  3705   3705       ** clause of the form X IS NULL or X=? that reference only outer
  3706   3706       ** loops.
................................................................................
  3834   3834                 continue;
  3835   3835               }
  3836   3836             }
  3837   3837             if( iColumn!=XN_ROWID ){
  3838   3838               pColl = sqlite3ExprNNCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
  3839   3839               if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
  3840   3840             }
  3841         -          pLoop->u.btree.nIdxCol = j+1;
         3841  +          if( wctrlFlags & WHERE_DISTINCTBY ){
         3842  +            pLoop->u.btree.nDistinctCol = j+1;
         3843  +          }
  3842   3844             isMatch = 1;
  3843   3845             break;
  3844   3846           }
  3845   3847           if( isMatch && (wctrlFlags & WHERE_GROUPBY)==0 ){
  3846   3848             /* Make sure the sort order is compatible in an ORDER BY clause.
  3847   3849             ** Sort order is irrelevant for a GROUP BY clause. */
  3848   3850             if( revSet ){
................................................................................
  5155   5157         int addrSeek = 0;
  5156   5158         Index *pIdx;
  5157   5159         int n;
  5158   5160         if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
  5159   5161          && i==pWInfo->nLevel-1  /* Ticket [ef9318757b152e3] 2017-10-21 */
  5160   5162          && (pLoop->wsFlags & WHERE_INDEXED)!=0
  5161   5163          && (pIdx = pLoop->u.btree.pIndex)->hasStat1
  5162         -       && (n = pLoop->u.btree.nIdxCol)>0
         5164  +       && (n = pLoop->u.btree.nDistinctCol)>0
  5163   5165          && pIdx->aiRowLogEst[n]>=36
  5164   5166         ){
  5165   5167           int r1 = pParse->nMem+1;
  5166   5168           int j, op;
  5167   5169           for(j=0; j<n; j++){
  5168   5170             sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
  5169   5171           }

Changes to src/whereInt.h.

   124    124     LogEst rRun;          /* Cost of running each loop */
   125    125     LogEst nOut;          /* Estimated number of output rows */
   126    126     union {
   127    127       struct {               /* Information for internal btree tables */
   128    128         u16 nEq;               /* Number of equality constraints */
   129    129         u16 nBtm;              /* Size of BTM vector */
   130    130         u16 nTop;              /* Size of TOP vector */
   131         -      u16 nIdxCol;           /* Index column used for ORDER BY */
          131  +      u16 nDistinctCol;      /* Index columns used to sort for DISTINCT */
   132    132         Index *pIndex;         /* Index used, or NULL */
   133    133       } btree;
   134    134       struct {               /* Information for virtual tables */
   135    135         int idxNum;            /* Index number */
   136    136         u8 needFree;           /* True if sqlite3_free(idxStr) is needed */
   137    137         i8 isOrdered;          /* True if satisfies ORDER BY */
   138    138         u16 omitMask;          /* Terms that may be omitted */

Changes to test/distinct2.test.

   224    224     INSERT INTO t1(a, b) VALUES(1, 'yes');
   225    225     CREATE TABLE t2(x PRIMARY KEY);
   226    226     INSERT INTO t2 VALUES('yes');
   227    227     SELECT DISTINCT a FROM t1, t2 WHERE x=b;
   228    228     ANALYZE;
   229    229     SELECT DISTINCT a FROM t1, t2 WHERE x=b;
   230    230   } {1 1}
          231  +
          232  +#-------------------------------------------------------------------------
          233  +reset_db
          234  +
          235  +do_execsql_test 2000 {
          236  +  CREATE TABLE t0 (c0, c1, c2, PRIMARY KEY (c0, c1));
          237  +  CREATE TABLE t1 (c2);
          238  +  INSERT INTO t0(c2) VALUES (0),(1),(3),(4),(5),(6),(7),(8),(9),(10),(11);
          239  +  INSERT INTO t0(c1) VALUES ('a');
          240  +  INSERT INTO t1(c2) VALUES (0);
          241  +}
          242  +do_execsql_test 2010 {
          243  +  SELECT DISTINCT t0.c0, t1._rowid_, t0.c1 FROM t1 CROSS JOIN t0 ORDER BY t0.c0;
          244  +} {{} 1 {} {} 1 a}
          245  +do_execsql_test 1.2 {
          246  +  ANALYZE;
          247  +}
          248  +do_execsql_test 2020 {
          249  +  SELECT DISTINCT t0.c0, t1._rowid_, t0.c1 FROM t1 CROSS JOIN t0 ORDER BY t0.c0;
          250  +} {{} 1 {} {} 1 a}
          251  +
          252  +
          253  +do_execsql_test 2030 {
          254  +  CREATE TABLE t2(a, b, c);
          255  +  CREATE INDEX t2ab ON t2(a, b);
          256  +  
          257  +  WITH c(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<64)
          258  +    INSERT INTO t2 SELECT 'one', i%2, 'one' FROM c;
          259  +
          260  +  WITH c(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<64)
          261  +    INSERT INTO t2 SELECT 'two', i%2, 'two' FROM c;
          262  +
          263  +  CREATE TABLE t3(x INTEGER PRIMARY KEY);
          264  +  INSERT INTO t3 VALUES(1);
          265  +
          266  +  ANALYZE;
          267  +}
          268  +do_execsql_test 2040 {
          269  +  SELECT DISTINCT a, b, x FROM t3 CROSS JOIN t2 ORDER BY a; 
          270  +} {
          271  +  one 0 1
          272  +  one 1 1
          273  +  two 0 1
          274  +  two 1 1
          275  +}
          276  +
   231    277   
   232    278   
   233    279   finish_test