postgresql 子查询在无效列上失败时返回行 [英] postgresql subquery returning rows when it should fail on invalid column

问题描述

我预计会失败的子查询，因为它引用了一个不存在的列，实际上返回子查询中引用的表的所有行.这是 postgresql 中的预期行为还是 postgresql 错误?

A subquery which I would expect to fail because it references a column which does not exist, actually returns all the rows of the table referenced in the subquery. Is this expected behaviour in postgresql or a postgresql bug?

模拟问题:

create table zz_temp_01
(
column_a    varchar(20),
column_b    int
)
;

create table zz_temp_02
(
column_c    int,
column_d    varchar(20)
)
;


insert into zz_temp_01
values
('test1', 1),
('test2', 2)
;

insert into zz_temp_02
values
(1, 'Monday'),
(2, 'Tuesday'),
(3, 'Wednesday'),
(4, 'Thursday'),
(5, 'Friday'),
(6, 'Saturday'),
(7, 'Sunday')
;

select * from zz_temp_02
where column_c in
(select column_c from zz_temp_01)
;

这个查询显然是不正确的，因为子查询引用了表 zz_temp_01 中的 column_c，而它本应使用 column_b.执行select column_c from zz_temp_01"会导致错误:列column_c"不存在.但是，如果执行整个查询，则结果是:

This query is clearly incorrect since the subquery references column_c from the table zz_temp_01 when it should have used column_b. Executing "select column_c from zz_temp_01" results in an error: column "column_c" does not exist. However if the whole query is executed, then the result is:

1;"Monday"
2;"Tuesday"
3;"Wednesday"
4;"Thursday"
5;"Friday"
6;"Saturday"
7;"Sunday"

这是完整的 zz_temp_02 表.我本来希望查询在错误条件下失败，或者至少不返回任何行.

which is the complete zz_temp_02 table. I would have expected the query to fail on an error condition or at least return no rows.

推荐答案

不，这是 SQL 标准要求的正确行为.

No, this is the correct behaviour as required by the SQL standard.

更详细的解释见这个邮件线程

一个常见的抱怨，但不幸的是，这只是必须的学到了.

子查询中对 column_c 的引用来自 zz_temp_02 - 在这种情况使 where 子句 (zz_temp_02.column_c IN (zz_temp_02.column_c))这将始终评估为真.

A common complaint but unfortunately something that simply has to be learned.

The reference to column_c in the subquery comes from zz_temp_02 - which in this case makes the where clause (zz_temp_02.column_c IN (zz_temp_02.column_c)) ​which will always evaluate to true.

<小时>

^{我用问题中的名称替换了邮件线程中的列名和表名，但引用没有改变.}

这篇关于postgresql 子查询在无效列上失败时返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！