Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not flatten subqueries in a join where the subquery includes a LIMIT. Ticket #1634. This is just an initial fix. Many test cases need to be added prior to closing the ticket. (CVS 2987) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
af18c0f431a1a6349e40249009f2ac22 |
User & Date: | drh 2006-01-21 22:19:55.000 |
Context
2006-01-22
| ||
00:14 | Tests for the subquery flattening fix of check-in (2987). (CVS 2988) (check-in: 72a067f0df user: drh tags: trunk) | |
2006-01-21
| ||
22:19 | Do not flatten subqueries in a join where the subquery includes a LIMIT. Ticket #1634. This is just an initial fix. Many test cases need to be added prior to closing the ticket. (CVS 2987) (check-in: af18c0f431 user: drh tags: trunk) | |
19:57 | Fix a bug in os.h. How did this slip by before now? (CVS 2986) (check-in: 29725de474 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.297 2006/01/21 22:19:55 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. |
︙ | ︙ | |||
2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 | ** (10) The subquery does not use aggregates or the outer query does not ** use LIMIT. ** ** (11) The subquery and the outer query do not both have ORDER BY clauses. ** ** (12) The subquery is not the right term of a LEFT OUTER JOIN or the ** subquery has no WHERE clause. (added by ticket #350) ** ** 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. | > > > > | 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 | ** (10) The subquery does not use aggregates or the outer query does not ** use LIMIT. ** ** (11) The subquery and the outer query do not both have ORDER BY clauses. ** ** (12) The subquery is not the right term of a LEFT OUTER JOIN or the ** subquery has no WHERE clause. (added by ticket #350) ** ** (13) The subquery and outer query do not both use LIMIT ** ** (14) The subquery does not use OFFSET ** ** 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. |
︙ | ︙ | |||
2050 2051 2052 2053 2054 2055 2056 | */ if( p==0 ) return 0; pSrc = p->pSrc; assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc ); pSubitem = &pSrc->a[iFrom]; pSub = pSubitem->pSelect; assert( pSub!=0 ); | | | > > > > > | | | > | | | | > > | 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085 2086 2087 | */ if( p==0 ) return 0; pSrc = p->pSrc; assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc ); pSubitem = &pSrc->a[iFrom]; pSub = pSubitem->pSelect; assert( pSub!=0 ); if( isAgg && subqueryIsAgg ) return 0; /* Restriction (1) */ if( subqueryIsAgg && pSrc->nSrc>1 ) return 0; /* Restriction (2) */ pSubSrc = pSub->pSrc; assert( pSubSrc ); /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants, ** not arbitrary expresssions, we allowed some combining of LIMIT and OFFSET ** because they could be computed at compile-time. But when LIMIT and OFFSET ** became arbitrary expressions, we were forced to add restrictions (13) ** and (14). */ if( pSub->pLimit && p->pLimit ) return 0; /* Restriction (13) */ if( pSub->pOffset ) return 0; /* Restriction (14) */ if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */ if( (pSub->isDistinct || pSub->pLimit) && (pSrc->nSrc>1 || isAgg) ){ /* Restrictions (4)(5)(8)(9) */ return 0; } if( p->isDistinct && subqueryIsAgg ) return 0; /* Restriction (6) */ if( (p->disallowOrderBy || p->pOrderBy) && pSub->pOrderBy ){ return 0; /* Restriction (11) */ } /* 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) ** |
︙ | ︙ | |||
2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 | /* The flattened query is distinct if either the inner or the ** outer query is distinct. */ p->isDistinct = p->isDistinct || pSub->isDistinct; /* ** SELECT ... FROM (SELECT ... LIMIT a OFFSET b) LIMIT x OFFSET y; */ if( pSub->pLimit ){ p->pLimit = pSub->pLimit; pSub->pLimit = 0; } /* Finially, delete what is left of the subquery and return | > > > | 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 | /* The flattened query is distinct if either the inner or the ** outer query is distinct. */ p->isDistinct = p->isDistinct || pSub->isDistinct; /* ** SELECT ... FROM (SELECT ... LIMIT a OFFSET b) LIMIT x OFFSET y; ** ** One is tempted to try to add a and b to combine the limits. But this ** does not work if either limit is negative. */ if( pSub->pLimit ){ p->pLimit = pSub->pLimit; pSub->pLimit = 0; } /* Finially, delete what is left of the subquery and return |
︙ | ︙ |