SQLite

Check-in [a350ea7c]
Login

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

Overview
Comment:Fix an obscure problem with multiple outer joins, ON clauses and query flattening. Forum thread 5c8a069d23.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: a350ea7c6b89725ba1a0058c77fc8e918f5cb78e868d100f5425cdee114d1320
User & Date: dan 2024-12-09 11:37:37
References
2024-12-09
13:02
Add NEVER() around branches that seems to have been made obsolete by [a350ea7c6b89725b]. (check-in: cb5bad5c user: drh tags: trunk)
Context
2024-12-09
11:47
Further improvements to the decimal-to-float conversion for values close to LARGEST_UINT64. (check-in: 453c9499 user: drh tags: trunk)
11:37
Fix an obscure problem with multiple outer joins, ON clauses and query flattening. Forum thread 5c8a069d23. (check-in: a350ea7c user: dan tags: trunk)
11:12
Fix an obscure problem with multiple outer joins, ON clauses and query flattening. Forum thread 5c8a069d23. (Closed-Leaf check-in: 289daf6c user: dan tags: forum-5c8a069d23-fix)
10:57
Add a note in Makefile.msc about EXTRA_SRC files possibly requiring manual editing. (check-in: a0a36bad user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
︙︙
4669
4670
4671
4672
4673
4674
4675

4676
4677
4678
4679
4680
4681
4682
4683
4684
4685
4686
4687
4688
4689
4690
4691
4692

      if( pSrc==0 ) break;
      pParent->pSrc = pSrc;
    }

    /* Transfer the FROM clause terms from the subquery into the
    ** outer query.
    */

    for(i=0; i<nSubSrc; i++){
      SrcItem *pItem = &pSrc->a[i+iFrom];
      assert( pItem->fg.isTabFunc==0 );
      assert( pItem->fg.isSubquery
           || pItem->fg.fixedSchema
           || pItem->u4.zDatabase==0 );
      if( pItem->fg.isUsing ) sqlite3IdListDelete(db, pItem->u3.pUsing);
      *pItem = pSubSrc->a[i];
      pItem->fg.jointype |= ltorj;
      iNewParent = pSubSrc->a[i].iCursor;
      memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
    }
    pSrc->a[iFrom].fg.jointype &= JT_LTORJ;
    pSrc->a[iFrom].fg.jointype |= jointype | ltorj;
 
    /* Now begin substituting subquery result set expressions for
    ** references to the iParent in the outer query.








>









<








4669
4670
4671
4672
4673
4674
4675
4676
4677
4678
4679
4680
4681
4682
4683
4684
4685

4686
4687
4688
4689
4690
4691
4692

      if( pSrc==0 ) break;
      pParent->pSrc = pSrc;
    }

    /* Transfer the FROM clause terms from the subquery into the
    ** outer query.
    */
    iNewParent = pSubSrc->a[0].iCursor;
    for(i=0; i<nSubSrc; i++){
      SrcItem *pItem = &pSrc->a[i+iFrom];
      assert( pItem->fg.isTabFunc==0 );
      assert( pItem->fg.isSubquery
           || pItem->fg.fixedSchema
           || pItem->u4.zDatabase==0 );
      if( pItem->fg.isUsing ) sqlite3IdListDelete(db, pItem->u3.pUsing);
      *pItem = pSubSrc->a[i];
      pItem->fg.jointype |= ltorj;

      memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
    }
    pSrc->a[iFrom].fg.jointype &= JT_LTORJ;
    pSrc->a[iFrom].fg.jointype |= jointype | ltorj;
 
    /* Now begin substituting subquery result set expressions for
    ** references to the iParent in the outer query.
︙︙
4718
4719
4720
4721
4722
4723
4724

4725
4726
4727
4728
4729
4730
4731

      assert( pParent->pOrderBy==0 );
      pParent->pOrderBy = pOrderBy;
      pSub->pOrderBy = 0;
    }
    pWhere = pSub->pWhere;
    pSub->pWhere = 0;
    if( isOuterJoin>0 ){

      sqlite3SetJoinExpr(pWhere, iNewParent, EP_OuterON);
    }
    if( pWhere ){
      if( pParent->pWhere ){
        pParent->pWhere = sqlite3PExpr(pParse, TK_AND, pWhere, pParent->pWhere);
      }else{
        pParent->pWhere = pWhere;








>








4718
4719
4720
4721
4722
4723
4724
4725
4726
4727
4728
4729
4730
4731
4732

      assert( pParent->pOrderBy==0 );
      pParent->pOrderBy = pOrderBy;
      pSub->pOrderBy = 0;
    }
    pWhere = pSub->pWhere;
    pSub->pWhere = 0;
    if( isOuterJoin>0 ){
      assert( pSubSrc->nSrc==1 );
      sqlite3SetJoinExpr(pWhere, iNewParent, EP_OuterON);
    }
    if( pWhere ){
      if( pParent->pWhere ){
        pParent->pWhere = sqlite3PExpr(pParse, TK_AND, pWhere, pParent->pWhere);
      }else{
        pParent->pWhere = pWhere;
︙︙
Changes to test/select6.test.
︙︙
623
624
625
626
627
628
629







































630
631

  CREATE TABLE t2(b);
  INSERT INTO t2 VALUES(3);
  SELECT * FROM (
    SELECT * FROM (SELECT * FROM t1 LIMIT 1)
    UNION ALL
    SELECT * from t2);
} {1 3}








































finish_test








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



623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670

  CREATE TABLE t2(b);
  INSERT INTO t2 VALUES(3);
  SELECT * FROM (
    SELECT * FROM (SELECT * FROM t1 LIMIT 1)
    UNION ALL
    SELECT * from t2);
} {1 3}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 13.100 {

  CREATE  TABLE  t1(y INT);
  INSERT INTO t1 (y) VALUES (1);

  CREATE  TABLE  t2(x INTEGER);
  INSERT INTO t2 VALUES(0);

  CREATE  TABLE  empty1(z);
}

do_execsql_test 13.110 {
  SELECT t1.y 
    FROM ( SELECT 'AAA' ) 
    INNER JOIN (
      SELECT 1 AS abc FROM (
        SELECT 1 FROM t2 LEFT JOIN empty1
      )
    ) AS sub0 ON sub0.abc
    , t1
    RIGHT JOIN (SELECT 'BBB' FROM ( SELECT 'CCC' ))
} {1}

do_execsql_test 13.120 {
  SELECT t1.y 
    FROM ( SELECT 'AAA' ) 
    INNER JOIN (
      SELECT 1 AS abc FROM (
        SELECT 1 FROM t2 LEFT JOIN empty1
      )
    ) AS sub0 ON sub0.abc
    , t1
    RIGHT JOIN (SELECT 'BBB' FROM ( SELECT 'CCC' ))
    WHERE t1.y
} {1}


finish_test