pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Carlos Sotto Maior \(UOL\) <csotto@uol.com.br>
To: pgsql-bugs@lists.postgresql.org
Subject: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR
Date: Sat, 30 Jan 2021 19:41:05 -0300
Message-ID: <01ca01d6f759$00c24510$0246cf30$@uol.com.br> (raw)

Use of an aggregate function on SELECT/GROUP By is a common need. 

Also is common to use a friendly output-name like "cnt" for count(*).

 

But trying to filter results with HAVING <condition> will give SYNTAX error
if the output-name is used as argument.

 

Comment: Although column <cnt> does not exists in table it is present in the
Group By result that will be filtered by HAVING clause. 

In other words: Postgresql is matching <HAVING> arguments to columns at
table level instead of matching to a group by result level.

 

Thanks for the attention.

Carlos Sotto Maior

 

 

 

--SELECT VERSION() ==> PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

 

-- Table: public.z_having

-- DROP TABLE public.z_having;

CREATE TABLE public.z_having

(

    id integer NOT NULL,

    f1 integer,

    f2 integer,

    payload character varying COLLATE pg_catalog."default",

    active boolean,

    CONSTRAINT pk_z_having_id PRIMARY KEY (id)

)

TABLESPACE pg_default;

ALTER TABLE public.z_having OWNER to postgres;

COMMENT ON TABLE public.z_having

    IS 'Use of Group By with count agregate with output-name (AS clause) is
OK\nBut If HAVING clause is used to filter on output-name (cnt) you get a
syntax error';

 

INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (1, 10, 10,
'aaaaa', false);

INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (2, 10, 20,
'aaaaa', true);

INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (3, 10, 30,
'aaaaa', false);

INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (4, 10, 10,
'aaaaa', true);

INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (5, 10, 20,
'aaaaa', true);

INSERT INTO public.z_having(id, f1, f2, payload, active) VALUES (6, 10, 30,
'aaaaa', true);

 

-- GROUP BY WORKS OK --

SELECT count(*) as cnt, f1, f2, active 

                FROM public.z_having

                GROUP BY f1, f2, active

                ORDER BY cnt DESC

 

-- Adding HAVING clause on output-name for the count() aggregate function
results in SYNTAXerror

SELECT count(*) as cnt, f1, f2, active 

                FROM public.z_having

                GROUP BY f1, f2, active

                HAVING active = True AND cnt > 1

                ORDER BY cnt DESC

 

--ERROR MESSAGE *******

--ERROR:  column "cnt" does not exist

--LINE 5:  HAVING active = True AND cnt > 1

                                  ^

--SQL state: 42703

--Character: 135

 

 

 

view thread (4+ messages)  latest in thread

Message-ID: <01ca01d6f759$00c24510$0246cf30$@uol.com.br>
Permalink:  ../01ca01d6f759$00c24510$0246cf30$@uol.com.br/
Also on:    postgresql.org/message-id/01ca01d6f759$00c24510$0246cf30$@uol.com.br

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: csotto@uol.com.br, pgsql-bugs@lists.postgresql.org
  Subject: Re: Use of HAVING (Select/Group By) on a output-name of an aggregate function causes SYNTAX ERROR
  In-Reply-To: <01ca01d6f759$00c24510$0246cf30$@uol.com.br>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox