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: |
d465c3eef458c851d97eea6e4117247d |
| 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
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 |
** 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.
*/
| < | > | | | < | 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
|