Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Ensure that the query planner knows that any column of a flattened LEFT JOIN can be NULL even if that column is labeled with "NOT NULL". Fix for ticket [892fc34f173e99d8]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
483462682d3a57fb9dd85b4772596e97 |
| User & Date: | dan 2017-06-20 17:43:26 |
Context
|
2017-06-20
| ||
| 18:19 | Rename the azCompileOpt global constant to avoid a harmless compiler warning about a name conflict with a local variable. (check-in: 94e420ec user: drh tags: trunk) | |
| 17:43 | Ensure that the query planner knows that any column of a flattened LEFT JOIN can be NULL even if that column is labeled with "NOT NULL". Fix for ticket [892fc34f173e99d8]. (check-in: 48346268 user: dan tags: trunk) | |
|
2017-06-17
| ||
| 18:49 | Fix a missing comma in the previous check-in. (check-in: f3db02f4 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
| ︙ | ︙ | |||
3201 3202 3203 3204 3205 3206 3207 |
memset(&ifNullRow, 0, sizeof(ifNullRow));
ifNullRow.op = TK_IF_NULL_ROW;
ifNullRow.pLeft = pCopy;
ifNullRow.iTable = pSubst->iNewTable;
pCopy = &ifNullRow;
}
pNew = sqlite3ExprDup(db, pCopy, 0);
| | > > > | | 3201 3202 3203 3204 3205 3206 3207 3208 3209 3210 3211 3212 3213 3214 3215 3216 3217 3218 3219 3220 |
memset(&ifNullRow, 0, sizeof(ifNullRow));
ifNullRow.op = TK_IF_NULL_ROW;
ifNullRow.pLeft = pCopy;
ifNullRow.iTable = pSubst->iNewTable;
pCopy = &ifNullRow;
}
pNew = sqlite3ExprDup(db, pCopy, 0);
if( pNew && pSubst->isLeftJoin ){
ExprSetProperty(pNew, EP_CanBeNull);
}
if( pNew && ExprHasProperty(pExpr,EP_FromJoin) ){
pNew->iRightJoinTable = pExpr->iRightJoinTable;
ExprSetProperty(pNew, EP_FromJoin);
}
sqlite3ExprDelete(db, pExpr);
pExpr = pNew;
}
}
}else{
if( pExpr->op==TK_IF_NULL_ROW && pExpr->iTable==pSubst->iTable ){
|
| ︙ | ︙ | |||
3496 3497 3498 3499 3500 3501 3502 | ** ** (t1 LEFT OUTER JOIN t2) JOIN t3 ** ** which is not at all the same thing. ** ** If the subquery is the right operand of a LEFT JOIN, then the outer ** query cannot be an aggregate. This is an artifact of the way aggregates | | | 3499 3500 3501 3502 3503 3504 3505 3506 3507 3508 3509 3510 3511 3512 3513 |
**
** (t1 LEFT OUTER JOIN t2) JOIN t3
**
** which is not at all the same thing.
**
** If the subquery is the right operand of a LEFT JOIN, then the outer
** query cannot be an aggregate. This is an artifact of the way aggregates
** are processed - there is no mechanism to determine if the LEFT JOIN
** table should be all-NULL.
**
** See also tickets #306, #350, and #3300.
*/
if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
isLeftJoin = 1;
if( pSubSrc->nSrc>1 || isAgg ){
|
| ︙ | ︙ |
Changes to test/join.test.
| ︙ | ︙ | |||
758 759 760 761 762 763 764 765 766 |
} {2 2 1 |}
do_execsql_test join-14.11 {
SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x WHERE b+1=x;
} {2 2 1 |}
do_execsql_test join-14.12 {
SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x ORDER BY b;
} {4 {} {} | 2 2 1 |}
finish_test
| > > > > > > > > > > > > > > > > > | 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 |
} {2 2 1 |}
do_execsql_test join-14.11 {
SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x WHERE b+1=x;
} {2 2 1 |}
do_execsql_test join-14.12 {
SELECT *, '|' FROM t3 LEFT JOIN v2 ON a=x ORDER BY b;
} {4 {} {} | 2 2 1 |}
# Verify the fix for ticket
# https://www.sqlite.org/src/info/892fc34f173e99d8
#
db close
sqlite3 db :memory:
do_execsql_test join-14.20 {
CREATE TABLE t1(id INTEGER PRIMARY KEY);
CREATE TABLE t2(id INTEGER PRIMARY KEY, c2 INTEGER);
CREATE TABLE t3(id INTEGER PRIMARY KEY, c3 INTEGER);
INSERT INTO t1(id) VALUES(456);
INSERT INTO t3(id) VALUES(1),(2);
SELECT t1.id, x2.id, x3.id
FROM t1
LEFT JOIN (SELECT * FROM t2) AS x2 ON t1.id=x2.c2
LEFT JOIN t3 AS x3 ON x2.id=x3.c3;
} {456 {} {}}
finish_test
|