SQLite中的错误自我加入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生成行的成对组合。 SQLite版本是3.5.9。表格内容如下:

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:

然后就是ID上的自联接:

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:

这显然不是我想要的。现在,更改t2和t1的顺序会产生正确的结果:

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.

现在,对于另一个实验,我尝试在除行id之外的数字列上进行组合。另一方面,在两种情况下都给出了正确的结果。

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.

我希望有人能够深入了解这里发生的事情。据我所知,它不是SQLite中的错误,也不是SQL的一些微妙方面我不知道。

Thanks,

2 个解决方案

#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.

似乎是SQLite中的一个错误 - 您发布的第一个结果是,如您所知,错误。我已经在我的工作站上的PG8.3和sqlite3.6.4上进行了测试,无法重现。在所有情况下得到正确的结果。可能链接到您的sqlite版本;尝试升级。

#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