pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedPossible wrong result with some "in" subquery with non-existing columns
4+ messages / 3 participants
[nested] [flat]
* Possible wrong result with some "in" subquery with non-existing columns
@ 2023-01-17 08:00 Xiong He <iihero@qq.com>
2023-01-17 08:04 ` Re: Possible wrong result with some "in" subquery with non-existing columns Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Xiong He @ 2023-01-17 08:00 UTC (permalink / raw)
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Dear All,
This is a common scenario in below query. I just verified in the pg14.x.
mydb=# create table test1(id1 int primary key, col2 varchar(32));
CREATE TABLE
mydb=# create table test2(id2 int primary key, col2 varchar(32));
CREATE TABLE
mydb=# insert into test1 values(1, 'wang');
INSERT 0 1
mydb=# insert into test2 values(2, 'tttt');
INSERT 0 1
mydb=# select * from test1 where id1 in (select id1 from test2 where id2 = 2);
id1 | col2
-----+------
1 | wang
(1 row)
mydb=# insert into test1 values(3, 'wang');
INSERT 0 1
mydb=# select * from test1 where id1 in (select id1 from test2 where id2 = 2);
id1 | col2
-----+------
1 | wang
3 | wang
(2 rows)
In the above query: id1 is not a column of table test2.
Should not we expect this should be en error instead of it thinking it's a column from the table : test1?
Regards,
Xiong
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Possible wrong result with some "in" subquery with non-existing columns
2023-01-17 08:00 Possible wrong result with some "in" subquery with non-existing columns Xiong He <iihero@qq.com>
@ 2023-01-17 08:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-17 09:06 ` Re: Possible wrong result with some "in" subquery with non-existing columns Xiong He <iihero@qq.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2023-01-17 08:04 UTC (permalink / raw)
To: Xiong He <iihero@qq.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
"=?ISO-8859-1?B?WGlvbmcgSGU=?=" <iihero@qq.com> writes:
> mydb=# create table test1(id1 int primary key, col2 varchar(32));
> mydb=# create table test2(id2 int primary key, col2 varchar(32));
> mydb=# select * from test1 where id1 in (select id1 from test2 where id2 = 2);
> In the above query: id1 is not a column of table test2.
Nope, but it's a legal outer reference.
> Should not we expect this should be en error instead of it thinking it's a column from the table : test1?
This behavior is required by the SQL standard, and has been for
decades. Yes, it trips up novices all the time, but it does
have valid use-cases.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Possible wrong result with some "in" subquery with non-existing columns
2023-01-17 08:00 Possible wrong result with some "in" subquery with non-existing columns Xiong He <iihero@qq.com>
2023-01-17 08:04 ` Re: Possible wrong result with some "in" subquery with non-existing columns Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-01-17 09:06 ` Xiong He <iihero@qq.com>
2023-01-17 15:07 ` Re: Possible wrong result with some "in" subquery with non-existing columns David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Xiong He @ 2023-01-17 09:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Thanks. Tom.
I think it just makes sense. It doesn't matter if the user knows how to express the real query.
Possibly it's better to emphasize this scenario as an example in the official document :-)
Regards,
Xiong
------------------ Original ------------------
From: "Tom Lane" <tgl@sss.pgh.pa.us>;
Date: Tue, Jan 17, 2023 04:04 PM
To: "Xiong He"<iihero@qq.com>;
Cc: "pgsql-bugs"<pgsql-bugs@lists.postgresql.org>;
Subject: Re: Possible wrong result with some "in" subquery with non-existing columns
"=?ISO-8859-1?B?WGlvbmcgSGU=?=" <iihero@qq.com> writes:
> mydb=# create table test1(id1 int primary key, col2 varchar(32));
> mydb=# create table test2(id2 int primary key, col2 varchar(32));
> mydb=# select * from test1 where id1 in (select id1 from test2 where id2 = 2);
> In the above query: id1 is not a column of table test2.
Nope, but it's a legal outer reference.
> Should not we expect this should be en error instead of it thinking it's a column from the table : test1?
This behavior is required by the SQL standard, and has been for
decades. Yes, it trips up novices all the time, but it does
have valid use-cases.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Possible wrong result with some "in" subquery with non-existing columns
2023-01-17 08:00 Possible wrong result with some "in" subquery with non-existing columns Xiong He <iihero@qq.com>
2023-01-17 08:04 ` Re: Possible wrong result with some "in" subquery with non-existing columns Tom Lane <tgl@sss.pgh.pa.us>
2023-01-17 09:06 ` Re: Possible wrong result with some "in" subquery with non-existing columns Xiong He <iihero@qq.com>
@ 2023-01-17 15:07 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2023-01-17 15:07 UTC (permalink / raw)
To: Xiong He <iihero@qq.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Tuesday, January 17, 2023, Xiong He <iihero@qq.com> wrote:
> Thanks. Tom.
> I think it just makes sense. It doesn't matter if the user knows how to
> express the real query.
> Possibly it's better to emphasize this scenario as an example in the
> official document :-)
>
It is in the FAQ:
https://wiki.postgresql.org/wiki/FAQ#Why_doesn.27t_PostgreSQL_report_a_column_not_found_error_when_u...
Where specifically in the docs would you put this, and how would be it be
presented?
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-01-17 15:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-17 08:00 Possible wrong result with some "in" subquery with non-existing columns Xiong He <iihero@qq.com>
2023-01-17 08:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-17 09:06 ` Xiong He <iihero@qq.com>
2023-01-17 15:07 ` David G. Johnston <david.g.johnston@gmail.com>
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