SQLite

Check-in [351bc22fa9]
Login

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

Overview
Comment:Do not apply the WHERE-clause pushdown optimization to terms that originate in the ON or USING clause of a LEFT JOIN. Fix for ticket [c2a19d81652f40568c].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 351bc22fa9b5a2e50da3583a882c5aa390bda19f
User & Date: drh 2015-08-22 01:32:29.479
Original Comment: Do not apply the WHERE-clause pushdown optimization to terms that originate in the ON or USING clause of a LEFT JOIN. Fix for ticket [6df18e949d3676290].
Context
2017-07-17
19:14
Do not apply the WHERE-clause pushdown optimization to terms that originate in the ON or USING clause of a LEFT JOIN. Fix for ticket [c2a19d81652f40568c]. (check-in: 52674f948c user: drh tags: push-down-backport)
2015-08-22
03:05
Fix a couple instances of OOM handling in the json extension. (check-in: 213a6c5ccb user: drh tags: trunk)
01:32
Do not apply the WHERE-clause pushdown optimization to terms that originate in the ON or USING clause of a LEFT JOIN. Fix for ticket [c2a19d81652f40568c]. (check-in: 351bc22fa9 user: drh tags: trunk)
2015-08-21
20:43
Add extension functions for processing JSON. (check-in: 178f9a352c user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
3752
3753
3754
3755
3756
3757
3758



3759
3760
3761
3762
3763
3764
3765
**
**   (3) The inner query has a LIMIT clause (since the changes to the WHERE
**       close would change the meaning of the LIMIT).
**
**   (4) The inner query is the right operand of a LEFT JOIN.  (The caller
**       enforces this restriction since this routine does not have enough
**       information to know.)



**
** Return 0 if no changes are made and non-zero if one or more WHERE clause
** terms are duplicated into the subquery.
*/
static int pushDownWhereTerms(
  sqlite3 *db,          /* The database connection (for malloc()) */
  Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */







>
>
>







3752
3753
3754
3755
3756
3757
3758
3759
3760
3761
3762
3763
3764
3765
3766
3767
3768
**
**   (3) The inner query has a LIMIT clause (since the changes to the WHERE
**       close would change the meaning of the LIMIT).
**
**   (4) The inner query is the right operand of a LEFT JOIN.  (The caller
**       enforces this restriction since this routine does not have enough
**       information to know.)
**
**   (5) The WHERE clause expression originates in the ON or USING clause
**       of a LEFT JOIN.
**
** Return 0 if no changes are made and non-zero if one or more WHERE clause
** terms are duplicated into the subquery.
*/
static int pushDownWhereTerms(
  sqlite3 *db,          /* The database connection (for malloc()) */
  Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
3775
3776
3777
3778
3779
3780
3781

3782
3783
3784
3785
3786
3787
3788
  if( pSubq->pLimit!=0 ){
     return 0; /* restriction (3) */
  }
  while( pWhere->op==TK_AND ){
    nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor);
    pWhere = pWhere->pLeft;
  }

  if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){
    nChng++;
    while( pSubq ){
      pNew = sqlite3ExprDup(db, pWhere, 0);
      pNew = substExpr(db, pNew, iCursor, pSubq->pEList);
      pSubq->pWhere = sqlite3ExprAnd(db, pSubq->pWhere, pNew);
      pSubq = pSubq->pPrior;







>







3778
3779
3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
  if( pSubq->pLimit!=0 ){
     return 0; /* restriction (3) */
  }
  while( pWhere->op==TK_AND ){
    nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor);
    pWhere = pWhere->pLeft;
  }
  if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction 5 */
  if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){
    nChng++;
    while( pSubq ){
      pNew = sqlite3ExprDup(db, pWhere, 0);
      pNew = substExpr(db, pNew, iCursor, pSubq->pEList);
      pSubq->pWhere = sqlite3ExprAnd(db, pSubq->pWhere, pNew);
      pSubq = pSubq->pPrior;
Changes to test/join5.test.
156
157
158
159
160
161
162























163
164
  CREATE TABLE x2(b NOT NULL);
  CREATE TABLE x3(c, d);
  INSERT INTO x3 VALUES('a', NULL);
  INSERT INTO x3 VALUES('b', NULL);
  INSERT INTO x3 VALUES('c', NULL);
  SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b;
} {}
























finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
  CREATE TABLE x2(b NOT NULL);
  CREATE TABLE x3(c, d);
  INSERT INTO x3 VALUES('a', NULL);
  INSERT INTO x3 VALUES('b', NULL);
  INSERT INTO x3 VALUES('c', NULL);
  SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b;
} {}

# Ticket https://www.sqlite.org/src/tktview/c2a19d81652f40568c770c43 on
# 2015-08-20.  LEFT JOIN and the push-down optimization.
#
do_execsql_test join6-4.1 {
  SELECT *
  FROM (
      SELECT 'apple' fruit
      UNION ALL SELECT 'banana'
  ) a
  JOIN (
      SELECT 'apple' fruit
      UNION ALL SELECT 'banana'
  ) b ON a.fruit=b.fruit
  LEFT JOIN (
      SELECT 1 isyellow
  ) c ON b.fruit='banana';
} {apple apple {} banana banana 1}
do_execsql_test join6-4.2 {
  SELECT *
    FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana')
         LEFT JOIN (SELECT 1) ON fruit='banana';
} {apple {} banana 1}

finish_test