Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not apply the query flattening optimization when the outer query is an aggregate and the inner query contains an ORDER BY clause. Ticket #2943. (CVS 4791) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
6d33cbd99cb0db680767ceb31ec6345e |
User & Date: | drh 2008-02-15 14:33:04.000 |
Context
2008-02-15
| ||
17:38 | Fix a bug in the ".show" command of the CLI. Ticket #2942. (CVS 4792) (check-in: dedf5f230b user: drh tags: trunk) | |
14:33 | Do not apply the query flattening optimization when the outer query is an aggregate and the inner query contains an ORDER BY clause. Ticket #2943. (CVS 4791) (check-in: 6d33cbd99c user: drh tags: trunk) | |
2008-02-14
| ||
23:44 | Fix a bug in EXPLAIN growing out of the new Mem implementation. (CVS 4790) (check-in: 4df62a55d6 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.414 2008/02/15 14:33:04 drh Exp $ */ #include "sqliteInt.h" /* ** Delete all the content of a Select structure but do not deallocate ** the select structure itself. |
︙ | ︙ | |||
2412 2413 2414 2415 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 | ** (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 both an ORDER BY and a LIMIT clause. ** (See ticket #2339) ** ** 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. | > > > > | 2412 2413 2414 2415 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 | ** (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 both an ORDER BY and a LIMIT clause. ** (See ticket #2339) ** ** (16) The outer query is not an aggregate or the subquery does ** not contain ORDER BY. (Ticket #2942) This used to not matter ** until we introduced the group_concat() function. ** ** 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. |
︙ | ︙ | |||
2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 | && (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) ** | > | 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 | && (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) */ } if( isAgg && pSub->pOrderBy ) return 0; /* Restriction (16) */ /* 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) ** |
︙ | ︙ | |||
3137 3138 3139 3140 3141 3142 3143 | if( !IgnorableOrderby(pDest) ){ pOrderBy = p->pOrderBy; } pGroupBy = p->pGroupBy; pHaving = p->pHaving; isDistinct = p->isDistinct; } | < < < < < < < < < < | 3142 3143 3144 3145 3146 3147 3148 3149 3150 3151 3152 3153 3154 3155 | if( !IgnorableOrderby(pDest) ){ pOrderBy = p->pOrderBy; } pGroupBy = p->pGroupBy; pHaving = p->pHaving; isDistinct = p->isDistinct; } #endif /* Check to see if this is a subquery that can be "flattened" into its parent. ** If flattening is a possiblity, do so and return immediately. */ #ifndef SQLITE_OMIT_VIEW if( pParent && pParentAgg && |
︙ | ︙ |
Added test/tkt2942.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 | # 2008 February 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. # #*********************************************************************** # # Ticket #2942. # # Queries of the form: # # SELECT group_concat(x) FROM (SELECT * FROM table ORDER BY 1); # # The ORDER BY would be dropped by the query flattener. This used # to not matter because aggregate functions sum(), min(), max(), avg(), # and so forth give the same result regardless of the order of inputs. # But with the addition of the group_concat() function, suddenly the # order does matter. # # $Id: tkt2942.test,v 1.1 2008/02/15 14:33:04 drh Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !subquery { finish_test return } do_test tkt2942.1 { execsql { create table t1(num int); insert into t1 values (2); insert into t1 values (1); insert into t1 values (3); insert into t1 values (4); SELECT group_concat(num) FROM (SELECT num FROM t1 ORDER BY num DESC); } } {4,3,2,1} do_test tkt2942.2 { execsql { SELECT group_concat(num) FROM (SELECT num FROM t1 ORDER BY num); } } {1,2,3,4} do_test tkt2942.3 { execsql { SELECT group_concat(num) FROM (SELECT num FROM t1); } } {2,1,3,4} do_test tkt2942.4 { execsql { SELECT group_concat(num) FROM (SELECT num FROM t1 ORDER BY rowid DESC); } } {4,3,1,2} finish_test |