Bug in SQLite self-join on id?

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,

Answers


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.


Need Your Help

Add different value types to mysql tables' columns

mysql table sqldatatypes

Is there any way to add different types of values in the SAME column in a MySQL table?

Common Facelets files in shared library JAR outside /WEB-INF/lib

jsf-2 jar shared-libraries facelets filenotfoundexception

I have a common shared Library (that is setup as a Shared Library in Websphere Application server).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.