Richard, with "PRAGMA reverse_unordered_selects = 1;". it's returns count(*)=0. This is strange for me and not help for development.
2010/10/26 Richard Hipp <d...@sqlite.org> > On Mon, Oct 25, 2010 at 4:06 PM, Alexey Pechnikov <pechni...@mobigroup.ru > >wrote: > > > > 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? > > > > You miss my point. "test" in this case doesn't have one row or two rows. > It has an arbitrary number of rows due to indeterminacy in your view. > Sometimes "test" will return one row. Sometimes it will return two. You > can never predict which. Both are "correct" in the sense that both are > allowed interpretations of what SQLite ought to do. > > The above will never happen for a simple table named "test". It only > happens for things like: > > ... WHERE record_id IN (SELECT record_id FROM view_user WHERE name LIKE > '%'); > > where the record_id value returned from the view_user view is > indeterminate. The WHERE clause above might be equivalent to > > ... WHERE record_id IN (76,86) > > and in that case count(*) will return 2. But the WHERE clause might also > be > equivalent to > > ... WHERE record_id IN (76,87) > > in which case count(*) will return 1. SQLite is free to choose either > interpretation for the subquery in your WHERE clause, and hence might get > either 1 or 2 as the count(*) result. Version 3.7.2 happened to get 2. > Version 3.7.3 happens to get 1. Who knows what 3.7.4 will get - both > answers are correct.... > > > > 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 > > > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- 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
