SQL performance issues.

[Andrew Mashenkov]

H2 documentation says: "Deterministic functions must always return the same
value for the same parameters."

In sql query example below, heavy "datediff" deterministic function will be
called 4 times per row.

Example:

Select
  avg(datediff('s',ts1,ts2)) as avg_diff,
  min(datediff('s',ts1,ts2)) as min_diff,
  max(datediff('s',ts1,ts2)) as max_diff
From table


I'd expected function was called once per row.
H2 have only optimization for function with constant arguments, however
previous query obviously can be optimized. We need to have a workaround
there.

See IGNITE-4035 <https://issues.apache.org/jira/browse/IGNITE-4035>

[Sergi]

select avg(d), min(d), max(d) from (select datediff('s', ts1, ts2) d from t)

will work for you.

Sergi

2016-10-11 12:13 GMT+03:00 Andrey Mashenkov <[hidden email]>:

> H2 documentation says: "Deterministic functions must always return the same
> value for the same parameters."
>
> In sql query example below, heavy "datediff" deterministic function will be
> called 4 times per row.
>
> Example:
>
> Select
>   avg(datediff('s',ts1,ts2)) as avg_diff,
>   min(datediff('s',ts1,ts2)) as min_diff,
>   max(datediff('s',ts1,ts2)) as max_diff
> From table
>
>
> I'd expected function was called once per row.
> H2 have only optimization for function with constant arguments, however
> previous query obviously can be optimized. We need to have a workaround
> there.
>
> See IGNITE-4035 <https://issues.apache.org/jira/browse/IGNITE-4035>
>