Hi ne 31. 5. 2020 v 22:02 odesÃlatel Vik Fearing <v...@postgresfriends.org> napsal:
> On 5/31/20 9:53 PM, Tom Lane wrote: > > Vik Fearing <v...@postgresfriends.org> writes: > >> postgres=# SELECT LAG(n, 1, -99) OVER (ORDER BY n) > >> postgres-# FROM (VALUES (1.1), (2.2), (3.3)) AS v (n) > >> postgres-# ORDER BY n; > >> ERROR: function lag(numeric, integer, integer) does not exist > >> LINE 1: SELECT LAG(n, 1, -99) OVER (ORDER BY n) > >> ^ > > > > Yeah, we have similar issues elsewhere. > > > >> Attached is a patch that fixes this issue using the new anycompatible > >> pseudotype. I am hoping this can be slipped into 13 even though it > >> requires a catversion bump after BETA1. > > > > When the anycompatible patch went in, I thought for a little bit about > > trying to use it with existing built-in functions, but didn't have the > > time to investigate the issue in detail. I'm not in favor of hacking > > things one-function-at-a-time here; we should look through the whole > > library and see what we've got. > > > > The main thing that makes this perhaps-not-trivial is that an > > anycompatible-ified function will match more cases than it would have > > before, possibly causing conflicts if the function or operator name > > is overloaded. We'd have to look at such cases and decide what we > > want to do --- one answer would be to drop some of the alternatives > > and rely on the parser to add casts, but that might slow things down. > > > > Anyway, I agree that this is a good direction to pursue, but not in > > a last-minute-hack-for-v13 way. > > Fair enough. I put it in the commitfest app for version 14. > https://commitfest.postgresql.org/28/2574/ > -- > Vik Fearing > The patch is ok. It is almost trivial. It solves one issue, but maybe it introduces a new issue. Other databases try to coerce default constant expression to value expression. I found a description about this behaviour for MSSQL, Sybase, BigQuery. I didn't find related documentation for Oracle, and I have not a access to some running instance of Oracle to test it. Ansi SQL say - type of default expression should be compatible with lag expression, and declared type of result should be type of value expression. IWD 9075-2:201?(E) 6.10 <window function> - j) v) Current implementation is limited (and the behaviour is not user friendly in all details), but new behaviour (implemented by patch) is in half cases out of standard :(. These cases are probably not often - and they are really corner cases, but I cannot to qualify how much important this fact is. For users, the implemented feature is better, and it is safe. Implementation is trivial - is significantly simpler than implementation that is 100% standard, although it should not be a hard problem too (in analyze stage it probably needs a few lines too). There has to be a decision, because now we can go in both directions. After choosing there will not be a way back. Regards Pavel
