pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18307: system columns does not support using join
4+ messages / 4 participants
[nested] [flat]
* BUG #18307: system columns does not support using join
@ 2024-01-24 04:49 PG Bug reporting form <noreply@postgresql.org>
2024-01-24 14:26 ` Re: BUG #18307: system columns does not support using join David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2024-01-24 04:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: rekgrpth@gmail.com
The following bug has been logged on the website:
Bug reference: 18307
Logged by: RekGRpth
Email address: rekgrpth@gmail.com
PostgreSQL version: 16.1
Operating system: docker alpine
Description:
create table t(i int);
explain (costs off) select * from t join t tt on t.xmin = tt.xmin;
QUERY PLAN
---------------------------------
Hash Join
Hash Cond: (t.xmin = tt.xmin)
-> Seq Scan on t
-> Hash
-> Seq Scan on t tt
(5 rows)
explain (costs off) select * from t join t tt using (xmin);
ERROR: column "xmin" specified in USING clause does not exist in left table
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18307: system columns does not support using join
2024-01-24 04:49 BUG #18307: system columns does not support using join PG Bug reporting form <noreply@postgresql.org>
@ 2024-01-24 14:26 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-25 03:33 ` Re: BUG #18307: system columns does not support using join RekGRpth <rekgrpth@gmail.com>
2024-01-25 03:54 ` Re: BUG #18307: system columns does not support using join Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: David G. Johnston @ 2024-01-24 14:26 UTC (permalink / raw)
To: rekgrpth@gmail.com <rekgrpth@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Tuesday, January 23, 2024, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18307
> Logged by: RekGRpth
> Email address: rekgrpth@gmail.com
> PostgreSQL version: 16.1
> Operating system: docker alpine
> Description:
>
> create table t(i int);
>
> explain (costs off) select * from t join t tt on t.xmin = tt.xmin;
> QUERY PLAN
> ---------------------------------
> Hash Join
> Hash Cond: (t.xmin = tt.xmin)
> -> Seq Scan on t
> -> Hash
> -> Seq Scan on t tt
> (5 rows)
>
> explain (costs off) select * from t join t tt using (xmin);
> ERROR: column "xmin" specified in USING clause does not exist in left
> table
I don’t this being worth the effort to change, and really seems like
completely expected behavior. “Select *” doesn’t output xmin, it requires
explicit table qualification to see it. This is the same thing.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18307: system columns does not support using join
2024-01-24 04:49 BUG #18307: system columns does not support using join PG Bug reporting form <noreply@postgresql.org>
2024-01-24 14:26 ` Re: BUG #18307: system columns does not support using join David G. Johnston <david.g.johnston@gmail.com>
@ 2024-01-25 03:33 ` RekGRpth <rekgrpth@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: RekGRpth @ 2024-01-25 03:33 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Thanks, I'll look into it.
explain (costs off) select t.xmin from t join t tt on t.xmin = tt.xmin;
QUERY PLAN
---------------------------------
Hash Join
Hash Cond: (t.xmin = tt.xmin)
-> Seq Scan on t
-> Hash
-> Seq Scan on t tt
(5 rows)
explain (costs off) select t.xmin from t join t tt using (xmin);
ERROR: column "xmin" specified in USING clause does not exist in left table
explain (costs off) select tt.xmin from t join t tt using (xmin);
ERROR: column "xmin" specified in USING clause does not exist in left table
explain (costs off) select t.xmin, tt.xmin from t join t tt using (xmin);
ERROR: column "xmin" specified in USING clause does not exist in left table
ср, 24 янв. 2024 г. в 19:26, David G. Johnston <david.g.johnston@gmail.com>:
>
> On Tuesday, January 23, 2024, PG Bug reporting form <noreply@postgresql.org> wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference: 18307
>> Logged by: RekGRpth
>> Email address: rekgrpth@gmail.com
>> PostgreSQL version: 16.1
>> Operating system: docker alpine
>> Description:
>>
>> create table t(i int);
>>
>> explain (costs off) select * from t join t tt on t.xmin = tt.xmin;
>> QUERY PLAN
>> ---------------------------------
>> Hash Join
>> Hash Cond: (t.xmin = tt.xmin)
>> -> Seq Scan on t
>> -> Hash
>> -> Seq Scan on t tt
>> (5 rows)
>>
>> explain (costs off) select * from t join t tt using (xmin);
>> ERROR: column "xmin" specified in USING clause does not exist in left table
>
>
> I don’t this being worth the effort to change, and really seems like completely expected behavior. “Select *” doesn’t output xmin, it requires explicit table qualification to see it. This is the same thing.
>
> David J.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18307: system columns does not support using join
2024-01-24 04:49 BUG #18307: system columns does not support using join PG Bug reporting form <noreply@postgresql.org>
2024-01-24 14:26 ` Re: BUG #18307: system columns does not support using join David G. Johnston <david.g.johnston@gmail.com>
@ 2024-01-25 03:54 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2024-01-25 03:54 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: rekgrpth@gmail.com <rekgrpth@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, January 23, 2024, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>> create table t(i int);
>>
>> explain (costs off) select * from t join t tt on t.xmin = tt.xmin;
>> QUERY PLAN
>> ---------------------------------
>> Hash Join
>> Hash Cond: (t.xmin = tt.xmin)
>> -> Seq Scan on t
>> -> Hash
>> -> Seq Scan on t tt
>> (5 rows)
>>
>> explain (costs off) select * from t join t tt using (xmin);
>> ERROR: column "xmin" specified in USING clause does not exist in left
>> table
> I don’t this being worth the effort to change, and really seems like
> completely expected behavior. “Select *” doesn’t output xmin, it requires
> explicit table qualification to see it. This is the same thing.
Well, it is odd that "using (xmin)" isn't equivalent to the allegedly
equivalent "on t.xmin = tt.xmin". This is down to the infrastructure
in transformFromClauseItem(), which searches the lists of (regular,
non-system) relation output column names to expand USING(). But like
you, I can't get excited about changing it. There are a couple of
practical reasons why not:
* NATURAL JOIN is defined in terms of USING. But we *certainly* don't
want "x NATURAL JOIN y" deciding that it should equate all the system
columns of x to those of y. So there's going to be inconsistency at
one level or the other no matter what.
* I really find it hard to imagine a valid use case for joining on any
system column. There are use-cases for joining on TID in an UPDATE
involving a self-join to the target table; but you can't write that
with JOIN USING syntax.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-01-25 03:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-24 04:49 BUG #18307: system columns does not support using join PG Bug reporting form <noreply@postgresql.org>
2024-01-24 14:26 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-25 03:33 ` RekGRpth <rekgrpth@gmail.com>
2024-01-25 03:54 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox