I did get this frustrate behaviour in my prototype of the versioning datastore where all versions of records are stored permanently. In my example the foreign identifiers are stored in the user table and all rows versions are stored in the user_record table. For visualization we need to get only last versions of records and so"group by timestamp" is needed in view (in the example above I did replace "timestamp" to "name" field). But some search operations can use all versions of records. My tests on the datastore are successfully tested with previous SQLite builds but returns strange results now and I simplificate "wrong" tests to this example. I think it may be not so trivial to find this "not bug" in production code... Of cource in development stage is not very difficult to rewrite such queries.
2010/10/26 Black, Michael (IS) <[email protected]> > And...if you drop the "group by" from the view it's correct again.... > > sqlite> drop view view_user; > sqlite> CREATE VIEW view_user AS > ...> SELECT user.id,user_record.* > ...> FROM user, user_record > ...> WHERE user.id=user_record.user_id > ...> ; > sqlite> select * from test; > 11|76|8|11|A > 4|86|11|4|B > 4|87|3|4|B > sqlite> select count(*) from test; > 3 > > 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 > ------------------------------------------------------------ > 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
