SQLite 3.2.5, freshly downloaded Windows binaries.
I start with an empty DB and execute the following commands in sqlite3
console:
create table a (id integer primary key);
create table b (id integer primary key, aId integer, text);
insert into a values (1);
insert into b values (1, 1, 'zzz');
insert into b values (2, 1, 'xxx');
insert into b values (3, 1, 'yyy');
-- sanity check
select * from a;
1
select * from b;
1|1|zzz
2|1|xxx
3|1|yyy
-- now for the bug
select a.id, b.id, b.text from a join b on (a.id = b.aId) order by a.id,
b.text;
1|1|zzz
1|2|xxx
1|3|yyy
The result of the last query is supposed to be sorted by b.text (the
last column), but it is obviously not. The engine seems to be confused
by a.id and b.id columns having the same names (sans table name prefix).
Slightly modifying the query yields the correct result:
select a.id mainId, b.id, b.text from a join b on (a.id = b.aId) order
by mainId, b.text;
1|2|xxx
1|3|yyy
1|1|zzz
Is this a bug, or am I missing something obvious?
Igor Tandetnik
