SQLite

Check-in [faaaae49]
Login

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 Unified Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

1690
1691
1692
1693
1694
1695
1696



















1697
1698
1699
1700
1701
1702
1703
      VdbeCoverage(v);
      VdbeCoverageIf(v, op==OP_Rewind);  testcase( op==OP_Rewind );
      VdbeCoverageIf(v, op==OP_Last);    testcase( op==OP_Last );
      VdbeCoverageIf(v, op==OP_SeekGT);  testcase( op==OP_SeekGT );
      VdbeCoverageIf(v, op==OP_SeekGE);  testcase( op==OP_SeekGE );
      VdbeCoverageIf(v, op==OP_SeekLE);  testcase( op==OP_SeekLE );
      VdbeCoverageIf(v, op==OP_SeekLT);  testcase( op==OP_SeekLT );



















    }

    /* Load the value for the inequality constraint at the end of the
    ** range (if any).
    */
    nConstraint = nEq;
    if( pRangeEnd ){







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







1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
      VdbeCoverage(v);
      VdbeCoverageIf(v, op==OP_Rewind);  testcase( op==OP_Rewind );
      VdbeCoverageIf(v, op==OP_Last);    testcase( op==OP_Last );
      VdbeCoverageIf(v, op==OP_SeekGT);  testcase( op==OP_SeekGT );
      VdbeCoverageIf(v, op==OP_SeekGE);  testcase( op==OP_SeekGE );
      VdbeCoverageIf(v, op==OP_SeekLE);  testcase( op==OP_SeekLE );
      VdbeCoverageIf(v, op==OP_SeekLT);  testcase( op==OP_SeekLT );

      if( bSeekPastNull && (pLoop->wsFlags & WHERE_TOP_LIMIT)==0 ){
        /* If bSeekPastNull is set only to skip past the NULL values for
        ** a query like "SELECT min(a), b FROM t1", then add code so that
        ** if there are no rows with (a IS NOT NULL), then do the seek 
        ** without jumping past NULLs instead. This allows the code in 
        ** select.c to pick a value for "b" in the above query.  */
        assert( startEq==0 && (op==OP_SeekGT || op==OP_SeekLT) );
        assert( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)!=0 && pWInfo->nOBSat>0 );
        sqlite3VdbeChangeP2(v, -1, sqlite3VdbeCurrentAddr(v)+1);
        sqlite3VdbeAddOp2(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+2);

        op = aStartOp[(start_constraints<<2) + (1<<1) + bRev];
        assert( op!=0 );
        sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
        VdbeCoverage(v);
        VdbeCoverageIf(v, op==OP_SeekGE);  testcase( op==OP_SeekGE );
        VdbeCoverageIf(v, op==OP_SeekLE);  testcase( op==OP_SeekLE );
      }
    }

    /* Load the value for the inequality constraint at the end of the
    ** range (if any).
    */
    nConstraint = nEq;
    if( pRangeEnd ){

Changes to test/minmax4.test.

15
16
17
18
19
20
21

22
23
24
25
26
27
28
#
# Demonstration that the value returned for p is on the same row as 
# the maximum q.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl


ifcapable !compound {
  finish_test
  return
}

do_test minmax4-1.1 {







>







15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
#
# Demonstration that the value returned for p is on the same row as 
# the maximum q.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix minmax4

ifcapable !compound {
  finish_test
  return
}

do_test minmax4-1.1 {
144
145
146
147
148
149
150










151









152








153
} {1 2 1 4 4 2 3 3 5 5}
do_test minmax4-2.7 {
  db eval {
    SELECT a, min(b), b, min(c), c FROM t2 GROUP BY a ORDER BY a;
  }
} {1 1 {} 2 2 2 3 3 5 5}






























finish_test







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

145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
} {1 2 1 4 4 2 3 3 5 5}
do_test minmax4-2.7 {
  db eval {
    SELECT a, min(b), b, min(c), c FROM t2 GROUP BY a ORDER BY a;
  }
} {1 1 {} 2 2 2 3 3 5 5}

#-------------------------------------------------------------------------
foreach {tn sql} {
  1 { CREATE INDEX i1 ON t1(a) }
  2 { CREATE INDEX i1 ON t1(a DESC) }
  3 { }
} {
  reset_db
  do_execsql_test 3.$tn.0 {
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(NULL, 1);
  }
  execsql $sql
  do_execsql_test 3.$tn.1 {
    SELECT min(a), b FROM t1;
  } {{} 1}
  do_execsql_test 3.$tn.2 {
    SELECT min(a), b FROM t1 WHERE a<50;
  } {{} {}}
  do_execsql_test 3.$tn.3 {
    INSERT INTO t1 VALUES(2, 2);
  }
  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