Re: FILTER/WITHIN GROUP vs. expressions; is a HINT possible here?

On Thu, Apr 16, 2015 at 12:22 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> Folks:
>
> SELECT
> device_id,
> count(*)::INT as present,
> count(*)::INT FILTER (WHERE valid) as valid_count,
> mode()::INT WITHIN GROUP (order by val) as mode,
> percentile_disc(0.5)::INT WITHIN GROUP (order by val)
> as median
> FROM dataflow_0913
> GROUP BY device_id
> ORDER BY device_id;
>
> ERROR: syntax error at or near "FILTER"
> LINE 4: count(*)::INT FILTER (WHERE valid)
> as valid_count,
>
>
> The error is right, that's invalid syntax. I can't insert a ::INT
> between the aggregate() and FILTER. However, the error message is also
> rather confusing to the user; they're likely to look for their mistake
> in the wrong place. The same goes for WITHIN GROUP (and OVER, too, I
> think).
>
>
​​SELECT count(*)::int OVER ()
FROM ( VALUES (1),(2),(3) ) src;
​

> Is there some kind of possible HINT we could add to make this easier to
> debug?
>

​Do you have a suggested hint so that the effort to make it work can be
compared to its usefulness?

​For kicks I ran the following - since "val::type" is simply another syntax
for "type(val)"...

SELECT ceil(count(*)) OVER ()
FROM ( VALUES (1),(2),(3) ) src

SQL Error: ERROR: OVER specified, but ceil is not a window function nor an
aggregate function
LINE 1: SELECT ceil(count(*)) OVER ()

The non-hint has been around as long as window functions and hasn't really
come up as an issue - not enough so to motivate a change at least. Is the
idiomatic usage of FILTER and WITHIN GROUP making this error more likely?

The foot-gun in your blog post is more problematic but also seemingly
impossible to avoid except through education of the user. It would not be
unreasonable to accept that the current error is acting like a canary and
forcing the user to go read the documentation on OVER/FILTER/WITHIN GROUP
and learn to write the expression as a single unit.

If this is not covered adequately enough in the documentation then that
should be remedied. Did you evaluate the documentation in that light while
preparing your blog post?

David J.