/ Check-in [d465c3eef4]
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 [71e183cab6]: MIN() malfunctions for a query with ISNULL condition plus 4 other changes artifact: 3dfda9a4da user: dan
Context
2019-08-03
19:06
Strengthen an assert() in the WHERE clause code generator for the min/max optimization. check-in: 1bd4b97d64 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: d465c3eef4 user: dan tags: trunk
16:17
In the ".wheretrace 0x100" debugging mode, show the structure of the main parameters to sqlite3WhereBegin() calls. check-in: fd598e475d user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

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

Changes to src/whereInt.h.

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

Changes to src/wherecode.c.

  1558   1558       ** was passed to this function to implement a "SELECT min(x) ..." 
  1559   1559       ** query, then the caller will only allow the loop to run for
  1560   1560       ** a single iteration. This means that the first row returned
  1561   1561       ** should not have a NULL value stored in 'x'. If column 'x' is
  1562   1562       ** the first one after the nEq equality constraints in the index,
  1563   1563       ** this requires some special handling.
  1564   1564       */
  1565         -    assert( pWInfo->pOrderBy==0
  1566         -         || pWInfo->pOrderBy->nExpr==1
         1565  +    assert( (pWInfo->pOrderBy!=0 && pWInfo->pOrderBy->nExpr==1)
  1567   1566            || (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 );
  1568         -    if( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)!=0
  1569         -     && pWInfo->nOBSat>0
  1570         -     && (pIdx->nKeyCol>nEq)
  1571         -    ){
         1567  +    if( pLoop->wsFlags & WHERE_MIN_ORDERED ){
         1568  +      assert( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN) );
         1569  +      assert( pWInfo->nOBSat );
         1570  +      assert( pIdx->nColumn>nEq );
  1572   1571         assert( pLoop->nSkip==0 );
  1573   1572         bSeekPastNull = 1;
  1574   1573         nExtraReg = 1;
  1575   1574       }
  1576   1575   
  1577   1576       /* Find any inequality constraint terms for the start and end 
  1578   1577       ** of the range. 

Changes to test/minmax4.test.

   173    173     do_execsql_test 3.$tn.4 {
   174    174       SELECT min(a), b FROM t1;
   175    175     } {2 2}
   176    176     do_execsql_test 3.$tn.5 {
   177    177       SELECT min(a), b FROM t1 WHERE a<50;
   178    178     } {2 2}
   179    179   }
          180  +
          181  +#-------------------------------------------------------------------------
          182  +reset_db
          183  +do_execsql_test 4.0 {
          184  +  CREATE TABLE t0 (c0, c1);
          185  +  CREATE INDEX i0 ON t0(c1, c1 + 1 DESC);
          186  +  INSERT INTO t0(c0) VALUES (1);
          187  +}
          188  +do_execsql_test 4.1 {
          189  +  SELECT MIN(t0.c1), t0.c0 FROM t0 WHERE t0.c1 ISNULL; 
          190  +} {{} 1}
          191  +
          192  +#-------------------------------------------------------------------------
          193  +reset_db
          194  +do_execsql_test 5.0 {
          195  +  CREATE TABLE t1 (a, b);
          196  +  INSERT INTO t1 VALUES(123, NULL);
          197  +  CREATE INDEX i1 ON t1(a, b DESC);
          198  +}
          199  +do_execsql_test 5.1 {
          200  +  SELECT MIN(a) FROM t1 WHERE a=123;
          201  +} {123}
   180    202   
   181    203   finish_test
          204  +