Subject: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query

From: PALAYRET Jacques <[email-masked]>
Date: Wed, 25 Feb 2026 11:14:28 +0000 (GMT)

Hello, 

I have 3 tables : t1, t2 and t3 
t1(posx integer, posy integer, dat timestamp, val_grid numeric) 
with Primary Key=(posx, posy, dat) 
t2(id integer, x integer, y integer) 
with Primary Key=(id) 
t3(id integer, dat timestamp, c1 numeric) 
with Primary Key=(id, dat) 

My (very simplified) query that doesn't work : 
SELECT 
id, to_char(dat, 'YYYYMM'), CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint, 
avg(val_grid), sum(c1), 
(SELECT col1 FROM another_table WHERE another_table.np=id LIMIT 1) 
FROM 
(SELECT * FROM t1 ) AS t1 
JOIN t2 ON (t1.posx=t2.x AND t1.posy=t2.y) 
FULL OUTER JOIN (SELECT * FROM t3 ) t3 USING(id, dat) 
--WHERE ... 
GROUP BY id, to_char(dat, 'YYYYMM'), CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint 
; 
ERROR: subquery uses ungrouped column "t2.id" from outer query 

-> The problem is on the third line (the subquery) : 
(SELECT col1 FROM another_table WHERE another_table.np=id LIMIT 1) 

However, If I replace " FULL OUTER " by " LEFT OUTER " or by " RIGHT OUTER ", it works . 

Could you please tell me how to correct the query, to fix this problem? 

Regards 
----- Météo-France ----- 
PALAYRET Jacques 
DCSC/GDC 
[email-masked] 
Fixe : +33 561078319

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 25 Feb 2026 08:00:03 -0800

On 2/25/26 3:14 AM, PALAYRET Jacques wrote:
> My (very simplified) query  that doesn't work:
> SELECT
>    id, to_char(dat, 'YYYYMM'), CASE 
> FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 
> THEN 2 ELSE 3 END::smallint,
> avg(val_grid), sum(c1),
> *(SELECT col1 FROM another_table WHERE another_table.np=id LIMIT 1)*
> FROM
> *JOIN* t2  ON (t1.posx=t2.x AND t1.posy=t2.y)
> *FULL OUTER JOIN* (SELECT * FROM t3   ) t3 USING(id, dat)
> --WHERE ...
> *GROUP BY* id, to_char(dat, 'YYYYMM'), CASE 
> FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 
> THEN 2 ELSE 3 END::smallint
> ;
> However, If I replace "FULL OUTER" by "*LEFT OUTER*" or by "*RIGHT 
> OUTER*", *it works*.
> 

Questions:

1) Why FROM (SELECT * FROM t1 ) AS t1 instead of just FROM t1?
    Same for JOIN (SELECT * FROM t3   ) t3

2) Why are the field names not table qualified e.g. t2.id, t3.id, etc?

3) What is the desired outcome?

-- 
Adrian Klaver
[email-masked]

---

From: PALAYRET Jacques <[email-masked]>
Date: Wed, 25 Feb 2026 16:00:11 +0000 (GMT)

hello again, 

It seems that following can be a solution (adding sub-query " SELECT * FROM " after the main FROM clause) : 
SELECT 
FROM ( 
SELECT * FROM ( 
) tb 
) t 

Regards 

De: "PALAYRET Jacques" <[email-masked]> 
À: [email-masked] 
Envoyé: Mercredi 25 Février 2026 12:14:28 
Objet: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query 

Hello, 

Regards 
----- Météo-France ----- 
PALAYRET Jacques 
DCSC/GDC

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 25 Feb 2026 09:20:18 -0700

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

---

From: Tom Lane <[email-masked]>
Date: Wed, 25 Feb 2026 12:33:39 -0500

> apparently executes in both left-join and right-join modes.

Well, it's a longstanding deficiency anyway.  The problem is that
the full-join-using merged column "id" is represented as
"COALESCE(t2.id, t3.id)" not as a single Var.  That should be okay,
because we can handle grouping by an expression, but
substitute_grouped_columns (and check_ungrouped_columns before it)
doesn't handle the case in subqueries:

 * NOTE: we recognize grouping expressions in the main query, but only
 * grouping Vars in subqueries.  For example, this will be rejected,
 * although it could be allowed:
 *		SELECT
 *			(SELECT x FROM bar where y = (foo.a + foo.b))
 *		FROM foo
 *		GROUP BY a + b;
 * The difficulty is the need to account for different sublevels_up.
 * This appears to require a whole custom version of equal(), which is
 * way more pain than the feature seems worth.

This commentary dates to 2003 (and it's from a patch that replaced an
older implementation with the same limitation; it doesn't look to me
like the case ever worked).  AFAIR, the number of complaints we've
gotten about this limitation in the past 30 years could be counted
without running out of thumbs.

Still, it is annoying.  I wonder if there's a way to do it without
either a large amount of new code or exponential time spent
trying useless subexpression matches...

			regards, tom lane

---

From: Tom Lane <[email-masked]>
Date: Wed, 25 Feb 2026 17:44:43 -0500

> Well, it's a longstanding deficiency anyway.
> ...

I've posted a draft fix for that at

https://www.postgresql.org/message-id/flat/531183.1772058731%40sss.pgh.pa.us

			regards, tom lane