SQLite

Check-in [f0ae251abb]
Login

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

Overview
Comment:Make sure the ON clause of a LEFT JOIN does not cause an index to be used to speed access to a table to the left of the join. Ticket [ebdbadade5]
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f0ae251abb9e8a8793f46a30b79b2ce541cf14d5
User & Date: drh 2010-02-25 04:15:48.000
Context
2010-02-25
14:47
Expire pragma statements when reset, even if they were not run to completion. (check-in: 78351d289b user: drh tags: trunk)
04:15
Make sure the ON clause of a LEFT JOIN does not cause an index to be used to speed access to a table to the left of the join. Ticket [ebdbadade5] (check-in: f0ae251abb user: drh tags: trunk)
2010-02-24
21:44
Add testcase() macros beside each sqlite3_log() call to make sure it is tested with both logging enable and disabled. (check-in: 1168763d2c user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106

  int idxTerm               /* Index of the term to be analyzed */
){
  WhereTerm *pTerm;                /* The term to be analyzed */
  WhereMaskSet *pMaskSet;          /* Set of table index masks */
  Expr *pExpr;                     /* The expression to be analyzed */
  Bitmask prereqLeft;              /* Prerequesites of the pExpr->pLeft */
  Bitmask prereqAll;               /* Prerequesites of pExpr */
  Bitmask extraRight = 0;          /* */
  Expr *pStr1 = 0;                 /* RHS of LIKE/GLOB operator */
  int isComplete = 0;              /* RHS of LIKE/GLOB ends with wildcard */
  int noCase = 0;                  /* LIKE/GLOB distinguishes case */
  int op;                          /* Top-level operator.  pExpr->op */
  Parse *pParse = pWC->pParse;     /* Parsing context */
  sqlite3 *db = pParse->db;        /* Database connection */









|








1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106

  int idxTerm               /* Index of the term to be analyzed */
){
  WhereTerm *pTerm;                /* The term to be analyzed */
  WhereMaskSet *pMaskSet;          /* Set of table index masks */
  Expr *pExpr;                     /* The expression to be analyzed */
  Bitmask prereqLeft;              /* Prerequesites of the pExpr->pLeft */
  Bitmask prereqAll;               /* Prerequesites of pExpr */
  Bitmask extraRight = 0;          /* Extra dependencies on LEFT JOIN */
  Expr *pStr1 = 0;                 /* RHS of LIKE/GLOB operator */
  int isComplete = 0;              /* RHS of LIKE/GLOB ends with wildcard */
  int noCase = 0;                  /* LIKE/GLOB distinguishes case */
  int op;                          /* Top-level operator.  pExpr->op */
  Parse *pParse = pWC->pParse;     /* Parsing context */
  sqlite3 *db = pParse->db;        /* Database connection */
︙︙
1164
1165
1166
1167
1168
1169
1170

1171
1172
1173
1174
1175
1176
1177
1178

        pDup = pExpr;
        pNew = pTerm;
      }
      exprCommute(pParse, pDup);
      pLeft = pDup->pLeft;
      pNew->leftCursor = pLeft->iTable;
      pNew->u.leftColumn = pLeft->iColumn;

      pNew->prereqRight = prereqLeft;
      pNew->prereqAll = prereqAll;
      pNew->eOperator = operatorMask(pDup->op);
    }
  }

#ifndef SQLITE_OMIT_BETWEEN_OPTIMIZATION
  /* If a term is the BETWEEN operator, create two new virtual terms








>
|








1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179

        pDup = pExpr;
        pNew = pTerm;
      }
      exprCommute(pParse, pDup);
      pLeft = pDup->pLeft;
      pNew->leftCursor = pLeft->iTable;
      pNew->u.leftColumn = pLeft->iColumn;
      testcase( (prereqLeft | extraRight) != prereqLeft );
      pNew->prereqRight = prereqLeft | extraRight;
      pNew->prereqAll = prereqAll;
      pNew->eOperator = operatorMask(pDup->op);
    }
  }

#ifndef SQLITE_OMIT_BETWEEN_OPTIMIZATION
  /* If a term is the BETWEEN operator, create two new virtual terms
︙︙
Changes to test/where6.test.
︙︙
123
124
125
126
127
128
129
























130

} {1 3 1 3}
do_test where6-2.14 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE 1=c;
  }
} {1 3 1 3}

























finish_test








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


123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154

} {1 3 1 3}
do_test where6-2.14 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE 1=c;
  }
} {1 3 1 3}

# Ticket [ebdbadade5b]:
# If the ON close on a LEFT JOIN is of the form x=y where both x and y
# are indexed columns on tables to left of the join, then do not use that 
# term with indices to either table.
#
do_test where6-3.1 {
  db eval {
    CREATE TABLE t4(x UNIQUE);
    INSERT INTO t4 VALUES('abc');
    INSERT INTO t4 VALUES('def');
    INSERT INTO t4 VALUES('ghi');
    CREATE TABLE t5(a, b, c, PRIMARY KEY(a,b));
    INSERT INTO t5 VALUES('abc','def',123);
    INSERT INTO t5 VALUES('def','ghi',456);

    SELECT t4a.x, t4b.x, t5.c, t6.v
      FROM t4 AS t4a
           INNER JOIN t4 AS t4b
           LEFT JOIN t5 ON t5.a=t4a.x AND t5.b=t4b.x
           LEFT JOIN (SELECT 1 AS v) AS t6 ON t4a.x=t4b.x
     ORDER BY 1, 2, 3;
  }
} {abc abc {} 1 abc def 123 {} abc ghi {} {} def abc {} {} def def {} 1 def ghi 456 {} ghi abc {} {} ghi def {} {} ghi ghi {} 1}

finish_test