Hello,
I run into an unexpected result from a SELECT on a view
in one of my schema for which I have distilled out the
following test script:
-- test.script
DROP TABLE IF EXISTS stamp
;
CREATE TABLE stamp
( date CHAR(10) NOT NULL
, time CHAR(8) NOT NULL
, project CHAR(2)
, _key CHAR(19)
)
;
DROP TRIGGER IF EXISTS stamp_added_key
;
CREATE TRIGGER stamp_added_key AFTER INSERT ON stamp
FOR EACH ROW WHEN new._key IS NULL
BEGIN
UPDATE stamp SET _key = date || 'T' || time;
END
;
DROP VIEW IF EXISTS most_recent_stamp
;
CREATE VIEW most_recent_stamp AS
SELECT w.project, w.date, w.time
, MAX(w._key) AS _key
FROM stamp AS w
GROUP BY w.project
;
DROP VIEW IF EXISTS oldest_among_recent_stamp
;
CREATE VIEW oldest_among_recent_stamp AS
SELECT m.project
, m.date
, m.time
, MIN(m._key) AS _key
FROM most_recent_stamp AS m
;
DELETE FROM stamp
;
INSERT INTO stamp (date, time, project)
VALUES ('2013-11-14','07:38:48','ra')
, ('2013-11-14','07:43:45','sp')
, ('2013-11-14','07:52:19','ws')
, ('2013-11-14','07:53:46','ca')
, ('2013-11-14','07:58:43','lj')
, ('2013-11-14','08:07:32','ma')
, ('2013-11-15','08:08:50','ra')
, ('2013-11-15','08:14:21','sp')
, ('2013-11-15','08:22:01','ws')
, ('2013-11-15','08:23:50','ca')
, ('2013-11-15','09:03:42','ma')
, ('2013-11-15','09:08:42','ra')
, ('2013-11-15','09:13:42','sp')
, ('2013-11-15','09:18:42','ws')
;
-- test sequence
select 'most recent stamps:';
select * from most_recent_stamp;
select ' ----- OK';
select 'oldest among recent stamps:';
select * from oldest_among_recent_stamp;
select ' ----- OK';
select 'oldest among recent stamps: (just show the project code)';
select project from oldest_among_recent_stamp;
select ' ----- WRONG';
select project, _key from oldest_among_recent_stamp;
select ' ----- OK';
select project, time from oldest_among_recent_stamp;
select ' ----- WRONG';
-- EOF
Here what my Linux with Ubuntu displays upon running it:
...$> ./sqlite3
SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read bug.script
most recent stamps:
ca|2013-11-15|08:23:50|2013-11-15T08:23:50
lj|2013-11-14|07:58:43|2013-11-14T07:58:43
ma|2013-11-15|09:03:42|2013-11-15T09:03:42
ra|2013-11-15|09:08:42|2013-11-15T09:08:42
sp|2013-11-15|09:13:42|2013-11-15T09:13:42
ws|2013-11-15|09:18:42|2013-11-15T09:18:42
----- OK
oldest among recent stamps:
lj|2013-11-14|07:58:43|2013-11-14T07:58:43
----- OK
oldest among recent stamps: (just show its project code)
ws
----- WRONG
lj|2013-11-14T07:58:43
----- OK
ws|09:18:42
----- WRONG
sqlite>
For me the view 'oldest_among_recent_stamp' is for all purposes
a table, albeit a volatile one that last only until the query process is
completed and its content as shown by SELECT * is correct.
The query "select project from oldest_among_recent_stamp;"
should return 'lj' and not 'ws'.
It is quite odd that the content of a table should depend on which of
its columns are selected or not further down.
There must certainly be a bug, either in my brain or in the sqlite
engine. ;)
Any clarifications are thankfully welcome.
Louis Jean-Richard
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
