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: |
08833dda3a25965cc509d0244d7cd68b |
| 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
Changes to src/select.c.
5211 5212 5213 5214 5215 5216 5217 5218 5219 5220 5221 5222 5223 5224 5225 |
*/
if( (pItem->fg.jointype & JT_LEFT)!=0
&& sqlite3ExprImpliesNonNullRow(p->pWhere, pItem->iCursor)
&& OptimizationEnabled(db, SQLITE_SimplifyJoin)
){
SELECTTRACE(0x100,pParse,p,
("LEFT-JOIN simplifies to JOIN on term %d\n",i));
pItem->fg.jointype &= ~JT_LEFT;
unsetJoinExpr(p->pWhere, pItem->iCursor);
}
/* No futher action if this term of the FROM clause is no a subquery */
if( pSub==0 ) continue;
/* Catch mismatch in the declared columns of a view and the number of
|
| |
5211 5212 5213 5214 5215 5216 5217 5218 5219 5220 5221 5222 5223 5224 5225 |
*/
if( (pItem->fg.jointype & JT_LEFT)!=0
&& sqlite3ExprImpliesNonNullRow(p->pWhere, pItem->iCursor)
&& OptimizationEnabled(db, SQLITE_SimplifyJoin)
){
SELECTTRACE(0x100,pParse,p,
("LEFT-JOIN simplifies to JOIN on term %d\n",i));
pItem->fg.jointype &= ~(JT_LEFT|JT_OUTER);
unsetJoinExpr(p->pWhere, pItem->iCursor);
}
/* No futher action if this term of the FROM clause is no a subquery */
if( pSub==0 ) continue;
/* Catch mismatch in the declared columns of a view and the number of
|
Changes to test/cursorhint2.test.
132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 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 |
do_extract_hints_test 2.5 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
} {
x2 {EQ(c0,r[2])}
}
do_extract_hints_test 2.6 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT NULL)
} {
x2 {EQ(c0,r[2])}
}
do_extract_hints_test 2.7 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT +NULL)
} {
x2 {EQ(c0,r[2])}
}
do_extract_hints_test 2.8 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NOT +NULL
} {
x2 {EQ(c0,r[2])}
}
do_extract_hints_test 2.9 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE CASE b WHEN 0 THEN 0 ELSE 1 END;
} {
x2 {EQ(c0,r[2])}
}
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]))}
}
ifcapable !icu {
# This test only works using the built-in LIKE, not the ICU LIKE extension.
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.12 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE coalesce(x2.b, 1)
} {
x2 {EQ(c0,r[2])}
}
finish_test
|
> > > | | | | | | | | | | | | | | | | | | | | > | | | | | | | | | | | | | | | | | | | > |
132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 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 188 189 |
do_extract_hints_test 2.5 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1)
} {
x2 {EQ(c0,r[2])}
}
if {0} {
# These tests no longer work due to the LEFT-JOIN strength reduction
# optimization
do_extract_hints_test 2.6 {
SELECT * FROM x1 CROSS JOIN x2 ON (a=x) WHERE 0 = (b IS NOT NULL)
} {
x2 {EQ(c0,r[2])}
}
do_extract_hints_test 2.7 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT +NULL)
} {
x2 {EQ(c0,r[2])}
}
do_extract_hints_test 2.8 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NOT +NULL
} {
x2 {EQ(c0,r[2])}
}
do_extract_hints_test 2.9 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x)
WHERE CASE b WHEN 0 THEN 0 ELSE 1 END;
} {
x2 {EQ(c0,r[2])}
}
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]))}
}
ifcapable !icu {
# This test only works using the built-in LIKE, not the ICU LIKE extension.
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.12 {
SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE coalesce(x2.b, 1)
} {
x2 {EQ(c0,r[2])}
}
finish_test
|