/ Check-in [9dbae1df]
Login

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

Overview
Comment:Do not use the flattening optimization for a sub-query on the RHS of a LEFT JOIN if that subquery reads data from a virtual table. Because it prevents the planner from creating an automatic index on the results of the sub-query, the flattening optimization sometimes slows things down in this case.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:9dbae1df75219e2accd8993015ce0ffc21966d7e69d04178b1a2cc46207fe260
User & Date: dan 2017-07-18 17:34:41
Context
2017-07-18
18:19
Add the union-vtab extension - still experimental and under active development. check-in: 948f9504 user: drh tags: trunk
17:34
Do not use the flattening optimization for a sub-query on the RHS of a LEFT JOIN if that subquery reads data from a virtual table. Because it prevents the planner from creating an automatic index on the results of the sub-query, the flattening optimization sometimes slows things down in this case. check-in: 9dbae1df user: dan tags: trunk
17:13
Avoid creating an automatic index on the RHS of a LEFT JOIN to optimize an IS term. Fix for [ce68383b]. check-in: d2f0b2e8 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

3335
3336
3337
3338
3339
3340
3341
3342

3343
3344
3345
3346
3347
3348
3349
3350
....
3541
3542
3543
3544
3545
3546
3547
3548
3549
3550
3551
3552
3553
3554
3555
**
**   (2)  The subquery is not an aggregate or (2a) the outer query is not a join
**        and (2b) the outer query does not use subqueries other than the one
**        FROM-clause subquery that is a candidate for flattening.  (2b is
**        due to ticket [2f7170d73bf9abf80] from 2015-02-09.)
**
**   (3)  The subquery is not the right operand of a LEFT JOIN
**        or the subquery is not itself a join and the outer query is not

**        an aggregate.
**
**   (4)  The subquery is not DISTINCT.
**
**  (**)  At one point restrictions (4) and (5) defined a subset of DISTINCT
**        sub-queries that were excluded from this optimization. Restriction 
**        (4) has since been expanded to exclude all DISTINCT subqueries.
**
................................................................................
  ** are processed - there is no mechanism to determine if the LEFT JOIN
  ** table should be all-NULL.
  **
  ** See also tickets #306, #350, and #3300.
  */
  if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
    isLeftJoin = 1;
    if( pSubSrc->nSrc>1 || isAgg ){
      return 0; /* Restriction (3) */
    }
  }
#ifdef SQLITE_EXTRA_IFNULLROW
  else if( iFrom>0 && !isAgg ){
    /* Setting isLeftJoin to -1 causes OP_IfNullRow opcodes to be generated for
    ** every reference to any result column from subquery in a join, even though







|
>
|







 







|







3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
....
3542
3543
3544
3545
3546
3547
3548
3549
3550
3551
3552
3553
3554
3555
3556
**
**   (2)  The subquery is not an aggregate or (2a) the outer query is not a join
**        and (2b) the outer query does not use subqueries other than the one
**        FROM-clause subquery that is a candidate for flattening.  (2b is
**        due to ticket [2f7170d73bf9abf80] from 2015-02-09.)
**
**   (3)  The subquery is not the right operand of a LEFT JOIN
**        or (a) the subquery is not itself a join and (b) the FROM clause
**        of the subquery does not contain a virtual table and (c) the 
**        outer query is not an aggregate.
**
**   (4)  The subquery is not DISTINCT.
**
**  (**)  At one point restrictions (4) and (5) defined a subset of DISTINCT
**        sub-queries that were excluded from this optimization. Restriction 
**        (4) has since been expanded to exclude all DISTINCT subqueries.
**
................................................................................
  ** are processed - there is no mechanism to determine if the LEFT JOIN
  ** table should be all-NULL.
  **
  ** See also tickets #306, #350, and #3300.
  */
  if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
    isLeftJoin = 1;
    if( pSubSrc->nSrc>1 || isAgg || IsVirtual(pSubSrc->a[0].pTab) ){
      return 0; /* Restriction (3) */
    }
  }
#ifdef SQLITE_EXTRA_IFNULLROW
  else if( iFrom>0 && !isAgg ){
    /* Setting isLeftJoin to -1 causes OP_IfNullRow opcodes to be generated for
    ** every reference to any result column from subquery in a join, even though

Changes to test/fts3join.test.

76
77
78
79
80
81
82



83

















84
  ) ON tt.a = rrr
} {1 1 abc 2 {} {}}

do_execsql_test 3.2 {
  SELECT * FROM tt LEFT JOIN vt ON (vt MATCH 'abc')
} {1 abc 2 abc}






















finish_test







>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
  ) ON tt.a = rrr
} {1 1 abc 2 {} {}}

do_execsql_test 3.2 {
  SELECT * FROM tt LEFT JOIN vt ON (vt MATCH 'abc')
} {1 abc 2 abc}

#-------------------------------------------------------------------------
# Test that queries of the form found in test case 4.2 use an automatic
# index to avoid running multiple fts queries.
#
do_execsql_test 4.1 {
  CREATE VIRTUAL TABLE ft4 USING fts3(x);
  CREATE TABLE t4(y, z);
  CREATE INDEX t4y ON t1(y);
}

do_eqp_test 4.2 {
  SELECT * FROM t4 LEFT JOIN (
      SELECT docid, * FROM ft4 WHERE ft4 MATCH ?
  ) AS rr ON t4.rowid=rr.docid 
  WHERE t4.y = ?;
} {
  1 0 0 {SCAN TABLE ft4 VIRTUAL TABLE INDEX 3:} 
  0 0 0 {SCAN TABLE t4}
  0 1 1 {SEARCH SUBQUERY 1 AS rr USING AUTOMATIC COVERING INDEX (docid=?)}
}

finish_test