> 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 ------------------------------------------------------------ CREATE TABLE user ( id INTEGER PRIMARY KEY ); INSERT INTO "user" VALUES(4); INSERT INTO "user" VALUES(11); CREATE TABLE user_record ( record_id INTEGER PRIMARY KEY, record_version INTEGER, user_id INTEGER NOT NULL, name TEXT ); INSERT INTO "user_record" VALUES(76,8,11,'A'); INSERT INTO "user_record" VALUES(86,11,4,'B'); INSERT INTO "user_record" VALUES(87,3,4,'B'); 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; create temp view test as select * from main.view_user where record_id in (select record_id from main.view_user where name like '%'); select * from test; select count(*) from test; ------------------------------------------------------------ -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
