Обсуждение: [HACKERS] lag(bigint,int,int), etc?
Hi, The following rather contrived example illustrates that lag(), lead() (and probably other functions) can't automatically cast an integer to a bigint: select lag(sum,1,0) over () from (select sum(generate_series) over (order by generate_series) from generate_series(1,10)) x; ERROR: function lag(bigint, integer, integer) does not exist LINE 1: select lag(sum,1,0) over () from (select sum(generate_series... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. I guess this is because the lag() and lead() functions take any type, and hence the default must be of the same type. This had me stumped for a few while until I realised that the types were different. Would there be any way to implement an automatic conversion? On the off-chance that this is actually a bug, this is on 9.6.3, but it also occurs on 9.3.17 Thanks, Colin
On Tue, Jun 27, 2017 at 10:01 AM, Colin 't Hart <colinthart@gmail.com> wrote: > Hi, > > The following rather contrived example illustrates that lag(), lead() > (and probably other functions) can't automatically cast an integer to > a bigint: > > select lag(sum,1,0) over () from (select sum(generate_series) over > (order by generate_series) from generate_series(1,10)) x; > ERROR: function lag(bigint, integer, integer) does not exist > LINE 1: select lag(sum,1,0) over () from (select sum(generate_series... > ^ > HINT: No function matches the given name and argument types. You > might need to add explicit type casts. > > > I guess this is because the lag() and lead() functions take any type, > and hence the default must be of the same type. > This had me stumped for a few while until I realised that the types > were different. > > Would there be any way to implement an automatic conversion? > > On the off-chance that this is actually a bug, this is on 9.6.3, but > it also occurs on 9.3.17 Why not cast the arguments? The first and the third argument have to be the same, and the second argument is always int. merlin
On 27 Jun 2017, at 17:06, Merlin Moncure <mmoncure@gmail.com> wrote: > >> On Tue, Jun 27, 2017 at 10:01 AM, Colin 't Hart <colinthart@gmail.com> wrote: >> Hi, >> >> The following rather contrived example illustrates that lag(), lead() >> (and probably other functions) can't automatically cast an integer to >> a bigint: >> >> select lag(sum,1,0) over () from (select sum(generate_series) over >> (order by generate_series) from generate_series(1,10)) x; >> ERROR: function lag(bigint, integer, integer) does not exist >> LINE 1: select lag(sum,1,0) over () from (select sum(generate_series... >> ^ >> HINT: No function matches the given name and argument types. You >> might need to add explicit type casts. >> >> >> I guess this is because the lag() and lead() functions take any type, >> and hence the default must be of the same type. >> This had me stumped for a few while until I realised that the types >> were different. >> >> Would there be any way to implement an automatic conversion? >> >> On the off-chance that this is actually a bug, this is on 9.6.3, but >> it also occurs on 9.3.17 > > Why not cast the arguments? The first and the third argument have to > be the same, and the second argument is always int. > > merlin I know that I can cast. I'm wondering if it would be possible/desirable to implement automatic casting. Automatic castingworks already for functions defined to take bigint and you pass in an integer. But not for these functions that takeany type. /Colin
On Tue, Jun 27, 2017 at 10:12 AM, Colin 't Hart <colinthart@gmail.com> wrote: > On 27 Jun 2017, at 17:06, Merlin Moncure <mmoncure@gmail.com> wrote: >> >>> On Tue, Jun 27, 2017 at 10:01 AM, Colin 't Hart <colinthart@gmail.com> wrote: >>> Hi, >>> >>> The following rather contrived example illustrates that lag(), lead() >>> (and probably other functions) can't automatically cast an integer to >>> a bigint: >>> >>> select lag(sum,1,0) over () from (select sum(generate_series) over >>> (order by generate_series) from generate_series(1,10)) x; >>> ERROR: function lag(bigint, integer, integer) does not exist >>> LINE 1: select lag(sum,1,0) over () from (select sum(generate_series... >>> ^ >>> HINT: No function matches the given name and argument types. You >>> might need to add explicit type casts. >>> >>> >>> I guess this is because the lag() and lead() functions take any type, >>> and hence the default must be of the same type. >>> This had me stumped for a few while until I realised that the types >>> were different. >>> >>> Would there be any way to implement an automatic conversion? >>> >>> On the off-chance that this is actually a bug, this is on 9.6.3, but >>> it also occurs on 9.3.17 >> >> Why not cast the arguments? The first and the third argument have to >> be the same, and the second argument is always int. >> >> merlin > > I know that I can cast. I'm wondering if it would be possible/desirable to implement automatic casting. Automatic castingworks already for functions defined to take bigint and you pass in an integer. But not for these functions that takeany type. Right. If you've got 2+ types being passed for 'any', which argument should you get? It's ambiguous, so the type rules into 'any' taking functions are stricter than for regular functions. Casting behaviors more complex than they look on the surface and changes to make them more flexible are typically difficult to make work. merlin