misuse of aggregate function max()

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

misuse of aggregate function max()

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: misuse of aggregate function max()

Simon Slavin-3
On 21 Jun 2019, at 10:29am, ingo <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Hick Gunter
In reply to this post by ingo
"Expressions in an index may not reference other tables and may not use subqueries nor functions whose result might change"

max(ts_from) not only depends on every other record in the table, but is also subject to change dependant on future inserts.

If what you want is a field that contains the highest previously seen ts_from, you will have to use a SELECT to determine the current max(ts_from).

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von ingo
Gesendet: Freitag, 21. Juni 2019 11:29
An: [hidden email]
Betreff: [EXTERNAL] [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
[hidden email]<mailto:[hidden email]>
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: misuse of aggregate function max()

ingo
In reply to this post by Simon Slavin-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: misuse of aggregate function max()

Jean-Christophe Deschamps-3


>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users