Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not apply the flattening optimization if the sub-query is DISTINCT. Fix for [e4b8a2ba6e]. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
497aafd8ed6a636a8bd5fa2cad265a20 |
User & Date: | dan 2010-08-13 16:38:49.000 |
Context
2010-08-13
| ||
18:41 | Add tests to test file e_expr.test. (check-in: 3fd1059e25 user: dan tags: trunk) | |
16:38 | Do not apply the flattening optimization if the sub-query is DISTINCT. Fix for [e4b8a2ba6e]. (check-in: 497aafd8ed user: dan tags: trunk) | |
2010-08-12
| ||
16:36 | If an error occurs in PagerSetPagesize(), set the output variable to the unmodified page-size before returning. (check-in: 02def8f925 user: dan tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
2491 2492 2493 2494 2495 2496 2497 | ** Flattening is only attempted if all of the following are true: ** ** (1) The subquery and the outer query do not both use aggregates. ** ** (2) The subquery is not an aggregate or the outer query is not a join. ** ** (3) The subquery is not the right operand of a left outer join | | | | > | | | | 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 | ** Flattening is only attempted if all of the following are true: ** ** (1) The subquery and the outer query do not both use aggregates. ** ** (2) The subquery is not an aggregate or the outer query is not a join. ** ** (3) The subquery is not the right operand of a left outer join ** (Originally ticket #306. Strengthened by ticket #3300) ** ** (4) The subquery is not DISTINCT. ** ** (**) At one point restrictions (4) and (5) defined a subset of DISTINCT ** sub-queries that were excluded from this optimization. Restriction ** (4) has since been expanded to exclude all DISTINCT subqueries. ** ** (6) The subquery does not use aggregates or the outer query is not ** DISTINCT. ** ** (7) The subquery has a FROM clause. ** ** (8) The subquery does not use LIMIT or the outer query is not a join. ** ** (9) The subquery does not use LIMIT or the outer query does not use ** aggregates. ** ** (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. ** ** (**) Not implemented. Subsumed into restriction (3). Was previously ** a separate restriction deriving from ticket #350. ** ** (13) The subquery and outer query do not both use LIMIT. ** ** (14) The subquery does not use OFFSET. ** ** (15) The outer query is not part of a compound select or the ** subquery does not have a LIMIT clause. ** (See ticket #2339 and ticket [02a8e81d44]). ** ** (16) The outer query is not an aggregate or the subquery does ** not contain ORDER BY. (Ticket #2942) This used to not matter |
︙ | ︙ | |||
2609 2610 2611 2612 2613 2614 2615 | ** and (14). */ if( pSub->pLimit && p->pLimit ) return 0; /* Restriction (13) */ if( pSub->pOffset ) return 0; /* Restriction (14) */ if( p->pRightmost && pSub->pLimit ){ return 0; /* Restriction (15) */ } if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */ | | | | | 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 2624 2625 2626 | ** and (14). */ if( pSub->pLimit && p->pLimit ) return 0; /* Restriction (13) */ if( pSub->pOffset ) return 0; /* Restriction (14) */ if( p->pRightmost && pSub->pLimit ){ return 0; /* Restriction (15) */ } if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */ if( pSub->selFlags & SF_Distinct ) return 0; /* Restriction (5) */ if( pSub->pLimit && (pSrc->nSrc>1 || isAgg) ){ return 0; /* Restrictions (8)(9) */ } if( (p->selFlags & SF_Distinct)!=0 && subqueryIsAgg ){ return 0; /* Restriction (6) */ } if( p->pOrderBy && pSub->pOrderBy ){ return 0; /* Restriction (11) */ } |
︙ | ︙ |
Changes to test/selectC.test.
︙ | ︙ | |||
206 207 208 209 210 211 212 213 | } {abc xxxabc def yyydef} do_test selectC-3.3 { execsql { SELECT b, max(a || b) FROM t2 WHERE (b||b||b)!='value' GROUP BY a; } } {xxx abcxxx yyy defyyy} finish_test | > > > > > > > > > > > > > > > > > > > > > > > | 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 | } {abc xxxabc def yyydef} do_test selectC-3.3 { execsql { SELECT b, max(a || b) FROM t2 WHERE (b||b||b)!='value' GROUP BY a; } } {xxx abcxxx yyy defyyy} proc udf {} { incr ::udf } set ::udf 0 db function udf udf do_execsql_test selectC-4.1 { create table t_distinct_bug (a, b, c); insert into t_distinct_bug values ('1', '1', 'a'); insert into t_distinct_bug values ('1', '2', 'b'); insert into t_distinct_bug values ('1', '3', 'c'); insert into t_distinct_bug values ('1', '1', 'd'); insert into t_distinct_bug values ('1', '2', 'e'); insert into t_distinct_bug values ('1', '3', 'f'); } {} do_execsql_test selectC-4.2 { select a from (select distinct a, b from t_distinct_bug) } {1 1 1} do_execsql_test selectC-4.3 { select a, udf() from (select distinct a, b from t_distinct_bug) } {1 1 1 2 1 3} finish_test |