/ Check-in [c75eee69]
Login
☰Home Timeline Branches

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

Overview
Comment:Enhance EXPLAIN QUERY PLAN to report the generation of constant rows using VALUES or just a SELECT without FROM.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rework-EQP
Files: files | file ages | folders
SHA3-256: c75eee69fa8a9b56ee58a4cc539e80cc982f43390dc3a357344d58479dd89a41
User & Date: drh 2018-05-03 01:37:13
Context
2018-05-03
19:47
Optimizations to the new EQP framework. (Closed-Leaf check-in: 956fef36 user: drh tags: rework-EQP)
01:37
Enhance EXPLAIN QUERY PLAN to report the generation of constant rows using VALUES or just a SELECT without FROM. (check-in: c75eee69 user: drh tags: rework-EQP)
2018-05-02
19:42
More test case updates. Tests are all running now. (check-in: dab5e529 user: drh tags: rework-EQP)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c. 

2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379

2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390

2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403

** Since the limit is exactly 1, we only need to evalutes the left-most VALUES.
*/
static int multiSelectValues(
  Parse *pParse,        /* Parsing context */
  Select *p,            /* The right-most of SELECTs to be coded */
  SelectDest *pDest     /* What to do with query results */
){
  Select *pPrior;
  Select *pRightmost = p;
  int nRow = 1;
  int rc = 0;

  assert( p->selFlags & SF_MultiValue );
  do{
    assert( p->selFlags & SF_Values );
    assert( p->op==TK_ALL || (p->op==TK_SELECT && p->pPrior==0) );
    assert( p->pNext==0 || p->pEList->nExpr==p->pNext->pEList->nExpr );
    if( p->pPrior==0 ) break;
    assert( p->pPrior->pNext==p );
    p = p->pPrior;
    nRow++;
  }while(1);
  ExplainQueryPlan((pParse, 0, "SCAN %d CONSTANT ROWS", nRow));

  while( p ){
    pPrior = p->pPrior;
    p->pPrior = 0;
    rc = sqlite3Select(pParse, p, pDest);
    p->pPrior = pPrior;
    if( rc || pRightmost->pLimit ) break;
    p->nSelectRow = nRow;
    p = p->pNext;
  }
  return rc;
}

/*








<
<


>








|

|
>

<
|
|
<
<








2369
2370
2371
2372
2373
2374
2375


2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391

2392
2393


2394
2395
2396
2397
2398
2399
2400

** Since the limit is exactly 1, we only need to evalutes the left-most VALUES.
*/
static int multiSelectValues(
  Parse *pParse,        /* Parsing context */
  Select *p,            /* The right-most of SELECTs to be coded */
  SelectDest *pDest     /* What to do with query results */
){


  int nRow = 1;
  int rc = 0;
  int bShowAll = p->pLimit==0;
  assert( p->selFlags & SF_MultiValue );
  do{
    assert( p->selFlags & SF_Values );
    assert( p->op==TK_ALL || (p->op==TK_SELECT && p->pPrior==0) );
    assert( p->pNext==0 || p->pEList->nExpr==p->pNext->pEList->nExpr );
    if( p->pPrior==0 ) break;
    assert( p->pPrior->pNext==p );
    p = p->pPrior;
    nRow += bShowAll;
  }while(1);
  ExplainQueryPlan((pParse, 0, "SCAN %d CONSTANT ROW%s", nRow,
                    nRow==1 ? "" : "S"));
  while( p ){

    selectInnerLoop(pParse, p, -1, 0, 0, pDest, 1, 1);
    if( !bShowAll ) break;


    p->nSelectRow = nRow;
    p = p->pNext;
  }
  return rc;
}

/*

Changes to src/where.c. 

4588
4589
4590
4591
4592
4593
4594
4595
4596
4597
4598
4599
4600
4601
4602

  /* Special case: No FROM clause
  */
  if( nTabList==0 ){
    if( pOrderBy ) pWInfo->nOBSat = pOrderBy->nExpr;
    if( wctrlFlags & WHERE_WANT_DISTINCT ){
      pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
    }
    /* ExplainQueryPlan((pParse, 0, "SCAN CONSTANT ROW")); */
  }else{
    /* Assign a bit from the bitmask to every term in the FROM clause.
    **
    ** The N-th term of the FROM clause is assigned a bitmask of 1<<N.
    **
    ** The rule of the previous sentence ensures thta if X is the bitmask for
    ** a table T, then X-1 is the bitmask for all other tables to the left of T.








|








4588
4589
4590
4591
4592
4593
4594
4595
4596
4597
4598
4599
4600
4601
4602

  /* Special case: No FROM clause
  */
  if( nTabList==0 ){
    if( pOrderBy ) pWInfo->nOBSat = pOrderBy->nExpr;
    if( wctrlFlags & WHERE_WANT_DISTINCT ){
      pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
    }
    ExplainQueryPlan((pParse, 0, "SCAN CONSTANT ROW"));
  }else{
    /* Assign a bit from the bitmask to every term in the FROM clause.
    **
    ** The N-th term of the FROM clause is assigned a bitmask of 1<<N.
    **
    ** The rule of the previous sentence ensures thta if X is the bitmask for
    ** a table T, then X-1 is the bitmask for all other tables to the left of T.

Changes to test/eqp.test. 

85
86
87
88
89
90
91

92
93
94
95
96
97
98
99
100
101

102

103
104
105
106
107
108
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
139
140
141
142
143
144

}

do_eqp_test 1.7 {
  SELECT * FROM t3 JOIN (SELECT 1)
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx

  |--SCAN SUBQUERY xxxxxx
  `--SCAN TABLE t3
}
do_eqp_test 1.8 {
  SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2)
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  `--COMPOUND QUERY
  |     |--LEFT-MOST SUBQUERY

  |     `--UNION USING TEMP B-TREE

  |--SCAN SUBQUERY xxxxxx
  `--SCAN TABLE t3
}
do_eqp_test 1.9 {
  SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17)
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  `--COMPOUND QUERY
  |     |--LEFT-MOST SUBQUERY

  |     `--EXCEPT USING TEMP B-TREE
  |        `--SCAN TABLE t3
  |--SCAN SUBQUERY xxxxxx
  `--SCAN TABLE t3
}
do_eqp_test 1.10 {
  SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17)
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  `--COMPOUND QUERY
  |     |--LEFT-MOST SUBQUERY

  |     `--INTERSECT USING TEMP B-TREE
  |        `--SCAN TABLE t3
  |--SCAN SUBQUERY xxxxxx
  `--SCAN TABLE t3
}

do_eqp_test 1.11 {
  SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17)
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  `--COMPOUND QUERY
  |     |--LEFT-MOST SUBQUERY

  |     `--UNION ALL
  |        `--SCAN TABLE t3
  |--SCAN SUBQUERY xxxxxx
  `--SCAN TABLE t3
}

#-------------------------------------------------------------------------








>










>

>










>












>













>








85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
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
139
140
141
142
143
144
145
146
147
148
149
150

}

do_eqp_test 1.7 {
  SELECT * FROM t3 JOIN (SELECT 1)
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  `--SCAN CONSTANT ROW
  |--SCAN SUBQUERY xxxxxx
  `--SCAN TABLE t3
}
do_eqp_test 1.8 {
  SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2)
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  `--COMPOUND QUERY
  |     |--LEFT-MOST SUBQUERY
  |     |  `--SCAN CONSTANT ROW
  |     `--UNION USING TEMP B-TREE
  |        `--SCAN CONSTANT ROW
  |--SCAN SUBQUERY xxxxxx
  `--SCAN TABLE t3
}
do_eqp_test 1.9 {
  SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17)
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  `--COMPOUND QUERY
  |     |--LEFT-MOST SUBQUERY
  |     |  `--SCAN CONSTANT ROW
  |     `--EXCEPT USING TEMP B-TREE
  |        `--SCAN TABLE t3
  |--SCAN SUBQUERY xxxxxx
  `--SCAN TABLE t3
}
do_eqp_test 1.10 {
  SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17)
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  `--COMPOUND QUERY
  |     |--LEFT-MOST SUBQUERY
  |     |  `--SCAN CONSTANT ROW
  |     `--INTERSECT USING TEMP B-TREE
  |        `--SCAN TABLE t3
  |--SCAN SUBQUERY xxxxxx
  `--SCAN TABLE t3
}

do_eqp_test 1.11 {
  SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17)
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  `--COMPOUND QUERY
  |     |--LEFT-MOST SUBQUERY
  |     |  `--SCAN CONSTANT ROW
  |     `--UNION ALL
  |        `--SCAN TABLE t3
  |--SCAN SUBQUERY xxxxxx
  `--SCAN TABLE t3
}

#-------------------------------------------------------------------------

Changes to test/orderby1.test. 

450
451
452
453
454
455
456
457
458
459



460
461
462
463
464
465
466

    
    SELECT b, y FROM t41 CROSS JOIN t42 ON x=a ORDER BY b, y;
  }
} {1 13 1 14 1 15 1 16}

# No sorting of queries that omit the FROM clause.
#
do_execsql_test 5.0 {
  EXPLAIN QUERY PLAN SELECT 5 ORDER BY 1
} {}



do_execsql_test 5.1 {
  EXPLAIN QUERY PLAN SELECT 5 UNION ALL SELECT 3 ORDER BY 1
} {~/B-TREE/}
do_execsql_test 5.2 {
  SELECT 5 UNION ALL SELECT 3 ORDER BY 1
} {3 5}
do_execsql_test 5.3 {








|
|
|
>
>
>








450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469

    
    SELECT b, y FROM t41 CROSS JOIN t42 ON x=a ORDER BY b, y;
  }
} {1 13 1 14 1 15 1 16}

# No sorting of queries that omit the FROM clause.
#
do_eqp_test 5.0 {
  SELECT 5 ORDER BY 1
} {
  QUERY PLAN
  `--SCAN CONSTANT ROW
}
do_execsql_test 5.1 {
  EXPLAIN QUERY PLAN SELECT 5 UNION ALL SELECT 3 ORDER BY 1
} {~/B-TREE/}
do_execsql_test 5.2 {
  SELECT 5 UNION ALL SELECT 3 ORDER BY 1
} {3 5}
do_execsql_test 5.3 {

Changes to test/with1.test. 

1001
1002
1003
1004
1005
1006
1007

1008
1009
1010
1011
1012
1013
1014

    x1(a) AS (values(100))
  INSERT INTO t1(x)
    SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2);
  SELECT * FROM t1;
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx

  |--SCAN SUBQUERY xxxxxx
  `--SCAN SUBQUERY xxxxxx
}

# 2017-10-28.
# See check-in https://sqlite.org/src/info/0926df095faf72c2
# Tried to optimize co-routine processing by changing a Copy opcode








>








1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015

    x1(a) AS (values(100))
  INSERT INTO t1(x)
    SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2);
  SELECT * FROM t1;
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  `--SCAN CONSTANT ROW
  |--SCAN SUBQUERY xxxxxx
  `--SCAN SUBQUERY xxxxxx
}

# 2017-10-28.
# See check-in https://sqlite.org/src/info/0926df095faf72c2
# Tried to optimize co-routine processing by changing a Copy opcode

Changes to test/with3.test. 

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
105
...
113
114
115
116
117
118
119

120
121
122
123
124
125
126
127
128
129
130

  do_eqp_test 3.1.2 {
    WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1)
    SELECT * FROM cnt, y1 WHERE i=a
  } [string map {"\n  " \n} {
    QUERY PLAN
    |--MATERIALIZE xxxxxx
    |  |--SETUP

    |  `--RECURSIVE STEP
    |     `--SCAN TABLE cnt
    |--SCAN SUBQUERY xxxxxx
    `--SEARCH TABLE y1 USING INDEX y1a (a=?)
  }]

  do_eqp_test 3.1.3 {
    WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000)
    SELECT * FROM cnt, y1 WHERE i=a
  } [string map {"\n  " \n} {
    QUERY PLAN
    |--MATERIALIZE xxxxxx
    |  |--SETUP

    |  `--RECURSIVE STEP
    |     `--SCAN TABLE cnt
    |--SCAN TABLE y1
    `--SEARCH SUBQUERY xxxxxx USING AUTOMATIC COVERING INDEX (i=?)
  }]
}

................................................................................
     UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1)
     SELECT * FROM c, w2, w1
     WHERE c.id=w2.pk AND c.id=w1.pk;
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  |--SETUP

  |  |  `--SCALAR SUBQUERY
  |  |     `--SCAN TABLE w2
  |  `--RECURSIVE STEP
  |     |--SCAN TABLE w1
  |     `--SCAN TABLE c
  |--SCAN SUBQUERY xxxxxx
  |--SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)
  `--SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)
}

finish_test








>













>







 







>












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
105
106
107
...
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133

  do_eqp_test 3.1.2 {
    WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1)
    SELECT * FROM cnt, y1 WHERE i=a
  } [string map {"\n  " \n} {
    QUERY PLAN
    |--MATERIALIZE xxxxxx
    |  |--SETUP
    |  |  `--SCAN CONSTANT ROW
    |  `--RECURSIVE STEP
    |     `--SCAN TABLE cnt
    |--SCAN SUBQUERY xxxxxx
    `--SEARCH TABLE y1 USING INDEX y1a (a=?)
  }]

  do_eqp_test 3.1.3 {
    WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000)
    SELECT * FROM cnt, y1 WHERE i=a
  } [string map {"\n  " \n} {
    QUERY PLAN
    |--MATERIALIZE xxxxxx
    |  |--SETUP
    |  |  `--SCAN CONSTANT ROW
    |  `--RECURSIVE STEP
    |     `--SCAN TABLE cnt
    |--SCAN TABLE y1
    `--SEARCH SUBQUERY xxxxxx USING AUTOMATIC COVERING INDEX (i=?)
  }]
}

................................................................................
     UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1)
     SELECT * FROM c, w2, w1
     WHERE c.id=w2.pk AND c.id=w1.pk;
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  |--SETUP
  |  |  |--SCAN CONSTANT ROW
  |  |  `--SCALAR SUBQUERY
  |  |     `--SCAN TABLE w2
  |  `--RECURSIVE STEP
  |     |--SCAN TABLE w1
  |     `--SCAN TABLE c
  |--SCAN SUBQUERY xxxxxx
  |--SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)
  `--SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)
}

finish_test

This page was generated in about 0.021s by Fossil 2.12 [2ae6b1cc01] 2020-07-05 14:42:06