Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not allow FROM-clause terms on the left side of a RIGHT or FULL JOIN to be reordered. forum post 6650cd40b5634f35. This is probably more strict that necessary to get correct behavior, but for the first release that supports RIGHT/FULL JOIN it is perhaps better to be correct than fast. A less strict constraint might be to prohibit FROM-clause terms that originate on the left side of a RIGHT JOIN from crossing from the right side to the left side of a LEFT JOIN. Revisit this later. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
238453ffab0ba1bdddb529be35da82d5 |
| User & Date: | drh 2022-06-20 18:26:14 |
Context
|
2022-06-20
| ||
| 19:12 | Add an ALWAYS() macro to an always-true branch. (check-in: 364645d8 user: drh tags: trunk) | |
| 18:26 | Do not allow FROM-clause terms on the left side of a RIGHT or FULL JOIN to be reordered. forum post 6650cd40b5634f35. This is probably more strict that necessary to get correct behavior, but for the first release that supports RIGHT/FULL JOIN it is perhaps better to be correct than fast. A less strict constraint might be to prohibit FROM-clause terms that originate on the left side of a RIGHT JOIN from crossing from the right side to the left side of a LEFT JOIN. Revisit this later. (check-in: 238453ff user: drh tags: trunk) | |
| 17:04 | Do not allow an ON clause to references tables to its right if there is a RIGHT or FULL join anywhere in the query. Other RDBMSes prohibit this always, but SQLite must allow ON clauses to reference tables to their right for legacy compatibility, unless there is a RIGHT or FULL join someplace in the query, in which case there is no legacy to support. (check-in: e615dbe0 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
4176 4177 4178 4179 4180 4181 4182 |
whereLoopInit(pNew);
pBuilder->iPlanLimit = SQLITE_QUERY_PLANNER_LIMIT;
for(iTab=0, pItem=pTabList->a; pItem<pEnd; iTab++, pItem++){
Bitmask mUnusable = 0;
pNew->iTab = iTab;
pBuilder->iPlanLimit += SQLITE_QUERY_PLANNER_LIMIT_INCR;
pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor);
| > | > | > > > > > > > > | 4176 4177 4178 4179 4180 4181 4182 4183 4184 4185 4186 4187 4188 4189 4190 4191 4192 4193 4194 4195 4196 4197 4198 4199 4200 4201 4202 4203 4204 |
whereLoopInit(pNew);
pBuilder->iPlanLimit = SQLITE_QUERY_PLANNER_LIMIT;
for(iTab=0, pItem=pTabList->a; pItem<pEnd; iTab++, pItem++){
Bitmask mUnusable = 0;
pNew->iTab = iTab;
pBuilder->iPlanLimit += SQLITE_QUERY_PLANNER_LIMIT_INCR;
pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor);
if( bFirstPastRJ
|| (pItem->fg.jointype & (JT_OUTER|JT_CROSS|JT_LTORJ))!=0
){
/* Add prerequisites to prevent reordering of FROM clause terms
** across CROSS joins and outer joins. The bFirstPastRJ boolean
** prevents the right operand of a RIGHT JOIN from being swapped with
** other elements even further to the right.
**
** The JT_LTORJ term prevents any FROM-clause term reordering for terms
** to the left of a RIGHT JOIN. This is conservative. Relaxing this
** constraint somewhat to prevent terms from crossing from the right
** side of a LEFT JOIN over to the left side when they are on the
** left side of a RIGHT JOIN would be sufficient for all known failure
** cases. FIX ME: Implement this optimization.
*/
mPrereq |= mPrior;
bFirstPastRJ = (pItem->fg.jointype & JT_RIGHT)!=0;
}
#ifndef SQLITE_OMIT_VIRTUALTABLE
if( IsVirtual(pItem->pTab) ){
SrcItem *p;
for(p=&pItem[1]; p<pEnd; p++){
|
| ︙ | ︙ |
Changes to test/join8.test.
| ︙ | ︙ | |||
760 761 762 763 764 765 766 767 768 |
CREATE TABLE t4(b INT, c INT);
CREATE TABLE t5(a INT, f INT);
INSERT INTO t5 VALUES(1,2);
WITH t7(x, y) AS (SELECT 100, 200 FROM t5)
SELECT * FROM t4 JOIN t7 ON true RIGHT JOIN (SELECT y AS z FROM t7) AS t6 ON (x=z);
} {- - - - 200}
finish_test
| > > > > > > > > > > > > > > > > > > > > > | 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 |
CREATE TABLE t4(b INT, c INT);
CREATE TABLE t5(a INT, f INT);
INSERT INTO t5 VALUES(1,2);
WITH t7(x, y) AS (SELECT 100, 200 FROM t5)
SELECT * FROM t4 JOIN t7 ON true RIGHT JOIN (SELECT y AS z FROM t7) AS t6 ON (x=z);
} {- - - - 200}
# 2022-06-20
# forum/forumpost/6650cd40b5634f35
#
reset_db
do_execsql_test join8-25000 {
CREATE TABLE t1(a1 INT);
CREATE TABLE t2(b2 INT);
CREATE TABLE t3(c3 INT, d3 INT UNIQUE);
CREATE TABLE t4(e4 INT, f4 TEXT);
INSERT INTO t3(c3, d3) VALUES (2, 1);
INSERT INTO t4(f4) VALUES ('x');
CREATE INDEX i0 ON t3(c3) WHERE d3 ISNULL;
ANALYZE main;
}
db null -
do_execsql_test join8-25010 {
SELECT * FROM t1 LEFT JOIN t2 ON true JOIN t3 ON (b2 IN (a1)) FULL JOIN t4 ON true;
} {- - - - - x}
do_execsql_test join8-25020 {
SELECT 1 FROM t1 LEFT JOIN t2 ON true JOIN t3 ON (b2 IN (a1)) FULL JOIN t4 ON true;
} {1}
finish_test
|