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
