The mistake is not obvious at first (took me a few takes to figure it out) and gets obscured by the use of views. I dismantled the
views ruling out interplay by designing a query that should do the same sans the views, like this:
SELECT _key FROM
(
SELECT m.project, m.date, m.time, MIN(m._key) AS _key
FROM (
SELECT w.project, w.date, w.time, MAX(w._key) AS _key
FROM stamp AS w GROUP BY w.project
) AS m
);
It works perfectly well with correct results. Now if I change the primary selector to anything that does not include "_key", it
breaks - such as:
SELECT project, time FROM
(
SELECT m.project, m.date, m.time, MIN(m._key) AS _key
FROM (
SELECT w.project, w.date, w.time, MAX(w._key) AS _key
FROM stamp AS w GROUP BY w.project
) AS m
);
The reason for this is easily understood in that MIN(m._key) does not work as intended because that query level (m) has no Group-by
clause and as such it displays whatever m.project it finds last (which is "ws") alongside whichever m._key is the minimum where the
m._key is not implicit in the requested results.
This does not violate the SQL standard to my best knowledge, however Interestingly, in MySQL the opposite happens in that it uses
whichever project is FIRST on the list, being "ca" alongside the minimum key for this exact same query.
To fix this issue there are several options, my favourite for all-round clarity
is:
SELECT * FROM
(
SELECT m.project, m.date, m.time, m._key
FROM (
SELECT w.project, w.date, w.time, MAX(w._key) AS _key
FROM stamp AS w GROUP BY w.project
) AS m ORDER BY m._key LIMIT 1
) AS v;
- which works for any requested columns in the primary selector. (The standard actually requires every sub-query to be uniquely
identifiable, hence the "AS v" at the end, even though SQLite is forgiving about this, it won't work in PostGres or MySQL etc.)
You can easily decompose this into views to achieve the same.
There are of course other ways to do it and if the query ever gets very big - into many thousands of lines in the implied "w" table,
some aggregation may be better.
Hope this helps!
Ryan
On 2013/12/19 17:54, Louis Jean-Richard wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
