Re: [sqlite] misuse of aggregate function max()
That is what I use now, together with the select as Hick mentioned. An other version I played with is two indexes, one unique on (id, ts_from) to find the last version if eol is not null and one unique on (id, ts_eol) where eol = null to find the current active version of id. Beware that in almost all contexts, null != null. Hence your condition eol = null will never be satisfied. Use isnull instead. JcD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] misuse of aggregate function max()
On 21-6-2019 11:46, Simon Slavin wrote: > You will note that SQLite is perfectly happy with > > CREATE UNIQUE INDEX idx_test_c >ON test(id, ts_from, ts_eol) > WHERE ts_eol = NULL > ; That is what I use now, together with the select as Hick mentioned. An other version I played with is two indexes, one unique on (id, ts_from) to find the last version if eol is not null and one unique on (id, ts_eol) where eol = null to find the current active version of id. Thanks, Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] misuse of aggregate function max()
On 21 Jun 2019, at 10:29am, ingo wrote: > Is this because max() is not deterministic, > or because current_timestamp is not, > or both? The max(). For the reason you identified: it would have to recalculate the index values for all rows in the table every time any of the rows changed. You will note that SQLite is perfectly happy with CREATE UNIQUE INDEX idx_test_c ON test(id, ts_from, ts_eol) WHERE ts_eol = NULL ; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] misuse of aggregate function max()
CREATE TABLE test( id INTEGER NOT NULL, ts_from TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, ts_eol TEXT DEFAULT NULL ); CREATE UNIQUE INDEX idx_test ON test(id, max(ts_from), ts_eol) WHERE ts_eol = NULL ; --Error: misuse of aggregate function max() Is this because max() is not deterministic, or because current_timestamp is not, or both? Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users