SQLite

Check-in [7455d932f5]
Login

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
Timelines: family | ancestors | descendants | both | cursor-hints
Files: files | file ages | folders
SHA1: 7455d932f5079ffe40462a8c119fc22b8a9bcbcc
User & Date: dan 2016-06-20 17:22:06.973
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: 913e595615 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: 7455d932f5 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: 998095aba0 user: dan tags: cursor-hints)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/wherecode.c.
︙
638
639
640
641
642
643
644
645

646
647
648
649





650



651
652
653
654
655
656
657

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








-
+




+
+
+
+
+
-
+
+
+








**
**   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_FUNCTION 
  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
︙
Changes to test/cursorhint2.test.
︙
159
160
161
162
163
164
165












166
167
168

159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180








+
+
+
+
+
+
+
+
+
+
+
+




}

do_extract_hints_test 2.10 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b = 32+32
} {
  x2 {AND(EQ(c1,ADD(32,32)),EQ(c0,r[2]))}
}

do_extract_hints_test 2.11 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b LIKE 'abc%'
} {
  x2 {AND(expr,EQ(c0,r[2]))}
}

do_extract_hints_test 2.11 {
  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE coalesce(x2.b, 1)
} {
  x2 {EQ(c0,r[2])}
}

finish_test