Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r0qE1-0086Nz-KN for pgsql-general@arkaria.postgresql.org; Wed, 08 Nov 2023 21:36:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r0qE0-0083ih-Ak for pgsql-general@arkaria.postgresql.org; Wed, 08 Nov 2023 21:36:36 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r0qE0-0083iY-0h for pgsql-general@lists.postgresql.org; Wed, 08 Nov 2023 21:36:36 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r0qDx-004dXI-E2 for pgsql-general@lists.postgresql.org; Wed, 08 Nov 2023 21:36:34 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 3A8LaVV6762199; Wed, 8 Nov 2023 16:36:31 -0500 From: Tom Lane To: Laurenz Albe cc: pgsql-general@lists.postgresql.org Subject: Re: Weirdness (bug?) with aggregates and subqueries In-reply-to: <895f34d19556dda5ff3319b584198df20085e596.camel@cybertec.at> References: <895f34d19556dda5ff3319b584198df20085e596.camel@cybertec.at> Comments: In-reply-to Laurenz Albe message dated "Wed, 08 Nov 2023 22:12:59 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <762197.1699479391.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Wed, 08 Nov 2023 16:36:31 -0500 Message-ID: <762198.1699479391@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Laurenz Albe writes: > I found this in a blog (https://buttondown.email/jaffray/archive/sql-sco= ping-is-surprisingly-subtle-and-semantic/): > CREATE TABLE aa (a INT); > INSERT INTO aa VALUES (1), (2), (3); > CREATE TABLE xx (x INT); > INSERT INTO xx VALUES (10), (20), (30); > SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa; > sum = > =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90 > 6 > (1 row) > Huh? Shouldn't that return three rows, just like No. The aggregate function is semantically of the closest query level that contributes a Var to its argument, so it's evaluated at the "FROM aa" level, causing that level to become an aggregated query that returns just one row. Then it acts like an outer reference as far as the sub-select is concerned. This is documented at the end of Section 4.2.7 in our manual, https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREG= ATES Thank the SQL spec for that weirdness. regards, tom lane