Thread: Possible wrong result with some "in" subquery with non-existing columns
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)
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 "=?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
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
> 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
Re: Possible wrong result with some "in" subquery with non-existing columns
From
"David G. Johnston"
Date:
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:
Where specifically in the docs would you put this, and how would be it be presented?
David J.