Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -5493,10 +5493,11 @@ ** 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. */ @@ -5516,10 +5517,11 @@ 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 */ @@ -5776,10 +5778,20 @@ } #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 */ @@ -5953,20 +5965,10 @@ #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x400 ){ SELECTTRACE(0x400,pParse,p,("After all FROM-clause analysis:\n")); sqlite3TreeViewSelect(0, p, 0); } -#endif - -#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 /* 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: ADDED test/countofview.test Index: test/countofview.test ================================================================== --- /dev/null +++ test/countofview.test @@ -0,0 +1,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 +