SQLite

Check-in [d8d89d69a4]
Login

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

Overview
Comment:Fix a problem in the previous commit affecting queries with three or more tables joined together to the right of a LEFT or CROSS JOIN operator.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d8d89d69a490a708b83147945f74134ae0e4b387
User & Date: dan 2016-03-05 21:32:14.419
Context
2016-03-07
17:37
Add the SQLITE_CONFIG_STMTJRNL_SPILL option for sqlite3_config(). (check-in: b6c4202432 user: drh tags: trunk)
2016-03-05
21:32
Fix a problem in the previous commit affecting queries with three or more tables joined together to the right of a LEFT or CROSS JOIN operator. (check-in: d8d89d69a4 user: dan tags: trunk)
21:07
Change the way SQLite invokes the xBestIndex method of virtual tables so that N-way joins involving virtual tables work as expected. (check-in: 195444380b user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
2990
2991
2992
2993
2994
2995
2996
2997
2998
2999
3000
3001
3002
3003
3004

            pWC->a[p->aConstraint[i].iTermOffset].prereqRight & ~mExtra
        );
        if( mThis>mPrev && mThis<mNext ) mNext = mThis;
      }
      mPrev = mNext;
      if( mNext==(Bitmask)(-1) ) break;
      if( mNext==mBest || mNext==mBestNoIn ) continue;
      rc = whereLoopAddVirtualOne(pBuilder, mExtra, mNext, 0, p, &bIn);
      if( pNew->prereq==mExtra ){
        seenZero = 1;
        if( bIn==0 ) seenZeroNoIN = 1;
      }
    }

    /* If the calls to xBestIndex() in the above loop did not find a plan








|








2990
2991
2992
2993
2994
2995
2996
2997
2998
2999
3000
3001
3002
3003
3004

            pWC->a[p->aConstraint[i].iTermOffset].prereqRight & ~mExtra
        );
        if( mThis>mPrev && mThis<mNext ) mNext = mThis;
      }
      mPrev = mNext;
      if( mNext==(Bitmask)(-1) ) break;
      if( mNext==mBest || mNext==mBestNoIn ) continue;
      rc = whereLoopAddVirtualOne(pBuilder, mExtra, mNext|mExtra, 0, p, &bIn);
      if( pNew->prereq==mExtra ){
        seenZero = 1;
        if( bIn==0 ) seenZeroNoIN = 1;
      }
    }

    /* If the calls to xBestIndex() in the above loop did not find a plan
︙︙
Changes to test/bestindex2.test.
︙︙
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124













125
126
127

  SELECT * FROM t1, t2 CROSS JOIN t3 WHERE t2.c = +t1.b AND t3.e=t2.d
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
  0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
  0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
}

# This is the one that fails (as of 2016/3/3).
#
do_eqp_test 1.6 {
  SELECT * FROM t1, t2, t3 WHERE t2.c = +t1.b AND t3.e = t2.d
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
  0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
  0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
}














finish_test









<
<







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




109
110
111
112
113
114
115


116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138

  SELECT * FROM t1, t2 CROSS JOIN t3 WHERE t2.c = +t1.b AND t3.e=t2.d
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
  0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
  0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
}



do_eqp_test 1.6 {
  SELECT * FROM t1, t2, t3 WHERE t2.c = +t1.b AND t3.e = t2.d
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
  0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
  0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
}

do_execsql_test 1.7.1 {
  CREATE TABLE x1(a, b);
}
do_eqp_test 1.7.2 {
  SELECT * FROM x1 CROSS JOIN t1, t2, t3 
    WHERE t1.a = t2.c AND t1.b = t3.e
} {
  0 0 0 {SCAN TABLE x1} 
  0 1 1 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:}
  0 2 2 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
  0 3 3 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
}

finish_test