Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch cursor-hints Excluding Merge-Ins
This is equivalent to a diff from 0b1579ca to 7455d932
2018-12-14
| ||
20:28 | Add a missing OP_ColumnsUsed opcode to code for expressions like "? IN (SELECT ...)" in cases where expression can use an index that may contain NULL values. (Backported fix from version 3.14.0). (Leaf check-in: 11368e67 user: drh tags: branch-3.8.9) | |
2016-06-20
| ||
17:25 | For a table on the rhs of a LEFT JOIN operator, do not include terms like "IS NULL" from the WHERE clause in the cursor-hint. These may be false for rows that the cursor would otherwise visit, but true for a row of all NULL values generated by the LEFT JOIN. (check-in: 913e5956 user: dan tags: trunk) | |
17:22 | Allow LIKE operators that appear in a WHERE clause to be included in the cursor-hint for a cursor on the rhs of a LEFT JOIN. (Closed-Leaf check-in: 7455d932 user: dan tags: cursor-hints) | |
2016-06-17
| ||
19:27 | Include WHERE terms in the cursor-hint passed to a cursor opened for the rhs of a LEFT JOIN iff we can be sure that those terms will not evaluate to true if the LEFT JOIN generates a row of NULLs. (check-in: 998095ab user: dan tags: cursor-hints) | |
14:33 | If a table is on the rhs of a LEFT JOIN, include only terms from the joins ON(...) clause in the cursor-hint passed via OP_CursorHint. (check-in: 2a2346b0 user: dan tags: cursor-hints) | |
13:01 | Add the json_quote() function to the JSON1 extension. (Closed-Leaf check-in: 2c3714ae user: drh tags: json_quote) | |
2016-06-16
| ||
17:14 | Add a missing OP_ColumnsUsed opcode to code for expressions like "? IN (SELECT ...)" in cases where expression can use an index that may contain NULL values. (check-in: 0b1579ca user: dan tags: trunk) | |
11:16 | Fix a typo in a comment on the SrcList object. (check-in: 48b555c4 user: drh tags: trunk) | |
Changes to src/wherecode.c.
︙ | |||
622 623 624 625 626 627 628 629 630 631 632 633 634 635 | 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 | + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + | && sqlite3ColumnOfIndex(pHint->pIdx, pExpr->iColumn)<0 ){ pWalker->eCode = 1; } return WRC_Continue; } /* ** Test whether or not expression pExpr, which was part of a WHERE clause, ** should be included in the cursor-hint for a table that is on the rhs ** of a LEFT JOIN. Set Walker.eCode to non-zero before returning if the ** expression is not suitable. ** ** An expression is unsuitable if it might evaluate to non NULL even if ** a TK_COLUMN node that does affect the value of the expression is set ** to NULL. For example: ** ** col IS NULL ** col IS NOT NULL ** coalesce(col, 1) ** CASE WHEN col THEN 0 ELSE 1 END */ static int codeCursorHintIsOrFunction(Walker *pWalker, Expr *pExpr){ if( pExpr->op==TK_IS || pExpr->op==TK_ISNULL || pExpr->op==TK_ISNOT || pExpr->op==TK_NOTNULL || pExpr->op==TK_CASE ){ pWalker->eCode = 1; }else if( pExpr->op==TK_FUNCTION ){ int d1; char d2[3]; if( 0==sqlite3IsLikeFunction(pWalker->pParse->db, pExpr, &d1, d2) ){ pWalker->eCode = 1; } } return WRC_Continue; } /* ** This function is called on every node of an expression tree used as an ** argument to the OP_CursorHint instruction. If the node is a TK_COLUMN ** that accesses any table other than the one identified by ** CCurHint.iTabCur, then do the following: ** |
︙ | |||
674 675 676 677 678 679 680 681 682 683 684 685 686 687 | 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 | + | return rc; } /* ** Insert an OP_CursorHint instruction if it is appropriate to do so. */ static void codeCursorHint( struct SrcList_item *pTabItem, /* FROM clause item */ WhereInfo *pWInfo, /* The where clause */ WhereLevel *pLevel, /* Which loop to provide hints for */ WhereTerm *pEndRange /* Hint this end-of-scan boundary term if not NULL */ ){ Parse *pParse = pWInfo->pParse; sqlite3 *db = pParse->db; Vdbe *v = pParse->pVdbe; |
︙ | |||
704 705 706 707 708 709 710 | 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 | + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - + + | sWalker.pParse = pParse; sWalker.u.pCCurHint = &sHint; pWC = &pWInfo->sWC; for(i=0; i<pWC->nTerm; i++){ pTerm = &pWC->a[i]; if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue; if( pTerm->prereqAll & pLevel->notReady ) continue; /* Any terms specified as part of the ON(...) clause for any LEFT ** JOIN for which the current table is not the rhs are omitted ** from the cursor-hint. ** ** If this table is the rhs of a LEFT JOIN, "IS" or "IS NULL" terms ** that were specified as part of the WHERE clause must be excluded. ** This is to address the following: ** ** SELECT ... t1 LEFT JOIN t2 ON (t1.a=t2.b) WHERE t2.c IS NULL; ** ** Say there is a single row in t2 that matches (t1.a=t2.b), but its ** t2.c values is not NULL. If the (t2.c IS NULL) constraint is ** pushed down to the cursor, this row is filtered out, causing ** SQLite to synthesize a row of NULL values. Which does match the ** WHERE clause, and so the query returns a row. Which is incorrect. ** ** For the same reason, WHERE terms such as: ** ** WHERE 1 = (t2.c IS NULL) ** ** are also excluded. See codeCursorHintIsOrFunction() for details. */ if( pTabItem->fg.jointype & JT_LEFT ){ Expr *pExpr = pTerm->pExpr; if( !ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable!=pTabItem->iCursor ){ sWalker.eCode = 0; sWalker.xExprCallback = codeCursorHintIsOrFunction; sqlite3WalkExpr(&sWalker, pTerm->pExpr); if( sWalker.eCode ) continue; } }else{ |
︙ | |||
738 739 740 741 742 743 744 | 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 | - + | sqlite3WalkExpr(&sWalker, pExpr); sqlite3VdbeAddOp4(v, OP_CursorHint, (sHint.pIdx ? sHint.iIdxCur : sHint.iTabCur), 0, 0, (const char*)pExpr, P4_EXPR); } } #else |
︙ | |||
994 995 996 997 998 999 1000 | 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 | - + | if( pLoop->wsFlags & WHERE_TOP_LIMIT ) pEnd = pLoop->aLTerm[j++]; assert( pStart!=0 || pEnd!=0 ); if( bRev ){ pTerm = pStart; pStart = pEnd; pEnd = pTerm; } |
︙ | |||
1208 1209 1210 1211 1212 1213 1214 | 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 | - + | SWAP(u8, bSeekPastNull, bStopAtNull); } /* Generate code to evaluate all constraint terms using == or IN ** and store the values of those terms in an array of registers ** starting at regBase. */ |
︙ | |||
1656 1657 1658 1659 1660 1661 1662 | 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 | - + | static const u8 aStart[] = { OP_Rewind, OP_Last }; assert( bRev==0 || bRev==1 ); if( pTabItem->fg.isRecursive ){ /* Tables marked isRecursive have only a single row that is stored in ** a pseudo-cursor. No need to Rewind or Next such cursors. */ pLevel->op = OP_Noop; }else{ |
︙ |
Added test/cursorhint2.test.