Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Always make sure the left-hand side of the IS NOT NULL operator is a simple column, not a general expression, before applying the IS NOT NULL query optimization. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
543f75a6abe3aa4f49df43e71a462702 |
| User & Date: | drh 2011-04-08 21:35:26.364 |
| Original Comment: | Always make sure the left-hand side of the IS NOT NULL operator is a smiple column, not a general expression, before applying the IS NOT NULL query optimization. |
References
|
2011-04-08
| ||
| 23:04 | Make sure the left-hand side of the IS NOT NULL operator is a simple column and not a general expression before applying the IS NOT NULL optimization. This is a backport of check-in [543f75a6abe3]. (check-in: e8177e0149 user: drh tags: branch-3.7.2) | |
Context
|
2011-04-09
| ||
| 02:09 | Do not do a backup if the number of reserved bytes in the source and destination do not match. Try to make the match, but if unable fail. (check-in: 0ca8a2332b user: drh tags: trunk) | |
|
2011-04-08
| ||
| 21:35 | Always make sure the left-hand side of the IS NOT NULL operator is a simple column, not a general expression, before applying the IS NOT NULL query optimization. (check-in: 543f75a6ab user: drh tags: trunk) | |
| 18:47 | Add some rtree tests to the backcompat.test script. (check-in: 8ea3601cd1 user: dan tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
1338 1339 1340 1341 1342 1343 1344 | ** virtual term of that form. ** ** Note that the virtual term must be tagged with TERM_VNULL. This ** TERM_VNULL tag will suppress the not-null check at the beginning ** of the loop. Without the TERM_VNULL flag, the not-null check at ** the start of the loop will prevent any results from being returned. */ | | > > > | 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 |
** virtual term of that form.
**
** Note that the virtual term must be tagged with TERM_VNULL. This
** TERM_VNULL tag will suppress the not-null check at the beginning
** of the loop. Without the TERM_VNULL flag, the not-null check at
** the start of the loop will prevent any results from being returned.
*/
if( pExpr->op==TK_NOTNULL
&& pExpr->pLeft->op==TK_COLUMN
&& pExpr->pLeft->iColumn>=0
){
Expr *pNewExpr;
Expr *pLeft = pExpr->pLeft;
int idxNew;
WhereTerm *pNewTerm;
pNewExpr = sqlite3PExpr(pParse, TK_GT,
sqlite3ExprDup(db, pLeft, 0),
|
| ︙ | ︙ |
Changes to test/analyze5.test.
| ︙ | ︙ | |||
205 206 207 208 209 210 211 212 213 214 215 216 217 218 |
#
foreach {testid where index rows} {
500 {x IS NULL AND u='charlie'} t1u 20
501 {x=1 AND u='charlie'} t1x 5
502 {x IS NULL} {} 100
503 {x=1} t1x 50
504 {x IS NOT NULL} t1x 25
} {
# Verify that the expected index is used with the expected row count
do_test analyze5-1.${testid}a {
set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
set idx {}
regexp {INDEX (t1.) } $x all idx
| > > | 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 |
#
foreach {testid where index rows} {
500 {x IS NULL AND u='charlie'} t1u 20
501 {x=1 AND u='charlie'} t1x 5
502 {x IS NULL} {} 100
503 {x=1} t1x 50
504 {x IS NOT NULL} t1x 25
505 {+x IS NOT NULL} {} 500
506 {upper(x) IS NOT NULL} {} 500
} {
# Verify that the expected index is used with the expected row count
do_test analyze5-1.${testid}a {
set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
set idx {}
regexp {INDEX (t1.) } $x all idx
|
| ︙ | ︙ |