|
2013-04-22
| ||
| 18:26 | • Ticket [ba82a4a4] Query optimizer removes ORDER BY when it is needed status still Open with 6 other changes (artifact: 294e00a4 user: drh) | |
|
2013-03-27
| ||
| 17:20 | • Fixed ticket [a179fe74]: Incorrect output order on a join with an ORDER BY plus 3 other changes (artifact: 16d5b731 user: drh) | |
| 17:20 | In order to optimize out the ORDER BY clause, outer loops must generate values for ORDER BY terms that are unique or else the inner loops must generate no more than a single row. Fix for ticket [a179fe7465]. (check-in: 2936f746 user: drh tags: trunk) | |
| 15:04 | A fix and test-case for the ORDER BY problem identified by ticket [a179fe7465]. This change causes sorting to occur in some cases where it is not strictly necessary. Further work is needed to avoid those extra sorts. (check-in: 488089e6 user: drh tags: orderby-fix) | |
| 14:28 | • Ticket [a179fe74] Incorrect output order on a join with an ORDER BY status still Open with 3 other changes (artifact: 82a0e96f user: drh) | |
| 11:39 | • Ticket [a179fe74]: 7 changes (artifact: 41623a1a user: drh) | |
|
2013-03-26
| ||
| 15:36 | • New ticket [a179fe74]. (artifact: 8e3e9bb5 user: anonymous) | |
| Ticket Hash: | a179fe746592d4aecc5c1ec2f6583a4e765e6ccf | |||
| Title: | Incorrect output order on a join with an ORDER BY | |||
| Status: | Fixed | Type: | Code_Defect | |
| Severity: | Critical | Priority: | Immediate | |
| Subsystem: | Unknown | Resolution: | Fixed | |
| Last Modified: | 2013-03-27 17:20:18 | |||
| Version Found In: | 3.7.16 | |||
| User Comments: | ||||
nobody added on 2013-03-26 15:36:51:
In the query below, there are multiple rows with the same combination of group by columns: select pgr.productgroupid, r.stationgroupid, sum(quantity) from tmp_duration d, productgrouprelation pgr, stationgrouprelation r where d.productid = pgr.productid and d.stationid = r.stationid group by r.stationgroupid, pgr.productgroupid results in Fun EB 13 Fun EB 12 Fun EB 1862 GK EB 4 GK EB 2 GK EB 505 GK EB 5 GK EB 26 GK EB 6 GK EB 155 GK EB 4284 GK EB 1781 Reversing the group by columns to "pgr.productgroupid, r.stationgroupid" will give the correct result: Fun EB 1887 GK EB 6768 This effect is also in 3.7.15.2. Version 3.7.2 is ok. drh added on 2013-03-27 11:39:51: The ORDER BY clause on the final SELECT of the following script does not work: CREATE TABLE t1(a, b, PRIMARY KEY(a,b)); INSERT INTO t1 VALUES(1,1),(1,2); CREATE TABLE t2(x, y, PRIMARY KEY(x,y)); INSERT INTO t2 VALUES(3,3),(4,4); SELECT a, x FROM t1, t2 ORDER BY 1, 2; This script is a reduction of the same bug that causes the problem in the initial bug report above. This problem was introduced to trunk by check-in [956e4d7f8958e706] on 2012-10-03 and first appeared in release 3.7.15. drh added on 2013-03-27 14:28:13: Another test case is shown below. This one uses single-column indices only. CREATE TABLE t3(a); INSERT INTO t3 VALUES(1),(1); CREATE INDEX t3a ON t3(a); CREATE TABLE t4(x); INSERT INTO t4 VALUES(3),(4); CREATE INDEX t4x ON t4(x); SELECT a, x FROM t3, t4 ORDER BY 1, 2; | ||||