/ Check-in [08833dda]
Login

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

Overview
Comment:Do a more thorough job of cleaning traces of the strength-reduced LEFT JOIN.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | join-strength-reduction
Files: files | file ages | folders
SHA3-256: 08833dda3a25965cc509d0244d7cd68bdb2306351ca52862f347e1efe5db4508
User & Date: drh 2018-03-20 22:52:27
Original Comment: Do a more thorough job of cleaning erasing traces of the strength-reduced LEFT JOIN.
Context
2018-03-21
01:59
Relax LEFT-JOIN restrictions on the push-down optimization. Closed-Leaf check-in: b5d3dd8c user: drh tags: join-strength-reduction
2018-03-20
22:52
Do a more thorough job of cleaning traces of the strength-reduced LEFT JOIN. check-in: 08833dda user: drh tags: join-strength-reduction
21:16
If terms of the WHERE clause require that the right table in a LEFT JOIN not be a null row, then simplify the LEFT JOIN into an ordinary JOIN. check-in: 5b7abecc user: drh tags: join-strength-reduction
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  5211   5211       */
  5212   5212       if( (pItem->fg.jointype & JT_LEFT)!=0
  5213   5213        && sqlite3ExprImpliesNonNullRow(p->pWhere, pItem->iCursor)
  5214   5214        && OptimizationEnabled(db, SQLITE_SimplifyJoin)
  5215   5215       ){
  5216   5216         SELECTTRACE(0x100,pParse,p,
  5217   5217                   ("LEFT-JOIN simplifies to JOIN on term %d\n",i));
  5218         -      pItem->fg.jointype &= ~JT_LEFT;
         5218  +      pItem->fg.jointype &= ~(JT_LEFT|JT_OUTER);
  5219   5219         unsetJoinExpr(p->pWhere, pItem->iCursor);
  5220   5220       }
  5221   5221   
  5222   5222       /* No futher action if this term of the FROM clause is no a subquery */
  5223   5223       if( pSub==0 ) continue;
  5224   5224   
  5225   5225       /* Catch mismatch in the declared columns of a view and the number of

Changes to test/cursorhint2.test.

   132    132   
   133    133   do_extract_hints_test 2.5 {
   134    134     SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
   135    135   } {
   136    136     x2 {EQ(c0,r[2])}
   137    137   }
   138    138   
   139         -do_extract_hints_test 2.6 {
   140         -  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT NULL)
   141         -} {
   142         -  x2 {EQ(c0,r[2])}
   143         -}
   144         -
   145         -do_extract_hints_test 2.7 {
   146         -  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT +NULL)
   147         -} {
   148         -  x2 {EQ(c0,r[2])}
   149         -}
   150         -
   151         -do_extract_hints_test 2.8 {
   152         -  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NOT +NULL
   153         -} {
   154         -  x2 {EQ(c0,r[2])}
   155         -}
   156         -
   157         -do_extract_hints_test 2.9 {
   158         -  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE CASE b WHEN 0 THEN 0 ELSE 1 END;
   159         -} {
   160         -  x2 {EQ(c0,r[2])}
   161         -}
   162         -
   163         -do_extract_hints_test 2.10 {
   164         -  SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b = 32+32
   165         -} {
   166         -  x2 {AND(EQ(c1,ADD(32,32)),EQ(c0,r[2]))}
   167         -}
   168         -
   169         -ifcapable !icu {
   170         -  # This test only works using the built-in LIKE, not the ICU LIKE extension.
   171         -  do_extract_hints_test 2.11 {
   172         -    SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b LIKE 'abc%'
          139  +if {0} {
          140  +  # These tests no longer work due to the LEFT-JOIN strength reduction
          141  +  # optimization
          142  +  do_extract_hints_test 2.6 {
          143  +    SELECT * FROM x1 CROSS JOIN x2 ON (a=x) WHERE 0 = (b IS NOT NULL)
          144  +  } {
          145  +    x2 {EQ(c0,r[2])}
          146  +  }
          147  +  
          148  +  do_extract_hints_test 2.7 {
          149  +    SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT +NULL)
          150  +  } {
          151  +    x2 {EQ(c0,r[2])}
          152  +  }
          153  +  
          154  +  do_extract_hints_test 2.8 {
          155  +    SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NOT +NULL
          156  +  } {
          157  +    x2 {EQ(c0,r[2])}
          158  +  }
          159  +  
          160  +  do_extract_hints_test 2.9 {
          161  +    SELECT * FROM x1 LEFT JOIN x2 ON (a=x)
          162  +      WHERE CASE b WHEN 0 THEN 0 ELSE 1 END;
          163  +  } {
          164  +    x2 {EQ(c0,r[2])}
          165  +  }
          166  +  
          167  +  do_extract_hints_test 2.10 {
          168  +    SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b = 32+32
   173    169     } {
   174         -    x2 {AND(expr,EQ(c0,r[2]))}
          170  +    x2 {AND(EQ(c1,ADD(32,32)),EQ(c0,r[2]))}
          171  +  }
          172  +  
          173  +  ifcapable !icu {
          174  +    # This test only works using the built-in LIKE, not the ICU LIKE extension.
          175  +    do_extract_hints_test 2.11 {
          176  +      SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b LIKE 'abc%'
          177  +    } {
          178  +      x2 {AND(expr,EQ(c0,r[2]))}
          179  +    }
   175    180     }
   176    181   }
   177         -
          182  +  
   178    183   do_extract_hints_test 2.12 {
   179    184     SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE coalesce(x2.b, 1)
   180    185   } {
   181    186     x2 {EQ(c0,r[2])}
   182    187   }
   183    188   
   184    189   finish_test