By sql script below I get count(*)=1 for two rows in result set! I use SQLite 3.7.3. In 3.7.0.1 this work correct.
--------------------------------------- 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 ORDER BY name ASC; -- returns two rows select * from main.view_user where record_id in (select record_id from main.view_user where name like '%'); -- but count(*) returns 1 select count(*) from main.view_user where record_id in (select record_id from main.view_user where name like '%'); -- equal query returns only single row! select * from main.view_user where record_id in (select record_id from main.view_user where name like '%') order by name; --------------------------------------- -- 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
