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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: FILTER/WITHIN GROUP vs. expressions; is a HINT possible here?
Дата
Msg-id CAKFQuwaPPdUKgX6ikNHTVhJCyUO=DLVMVn796e0Z-bXFVfK65g@mail.gmail.com
обсуждение исходный текст
Ответ на FILTER/WITHIN GROUP vs. expressions; is a HINT possible here?  (Josh Berkus <josh@agliodbs.com>)
Список pgsql-hackers
On Thu, Apr 16, 2015 at 12:22 PM, Josh Berkus <josh@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.

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: Supporting src/test/modules in MSVC builds
Следующее
От: Josh Berkus
Дата:
Сообщение: Re: FILTER/WITHIN GROUP vs. expressions; is a HINT possible here?