On 2011/10/23, Yuriy Kaminskiy wrote:
> Yuriy Kaminskiy wrote:
>> Yuriy Kaminskiy wrote:
>>> Yuriy Kaminskiy wrote:
>>>> When WHERE condition is constant, there are no need to evaluate and check
>>>> it for
>>>> each row. It works, but only partially:
>>> ...
>>>> [In fact, you can move out out loop not only *whole* constant WHERE, but
>>>> also
>>>> all constant AND terms of WHERE, like this:
>>>> SELECT * FROM t WHERE const1 AND notconst AND const2 ->
>>>> SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND const2
>>>> I'll take a shot on that later.]
>>> Here it goes.
>>>
>>> Prerequisite: previous patch.
>>> Passes quick regression test (make test).
>>> Possible problem: short-circuits evaluation. Should not be problem, IMO, as
>>> only
>>> constants references? Please verify.
>> Ping.
> Ping.
Ping.
For convenience all 3 patches collected below (needed no change for 3.7.11).
--
Part 1: Move whereSplit() to unbreak constant condition elimination.
Index: sqlite3-3.7.8/src/where.c
===================================================================
--- sqlite3-3.7.8.orig/src/where.c 2011-10-23 20:04:58.000000000 +0400
+++ sqlite3-3.7.8/src/where.c 2011-10-23 20:06:30.000000000 +0400
@@ -4625,7 +4625,6 @@ WhereInfo *sqlite3WhereBegin(
initMaskSet(pMaskSet);
whereClauseInit(pWC, pParse, pMaskSet);
sqlite3ExprCodeConstants(pParse, pWhere);
- whereSplit(pWC, pWhere, TK_AND); /* IMP: R-15842-53296 */
/* Special case: a WHERE clause that is constant. Evaluate the
** expression and either jump over all of the code or fall thru.
@@ -4635,6 +4634,8 @@ WhereInfo *sqlite3WhereBegin(
pWhere = 0;
}
+ whereSplit(pWC, pWhere, TK_AND); /* IMP: R-15842-53296 */
+
/* Assign a bit from the bitmask to every term in the FROM clause.
**
** When assigning bitmask values to FROM clause cursors, it must be
========================================================================================
Part 2: optimize "WHERE const AND notconst" too
Index: sqlite3-3.7.8/src/where.c
===================================================================
--- sqlite3-3.7.8.orig/src/where.c 2011-10-23 20:41:44.000000000 +0400
+++ sqlite3-3.7.8/src/where.c 2011-10-23 20:48:33.000000000 +0400
@@ -4636,6 +4636,21 @@ WhereInfo *sqlite3WhereBegin(
whereSplit(pWC, pWhere, TK_AND); /* IMP: R-15842-53296 */
+ {
+ /* Move const in "WHERE const AND notconst" out of internal loop */
+ int j;
+ for(j=i=0; i<pWC->nTerm; i++){
+ if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){
+ sqlite3ExprIfFalse(pParse, pWC->a[i].pExpr, pWInfo->iBreak,
SQLITE_JUMPIFNULL);
+ continue;
+ }
+ if( j!=i )
+ pWC->a[j]=pWC->a[i];
+ j++;
+ }
+ pWC->nTerm -= i-j;
+ }
+
/* Assign a bit from the bitmask to every term in the FROM clause.
**
** When assigning bitmask values to FROM clause cursors, it must be
========================================================================================
Part 2: Remove now-redundant sqlite3ExprIsConstantNotJoin call.
Index: sqlite3-3.7.8/src/where.c
===================================================================
--- sqlite3-3.7.8.orig/src/where.c 2011-10-23 21:23:00.000000000 +0400
+++ sqlite3-3.7.8/src/where.c 2011-10-23 21:25:06.000000000 +0400
@@ -4626,18 +4626,12 @@ WhereInfo *sqlite3WhereBegin(
whereClauseInit(pWC, pParse, pMaskSet);
sqlite3ExprCodeConstants(pParse, pWhere);
- /* Special case: a WHERE clause that is constant. Evaluate the
- ** expression and either jump over all of the code or fall thru.
- */
- if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
- sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
- pWhere = 0;
- }
-
whereSplit(pWC, pWhere, TK_AND); /* IMP: R-15842-53296 */
{
- /* Move const in "WHERE const AND notconst" out of internal loop */
+ /* Special case: constant AND subterm of WHERE clause. Evaluate the
+ ** expression and either jump over all of the code or fall thru.
+ */
int j;
for(j=i=0; i<pWC->nTerm; i++){
if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
