Bug in SQLite self-join on id?

Question

I'm trying to generate pairwise combinations of rows on based on their ids. SQLite version is 3.5.9. The table contents are the following:

id|name|val  
1|A|20
2|B|21
3|C|22

with table schema being:

CREATE TABLE mytable (
    id INTEGER NOT NULL, 
    name VARCHAR, 
    val INTEGER, 
    PRIMARY KEY (id)
);

Then there's the self-join on ids:

sqlite> select t1.id, t2.id from mytable as t1, mytable as t2 where t2.id > t1.id;
id|id
2|2
2|3
3|3

Which is clearly not what I want. Now, changing the order of t2 and t1 produces the correct result:

sqlite> select t1.id, t2.id from mytable as t2, mytable as t1 where t2.id > t1.id;
id|id
1|2
1|3
2|3

Now, for another experiment, I tried combining on a numeric column other than row id. That, on the other hand, gives correct result in both cases.

I am hoping someone can give an insight into what's going on here. As far as I understand, its either a bug in SQLite or some delicate aspect of SQL I don't know.

Thanks,

Answer 1

Seems to be a bug in SQLite - the first result you posted is, as you suspected, wrong. I've tested it on both PG8.3 and sqlite3.6.4 on my workstation, couldn't reproduce. Got correct result in all cases. Might be linked to your sqlite version; try upgrading.

Answer 2

SQLite version 3.6.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table mytable (
   ...> id integer not null,
   ...> name varchar,
   ...> val integer,
   ...> primary key (id)
   ...> );
sqlite> insert into mytable values(null,'A',20);
sqlite> insert into mytable values(null,'B',21);
sqlite> insert into mytable values(null,'C',22);
sqlite> select t1.id, t2.id from mytable as t1, mytable as t2 where t2.id > t1.id;
1|2
1|3
2|3