/ Check-in [1fdaf2c3]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Fix a problem causing the LEFT JOIN strength reduction optimization to be incorrectly applied in some cases where the WHERE clause of the query contains a filter expression of the form "lhs.x IS NOT ?".
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 1fdaf2c34431adcac1c7ff29aae0623c4cbaa6a7f38e843c786bd407d8b3e730
User & Date: dan 2018-04-10 15:31:56
Context
2018-04-10
17:39
Version 3.23.1 check-in: 4bb22940 user: drh tags: trunk, release, version-3.23.1
15:31
Fix a problem causing the LEFT JOIN strength reduction optimization to be incorrectly applied in some cases where the WHERE clause of the query contains a filter expression of the form "lhs.x IS NOT ?". check-in: 1fdaf2c3 user: dan tags: trunk
14:29
When testing, avoid injecting an OOM fault into a file-control call made by SQLite on an unopened file-descriptor. check-in: 9c1a5eba user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

5014
5015
5016
5017
5018
5019
5020

5021
5022
5023
5024
5025
5026
5027


5028
5029
5030
5031
5032
5033
5034
  ** a later stage of processing, so the TK_AGG_FUNCTION case does not
  ** need to be considered here. */
  assert( pExpr->op!=TK_AGG_COLUMN );
  testcase( pExpr->op==TK_AGG_FUNCTION );

  if( ExprHasProperty(pExpr, EP_FromJoin) ) return WRC_Prune;
  switch( pExpr->op ){

    case TK_NOT:
    case TK_ISNULL:
    case TK_IS:
    case TK_OR:
    case TK_CASE:
    case TK_IN:
    case TK_FUNCTION:


      testcase( pExpr->op==TK_ISNULL );
      testcase( pExpr->op==TK_IS );
      testcase( pExpr->op==TK_OR );
      testcase( pExpr->op==TK_CASE );
      testcase( pExpr->op==TK_IN );
      testcase( pExpr->op==TK_FUNCTION );
      return WRC_Prune;







>







>
>







5014
5015
5016
5017
5018
5019
5020
5021
5022
5023
5024
5025
5026
5027
5028
5029
5030
5031
5032
5033
5034
5035
5036
5037
  ** a later stage of processing, so the TK_AGG_FUNCTION case does not
  ** need to be considered here. */
  assert( pExpr->op!=TK_AGG_COLUMN );
  testcase( pExpr->op==TK_AGG_FUNCTION );

  if( ExprHasProperty(pExpr, EP_FromJoin) ) return WRC_Prune;
  switch( pExpr->op ){
    case TK_ISNOT:
    case TK_NOT:
    case TK_ISNULL:
    case TK_IS:
    case TK_OR:
    case TK_CASE:
    case TK_IN:
    case TK_FUNCTION:
      testcase( pExpr->op==TK_ISNOT );
      testcase( pExpr->op==TK_NOT );
      testcase( pExpr->op==TK_ISNULL );
      testcase( pExpr->op==TK_IS );
      testcase( pExpr->op==TK_OR );
      testcase( pExpr->op==TK_CASE );
      testcase( pExpr->op==TK_IN );
      testcase( pExpr->op==TK_FUNCTION );
      return WRC_Prune;

Changes to test/join.test.

806
807
808
809
810
811
812





813
814
815
816
817
818
819
    FROM t1 LEFT JOIN t2
   WHERE a IN (1,3,x,y);
} {1 2 {} {} x 3 4 {} {} x}
do_execsql_test join-15.106 {
  SELECT *, 'x' 
    FROM t1 LEFT JOIN t2 
   WHERE NOT ( 'x'='y' AND t2.y=1 );





} {1 2 {} {} x 3 4 {} {} x}
do_execsql_test join-15.110 {
  DROP TABLE t1;
  DROP TABLE t2;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
  INSERT INTO t1(a,b) VALUES(1,0),(11,1),(12,1),(13,1),(121,12);
  CREATE INDEX t1b ON t1(b);







>
>
>
>
>







806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
    FROM t1 LEFT JOIN t2
   WHERE a IN (1,3,x,y);
} {1 2 {} {} x 3 4 {} {} x}
do_execsql_test join-15.106 {
  SELECT *, 'x' 
    FROM t1 LEFT JOIN t2 
   WHERE NOT ( 'x'='y' AND t2.y=1 );
} {1 2 {} {} x 3 4 {} {} x}
do_execsql_test join-15.107 {
  SELECT *, 'x' 
    FROM t1 LEFT JOIN t2 
   WHERE t2.y IS NOT 'abc'
} {1 2 {} {} x 3 4 {} {} x}
do_execsql_test join-15.110 {
  DROP TABLE t1;
  DROP TABLE t2;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
  INSERT INTO t1(a,b) VALUES(1,0),(11,1),(12,1),(13,1),(121,12);
  CREATE INDEX t1b ON t1(b);