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: |
c75eee69fa8a9b56ee58a4cc539e80cc |
| 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
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
|