On Thu, Apr 16, 2015 at 12:22 PM, Josh Berkus <j...@agliodbs.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.
