Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix the handling of sub-queries with LIMIT clauses by the optimization activated by compile-time symbol SQLITE_COUNTOFVIEW_OPTIMIZATION. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
21235d9a41567897418aa12f7bd6dd8d |
User & Date: | dan 2018-08-03 20:19:52.614 |
Context
2018-08-04
| ||
15:16 | Ensure that all expressions that are to be evaluated once at the start of a prepared statement (the Parse.pConstExpr expressions) pass the sqlite3ExprIsConstantNotJoin() test. It is not sufficient to pass just the sqlite3ExprIsConstant() test as that would allow through column references that are bound to constants by the WHERE clause in the constant propagation optimization. This fixes a problem discovered by OSSFuzz. (check-in: 8bc7f84c39 user: drh tags: trunk) | |
2018-08-03
| ||
23:04 | Completely remove the column cache logic, which has been a persistent source of bugs for many years. Due to recent enhancements to the performance of the OP_Column opcode, removing the column cache actually makes speed-check.sh run faster. Removing the column cache also saves about 1,800 bytes of code space. (check-in: 3f5f60cd75 user: drh tags: omit-column-cache) | |
20:19 | Fix the handling of sub-queries with LIMIT clauses by the optimization activated by compile-time symbol SQLITE_COUNTOFVIEW_OPTIMIZATION. (check-in: 21235d9a41 user: dan tags: trunk) | |
15:58 | Fix the OP_SeekRowid opcode so that it has no type-change side-effects on the key register in P3. This fixes an obcure problem that arises when doing equi-joins between a table with a TEXT column against another table with an INTEGER PRIMARY KEY. The original problem was discovered when OSSFuzz created such a query and hit an assert() in OP_VerifyTabCol that was specifically designed to catch these kinds of errors at run-time. Test cases for this fix are in TH3. (check-in: fa94b49e02 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
5491 5492 5493 5494 5495 5496 5497 5498 5499 5500 5501 5502 5503 5504 | ** Into this: ** ** SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2) ** ** The transformation only works if all of the following are true: ** ** * The subquery is a UNION ALL of two or more terms ** * There is no WHERE or GROUP BY or HAVING clauses on the subqueries ** * The outer query is a simple count(*) ** ** Return TRUE if the optimization is undertaken. */ static int countOfViewOptimization(Parse *pParse, Select *p){ Select *pSub, *pPrior; | > | 5491 5492 5493 5494 5495 5496 5497 5498 5499 5500 5501 5502 5503 5504 5505 | ** Into this: ** ** SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2) ** ** The transformation only works if all of the following are true: ** ** * The subquery is a UNION ALL of two or more terms ** * The subquery does not have a LIMIT clause ** * There is no WHERE or GROUP BY or HAVING clauses on the subqueries ** * The outer query is a simple count(*) ** ** Return TRUE if the optimization is undertaken. */ static int countOfViewOptimization(Parse *pParse, Select *p){ Select *pSub, *pPrior; |
︙ | ︙ | |||
5514 5515 5516 5517 5518 5519 5520 5521 5522 5523 5524 5525 5526 5527 | if( p->pSrc->nSrc!=1 ) return 0; /* One table in FROM */ pSub = p->pSrc->a[0].pSelect; if( pSub==0 ) return 0; /* The FROM is a subquery */ if( pSub->pPrior==0 ) return 0; /* Must be a compound ry */ do{ if( pSub->op!=TK_ALL && pSub->pPrior ) return 0; /* Must be UNION ALL */ if( pSub->pWhere ) return 0; /* No WHERE clause */ if( pSub->selFlags & SF_Aggregate ) return 0; /* Not an aggregate */ pSub = pSub->pPrior; /* Repeat over compound */ }while( pSub ); /* If we reach this point then it is OK to perform the transformation */ db = pParse->db; | > | 5515 5516 5517 5518 5519 5520 5521 5522 5523 5524 5525 5526 5527 5528 5529 | if( p->pSrc->nSrc!=1 ) return 0; /* One table in FROM */ pSub = p->pSrc->a[0].pSelect; if( pSub==0 ) return 0; /* The FROM is a subquery */ if( pSub->pPrior==0 ) return 0; /* Must be a compound ry */ do{ if( pSub->op!=TK_ALL && pSub->pPrior ) return 0; /* Must be UNION ALL */ if( pSub->pWhere ) return 0; /* No WHERE clause */ if( pSub->pLimit ) return 0; /* No LIMIT clause */ if( pSub->selFlags & SF_Aggregate ) return 0; /* Not an aggregate */ pSub = pSub->pPrior; /* Repeat over compound */ }while( pSub ); /* If we reach this point then it is OK to perform the transformation */ db = pParse->db; |
︙ | ︙ | |||
5774 5775 5776 5777 5778 5779 5780 5781 5782 5783 5784 5785 5786 5787 | SELECTTRACE(0x100,pParse,p,("After constant propagation:\n")); sqlite3TreeViewSelect(0, p, 0); } #endif }else{ SELECTTRACE(0x100,pParse,p,("Constant propagation not helpful\n")); } /* 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++){ struct SrcList_item *pItem = &pTabList->a[i]; | > > > > > > > > > > | 5776 5777 5778 5779 5780 5781 5782 5783 5784 5785 5786 5787 5788 5789 5790 5791 5792 5793 5794 5795 5796 5797 5798 5799 | SELECTTRACE(0x100,pParse,p,("After constant propagation:\n")); sqlite3TreeViewSelect(0, p, 0); } #endif }else{ SELECTTRACE(0x100,pParse,p,("Constant propagation not helpful\n")); } #ifdef SQLITE_COUNTOFVIEW_OPTIMIZATION if( OptimizationEnabled(db, SQLITE_QueryFlattener|SQLITE_CountOfView) && countOfViewOptimization(pParse, p) ){ if( db->mallocFailed ) goto select_end; pEList = p->pEList; pTabList = p->pSrc; } #endif /* 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++){ struct SrcList_item *pItem = &pTabList->a[i]; |
︙ | ︙ | |||
5951 5952 5953 5954 5955 5956 5957 | sDistinct.isTnct = (p->selFlags & SF_Distinct)!=0; #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x400 ){ SELECTTRACE(0x400,pParse,p,("After all FROM-clause analysis:\n")); sqlite3TreeViewSelect(0, p, 0); } | < < < < < < < < < < | 5963 5964 5965 5966 5967 5968 5969 5970 5971 5972 5973 5974 5975 5976 | sDistinct.isTnct = (p->selFlags & SF_Distinct)!=0; #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x400 ){ SELECTTRACE(0x400,pParse,p,("After all FROM-clause analysis:\n")); sqlite3TreeViewSelect(0, p, 0); } #endif /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and ** if the select-list is the same as the ORDER BY list, then this query ** can be rewritten as a GROUP BY. In other words, this: ** ** SELECT DISTINCT xyz FROM ... ORDER BY xyz |
︙ | ︙ |
Added test/countofview.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | # 2018-08-04 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # set testdir [file dirname $argv0] source $testdir/tester.tcl source $testdir/malloc_common.tcl set testprefix countofview do_execsql_test 1.0 { CREATE TABLE t2(c); CREATE TABLE t3(f); INSERT INTO t2 VALUES(1), (2); INSERT INTO t3 VALUES(3); } do_execsql_test 1.1 { select c from t2 union all select f from t3 limit 1 offset 1 } {2} do_execsql_test 1.2 { select count(*) from ( select c from t2 union all select f from t3 limit 1 offset 1 ) } {1} do_execsql_test 1.3 { select count(*) from ( select c from t2 union all select f from t3 ) } {3} finish_test |