/ Check-in [d465c3ee]
Login

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

Overview
Comment:Fix a problem with queries of the form "SELECT min(<expr>) ... WHERE <expr>=?" where there is an index on <expr>. Fix for [71e183ca].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: d465c3eef458c851d97eea6e4117247d9c69386b276168080dbff7bb64070c93
User & Date: dan 2019-08-03 16:37:40
References
2019-08-03
16:51 Closed ticket [71e183ca]: MIN() malfunctions for a query with ISNULL condition plus 4 other changes artifact: 3dfda9a4 user: dan
Context
2019-08-03
19:06
Strengthen an assert() in the WHERE clause code generator for the min/max optimization. check-in: 1bd4b97d user: drh tags: trunk
16:37
Fix a problem with queries of the form "SELECT min(<expr>) ... WHERE <expr>=?" where there is an index on <expr>. Fix for [71e183ca]. check-in: d465c3ee user: dan tags: trunk
16:17
In the ".wheretrace 0x100" debugging mode, show the structure of the main parameters to sqlite3WhereBegin() calls. check-in: fd598e47 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

3858
3859
3860
3861
3862
3863
3864



3865
3866
3867
3868
3869
3870
3871
        }
        if( isMatch ){
          if( iColumn==XN_ROWID ){
            testcase( distinctColumns==0 );
            distinctColumns = 1;
          }
          obSat |= MASKBIT(i);



        }else{
          /* No match found */
          if( j==0 || j<nKeyCol ){
            testcase( isOrderDistinct!=0 );
            isOrderDistinct = 0;
          }
          break;







>
>
>







3858
3859
3860
3861
3862
3863
3864
3865
3866
3867
3868
3869
3870
3871
3872
3873
3874
        }
        if( isMatch ){
          if( iColumn==XN_ROWID ){
            testcase( distinctColumns==0 );
            distinctColumns = 1;
          }
          obSat |= MASKBIT(i);
          if( (wctrlFlags & WHERE_ORDERBY_MIN) && j==pLoop->u.btree.nEq ){
            pLoop->wsFlags |= WHERE_MIN_ORDERED;
          }
        }else{
          /* No match found */
          if( j==0 || j<nKeyCol ){
            testcase( isOrderDistinct!=0 );
            isOrderDistinct = 0;
          }
          break;

Changes to src/whereInt.h.

582
583
584
585
586
587
588

589
590
#define WHERE_ONEROW       0x00001000  /* Selects no more than one row */
#define WHERE_MULTI_OR     0x00002000  /* OR using multiple indices */
#define WHERE_AUTO_INDEX   0x00004000  /* Uses an ephemeral index */
#define WHERE_SKIPSCAN     0x00008000  /* Uses the skip-scan algorithm */
#define WHERE_UNQ_WANTED   0x00010000  /* WHERE_ONEROW would have been helpful*/
#define WHERE_PARTIALIDX   0x00020000  /* The automatic index is partial */
#define WHERE_IN_EARLYOUT  0x00040000  /* Perhaps quit IN loops early */


#endif /* !defined(SQLITE_WHEREINT_H) */







>


582
583
584
585
586
587
588
589
590
591
#define WHERE_ONEROW       0x00001000  /* Selects no more than one row */
#define WHERE_MULTI_OR     0x00002000  /* OR using multiple indices */
#define WHERE_AUTO_INDEX   0x00004000  /* Uses an ephemeral index */
#define WHERE_SKIPSCAN     0x00008000  /* Uses the skip-scan algorithm */
#define WHERE_UNQ_WANTED   0x00010000  /* WHERE_ONEROW would have been helpful*/
#define WHERE_PARTIALIDX   0x00020000  /* The automatic index is partial */
#define WHERE_IN_EARLYOUT  0x00040000  /* Perhaps quit IN loops early */
#define WHERE_MIN_ORDERED  0x00080000  /* Column nEq of index is min() expr */

#endif /* !defined(SQLITE_WHEREINT_H) */

Changes to src/wherecode.c.

1558
1559
1560
1561
1562
1563
1564
1565
1566
1567

1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
    ** was passed to this function to implement a "SELECT min(x) ..." 
    ** query, then the caller will only allow the loop to run for
    ** a single iteration. This means that the first row returned
    ** should not have a NULL value stored in 'x'. If column 'x' is
    ** the first one after the nEq equality constraints in the index,
    ** this requires some special handling.
    */
    assert( pWInfo->pOrderBy==0
         || pWInfo->pOrderBy->nExpr==1
         || (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 );

    if( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)!=0
     && pWInfo->nOBSat>0
     && (pIdx->nKeyCol>nEq)
    ){
      assert( pLoop->nSkip==0 );
      bSeekPastNull = 1;
      nExtraReg = 1;
    }

    /* Find any inequality constraint terms for the start and end 
    ** of the range. 







<
|

>
|
|
|
<







1558
1559
1560
1561
1562
1563
1564

1565
1566
1567
1568
1569
1570

1571
1572
1573
1574
1575
1576
1577
    ** was passed to this function to implement a "SELECT min(x) ..." 
    ** query, then the caller will only allow the loop to run for
    ** a single iteration. This means that the first row returned
    ** should not have a NULL value stored in 'x'. If column 'x' is
    ** the first one after the nEq equality constraints in the index,
    ** this requires some special handling.
    */

    assert( (pWInfo->pOrderBy!=0 && pWInfo->pOrderBy->nExpr==1)
         || (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 );
    if( pLoop->wsFlags & WHERE_MIN_ORDERED ){
      assert( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN) );
      assert( pWInfo->nOBSat );
      assert( pIdx->nColumn>nEq );

      assert( pLoop->nSkip==0 );
      bSeekPastNull = 1;
      nExtraReg = 1;
    }

    /* Find any inequality constraint terms for the start and end 
    ** of the range. 

Changes to test/minmax4.test.

173
174
175
176
177
178
179
180






















181

  do_execsql_test 3.$tn.4 {
    SELECT min(a), b FROM t1;
  } {2 2}
  do_execsql_test 3.$tn.5 {
    SELECT min(a), b FROM t1 WHERE a<50;
  } {2 2}
}























finish_test









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

>
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
  do_execsql_test 3.$tn.4 {
    SELECT min(a), b FROM t1;
  } {2 2}
  do_execsql_test 3.$tn.5 {
    SELECT min(a), b FROM t1 WHERE a<50;
  } {2 2}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.0 {
  CREATE TABLE t0 (c0, c1);
  CREATE INDEX i0 ON t0(c1, c1 + 1 DESC);
  INSERT INTO t0(c0) VALUES (1);
}
do_execsql_test 4.1 {
  SELECT MIN(t0.c1), t0.c0 FROM t0 WHERE t0.c1 ISNULL; 
} {{} 1}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 5.0 {
  CREATE TABLE t1 (a, b);
  INSERT INTO t1 VALUES(123, NULL);
  CREATE INDEX i1 ON t1(a, b DESC);
}
do_execsql_test 5.1 {
  SELECT MIN(a) FROM t1 WHERE a=123;
} {123}

finish_test