Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix for ticket #31: Do not attempt the flattening optimization if the subselect does not contain a FROM clause. Handle the special case where a WHERE clause is constant. (CVS 548) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
24e4cf73d22bb41d26bf3c833f1854a9 |
User & Date: | drh 2002-04-30 19:20:28.000 |
Context
2002-05-03
| ||
00:17 | Update the change log prior to releasing version 2.4.10. (CVS 549) (check-in: 67838bbc80 user: drh tags: trunk) | |
2002-04-30
| ||
19:20 | Fix for ticket #31: Do not attempt the flattening optimization if the subselect does not contain a FROM clause. Handle the special case where a WHERE clause is constant. (CVS 548) (check-in: 24e4cf73d2 user: drh tags: trunk) | |
2002-04-26
| ||
09:47 | Fix for ticket #28: Export the "sqlite_changes" function in Windows DLLs. (CVS 547) (check-in: f7cea4634d user: drh tags: trunk) | |
Changes
Changes to VERSION.
|
| | | 1 | 2.4.10 |
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** ** $Id: select.c,v 1.80 2002/04/30 19:20:29 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. */ |
︙ | ︙ | |||
913 914 915 916 917 918 919 920 921 922 923 924 925 926 | ** (4) The subquery is not DISTINCT or the outer query is not a join. ** ** (5) The subquery is not DISTINCT or the outer query does not use ** aggregates. ** ** (6) The subquery does not use aggregates or the outer query is not ** DISTINCT. ** ** In this routine, the "p" parameter is a pointer to the outer query. ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates. ** ** If flattening is not attempted, this routine is a no-op and return 0. ** If flattening is attempted this routine returns 1. | > > | 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 | ** (4) The subquery is not DISTINCT or the outer query is not a join. ** ** (5) The subquery is not DISTINCT or the outer query does not use ** aggregates. ** ** (6) The subquery does not use aggregates or the outer query is not ** DISTINCT. ** ** (7) The subquery has a FROM clause. ** ** In this routine, the "p" parameter is a pointer to the outer query. ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates. ** ** If flattening is not attempted, this routine is a no-op and return 0. ** If flattening is attempted this routine returns 1. |
︙ | ︙ | |||
943 944 945 946 947 948 949 | assert( pSrc && iFrom>=0 && iFrom<pSrc->nId ); pSub = pSrc->a[iFrom].pSelect; assert( pSub!=0 ); if( isAgg && subqueryIsAgg ) return 0; if( subqueryIsAgg && pSrc->nId>1 ) return 0; pSubSrc = pSub->pSrc; assert( pSubSrc ); | | | 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 | assert( pSrc && iFrom>=0 && iFrom<pSrc->nId ); pSub = pSrc->a[iFrom].pSelect; assert( pSub!=0 ); if( isAgg && subqueryIsAgg ) return 0; if( subqueryIsAgg && pSrc->nId>1 ) return 0; pSubSrc = pSub->pSrc; assert( pSubSrc ); if( pSubSrc->nId!=1 ) return 0; if( pSub->isDistinct && pSrc->nId>1 ) return 0; if( pSub->isDistinct && isAgg ) return 0; if( p->isDistinct && subqueryIsAgg ) return 0; /* If we reach this point, it means flatting is permitted for the ** i-th entry of the FROM clause in the outer query. */ |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. Also found here are subroutines ** to generate VDBE code to evaluate expressions. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. Also found here are subroutines ** to generate VDBE code to evaluate expressions. ** ** $Id: where.c,v 1.41 2002/04/30 19:20:29 drh Exp $ */ #include "sqliteInt.h" /* ** The query generator uses an array of instances of this structure to ** help it analyze the subexpressions of the WHERE clause. Each WHERE ** clause subexpression is separated from the others by an AND operator. |
︙ | ︙ | |||
189 190 191 192 193 194 195 196 197 198 199 200 201 202 | sqliteFree(pWInfo); return 0; } pWInfo->pParse = pParse; pWInfo->pTabList = pTabList; pWInfo->base = base; pWInfo->peakNTab = pWInfo->savedNTab = pParse->nTab; /* Split the WHERE clause into as many as 32 separate subexpressions ** where each subexpression is separated by an AND operator. Any additional ** subexpressions are attached in the aExpr[32] and will not enter ** into the query optimizer computations. 32 is chosen as the cutoff ** since that is the number of bits in an integer that we use for an ** expression-used mask. | > > > > > > > > | 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 | sqliteFree(pWInfo); return 0; } pWInfo->pParse = pParse; pWInfo->pTabList = pTabList; pWInfo->base = base; pWInfo->peakNTab = pWInfo->savedNTab = pParse->nTab; pWInfo->iBreak = sqliteVdbeMakeLabel(v); /* Special case: a WHERE clause that is constant. Evaluate the ** expression and either jump over all of the code or fall thru. */ if( pWhere && sqliteExprIsConstant(pWhere) ){ sqliteExprIfFalse(pParse, pWhere, pWInfo->iBreak); } /* Split the WHERE clause into as many as 32 separate subexpressions ** where each subexpression is separated by an AND operator. Any additional ** subexpressions are attached in the aExpr[32] and will not enter ** into the query optimizer computations. 32 is chosen as the cutoff ** since that is the number of bits in an integer that we use for an ** expression-used mask. |
︙ | ︙ | |||
418 419 420 421 422 423 424 | sqliteVdbeChangeP3(v, -1, pWInfo->a[i].pIdx->zName, P3_STATIC); } } /* Generate the code to do the search */ loopMask = 0; | < | 426 427 428 429 430 431 432 433 434 435 436 437 438 439 | sqliteVdbeChangeP3(v, -1, pWInfo->a[i].pIdx->zName, P3_STATIC); } } /* Generate the code to do the search */ loopMask = 0; for(i=0; i<pTabList->nId; i++){ int j, k; int idx = aOrder[i]; Index *pIdx; WhereLevel *pLevel = &pWInfo->a[i]; pIdx = pLevel->pIdx; |
︙ | ︙ |
Changes to test/select6.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that contain # subqueries in their FROM clause. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that contain # subqueries in their FROM clause. # # $Id: select6.test,v 1.9 2002/04/30 19:20:29 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test select6-1.0 { execsql { BEGIN; |
︙ | ︙ | |||
331 332 333 334 335 336 337 338 339 | do_test select6-6.6 { execsql { SELECT * FROM ( SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1 ) ORDER BY a; } } {1 3} finish_test | > > > > > > > > > > > > > > > > > > > > > > > | 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 | do_test select6-6.6 { execsql { SELECT * FROM ( SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1 ) ORDER BY a; } } {1 3} # Subselects with no FROM clause # do_test select6-7.1 { execsql { SELECT * FROM (SELECT 1) } } {1} do_test select6-7.2 { execsql { SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c') } } {abc 2 1 1 2 abc} do_test select6-7.3 { execsql { SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 0) } } {} do_test select6-7.4 { execsql2 { SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1) } } {c abc b 2 a 1 a 1 b 2 c abc} finish_test |
Changes to test/where.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the use of indices in WHERE clases. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the use of indices in WHERE clases. # # $Id: where.test,v 1.6 2002/04/30 19:20:29 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # do_test where-1.0 { |
︙ | ︙ | |||
232 233 234 235 236 237 238 239 240 | } {12 89 12 9} do_test where-3.3 { count { SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y } } {15 86 86 9} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > | 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 | } {12 89 12 9} do_test where-3.3 { count { SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y } } {15 86 86 9} # Test to see that the special case of a constant WHERE clause is # handled. # do_test where-4.1 { count { SELECT * FROM t1 WHERE 0 } } {0} do_test where-4.2 { count { SELECT * FROM t1 WHERE 1 LIMIT 1 } } {1 0 4 0} do_test where-4.3 { execsql { SELECT 99 WHERE 0 } } {} do_test where-4.4 { execsql { SELECT 99 WHERE 1 } } {99} finish_test |