Rick Innis
2007-12-22 22:12:45 UTC
Wondering if this is actually a bug, before I file a bug report on it.
Consider this sequence:
=# create table public.test1 (id int);
CREATE TABLE
=# create table public.test2 (it int);
CREATE TABLE
=# select * from test1 where id in (select id from test2);
id
----
(0 rows)
Note that the select from test2, which references a non-existent
field, doesn't cause an error when that statement is used as a
subselect AND the non-existent field does exist in a table referenced
in the outer select.
This is particularly catastrophic when you replace 'select *' by
'delete', as illustrated below.
=# insert into test1 values(1);
INSERT 0 1
=# insert into test1 values(2);
INSERT 0 1
=# insert into test1 values(3);
INSERT 0 1
=# delete from test1 where id in (select id from test2);
DELETE 0
=# select * From test1;
id
----
1
2
3
(3 rows)
=# insert into test2 values(11);
INSERT 0 1
=# insert into test2 values(12);
INSERT 0 1
=# insert into test2 values(13);
INSERT 0 1
=# delete from test1 where id in (select id from test2);
DELETE 3
=# select * From test1;
id
----
(0 rows)
This has bitten me twice, recently, with consequent (though
recoverable) data loss. It may be that SQL is doing exactly as it
should, since 'id' is in scope within the subselect, but if that's
the case it's a nasty gotcha.
Obviously the subselect, when used as a select, does generate an error.
=# select id from test2;
ERROR: column "id" does not exist
LINE 1: select id from test2;
^
Referencing a field that doesn't exist in either also causes an error.
=# select * from test1 where id in (select if from test2);
ERROR: column "if" does not exist
LINE 1: select * from test1 where id in (select if from test2);
^
Insight from wiser SQL heads than mine would be appreciated.
--Rick.
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Consider this sequence:
=# create table public.test1 (id int);
CREATE TABLE
=# create table public.test2 (it int);
CREATE TABLE
=# select * from test1 where id in (select id from test2);
id
----
(0 rows)
Note that the select from test2, which references a non-existent
field, doesn't cause an error when that statement is used as a
subselect AND the non-existent field does exist in a table referenced
in the outer select.
This is particularly catastrophic when you replace 'select *' by
'delete', as illustrated below.
=# insert into test1 values(1);
INSERT 0 1
=# insert into test1 values(2);
INSERT 0 1
=# insert into test1 values(3);
INSERT 0 1
=# delete from test1 where id in (select id from test2);
DELETE 0
=# select * From test1;
id
----
1
2
3
(3 rows)
=# insert into test2 values(11);
INSERT 0 1
=# insert into test2 values(12);
INSERT 0 1
=# insert into test2 values(13);
INSERT 0 1
=# delete from test1 where id in (select id from test2);
DELETE 3
=# select * From test1;
id
----
(0 rows)
This has bitten me twice, recently, with consequent (though
recoverable) data loss. It may be that SQL is doing exactly as it
should, since 'id' is in scope within the subselect, but if that's
the case it's a nasty gotcha.
Obviously the subselect, when used as a select, does generate an error.
=# select id from test2;
ERROR: column "id" does not exist
LINE 1: select id from test2;
^
Referencing a field that doesn't exist in either also causes an error.
=# select * from test1 where id in (select if from test2);
ERROR: column "if" does not exist
LINE 1: select * from test1 where id in (select if from test2);
^
Insight from wiser SQL heads than mine would be appreciated.
--Rick.
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate