SQLite: Check-in [7455d932]

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

Overview

Comment:	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.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| cursor-hints
Files:	files \| file ages \| folders
SHA1:	7455d932f5079ffe40462a8c119fc22b8a9bcbcc
User & Date:	dan 2016-06-20 17:22:06

Context

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

Changes

Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

   638    638   **
   639    639   **   col IS NULL
   640    640   **   col IS NOT NULL
   641    641   **   coalesce(col, 1)
   642    642   **   CASE WHEN col THEN 0 ELSE 1 END
   643    643   */
   644    644   static int codeCursorHintIsOrFunction(Walker *pWalker, Expr *pExpr){
   645         -  if( pExpr->op==TK_IS || pExpr->op==TK_FUNCTION 
          645  +  if( pExpr->op==TK_IS 
   646    646      || pExpr->op==TK_ISNULL || pExpr->op==TK_ISNOT 
   647    647      || pExpr->op==TK_NOTNULL || pExpr->op==TK_CASE 
   648    648     ){
   649    649       pWalker->eCode = 1;
          650  +  }else if( pExpr->op==TK_FUNCTION ){
          651  +    int d1;
          652  +    char d2[3];
          653  +    if( 0==sqlite3IsLikeFunction(pWalker->pParse->db, pExpr, &d1, d2) ){
          654  +      pWalker->eCode = 1;
          655  +    }
   650    656     }
          657  +
   651    658     return WRC_Continue;
   652    659   }
   653    660   
   654    661   
   655    662   /*
   656    663   ** This function is called on every node of an expression tree used as an
   657    664   ** argument to the OP_CursorHint instruction. If the node is a TK_COLUMN

Changes to test/cursorhint2.test.

   159    159   }
   160    160   
   161    161   do_extract_hints_test 2.10 {
   162    162     SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b = 32+32
   163    163   } {
   164    164     x2 {AND(EQ(c1,ADD(32,32)),EQ(c0,r[2]))}
   165    165   }
          166  +
          167  +do_extract_hints_test 2.11 {
          168  +  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b LIKE 'abc%'
          169  +} {
          170  +  x2 {AND(expr,EQ(c0,r[2]))}
          171  +}
          172  +
          173  +do_extract_hints_test 2.11 {
          174  +  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE coalesce(x2.b, 1)
          175  +} {
          176  +  x2 {EQ(c0,r[2])}
          177  +}
   166    178   
   167    179   finish_test
   168    180