Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not flatten subqueries where the subquery has a LIMIT and the outer query has a WHERE clause. Ticket #3334. (CVS 5613) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
4995a1d1c9530be9ce647d338169620c |
User & Date: | drh 2008-08-26 12:56:14.000 |
Context
2008-08-26
| ||
14:42 | Implement a "counter" SQL function that can be used to insert a sequence number each row of a result set. Currently in the test harness only, but a candidate to move into the core. (CVS 5614) (check-in: c84d46c712 user: drh tags: trunk) | |
12:56 | Do not flatten subqueries where the subquery has a LIMIT and the outer query has a WHERE clause. Ticket #3334. (CVS 5613) (check-in: 4995a1d1c9 user: drh tags: trunk) | |
2008-08-25
| ||
21:23 | Add the SQLITE_OPEN_FULLMUTEX definition to sqlite3.h. It currently is not valid for anything. This is merely to reserve the number. (CVS 5612) (check-in: 3b6ffb4492 user: drh tags: trunk) | |
Changes
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.471 2008/08/26 12:56:14 drh Exp $ */ #include "sqliteInt.h" /* ** Delete all the content of a Select structure but do not deallocate ** the select structure itself. |
︙ | ︙ | |||
2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 | ** The parent and sub-query may contain WHERE clauses. Subject to ** rules (11), (13) and (14), they may also contain ORDER BY, ** LIMIT and OFFSET clauses. ** ** (18) If the sub-query is a compound select, then all terms of the ** ORDER by clause of the parent must be simple references to ** columns of the sub-query. ** ** 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 returns 0. ** If flattening is attempted this routine returns 1. | > > > | 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 | ** The parent and sub-query may contain WHERE clauses. Subject to ** rules (11), (13) and (14), they may also contain ORDER BY, ** LIMIT and OFFSET clauses. ** ** (18) If the sub-query is a compound select, then all terms of the ** ORDER by clause of the parent must be simple references to ** columns of the sub-query. ** ** (19) The subquery does not use LIMIT or the outer query does not ** have a WHERE 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 returns 0. ** If flattening is attempted this routine returns 1. |
︙ | ︙ | |||
2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 | if( (p->selFlags & SF_Distinct)!=0 && subqueryIsAgg ){ return 0; /* Restriction (6) */ } if( p->pOrderBy && pSub->pOrderBy ){ return 0; /* Restriction (11) */ } if( isAgg && pSub->pOrderBy ) return 0; /* Restriction (16) */ /* OBSOLETE COMMENT 1: ** Restriction 3: If the subquery is a join, make sure the subquery is ** not used as the right operand of an outer join. Examples of why this ** is not allowed: ** ** t1 LEFT OUTER JOIN (t2 JOIN t3) | > | 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 | if( (p->selFlags & SF_Distinct)!=0 && subqueryIsAgg ){ return 0; /* Restriction (6) */ } if( p->pOrderBy && pSub->pOrderBy ){ return 0; /* Restriction (11) */ } if( isAgg && pSub->pOrderBy ) return 0; /* Restriction (16) */ if( pSub->pLimit && p->pWhere ) return 0; /* Restriction (19) */ /* OBSOLETE COMMENT 1: ** Restriction 3: If the subquery is a join, make sure the subquery is ** not used as the right operand of an outer join. Examples of why this ** is not allowed: ** ** t1 LEFT OUTER JOIN (t2 JOIN t3) |
︙ | ︙ |
Added test/tkt3334.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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 | # 2008 August 26 # # 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. # Specifically, it tests that bug #3334 has been fixed by the # addition of restriction (19) to the subquery flattener optimization. # # $Id: tkt3334.test,v 1.1 2008/08/26 12:56:14 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test tkt3334-1.0 { execsql { CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,934); INSERT INTO t1 VALUES(2,221); INSERT INTO t1 VALUES(1,372); INSERT INTO t1 VALUES(3,552); INSERT INTO t1 VALUES(1,719); INSERT INTO t1 VALUES(4,102); SELECT * FROM t1 ORDER BY b; } } {4 102 2 221 1 372 3 552 1 719 1 934} do_test tkt3334-1.1 { execsql { SELECT a FROM (SELECT a FROM t1 ORDER BY b LIMIT 2) WHERE a=1; } } {} do_test tkt3334-1.2 { execsql { SELECT count(*) FROM (SELECT a FROM t1 ORDER BY b LIMIT 2) WHERE a=1; } } {0} do_test tkt3334-1.3 { execsql { SELECT a FROM (SELECT a FROM t1 ORDER BY b LIMIT 3) WHERE a=1; } } {1} do_test tkt3334-1.4 { execsql { SELECT count(*) FROM (SELECT a FROM t1 ORDER BY b LIMIT 3) WHERE a=1; } } {1} do_test tkt3334-1.5 { execsql { SELECT a FROM (SELECT a FROM t1 ORDER BY b LIMIT 99) WHERE a=1; } } {1 1 1} do_test tkt3334-1.6 { execsql { SELECT count(*) FROM (SELECT a FROM t1 ORDER BY b LIMIT 99) WHERE a=1; } } {3} do_test tkt3334-1.7 { execsql { SELECT a FROM (SELECT a FROM t1 ORDER BY b) WHERE a=1; } } {1 1 1} do_test tkt3334-1.8 { execsql { SELECT count(*) FROM (SELECT a FROM t1 ORDER BY b) WHERE a=1; } } {3} do_test tkt3334-1.9 { execsql { SELECT a FROM (SELECT a FROM t1) WHERE a=1; } } {1 1 1} do_test tkt3334-1.10 { execsql { SELECT count(*) FROM (SELECT a FROM t1) WHERE a=1; } } {3} |