/ Check-in [faaaae49]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:If a query like "SELECT min(a), b FROM t1" visits no rows where "a" is not null, extract a value for "b" from one of the rows where "a" is null. Fix for ticket [41866dc37].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: faaaae4940b5f4f70e4988ad5b45582410b381ace0031e6abcde0b9217c06796
User & Date: dan 2019-08-02 19:40:01
References
2019-08-02
19:40 Closed ticket [41866dc3]: MIN() malfunctions for UNIQUE column plus 6 other changes artifact: 7206ad61 user: dan
Context
2019-08-03
01:40
Add the SQLITE_TESTCTRL_PRNG_SEED which can control the PRNG seed either directly or through the schema cookie of a supplied database connection. check-in: 2660e929 user: drh tags: trunk
2019-08-02
20:45
Add the SQLITE_TESTCTRL_PRNG_SEED test control. check-in: 3ac57231 user: drh tags: prng-seed-test-control
19:40
If a query like "SELECT min(a), b FROM t1" visits no rows where "a" is not null, extract a value for "b" from one of the rows where "a" is null. Fix for ticket [41866dc37]. check-in: faaaae49 user: dan tags: trunk
18:43
If a query like "SELECT min(a), b FROM t1" visits no rows where "a" is not null, extract a value for "b" from one of the rows where "a" is null. Possible fix for ticket [41866dc37]. Closed-Leaf check-in: a7277ed0 user: dan tags: tkt-41866dc37
2019-08-01
22:48
The sqlite3_set_authorizer() interface should only expire prepared statements when it is setting a new authorizer, not when clearing the authorizer. And statements that are running when sqlite3_set_authorizer() is invoked should be allowed to continue running to completion. check-in: 961e2f08 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

  1690   1690         VdbeCoverage(v);
  1691   1691         VdbeCoverageIf(v, op==OP_Rewind);  testcase( op==OP_Rewind );
  1692   1692         VdbeCoverageIf(v, op==OP_Last);    testcase( op==OP_Last );
  1693   1693         VdbeCoverageIf(v, op==OP_SeekGT);  testcase( op==OP_SeekGT );
  1694   1694         VdbeCoverageIf(v, op==OP_SeekGE);  testcase( op==OP_SeekGE );
  1695   1695         VdbeCoverageIf(v, op==OP_SeekLE);  testcase( op==OP_SeekLE );
  1696   1696         VdbeCoverageIf(v, op==OP_SeekLT);  testcase( op==OP_SeekLT );
         1697  +
         1698  +      if( bSeekPastNull && (pLoop->wsFlags & WHERE_TOP_LIMIT)==0 ){
         1699  +        /* If bSeekPastNull is set only to skip past the NULL values for
         1700  +        ** a query like "SELECT min(a), b FROM t1", then add code so that
         1701  +        ** if there are no rows with (a IS NOT NULL), then do the seek 
         1702  +        ** without jumping past NULLs instead. This allows the code in 
         1703  +        ** select.c to pick a value for "b" in the above query.  */
         1704  +        assert( startEq==0 && (op==OP_SeekGT || op==OP_SeekLT) );
         1705  +        assert( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)!=0 && pWInfo->nOBSat>0 );
         1706  +        sqlite3VdbeChangeP2(v, -1, sqlite3VdbeCurrentAddr(v)+1);
         1707  +        sqlite3VdbeAddOp2(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+2);
         1708  +
         1709  +        op = aStartOp[(start_constraints<<2) + (1<<1) + bRev];
         1710  +        assert( op!=0 );
         1711  +        sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
         1712  +        VdbeCoverage(v);
         1713  +        VdbeCoverageIf(v, op==OP_SeekGE);  testcase( op==OP_SeekGE );
         1714  +        VdbeCoverageIf(v, op==OP_SeekLE);  testcase( op==OP_SeekLE );
         1715  +      }
  1697   1716       }
  1698   1717   
  1699   1718       /* Load the value for the inequality constraint at the end of the
  1700   1719       ** range (if any).
  1701   1720       */
  1702   1721       nConstraint = nEq;
  1703   1722       if( pRangeEnd ){

Changes to test/minmax4.test.

    15     15   #
    16     16   # Demonstration that the value returned for p is on the same row as 
    17     17   # the maximum q.
    18     18   #
    19     19   
    20     20   set testdir [file dirname $argv0]
    21     21   source $testdir/tester.tcl
           22  +set testprefix minmax4
    22     23   
    23     24   ifcapable !compound {
    24     25     finish_test
    25     26     return
    26     27   }
    27     28   
    28     29   do_test minmax4-1.1 {
................................................................................
   144    145   } {1 2 1 4 4 2 3 3 5 5}
   145    146   do_test minmax4-2.7 {
   146    147     db eval {
   147    148       SELECT a, min(b), b, min(c), c FROM t2 GROUP BY a ORDER BY a;
   148    149     }
   149    150   } {1 1 {} 2 2 2 3 3 5 5}
   150    151   
   151         -
          152  +#-------------------------------------------------------------------------
          153  +foreach {tn sql} {
          154  +  1 { CREATE INDEX i1 ON t1(a) }
          155  +  2 { CREATE INDEX i1 ON t1(a DESC) }
          156  +  3 { }
          157  +} {
          158  +  reset_db
          159  +  do_execsql_test 3.$tn.0 {
          160  +    CREATE TABLE t1(a, b);
          161  +    INSERT INTO t1 VALUES(NULL, 1);
          162  +  }
          163  +  execsql $sql
          164  +  do_execsql_test 3.$tn.1 {
          165  +    SELECT min(a), b FROM t1;
          166  +  } {{} 1}
          167  +  do_execsql_test 3.$tn.2 {
          168  +    SELECT min(a), b FROM t1 WHERE a<50;
          169  +  } {{} {}}
          170  +  do_execsql_test 3.$tn.3 {
          171  +    INSERT INTO t1 VALUES(2, 2);
          172  +  }
          173  +  do_execsql_test 3.$tn.4 {
          174  +    SELECT min(a), b FROM t1;
          175  +  } {2 2}
          176  +  do_execsql_test 3.$tn.5 {
          177  +    SELECT min(a), b FROM t1 WHERE a<50;
          178  +  } {2 2}
          179  +}
   152    180   
   153    181   finish_test