public inbox for [email protected]
help / color / mirror / Atom feedPostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
6+ messages / 4 participants
[nested] [flat]
* PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
@ 2026-02-25 11:14 PALAYRET Jacques <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: PALAYRET Jacques @ 2026-02-25 11:14 UTC (permalink / raw)
To: [email protected]
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 protected]
Fixe : +33 561078319
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
@ 2026-02-25 16:00 Adrian Klaver <[email protected]>
parent: PALAYRET Jacques <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2026-02-25 16:00 UTC (permalink / raw)
To: PALAYRET Jacques <[email protected]>; [email protected]
On 2/25/26 3:14 AM, PALAYRET Jacques wrote:
> 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?
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?
>
> Regards
> ----- Météo-France -----
> PALAYRET Jacques
> DCSC/GDC
> [email protected]
> Fixe : +33 561078319
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
@ 2026-02-25 16:00 PALAYRET Jacques <[email protected]>
parent: PALAYRET Jacques <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: PALAYRET Jacques @ 2026-02-25 16:00 UTC (permalink / raw)
To: [email protected]
hello again,
It seems that following can be a solution (adding sub-query " SELECT * FROM " after the main FROM clause) :
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 (
(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)
) tb
) t
--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
;
Regards
De: "PALAYRET Jacques" <[email protected]>
À: [email protected]
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,
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 protected]
Fixe : +33 561078319
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
@ 2026-02-25 16:20 David G. Johnston <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2026-02-25 16:20 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: PALAYRET Jacques <[email protected]>; [email protected]
On Wed, Feb 25, 2026 at 9:00 AM Adrian Klaver <[email protected]>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
@ 2026-02-25 17:33 Tom Lane <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2026-02-25 17:33 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Adrian Klaver <[email protected]>; PALAYRET Jacques <[email protected]>; [email protected]
"David G. Johnston" <[email protected]> writes:
> My first impression is that this is a bug. Especially since the query
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
@ 2026-02-25 22:44 Tom Lane <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2026-02-25 22:44 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Adrian Klaver <[email protected]>; PALAYRET Jacques <[email protected]>; [email protected]
I wrote:
> "David G. Johnston" <[email protected]> writes:
>> My first impression is that this is a bug. Especially since the query
>> apparently executes in both left-join and right-join modes.
> Well, it's a longstanding deficiency anyway.
> ...
> 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...
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-02-25 22:44 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-25 11:14 PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query PALAYRET Jacques <[email protected]>
2026-02-25 16:00 ` Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query Adrian Klaver <[email protected]>
2026-02-25 16:20 ` Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query David G. Johnston <[email protected]>
2026-02-25 17:33 ` Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query Tom Lane <[email protected]>
2026-02-25 22:44 ` Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query Tom Lane <[email protected]>
2026-02-25 16:00 ` PALAYRET Jacques <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox