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.96) (envelope-from ) id 1vvHdE-00Bvlh-2k for pgsql-general@arkaria.postgresql.org; Wed, 25 Feb 2026 16:21:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vvHdD-007BzP-1p for pgsql-general@arkaria.postgresql.org; Wed, 25 Feb 2026 16:20:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vvHdD-007BzC-0N for pgsql-general@lists.postgresql.org; Wed, 25 Feb 2026 16:20:59 +0000 Received: from mail-ot1-x331.google.com ([2607:f8b0:4864:20::331]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vvHd9-00000001FFr-3q84 for pgsql-general@lists.postgresql.org; Wed, 25 Feb 2026 16:20:58 +0000 Received: by mail-ot1-x331.google.com with SMTP id 46e09a7af769-7d4beaf25deso820939a34.0 for ; Wed, 25 Feb 2026 08:20:56 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1772036455; cv=none; d=google.com; s=arc-20240605; b=I4k8fWGG/ghM7JNcF1zKGtepv12p0iq7Yl2KpFwiVkZ34mK7iW05jS6aK41hlUHMTh 4MDu5Gy7Y2L+sxPO/GftHi4JjRJNnnO89hlKMTPMYA6bba535PVpMGpja51ME/yS9X5B OwZhpbGe9UlKg2zcnUToF9t2FdJTB7M8xVYTvZPIYniFPLT2diCCnzfYnOhw4DD5pKS/ F34IsUPoeqYqI2Gjdx/5gdhzeBpnEBvMLAJe5M2qc1A0zeLuEXxWntph2vkyCp1435p/ bL+jMlaZ4qPGwSmUnQ/S2LroMoZOiUq9bQW9l5DpmCcA6tRa1FdtbM7Ph6F9YUuOt3ye +FhQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=BU7Yo28LrgH9JUA2lobQUvFixqc1oKvlE8IujwrUij0=; fh=EiJN6Oj2Pv20ze+vB071EWh6ykDYTQ0I2ROekl24oCE=; b=LXKPwPt3wVdlqKLYqT/yvPIHOgeawSKl/yw6zGihDQQoxaX0kp+T47kpKWC9oj8HJJ GwJarGtWuTWqBTjdY9pgIqohNCbr1hXRIngdgSRHCvSyv2jJvfoCk6O0dEGpcnOnOKGu 3zDPMA9gcSKQezwYfLYJ2Ao4gfcakwPthIfReviZ6QymFSg1VOROM3hYwOe/YSzUeOJ/ bLSMBMxdbUisC4FaPOQ9olfXmEFN/+q1CVsn1R5O28UaJ3+jgWuH5aYDy8KTkPLJt0lt I5GJnUVxo0+NoLXO0r2XErvnzRlhn0jrD8Fzd0Awd+0l8Gl1LFY8h4Hf7uQOKMI6d3bZ f3Tw==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1772036455; x=1772641255; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=BU7Yo28LrgH9JUA2lobQUvFixqc1oKvlE8IujwrUij0=; b=L2NPtqvI+vqsAmAzx2+rWEnFlhgCVYMEIXx9OmU/EHU7MY21HClEeJQQAoyJv2r+Zw zm+zJ31Xe0aV96nFuuSNoCpMpbRraNrPtNteA6j+SEkPfXLjWF5FdFlPOUAZ0IfyAVb/ 0LtsLkf9l/HPqdCtCdQ4FJ4TfRd0GY87TOD8NMs3zEtmr4bVo/Ss45jH97J7G5xbNuiY Ejgzk2SB2L7QKWMbRBUA/1gPUzZm4Ubkd++czyzd64QXbtPG7jpFIbRT7Rlo4ewd3NlL 8uw8G1/qYtGrYzsROw7oHMSCiODSRo0hh2goFrAf04bYWYg7yJpZEn/5OnMqrBZ0sDXU vnHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1772036455; x=1772641255; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=BU7Yo28LrgH9JUA2lobQUvFixqc1oKvlE8IujwrUij0=; b=chgLTy8GmVg+7pDOHZszPZW118q9nZVJ9cOnlH9v5FqhZjVz4J8sPwIgXtbNs/i1E/ x0GpQx5ke/hQd/KVx2VbFLyf9WddULrX2rLgklM1BFgtFnzO8YNRFjgIoJXzK6V2YuMq jI2qrYz5KQHuTouk4f+s0rwDTS5/wrTvKy4ZuPWBCqFsmx645szbOAY/UNPmWq0XcWAg euglylVXOGXkvnFDtol03CagkvCHtDFS2Ct/HTz2M5VKf3UI2TnqD2TrdALDtfnYBb51 LiknoN41JabAt8kL9A1ZnFU84laQjnhDUc/tR7JCZ4aNnoi4d1Nsfaa5YAen6zLOUJAg gqRA== X-Forwarded-Encrypted: i=1; AJvYcCW3McvPljXfozxpjVLdGu5jxThj7OdiMaXE3/0LQdza5kT/PqgKQC18kqhVwpJMUDqAEtWJk1TXeGURS60a@lists.postgresql.org X-Gm-Message-State: AOJu0YwAnBeCjqfLk2iSi3dHizPBBUc7ESTNbgE9QmvANPSBphdqOT+b COtjDhQF5+6zNyP3l6Ibrj5TBuRbW+jLVuOkyeq7FEq/FBExMZ6hJf8f5FIce0tE+NuDwHIFXyh +25r6r1lb6gj/Kq/hm3GXxyP4PGG3uC9UGClX X-Gm-Gg: ATEYQzxANUqWaeJ6fK06MTjzchNwHgPkWIXpsYwTl5kI840gxeJi+mbYYUh6rzWp7EL AtAShqVjhTTTOki7vf13tDkQDfG7BNiABtAoOMw8bkRy2wKpXppHTScpI4za/r0s11g7jtLs7tE 8U8rdeV4lgIB8b2cVmzPNR/pCPvXj8miv6CMDcO1ohV9+/aZgpwhyasUhOE2qp8vx8SEqSiSV11 lQxNBdvLMsX9Vz7brp2Gxx8AcDc5ovADEEkYIgAB2ey+0H2xSnkrym/0vAbK8L4foXKGWUGrMOA syF+/fg= X-Received: by 2002:a05:6820:4a8b:b0:674:e92f:3c89 with SMTP id 006d021491bc7-679e629047bmr1647384eaf.22.1772036454790; Wed, 25 Feb 2026 08:20:54 -0800 (PST) MIME-Version: 1.0 References: <970600119.344834366.1772018068349.JavaMail.zimbra@meteo.fr> <52acd108-2949-4762-80c2-c761662fde73@aklaver.com> In-Reply-To: <52acd108-2949-4762-80c2-c761662fde73@aklaver.com> From: "David G. Johnston" Date: Wed, 25 Feb 2026 09:20:18 -0700 X-Gm-Features: AaiRm53vrOxlEnAWcRck6ISjZwdj6ymyYsINMxUpkdt15lQKXDoJTeTx9duYCSw Message-ID: Subject: Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query To: Adrian Klaver Cc: PALAYRET Jacques , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000006abdb1064ba863c9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006abdb1064ba863c9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Feb 25, 2026 at 9:00=E2=80=AFAM Adrian Klaver wrote: > 1) Why FROM (SELECT * FROM t1 ) AS t1 instead of just FROM t1? > Same for JOIN (SELECT * FROM t3 ) t3 > Seems immaterial. It's just a consequence of simplifying the original problem query. If it is consequential that would seem buggy. > 2) Why are the field names not table qualified e.g. t2.id, t3.id, etc? > Because of the USING clause, those references shouldn't exist in the main query. > 3) What is the desired outcome? > For the engine to realize the 'id' in the group by and the 'id' in the scalar subquery are the same 'id' - the one produced by the USING clause, not either of the t2 or t3 ids. My first impression is that this is a bug. Especially since the query apparently executes in both left-join and right-join modes. David J. --0000000000006abdb1064ba863c9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Feb 25, 2026 at 9:00=E2=80=AFAM Adrian Klaver <= adrian.klaver@aklaver.com&= gt; wrote:
1) Why FROM (SELECT * = FROM t1 ) AS t1 instead of just FROM t1?
=C2=A0 =C2=A0 Same for JOIN (SELECT * FROM t3=C2=A0 =C2=A0) t3

Seems immaterial.=C2=A0 It's just a consequence o= f simplifying=C2=A0the original problem query. If it is consequential that = would seem buggy.


2) Why are the field names not table qualified e.g. t2.id, t3.id, etc?
<= br>
Because=C2=A0of the USING clause, those references shouldn'= t exist in the main query.


3) What is the desired outcome?

For the= engine to realize the 'id' in the group by and the 'id' in= the scalar subquery are the same 'id' - the one produced by the US= ING clause, not either of the t2 or t3 ids.

My first i= mpression is that this is a bug.=C2=A0 Especially=C2=A0since the query appa= rently executes in both left-join and right-join modes.

<= div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif= ">David J.

--0000000000006abdb1064ba863c9--