|18:26||• Ticket [ba82a4a4] Query optimizer removes ORDER BY when it is needed status still Open with 6 other changes (artifact: 294e00a4 user: drh)|
|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)|
|15:36||• New ticket [a179fe74]. (artifact: 8e3e9bb5 user: anonymous)|
|Title:||Incorrect output order on a join with an ORDER BY|
|Last Modified:||2013-03-27 17:20:18|
|Version Found In:||3.7.16|
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 22.214.171.124. 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;