SQLite
View Ticket
Not logged in
Ticket UUID: 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;