You failed to say what version you are using. Running your SQL on 3.7.2 works just fine. SQLite version 3.7.2 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE user ...> ( ...> id INTEGER PRIMARY KEY ...> ); sqlite> INSERT INTO "user" VALUES(4); sqlite> INSERT INTO "user" VALUES(11); sqlite> sqlite> CREATE TABLE user_record ...> ( ...> record_id INTEGER PRIMARY KEY, ...> record_version INTEGER, ...> user_id INTEGER NOT NULL, ...> name TEXT ...> ); sqlite> INSERT INTO "user_record" VALUES(76,8,11,'A'); sqlite> INSERT INTO "user_record" VALUES(86,11,4,'B'); sqlite> INSERT INTO "user_record" VALUES(87,3,4,'B'); sqlite> sqlite> CREATE VIEW view_user AS ...> SELECT user.id,user_record.* ...> FROM user, user_record ...> WHERE user.id=user_record.user_id ...> GROUP BY user.id; sqlite> sqlite> create temp view test as select * from main.view_user where record_id in ...> (select record_id from main.view_user where name like '%'); sqlite> sqlite> select * from test; 4|86|11|4|B 11|76|8|11|A sqlite> select count(*) from test; 2 Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems
________________________________ From: [email protected] on behalf of Alexey Pechnikov Sent: Mon 10/25/2010 3:06 PM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] BUG > The result of the view above is undefined. It will choose one of the > user_record rows for each distinct user.id, but you don't know which row. Yes! But it choose only single user_record row for two distinct user_id in count(*) expression. Are you really think that count(*)=1 for _two_ rows is not the bug? sqlite> select * from test; 4|87|3|4|B 11|76|8|11|A sqlite> select count(*) from test; 1
_______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
