Geek asks and answers

Error in SQLite self-identification on id?

I am trying to create paired string combinations based on their identifiers. SQLite version is 3.5.9. The contents of the table are as follows:

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

with tabular diagram:

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

Then there is self-connection 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

This is clearly not what I want. Now changing the order of t2 and t1 gives 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 to combine a numeric column different from the row id. This, on the other hand, gives the correct result in both cases.

I hope that someone can make it clear what is happening here. As far as I understand, this is either a mistake in SQLite, or some subtle aspect of SQL that I do not know.

Thank,

+3
source share
2 answers

, SQLite - , , , . PG8.3, sqlite3.6.4 , . . sqlite; .

+4
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
0

More articles:


All Articles