Re: [sqlite] BUG

[Black, Michael (IS)]

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: sqlite-users-boun...@sqlite.org 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users