sql - 子查询中错误命名的字段导致加入

Question

我遇到了由错误查询导致的数据丢失问题。
数据已恢复，但现在我想了解问题所在。

我在 SQL Server 2014 上遇到了这个问题，但我在 SQL Server 2000 和 PostgreSQL 上复制了它。具体来说，有一个DELETE。在以下场景中，我使用了 SELECT。

sql server 2014 的表创建:

CREATE TABLE [dbo].[tmp_color](
    [color_id] [int] NOT NULL,
    [color_name] [nvarchar](50) NOT NULL,
    [color_cat] [int] NOT NULL,
 CONSTRAINT [PK_tmp_color] PRIMARY KEY CLUSTERED (
    [color_id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
      , ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tmp_color_cat](
    [catid] [int] NOT NULL,
    [catname] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_tmp_color_cat] PRIMARY KEY CLUSTERED (
    [catid] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
      , ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

和 Postgres 版本:

CREATE TABLE tmp_color (
  color_id integer NOT NULL,
  color_name text,
  color_cat integer,
  CONSTRAINT tmp_color_pkey PRIMARY KEY (color_id)
);

CREATE TABLE tmp_color_cat (
  catid integer NOT NULL,
  catname text,
  CONSTRAINT tmp_color_cat_pkey PRIMARY KEY (catid)
);

数据填充(适用于两个 RDBMS):

INSERT INTO tmp_color_cat (catid, catname) VALUES (1, 'magic color');
INSERT INTO tmp_color_cat (catid, catname) VALUES (2, 'normal color');

INSERT INTO tmp_color (color_id, color_name, color_cat) VALUES (1, 'red', 1);
INSERT INTO tmp_color (color_id, color_name, color_cat) VALUES (2, 'green', 2);
INSERT INTO tmp_color (color_id, color_name, color_cat) VALUES (3, 'black', 1);

以下 SELECT 是错误的:

SELECT color_cat
FROM tmp_color_cat;

因为 color_cat 不存在于 tmp_color_cat 中。
但是，当您在子查询中使用它时:

SELECT * FROM tmp_color
WHERE color_cat IN(
    SELECT color_cat
    FROM tmp_color_cat
    WHERE catname = 'magic color'
);

它从tmp_color返回每条记录。
脚本中的逻辑错误很明显:开发人员编写了错误的列来标识类别。如果您要删除记录而不是选择它们，您将删除整个表格。不好。

这是期望的行为吗？还是子查询设计的结果？

通过观察SQL Server的执行计划，逻辑操作是Left Semi Join。

我找到了几个帖子，一个 for PostgreSQL和一个for SQL Server .我可以向开发人员组发送任何好的文档来解释为什么这不是错误吗？

如何避免此类问题？我的第一个想法是使用别名。别名很好。

Answer 1

Postgres 的权威报价

子查询的范围包括外部查询的所有可见列。不合格的名称首先解析到内部查询，然后向外扩展搜索。
分配表别名并使用这些别名对列名进行表限定以消除任何歧义 - 正如您已经暗示过的那样。

这是一个 example in the Postgres manual with a definitive statement explaining the scope :

SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)

[...]

Qualifying c1 as fdt.c1 is only necessary if c1 is also the name of a column in the derived input table of the subquery. But qualifying the column name adds clarity even when it is not needed. This example shows how the column naming scope of an outer query extends into its inner queries.

大胆强调我的。

手册同一章的示例列表中还有一个带有EXISTS 半连接的示例。这通常是 WHERE x IN(子查询) 的高级替代。但在这种特殊情况下，您也不需要。见下文。

一个例子:

• sql query to extract new records

数据库设计、命名规范

这场灾难的发生是因为对列名的混淆。表格定义中的清晰且一致的命名约定将大大降低这种情况发生的可能性。这适用于任何 RDBMS。让它们尽可能长以便清楚，但尽可能短。无论您的政策如何，都要保持一致。

对于 Postgres，我建议:

CREATE TABLE colorcat (
  colorcat_id integer NOT NULL PRIMARY KEY,
  colorcat    text UNIQUE NOT NULL
);

CREATE TABLE color (
  color_id    integer NOT NULL PRIMARY KEY,
  color       text NOT NULL,
  colorcat_id integer REFERENCES colorcat   -- assuming an FK
);

• 您已经有了合法的、小写的、不带引号的标识符。这很好。

• 使用一致的政策。不一致的政策比糟糕的政策更糟糕。不是 color_name(带下划线)与 catname。

• 我很少在标识符中使用“名称”。它不添加信息，只是使它们更长。所有标识符都是名称。您选择了 cat_name，留下了实际上携带信息的 color，并添加了没有携带信息的 name。如果您的数据库中有其他“类别”(这很常见)，您将拥有多个 cat_name，它们很容易在更大的查询中发生冲突。我宁愿使用 colorcat(就像表名一样)。

• 使名称指示列中的内容。对于颜色类别的 ID，colorcat_id 是一个不错的选择。 id 不是描述性的，colorcat 会产生误导。

• FK 列 colorcat_id 可以与引用列同名。两者的内容完全相同。还允许在连接中使用 USING 的简短语法。

包含更多详细信息的相关答案:

• How to implement a many-to-many relationship in PostgreSQL?

更好的查询

基于我假设的设计:

SELECT c.*
FROM   colorcat cc
JOIN   color c USING (colorcat_id)
WHERE  cc.colorcat = 'magic color';

这是假设 colorcat 和 color 之间的 1:n 关系(您没有指定，但似乎有可能)。

鲜为人知(因为语法在 SQL Server 等其他 RDBMS 中不同)，您可以 join in additional tables in a DELETE还有:

DELETE FROM color c
USING  colorcat cc
WHERE  cc.colorcat = 'magic color'
AND    cc.colorcat_id = c.colorcat_id;