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

On 04/16/2015 01:18 PM, David G. Johnston wrote:
> On Thu, Apr 16, 2015 at 1:06 PM, Josh Berkus <josh(at)agliodbs(dot)com
> <mailto:josh(at)agliodbs(dot)com>>wrote:
>
> On 04/16/2015 01:01 PM, David G. Johnston wrote:
> > 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?
>
> Your response seems very defensive. I'm unclear on why discussing
> improving an error message should get such a hostile response; it
> certainly doesn't invite a serious reply.
>
>
> Mostly because ​I have no clue how difficult it would be​...
>
> ​I'm all for improvement but obviously if it was that simple you would
> have just proposed the desired wording and asked someone to commit it.
> I was trying to understand (and communicate my understanding) why it
> wasn't done better in the first place.

Right. First, I don't know that it's possible for the parser to figure
out that error as opposed to actual mistakes in the filter clause, or
just straight gibberish. Second, I'm not sure how to write a succinct
HINT which gets the point across.

However, the error we're giving for windowing suggests that we can
improve things. In the example you quote:

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

... that's actually a lot clearer error. However, we still get the
same error with casting:

josh=# select count(*)::INT OVER (order by device_id, val) from
dataflow_0913 ;
ERROR: syntax error at or near "OVER"
LINE 1: select count(*)::INT OVER (order by device_id, val) from dat...

... so maybe we can't.

> Beyond that there is still the need to teach the user the correct syntax
> of these functions - and not only point out when they get it wrong so
> they blindly fix their typo - in order to better avoid the situation
> where the syntax is valid but the outcome is unexpected. Two separate
> problems of which the later seems more important than the former - and
> probably somewhat easier to implement.

Absolutely. Hence my blog post; I'm hoping that people will at least
get a google hit on the error text.

The problem is, in the main docs, how do we reasonably document
*combining* features? In Syntax? My head hurts just thinking about it.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com