/ Check-in [0d9edfab]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:If a query contains "FROM t1 LEFT JOIN t2, t3, t4", ensure that tables t3 and t4 are not scanned before t2. The trunk already does this.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | vtab-left-join
Files: files | file ages | folders
SHA1: 0d9edfab9fb61322620f188b48ae2a1798a07581
User & Date: dan 2015-06-08 18:48:29
Context
2015-06-09
10:58
Remove some repeated lines of source code. Probably introduced by careless cut'n'pasting. Closed-Leaf check-in: a34cd71c user: dan tags: vtab-left-join
2015-06-08
18:48
If a query contains "FROM t1 LEFT JOIN t2, t3, t4", ensure that tables t3 and t4 are not scanned before t2. The trunk already does this. check-in: 0d9edfab user: dan tags: vtab-left-join
18:05
Avoid passing constraints that are unusable due to LEFT or CROSS joins to virtual table xBestIndex() methods. check-in: 80ee56dd user: dan tags: vtab-left-join
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

2993
2994
2995
2996
2997
2998
2999

3000
3001
3002
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015

3016
3017
3018
3019
3020
3021
3022
  int iTab;
  SrcList *pTabList = pWInfo->pTabList;
  struct SrcList_item *pItem;
  struct SrcList_item *pEnd = &pTabList->a[pWInfo->nLevel];
  sqlite3 *db = pWInfo->pParse->db;
  int rc = SQLITE_OK;
  WhereLoop *pNew;


  pNew = pBuilder->pNew;
  whereLoopInit(pNew);

  /* Loop over the tables in the join, from left to right */
  pNew = pBuilder->pNew;
  whereLoopInit(pNew);
  for(iTab=0, pItem=pTabList->a; pItem<pEnd; iTab++, pItem++){
    Bitmask mUnusable = 0;
    pNew->iTab = iTab;
    pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor);
    if( (pItem->jointype & (JT_LEFT|JT_CROSS))!=0 ){
      /* This condition is true when pItem is the FROM clause term on the
      ** right-hand-side of a LEFT or CROSS JOIN.  */
      mExtra = mPrior;
    }

    if( IsVirtual(pItem->pTab) ){
      struct SrcList_item *p;
      for(p=&pItem[1]; p<pEnd; p++){
        if( mUnusable || (p->jointype & (JT_LEFT|JT_CROSS)) ){
          mUnusable |= sqlite3WhereGetMask(&pWInfo->sMaskSet, p->iCursor);
        }
      }







>











|




>







2993
2994
2995
2996
2997
2998
2999
3000
3001
3002
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
3024
  int iTab;
  SrcList *pTabList = pWInfo->pTabList;
  struct SrcList_item *pItem;
  struct SrcList_item *pEnd = &pTabList->a[pWInfo->nLevel];
  sqlite3 *db = pWInfo->pParse->db;
  int rc = SQLITE_OK;
  WhereLoop *pNew;
  u8 priorJointype = 0;

  pNew = pBuilder->pNew;
  whereLoopInit(pNew);

  /* Loop over the tables in the join, from left to right */
  pNew = pBuilder->pNew;
  whereLoopInit(pNew);
  for(iTab=0, pItem=pTabList->a; pItem<pEnd; iTab++, pItem++){
    Bitmask mUnusable = 0;
    pNew->iTab = iTab;
    pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor);
    if( ((pItem->jointype|priorJointype) & (JT_LEFT|JT_CROSS))!=0 ){
      /* This condition is true when pItem is the FROM clause term on the
      ** right-hand-side of a LEFT or CROSS JOIN.  */
      mExtra = mPrior;
    }
    priorJointype = pItem->jointype;
    if( IsVirtual(pItem->pTab) ){
      struct SrcList_item *p;
      for(p=&pItem[1]; p<pEnd; p++){
        if( mUnusable || (p->jointype & (JT_LEFT|JT_CROSS)) ){
          mUnusable |= sqlite3WhereGetMask(&pWInfo->sMaskSet, p->iCursor);
        }
      }

Changes to test/join.test.

682
683
684
685
686
687
688
689


























690
  if {[lsearch [db eval {PRAGMA compile_options}] MEMDEBUG]<0} {
    jointest join-12.10 65534 {1 {at most 64 tables in a join}}
    jointest join-12.11 65535 {1 {too many references to "t14": max 65535}}
    jointest join-12.12 65536 {1 {too many references to "t14": max 65535}}
    jointest join-12.13 65537 {1 {too many references to "t14": max 65535}}
  }
}



























finish_test








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

682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
  if {[lsearch [db eval {PRAGMA compile_options}] MEMDEBUG]<0} {
    jointest join-12.10 65534 {1 {at most 64 tables in a join}}
    jointest join-12.11 65535 {1 {too many references to "t14": max 65535}}
    jointest join-12.12 65536 {1 {too many references to "t14": max 65535}}
    jointest join-12.13 65537 {1 {too many references to "t14": max 65535}}
  }
}


#-------------------------------------------------------------------------
# Test a problem with reordering tables following a LEFT JOIN.
#
do_execsql_test join-13.0 {
  CREATE TABLE aa(a);
  CREATE TABLE bb(b);
  CREATE TABLE cc(c);

  INSERT INTO aa VALUES(45);
  INSERT INTO cc VALUES(45);
  INSERT INTO cc VALUES(45);
}

do_execsql_test join-13.1 {
  SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
} {45 {} 45 45 {} 45}

# In the following, the order of [cc] and [bb] must not be exchanged, even
# though this would be helpful if the query used an inner join.
do_execsql_test join-13.2 {
  CREATE INDEX ccc ON cc(c);
  SELECT * FROM aa LEFT JOIN bb, cc WHERE cc.c=aa.a;
} {45 {} 45 45 {} 45}


finish_test