Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix an obscure problem with the join-strength-reduction optimization that could occur when mixing LEFT and RIGHT joins in the same query. Forum post 7f74ce0bee and separately post d64f0abe723ac98e. The problem was introduced by check-in [d747afda5683ca5f]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
530d10e93a5f63b71aaa94a2b89102d0 |
| User & Date: | dan 2023-11-10 15:03:18 |
| Original Comment: | Fix an obscure problem with the join-strength-reduction optimization that could occur when mixing LEFT and RIGHT joins in the same query. Forum post 7f74ce0bee. |
Context
|
2023-11-10
| ||
| 16:29 | Remove a NEVER() from whereAddIndexedExpr() that is reachable if there is an unknown indexed function in the schema. (check-in: a976b720 user: drh tags: trunk) | |
| 15:20 | Fix an obscure problem with the join-strength-reduction optimization that could occur when mixing LEFT and RIGHT joins in the same query. (check-in: dcf1c137 user: drh tags: branch-3.44) | |
| 15:03 | Fix an obscure problem with the join-strength-reduction optimization that could occur when mixing LEFT and RIGHT joins in the same query. Forum post 7f74ce0bee and separately post d64f0abe723ac98e. The problem was introduced by check-in [d747afda5683ca5f]. (check-in: 530d10e9 user: dan tags: trunk) | |
| 15:00 | Expose the missing SQLITE_SUBTYPE to wasm. (check-in: ac9534b2 user: stephan tags: trunk) | |
Changes
Changes to src/select.c.
| ︙ | ︙ | |||
7393 7394 7395 7396 7397 7398 7399 7400 7401 7402 7403 7404 7405 7406 7407 7408 7409 7410 7411 7412 7413 7414 7415 7416 7417 7418 7419 7420 7421 |
TREETRACE(0x1000,pParse,p,
("FULL-JOIN simplifies to RIGHT-JOIN on term %d\n",i));
pItem->fg.jointype &= ~JT_LEFT;
}else{
TREETRACE(0x1000,pParse,p,
("LEFT-JOIN simplifies to JOIN on term %d\n",i));
pItem->fg.jointype &= ~(JT_LEFT|JT_OUTER);
}
}
if( pItem->fg.jointype & JT_LTORJ ){
for(j=i+1; j<pTabList->nSrc; j++){
SrcItem *pI2 = &pTabList->a[j];
if( pI2->fg.jointype & JT_RIGHT ){
if( pI2->fg.jointype & JT_LEFT ){
TREETRACE(0x1000,pParse,p,
("FULL-JOIN simplifies to LEFT-JOIN on term %d\n",j));
pI2->fg.jointype &= ~JT_RIGHT;
}else{
TREETRACE(0x1000,pParse,p,
("RIGHT-JOIN simplifies to JOIN on term %d\n",j));
pI2->fg.jointype &= ~(JT_RIGHT|JT_OUTER);
}
}
}
for(j=pTabList->nSrc-1; j>=i; j--){
pTabList->a[j].fg.jointype &= ~JT_LTORJ;
if( pTabList->a[j].fg.jointype & JT_RIGHT ) break;
}
}
| > > < < < | 7393 7394 7395 7396 7397 7398 7399 7400 7401 7402 7403 7404 7405 7406 7407 7408 7409 7410 7411 7412 7413 7414 7415 7416 7417 7418 7419 7420 7421 7422 7423 7424 7425 7426 7427 7428 7429 7430 |
TREETRACE(0x1000,pParse,p,
("FULL-JOIN simplifies to RIGHT-JOIN on term %d\n",i));
pItem->fg.jointype &= ~JT_LEFT;
}else{
TREETRACE(0x1000,pParse,p,
("LEFT-JOIN simplifies to JOIN on term %d\n",i));
pItem->fg.jointype &= ~(JT_LEFT|JT_OUTER);
unsetJoinExpr(p->pWhere, pItem->iCursor, 0);
}
}
if( pItem->fg.jointype & JT_LTORJ ){
for(j=i+1; j<pTabList->nSrc; j++){
SrcItem *pI2 = &pTabList->a[j];
if( pI2->fg.jointype & JT_RIGHT ){
if( pI2->fg.jointype & JT_LEFT ){
TREETRACE(0x1000,pParse,p,
("FULL-JOIN simplifies to LEFT-JOIN on term %d\n",j));
pI2->fg.jointype &= ~JT_RIGHT;
}else{
TREETRACE(0x1000,pParse,p,
("RIGHT-JOIN simplifies to JOIN on term %d\n",j));
pI2->fg.jointype &= ~(JT_RIGHT|JT_OUTER);
unsetJoinExpr(p->pWhere, pI2->iCursor, 1);
}
}
}
for(j=pTabList->nSrc-1; j>=i; j--){
pTabList->a[j].fg.jointype &= ~JT_LTORJ;
if( pTabList->a[j].fg.jointype & JT_RIGHT ) break;
}
}
}
/* No further action if this term of the FROM clause is not a subquery */
if( pSub==0 ) continue;
/* Catch mismatch in the declared columns of a view and the number of
** columns in the SELECT on the RHS */
|
| ︙ | ︙ |
Changes to test/joinH.test.
| ︙ | ︙ | |||
245 246 247 248 249 250 251 252 253 254 |
} {0 99}
do_catchsql_test 9.10 {
SELECT oid FROM wo1 JOIN (wo3 JOIN x3)
} {0 99}
do_catchsql_test 9.11 {
SELECT oid FROM wo2 JOIN (wo3 JOIN x3)
} {0 99}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 |
} {0 99}
do_catchsql_test 9.10 {
SELECT oid FROM wo1 JOIN (wo3 JOIN x3)
} {0 99}
do_catchsql_test 9.11 {
SELECT oid FROM wo2 JOIN (wo3 JOIN x3)
} {0 99}
reset_db
do_execsql_test 10.0 {
CREATE TABLE rt0 (c0 INTEGER, c1 INTEGER, c2 INTEGER, c3 INTEGER, c4 INTEGER);
CREATE TABLE rt3 (c3 INTEGER);
INSERT INTO rt0(c3, c1) VALUES (x'', '1');
INSERT INTO rt0(c3, c1) VALUES ('-1', -1e500);
INSERT INTO rt0(c3, c1) VALUES (1, x'');
CREATE VIEW v6(c0, c1, c2) AS SELECT 0, 0, 0;
}
do_execsql_test 10.1 {
SELECT COUNT(*) FROM rt0 LEFT JOIN rt3 JOIN v6 ON ((CASE v6.c0 WHEN rt0.c4 THEN rt3.c3 END) NOT BETWEEN (rt0.c4) AND (NULL)) WHERE (rt0.c1); -- 2
} {0}
do_execsql_test 10.2 {
SELECT COUNT(*) FROM rt0 LEFT JOIN rt3 RIGHT OUTER JOIN v6 ON ((CASE v6.c0 WHEN rt0.c4 THEN rt3.c3 END) NOT BETWEEN (rt0.c4) AND (NULL)) WHERE (rt0.c1); -- 2
} {0}
#-------------------------------------------------------------------------
do_execsql_test 11.1 {
CREATE TABLE t1(a, b);
CREATE TABLE t2(c, d);
CREATE TABLE t3(e, f);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(2, 2);
INSERT INTO t3 VALUES(3, 3);
}
do_execsql_test 11.2 {
SELECT * FROM t1 LEFT JOIN t2 RIGHT JOIN t3 ON (t2.c=10)
} {{} {} {} {} 3 3}
do_execsql_test 11.3 {
SELECT * FROM t1 LEFT JOIN t2 RIGHT JOIN t3 ON (t2.c=10) WHERE t1.a=1
} {}
finish_test
|