SQLite

Check-in [c7e3a13a]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Ensure that ON clauses are applied to the correct outer join.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c7e3a13a3288c577209be99c630fbe924e19880e8af1aa8a83b517acaa8b43d7
User & Date: drh 2022-05-12 11:56:44
Context
2022-05-12
17:09
Fix a harmless compiler warning in the CLI. (check-in: 7a2ac303 user: drh tags: trunk)
11:56
Ensure that ON clauses are applied to the correct outer join. (check-in: c7e3a13a user: drh tags: trunk)
11:45
Add IS NOT DISTINCT FROM and IS DISTINCT FROM binary operators which are equivalent to IS and IS NOT, respectively, for compatability with PostgreSQL and hence standard SQL. (check-in: db27611e user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

︙︙
2617
2618
2619
2620
2621
2622
2623
2624

2625
2626
2627
2628
2629
2630
2631

            && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 );
        pWInfo->untestedTerms = 1;
        continue;
      }
      pE = pTerm->pExpr;
      assert( pE!=0 );
      if( (pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ))
       && !ExprHasProperty(pE,EP_FromJoin|EP_InnerJoin)

      ){
        continue;
      }
      
      if( iLoop==1 && !sqlite3ExprCoveredByIndex(pE, pLevel->iTabCur, pIdx) ){
        iNext = 2;
        continue;








|
>








2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632

            && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 );
        pWInfo->untestedTerms = 1;
        continue;
      }
      pE = pTerm->pExpr;
      assert( pE!=0 );
      if( (pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ))
       && (!ExprHasProperty(pE,EP_FromJoin|EP_InnerJoin)
             || pE->w.iJoin!=pTabItem->iCursor)
      ){
        continue;
      }
      
      if( iLoop==1 && !sqlite3ExprCoveredByIndex(pE, pLevel->iTabCur, pIdx) ){
        iNext = 2;
        continue;
︙︙

Changes to test/join8.test.

︙︙
255
256
257
258
259
260
261























262
263

    SELECT t1.*, t2.*, t3.*
      FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0
        RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0
  )
  SELECT * FROM t0 FULL JOIN t4 ON t0.a=t4.d AND t4.z>0
   ORDER BY coalesce(t0.a, t0.y+200, t4.d);
} {/.*BLOOM FILTER ON t2.*BLOOM FILTER ON t3.*BLOOM FILTER ON t4.*/}
























finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



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

    SELECT t1.*, t2.*, t3.*
      FROM t1 INNER JOIN t2 ON t1.b=t2.b AND t2.x>0
        RIGHT JOIN t3 ON t1.c=t3.c AND t3.y>0
  )
  SELECT * FROM t0 FULL JOIN t4 ON t0.a=t4.d AND t4.z>0
   ORDER BY coalesce(t0.a, t0.y+200, t4.d);
} {/.*BLOOM FILTER ON t2.*BLOOM FILTER ON t3.*BLOOM FILTER ON t4.*/}

# 2022-05-12 Difference with PG found (by Dan) while exploring
# https://sqlite.org/forum/forumpost/677a0ab93fcd9ccd
#
reset_db
do_execsql_test join8-8000 {
  CREATE TABLE t1(a INT, b INT);
  CREATE TABLE t2(c INT, d INT);
  CREATE TABLE t3(e INT, f INT);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t2 VALUES(3, 4);
  INSERT INTO t3 VALUES(5, 6);
} {}
do_execsql_test join8-8010 {
  SELECT *
    FROM t3 LEFT JOIN t2 ON true
            JOIN t1 ON (t3.e IS t2.c);
} {}
do_execsql_test join8-8020 {
  SELECT *
    FROM t3 LEFT JOIN t2 ON true
            JOIN t1 ON (t3.e IS NOT DISTINCT FROM t2.c);
} {}

finish_test