/ Check-in [f5752517]
Login

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

Overview
Comment:Fix a fairly obscure problem causing the planner to sometimes choose sub-optimal plans for a query with a single virtual table in the FROM clause, and at least one IN(...) constraint in the WHERE clause.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: f5752517f590b37bfc0267650f5800320e22a8ecaba34aa6893281ce8d268026
User & Date: dan 2019-03-29 13:17:50
Context
2019-03-29
15:21
Remove the unused P5 flag from the OP_Rewind opcode. check-in: c2edbf3b user: drh tags: trunk
13:17
Fix a fairly obscure problem causing the planner to sometimes choose sub-optimal plans for a query with a single virtual table in the FROM clause, and at least one IN(...) constraint in the WHERE clause. check-in: f5752517 user: dan tags: trunk
11:48
Fix typos in the comments of the sessions extension, one of which affects the generated documentation. No code changes. check-in: 040d5d51 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3331   3331   
  3332   3332     /* First call xBestIndex() with all constraints usable. */
  3333   3333     WHERETRACE(0x800, ("BEGIN %s.addVirtual()\n", pSrc->pTab->zName));
  3334   3334     WHERETRACE(0x40, ("  VirtualOne: all usable\n"));
  3335   3335     rc = whereLoopAddVirtualOne(pBuilder, mPrereq, ALLBITS, 0, p, mNoOmit, &bIn);
  3336   3336   
  3337   3337     /* If the call to xBestIndex() with all terms enabled produced a plan
  3338         -  ** that does not require any source tables (IOW: a plan with mBest==0),
  3339         -  ** then there is no point in making any further calls to xBestIndex() 
  3340         -  ** since they will all return the same result (if the xBestIndex()
  3341         -  ** implementation is sane). */
  3342         -  if( rc==SQLITE_OK && (mBest = (pNew->prereq & ~mPrereq))!=0 ){
         3338  +  ** that does not require any source tables (IOW: a plan with mBest==0)
         3339  +  ** and does not use an IN(...) operator, then there is no point in making 
         3340  +  ** any further calls to xBestIndex() since they will all return the same
         3341  +  ** result (if the xBestIndex() implementation is sane). */
         3342  +  if( rc==SQLITE_OK && ((mBest = (pNew->prereq & ~mPrereq))!=0 || bIn) ){
  3343   3343       int seenZero = 0;             /* True if a plan with no prereqs seen */
  3344   3344       int seenZeroNoIN = 0;         /* Plan with no prereqs and no IN(...) seen */
  3345   3345       Bitmask mPrev = 0;
  3346   3346       Bitmask mBestNoIn = 0;
  3347   3347   
  3348   3348       /* If the plan produced by the earlier call uses an IN(...) term, call
  3349   3349       ** xBestIndex again, this time with IN(...) terms disabled. */

Changes to test/bestindex1.test.

   261    261     WHERE a.FlagA=0 AND a.ColumnA IN ('ValueA', 'ValueB') 
   262    262   } {
   263    263     1 0 ValueA 1 0 ValueA
   264    264     2 0 ValueA 2 0 ValueA
   265    265     3 0 ValueB 3 0 ValueB
   266    266     4 0 ValueB 4 0 ValueB
   267    267   }
          268  +
          269  +#-------------------------------------------------------------------------
          270  +# If there is an IN(..) condition in the WHERE clause of a query on a
          271  +# virtual table, the xBestIndex method is first invoked with the IN(...)
          272  +# represented by a "usable" SQLITE_INDEX_CONSTRAINT_EQ constraint. If
          273  +# the virtual table elects to use the IN(...) constraint, then the 
          274  +# xBestIndex method is invoked again, this time with the IN(...) marked
          275  +# as "not usable". Depending on the relative costs of the two plans as
          276  +# defined by the virtual table implementation, and the cardinality of the
          277  +# IN(...) operator, SQLite chooses the most efficient plan. 
          278  +#
          279  +# At one point the second invocation of xBestIndex() was only being made
          280  +# for join queries. The following tests check that this problem has been
          281  +# fixed.
          282  +#
          283  +proc vtab_command {method args} {
          284  +  switch -- $method {
          285  +    xConnect {
          286  +      return "CREATE TABLE t1(a, b, c, d)"
          287  +    }
          288  +
          289  +    xBestIndex {
          290  +      set clist [lindex $args 0]
          291  +      lappend ::bestindex_calls $clist
          292  +      set ret "cost 1000000 idxnum 555"
          293  +      for {set i 0} {$i < [llength $clist]} {incr i} {
          294  +        array set C [lindex $clist $i]
          295  +        if {$C(usable)} { lappend ret use $i }
          296  +      }
          297  +      return $ret
          298  +    }
          299  +  }
          300  +  return {}
          301  +}
          302  +
          303  +do_execsql_test 4.0 {
          304  +  CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);
          305  +} {}
          306  +
          307  +do_test 4.1 {
          308  +  set ::bestindex_calls [list]
          309  +  execsql {
          310  +    SELECT * FROM x1 WHERE a=? AND b BETWEEN ? AND ? AND c IN (1, 2, 3, 4);
          311  +  }
          312  +  set ::bestindex_calls
          313  +} [list \
          314  +    [list {op eq column 0 usable 1} \
          315  +          {op eq column 2 usable 1} \
          316  +          {op ge column 1 usable 1} \
          317  +          {op le column 1 usable 1} \
          318  +    ] \
          319  +    [list {op eq column 0 usable 1} \
          320  +          {op eq column 2 usable 0} \
          321  +          {op ge column 1 usable 1} \
          322  +          {op le column 1 usable 1}
          323  +    ]
          324  +]
   268    325   
   269    326   
   270    327   finish_test