Re: [sqlite] BUG

[Black, Michael (IS)]

You failed to say what version you are using.  Running your SQL on 3.7.2 works 
just fine.
 
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE user
   ...> (
   ...>   id INTEGER PRIMARY KEY
   ...> );
sqlite> INSERT INTO "user" VALUES(4);
sqlite> INSERT INTO "user" VALUES(11);
sqlite>
sqlite> CREATE TABLE user_record
   ...> (
   ...>   record_id INTEGER PRIMARY KEY,
   ...>   record_version INTEGER,
   ...>   user_id INTEGER NOT NULL,
   ...>   name TEXT
   ...> );
sqlite> INSERT INTO "user_record" VALUES(76,8,11,'A');
sqlite> INSERT INTO "user_record" VALUES(86,11,4,'B');
sqlite> INSERT INTO "user_record" VALUES(87,3,4,'B');
sqlite>
sqlite> 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;
sqlite>
sqlite> create temp view test as select * from main.view_user where record_id in
   ...> (select record_id from main.view_user where name like '%');
sqlite>
sqlite> select * from test;
4|86|11|4|B
11|76|8|11|A
sqlite> select count(*) from test;
2
 
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



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