SQLite

Check-in [238453ff]
Login

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: 238453ffab0ba1bdddb529be35da82d5e8fb312a9574003a5441f455e601a909
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

4176
4177
4178
4179
4180
4181
4182

4183

4184
4185
4186
4187








4188
4189
4190
4191
4192
4193
4194
  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))!=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. */








      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++){







>
|
>



|
>
>
>
>
>
>
>
>







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