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
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