Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l5yvo-0004wx-RT for pgsql-bugs@arkaria.postgresql.org; Sat, 30 Jan 2021 22:41:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l5yvk-0005vA-PP for pgsql-bugs@arkaria.postgresql.org; Sat, 30 Jan 2021 22:41:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l5yvj-0005v3-V9 for pgsql-bugs@lists.postgresql.org; Sat, 30 Jan 2021 22:41:24 +0000 Received: from a2-socom10.uol.com.br ([200.147.33.253] helo=smtps.uol.com.br) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l5yvb-0003d1-1P for pgsql-bugs@lists.postgresql.org; Sat, 30 Jan 2021 22:41:22 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by a2-socom9.uol.com.br (Postfix) with ESMTP id B494EC000051 for ; Sat, 30 Jan 2021 19:41:09 -0300 (-03) X-UOL-SMTP: SMTP587 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=uol.com.br; s=ubz; t=1612046468; bh=PVBU/xilgR80G2i5fK+R7+Hz4Ca3VbmlV3lPcHWRMqQ=; h=From:To:Subject:Date; b=VjvrZY4qP0c4/MAHcY6aiEGVz2E+o4yKHIFrgPA1mXIhjRLchqqTRyq2Yjkc/2AOa eVDU/Ay2Z/SaXYrRA0hiH288Z+qTDzcPPQ/9niygCrEILSuxRNimdCH/mZSyjDFJ7z K0edXcvy2q642z1IDLT6V++buYPt0eN3wc4Azgm4= Received: from CsottoLenovo (unknown [191.162.226.38]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) (Authenticated sender: csotto@uol.com.br) by a2-socom9.uol.com.br (Postfix) with ESMTPSA id DB224C00004F for ; Sat, 30 Jan 2021 19:41:06 -0300 (-03) From: "Carlos Sotto Maior \(UOL\)" To: 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> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01CB_01D6F73F.DB75D060" X-Mailer: Microsoft Outlook 16.0 Thread-Index: Adb3V3he8JE4c15BQraSmVYUPjI7GA== Content-Language: pt-br X-VADE-SPAMSTATE: clean X-VADE-SPAMSCORE: 0 X-VADE-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrgeduledrfeeggdduieehucetufdoteggodetrfdotffvucfrrhhofhhilhgvmecufgfqnfdqrfetkfffpdcuqfgfvfenuceurghilhhouhhtmecufedtudenucenucfjughrpefhvffufffkgggtofhtsegrtdhgpedvtddvnecuhfhrohhmpedfvegrrhhlohhsucfuohhtthhoucforghiohhrucgmldgfqffnmgdmfdcuoegtshhothhtohesuhholhdrtghomhdrsghrqeenucggtffrrghtthgvrhhnpeehtdeiheehudeludfffeeuteeuteelffehfeekgedtfeffgfejhfegvdevgfetveenucfkphepudeluddrudeivddrvddviedrfeeknecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehinhgvthepudeluddrudeivddrvddviedrfeekpdhhvghlohepvehsohhtthhonfgvnhhovhhopdhmrghilhhfrhhomhepfdevrghrlhhoshcuufhothhtohcuofgrihhorhcumgdlfgfqnfgmmddfuceotghsohhtthhosehuohhlrdgtohhmrdgsrheqpdhrtghpthhtohepphhgshhqlhdqsghughhssehlihhsthhsrdhpohhsthhgrhgvshhqlhdrohhrgh X-SIG5: c1bd748f6ce12175620ce61580263a12 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_01CB_01D6F73F.DB75D060 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 will give SYNTAX error if the output-name is used as argument. Comment: Although column 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 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 ------=_NextPart_000_01CB_01D6F73F.DB75D060 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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() =3D=3D> 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 =3D True AND cnt > = 1

          =       ORDER BY cnt DESC

 

--ERROR MESSAGE = *******

--ERROR:  column "cnt" does not = exist

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

          =             &= nbsp;           = ^

--SQL = state: 42703

--Character: 135

 

 

 

------=_NextPart_000_01CB_01D6F73F.DB75D060--