Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enable the count-of-view optimization by default. Enhancement request [eaed8e36ce888f1e]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
a4aacdd323a854d771c8cb1e2e4cfc4f |
| User & Date: | drh 2023-02-22 21:47:02 |
Context
|
2023-02-23
| ||
| 01:52 | Provide -DHAVE_LOG2=0 and -DHAVE_LOG10=0 compile-time options for use on systems that lack the log2() and log10() standard math library routines, to cause SQLite to substitute its own alternatives. (check-in: 7ee22f95 user: drh tags: trunk) | |
|
2023-02-22
| ||
| 21:47 | Enable the count-of-view optimization by default. Enhancement request [eaed8e36ce888f1e]. (check-in: a4aacdd3 user: drh tags: trunk) | |
| 21:11 | Avoid computing the values for unused result-set columns in subqueries. Performance optimization request [baa5bb76c35a124c]. (check-in: 7c2d3406 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
| ︙ | ︙ | |||
6834 6835 6836 6837 6838 6839 6840 |
*/
static void agginfoFree(sqlite3 *db, AggInfo *p){
sqlite3DbFree(db, p->aCol);
sqlite3DbFree(db, p->aFunc);
sqlite3DbFreeNN(db, p);
}
| < | 6834 6835 6836 6837 6838 6839 6840 6841 6842 6843 6844 6845 6846 6847 |
*/
static void agginfoFree(sqlite3 *db, AggInfo *p){
sqlite3DbFree(db, p->aCol);
sqlite3DbFree(db, p->aFunc);
sqlite3DbFreeNN(db, p);
}
/*
** Attempt to transform a query of the form
**
** SELECT count(*) FROM (SELECT x FROM t1 UNION ALL SELECT y FROM t2)
**
** Into this:
**
|
| ︙ | ︙ | |||
6922 6923 6924 6925 6926 6927 6928 |
if( sqlite3TreeTrace & 0x200 ){
TREETRACE(0x200,pParse,p,("After count-of-view optimization:\n"));
sqlite3TreeViewSelect(0, p, 0);
}
#endif
return 1;
}
| < | 6921 6922 6923 6924 6925 6926 6927 6928 6929 6930 6931 6932 6933 6934 |
if( sqlite3TreeTrace & 0x200 ){
TREETRACE(0x200,pParse,p,("After count-of-view optimization:\n"));
sqlite3TreeViewSelect(0, p, 0);
}
#endif
return 1;
}
/*
** If any term of pSrc, or any SF_NestedFrom sub-query, is not the same
** as pSrcItem but has the same alias as p0, then return true.
** Otherwise return false.
*/
static int sameSrcAlias(SrcItem *p0, SrcList *pSrc){
|
| ︙ | ︙ | |||
7311 7312 7313 7314 7315 7316 7317 |
sqlite3TreeViewSelect(0, p, 0);
}
#endif
}else{
TREETRACE(0x2000,pParse,p,("Constant propagation not helpful\n"));
}
| < < | 7309 7310 7311 7312 7313 7314 7315 7316 7317 7318 7319 7320 7321 7322 7323 7324 7325 7326 7327 7328 7329 |
sqlite3TreeViewSelect(0, p, 0);
}
#endif
}else{
TREETRACE(0x2000,pParse,p,("Constant propagation not helpful\n"));
}
if( OptimizationEnabled(db, SQLITE_QueryFlattener|SQLITE_CountOfView)
&& countOfViewOptimization(pParse, p)
){
if( db->mallocFailed ) goto select_end;
pEList = p->pEList;
pTabList = p->pSrc;
}
/* For each term in the FROM clause, do two things:
** (1) Authorized unreferenced tables
** (2) Generate code for all sub-queries
*/
for(i=0; i<pTabList->nSrc; i++){
SrcItem *pItem = &pTabList->a[i];
|
| ︙ | ︙ |
Changes to test/pushdown.test.
| ︙ | ︙ | |||
158 159 160 161 162 163 164 165 166 | | `--UNION ALL | `--SEARCH t2 USING PRIMARY KEY (a=? AND b=?) `--SCAN v3 } # ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ # We want both arms of the compound subquery to use the # primary key. finish_test | > > > > > > > > > > > > > > > > > > > > > | 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 |
| `--UNION ALL
| `--SEARCH t2 USING PRIMARY KEY (a=? AND b=?)
`--SCAN v3
}
# ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
# We want both arms of the compound subquery to use the
# primary key.
# The following is a test of the count-of-view optimization. This does
# not have anything to do with push-down. It is here because this is a
# convenient place to put the test.
#
do_execsql_test 3.7 {
SELECT count(*) FROM v3;
} 6
do_eqp_test 3.8 {
SELECT count(*) FROM v3;
} {
QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY xxxxxx
| `--SCAN t1
`--SCALAR SUBQUERY xxxxxx
`--SCAN t2
}
# ^^^^^^^^^^^^^^^^^^^^
# The query should be converted into:
# SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2)
finish_test
|