[hibernate-dev] 6.0 - formalized standard functions

[Christian Beikov]

Answers inline...

Am 31.05.2017 um 14:38 schrieb Steve Ebersole:
> Thanks for reply Christian..
>
>
> On Wed, May 31, 2017 at 7:18 AM Christian Beikov 
> <christian.beikov at gmail.com <mailto:christian.beikov at gmail.com>> wrote:
>
>     Looks good. OTOH I'd also like to see the following functions
>
>       * millisecond_diff
>       * second_diff
>       * minute_diff
>       * hour_diff
>       * day_diff
>       * week_diff
>       * month_diff
>       * quater_diff
>       * year_diff
>
>
> Why not basic temporal arithmetic?  E.g. `second(x) - second(y)` 
> (which isgenerally resolved to `extract(second from x) - 
> extract(second from y)`...
That would only extract the second part of the date/time which is not 
what these functions are about. These functions have semantics like 
`(epoch(x) - epoch(y)) / unit_factor`. The functions "datediff" from SQL 
Server[1] or "timestampdiff" from MySQL[2] have exactly these semantics. 
To give an example, consider the following expression

second_diff(
     '2017-01-01 00:01:01'::timestamp,
     '2017-01-01 00:00:01'::timestamp
)

When defining second_diff(x, y) as `second(y) - second(x)`, the result 
of that expression would be 0.
The semantics I anticipate are that this returns 60 which is what could 
be implemented by doing `(epoch(y) - epoch(x)) / unit_factor` where 
unit_factor for seconds would be simply 1.

This is not that easy to implement correctly(I have implementations for 
the most common DBMS), although I think these are quite commonly needed 
functions.

>       * epoch - generally defined as `extract(epoch from ?1)`
>
>
> Because ANSI SQL does not define epoch as an extractable part of a 
> temporal. TBH I do not think it defines milliseconds as extractable 
> either but would have to check. Also, I am not sure of databases that 
> support that, nor any alternate (which we'd *need* to support this as 
> a standard func) for databases which do not.
I have implementations for the most common DBMS that use the proprietary 
counterparts or calculate the epoch appropriately. Although I don't 
think there are that many DBMS that don't support this in any way, the 
question is if we ought to "not implement the function" because e.g. 
SQLite can't do it. I think one of the main questions we should ask 
ourselves is for which DBMS we'd like to provide support for these 
stanard functions.
>
>       * group_concat - string aggregation function
>
>
> How is this different from concat()?
>
>     The group_concat function would probably be not be possible to
>     implement
>     for all DBMS, but at least the bigger ones have one or the other
>     function that could be used to implement this.
>
>
> Unless I am missing something in your idea here (which is why I asked 
> above) we can absolutely implement this on all databases - everyone 
> has a concat operator as well.  And a SQM/SQL-AST function need not 
> evaluate to a db function - it just needs to be an expression.
group_concat is an aggregate and/or window function like count, sum, avg 
etc. which aggregates text values. You can think of group_concat as 
being the StringJoiner of SQL. It allows to do e.g.

select order.id, group_concat( concat ( i.amount, 'x ', i.product.name 
), ', ' )
from Order o
left join o.items i
group by order.id

Assume you have data like

Order( id = 1, items = { Item( amount = 2, product.name = 'Beer' ), 
Item( amount = 1, product.name = 'Burger' ) } )

this would produce 1 row [1, '2x Beer, 1x Burger']

This was just a simple example to demonstrate the purpose, but in 
reality the function must also be able to have an order by clause. MySQL 
defines group_concat([distinct] expression ORDER BY expression1 
[,expressionN] [SEPARATOR sep]).
Other DBMS have similar functions like listagg, string_agg etc. that all 
have that same functionality.

>     Don't know what is best regarding the "str" function. 
>
>
> `str()` is defined as `cast`; that's not the issue.  The issue is 
> merely the type - I am simply arguing that VARCHAR is more correct
VARCHAR as type sounds ok to me, but I don't think I ever used it. 
Whenever I need conversion I do so to concat the value with some other 
string so I use the concat operation without "str".

[1] 
https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql
[2] 
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff