Re: [sqlite] misuse of aggregate function max()

2019-06-21 Thread Jean-Christophe Deschamps




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()

2019-06-21 Thread ingo
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()

2019-06-21 Thread Simon Slavin
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()

2019-06-21 Thread ingo
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