SQLite

Check-in [289daf6cee]
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 | forum-5c8a069d23-fix
Files: files | file ages | folders
SHA3-256: 289daf6cee39625e8f068179cd58efcc1d28242f46064e58ec4175a019cf48ad
User & Date: dan 2024-12-09 11:12:12.968
Context
2024-12-09
11:37
Fix an obscure problem with multiple outer joins, ON clauses and query flattening. Forum thread 5c8a069d23. (check-in: a350ea7c6b 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: 289daf6cee user: dan tags: forum-5c8a069d23-fix)
2024-12-07
14:48
On x64 hardware, round-trip uint64_t→double→uint64_t conversions fail for values greater than UINT64_MAX-2047. This caused the SQLite text-to-float converter routine to give incorrect results for values between '1.8446744073709550592eNNN' and '1.8446744073709551609eNNN' for any exponent NNN. This problem was introduced by check-in [761d8fd18b0ee868] and first appeared in version 3.47.0 and was reported by forum post 569a7209179a7f5e. Fixed by this check-in. (check-in: 81342fa6dd 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