Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix an affinity problem caused by a USING or NATURAL JOIN on the LHS of a FULL JOIN. Forum post 5028c785b6. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
8d393ca07fe09f48d77adb517e2e4baa |
| User & Date: | dan 2025-05-31 18:16:21.946 |
Context
|
2025-05-31
| ||
| 18:46 | Cherrypick obscure (fuzzer-generated) RIGHT JOIN bug fixes from trunk. (check-in: a61da8169b user: drh tags: branch-3.50) | |
| 18:26 | New makefile target "xdevtest" works like "releasetest" except that it omits the "verify-source" dependency so that it can be run with uncommitted changes in the source tree. (check-in: 1afb1ac3e9 user: drh tags: trunk) | |
| 18:16 | Fix an affinity problem caused by a USING or NATURAL JOIN on the LHS of a FULL JOIN. Forum post 5028c785b6. (check-in: 8d393ca07f user: dan tags: trunk) | |
| 16:17 | Relax query flattener constraint (3b) and thereby allow flattening the RHS of a LEFT JOIN even if the RHS contains a virtual table. This was previously disallowed by [9dbae1df75219e2a] as a performance optimization. It turns out that the constraint causes performance issues, and we do not have a record of any performance issue that it solves. (check-in: 1ddaa92057 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
| ︙ | ︙ | |||
626 627 628 629 630 631 632 633 634 635 636 637 638 639 |
pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1);
pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iLeftCol);
sqlite3SrcItemColumnUsed(&pSrc->a[iLeft], iLeftCol);
}
if( pFuncArgs ){
pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1);
pE1 = sqlite3ExprFunction(pParse, pFuncArgs, &tkCoalesce, 0);
}
}else if( (pSrc->a[i+1].fg.jointype & JT_LEFT)!=0 && pParse->nErr==0 ){
assert( pE1!=0 );
ExprSetProperty(pE1, EP_CanBeNull);
}
pE2 = sqlite3CreateColumnExpr(db, pSrc, i+1, iRightCol);
sqlite3SrcItemColumnUsed(pRight, iRightCol);
| > > > | 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 |
pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1);
pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iLeftCol);
sqlite3SrcItemColumnUsed(&pSrc->a[iLeft], iLeftCol);
}
if( pFuncArgs ){
pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1);
pE1 = sqlite3ExprFunction(pParse, pFuncArgs, &tkCoalesce, 0);
if( pE1 ){
pE1->affExpr = sqlite3ExprAffinity(pFuncArgs->a[0].pExpr);
}
}
}else if( (pSrc->a[i+1].fg.jointype & JT_LEFT)!=0 && pParse->nErr==0 ){
assert( pE1!=0 );
ExprSetProperty(pE1, EP_CanBeNull);
}
pE2 = sqlite3CreateColumnExpr(db, pSrc, i+1, iRightCol);
sqlite3SrcItemColumnUsed(pRight, iRightCol);
|
| ︙ | ︙ |
Changes to test/joinH.test.
| ︙ | ︙ | |||
336 337 338 339 340 341 342 343 344 |
SELECT a1.a, sum( a1.a+a1.b ) FROM t3 AS a1 RIGHT JOIN t4 ON a=x
GROUP BY a1.a ORDER BY 1;
} {NULL NULL 1 -592 4 192 16 48}
do_execsql_test 13.4 {
SELECT sum( a1.a+a1.b ) FROM t3 AS a1 RIGHT JOIN t3 ON true
GROUP BY a1.a ORDER BY 1;
} {-1480 240 480}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 |
SELECT a1.a, sum( a1.a+a1.b ) FROM t3 AS a1 RIGHT JOIN t4 ON a=x
GROUP BY a1.a ORDER BY 1;
} {NULL NULL 1 -592 4 192 16 48}
do_execsql_test 13.4 {
SELECT sum( a1.a+a1.b ) FROM t3 AS a1 RIGHT JOIN t3 ON true
GROUP BY a1.a ORDER BY 1;
} {-1480 240 480}
#-------------------------------------------------------------------------
# 2025-05-30
# https://sqlite.org/forum/forumpost/5028c785b6
#
reset_db
do_execsql_test 14.0 {
CREATE TABLE t1(c0 INT);
CREATE TABLE t2(c0 BLOB);
CREATE TABLE t3(c0 BLOB);
CREATE TABLE t4(c4 BLOB);
INSERT INTO t1(c0) VALUES(0);
INSERT INTO t3(c0) VALUES('0');
}
do_execsql_test 14.1.1 {
SELECT * FROM t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3;
} {0}
do_execsql_test 14.1.2 {
SELECT * FROM t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3 FULL JOIN t4 ON true;
} {0 {}}
do_execsql_test 14.1.3 {
SELECT * FROM (t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3) FULL JOIN t4 ON true;
} {0 {}}
do_execsql_test 14.1.4 {
SELECT *
FROM (t1 NATURAL LEFT JOIN t2 NATURAL JOIN t3) AS qq FULL JOIN t4 ON true;
} {0 {}}
do_execsql_test 14.2.1 {
SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1;
} {0}
do_execsql_test 14.2.2 {
SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1 FULL JOIN t4 ON true;
} {0 {}}
do_execsql_test 14.2.3 {
SELECT * FROM (t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1) FULL JOIN t4 ON true;
} {0 {}}
do_execsql_test 14.2.4 {
SELECT *
FROM (t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1) AS qq FULL JOIN t4 ON true;
} {0 {}}
finish_test
|