⬆️ ‼️ 💢 PostgreSQL反模式：在SQL中计算条件 👨🏻‍💻 💹 🚽

SQL不是C ++，也不是JavaScript。因此，逻辑表达式的计算是不同的，并且这不是一回事：

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

在优化查询执行计划的过程中，PostgreSQL 可以任意“重新排列”等效条件，而不必为单个记录计算任何条件，而是将其分配给所使用的索引条件。简而言之，最简单的假设是您无法控制顺序将计算对等条件（以及是否计算）。

因此，如果仍要控制优先级，则需要使用条件表达式和运算符在结构上使这些条件不相等。

数据并与之合作是我们VLSI复合体的基础，因此对我们来说非常重要的是不仅要正确而且高效地执行操作。让我们看一些具体的例子，在这些例子中可能会出现表达式计算错误，并且它们的效率值得改善。

＃0：RTFM

从文档开始的示例：

当计算顺序很重要时，可以使用以下结构进行固定CASE。例如，这种避免在句子中除以零的方法是WHERE不可靠的：
SELECT ... WHERE x > 0 AND y/x > 1.5;
安全选项：
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
以这种方式使用的构造CASE可保护表达式免于优化，因此仅应在必要时使用。

＃1：触发条件

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

看起来一切看起来都不错，但是...没有人保证SELECT如果第一个条件为false，将不执行封闭的操作。正确使用嵌套IF：

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

现在让我们仔细看看-触发函数的整个结果被“包装”在中IF。这意味着没有什么可以阻止我们使用WHEN-condition从过程中删除此条件：

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

这种方法可以保证在错误情况下节省服务器资源。

＃2：OR / AND链

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

否则，您会发现两者EXISTS都是“ true”，但两者都会实现。

但是，如果我们确定其中一个更常为“真”（或AND链上为“假” ），是否有可能以某种方式“提高其优先级”，从而使第二个不再执行？

事实证明，这是可能的-算法方法接近PostgreSQL Antipatterns文章的主题：罕见的记录将到达JOIN的中间。

让我们仅在以下两种情况下“在情况下流行”：

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

在这种情况下，我们没有确定ELSE-value，也就是说，如果两个条件都为false ，它将CASE返回NULL，这FALSE与WHERE-condition相同。

此示例可以以另一种方式组合-口味和颜色：

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

＃3：如何[不]写条件

我们花了两天时间来分析此触发事件“奇怪”触发的原因-让我们看看原因。

资源：

IF( NEW."_" is null or NEW."_" = (select '""'::regclass::oid) or NEW."_" = (select to_regclass('""')::oid)
     AND (   OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW."" ) ) THEN ...

问题＃1：不等式不考虑NULL

想象一下，所有领域OLD都很重要NULL。会发生什么？

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

如上所述，从计算条件的角度来看，条件是NULL等效的FALSE。

解决方法：使用操作员IS DISTINCT FROM从ROW-运算符，在一次比较完整记录：

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

问题2：相同功能的不同实现

相比：

NEW."_" = (select '""'::regclass::oid)
NEW."_" = (select to_regclass('""')::oid)

为什么会有多余的嵌套SELECT？函数to_regclass呢？并且以不同的方式，为什么？..

修复：

NEW."_" = '""'::regclass::oid
NEW."_" = '""'::regclass::oid

问题3：布尔操作优先级

格式化源：

{... IS NULL} OR
{... } OR
{... } AND
( {... } )

糟糕...实际上，事实证明，在前两个条件中任何一个为真时，整个条件变为TRUE，而没有考虑不平等。这根本不是我们想要的。

固定：

(
  {... IS NULL} OR
  {... } OR
  {... }
) AND
( {... } )

问题4（小）：一个字段的复杂或条件

实际上，我们在第三名中遇到问题的原因恰恰是因为有三个条件。但是，您可以使用以下机制来代替它们coalesce ... IN：

coalesce(NEW."_"::text, '') IN ('', '""', '""')

因此，我们可以NULL“捕捉”，而OR带有方括号的复合体不必围栏。

总

我们修复了所得到的：

IF (
  coalesce(NEW."_"::text, '') IN ('', '""', '""') AND
  (
    OLD.""
  , OLD.""
  , OLD.""
  , OLD.""
  , OLD.""
  ) IS DISTINCT FROM (
    NEW.""
  , NEW.""
  , NEW.""
  , NEW.""
  , NEW.""
  )
) THEN ...

并且如果UPDATE由于条件中存在OLD/NEW顶层而考虑到该触发函数只能在-trigger 中使用，则通常可以将该条件放入WHEN-condition中，如＃1所示...

PostgreSQL反模式：在SQL中计算条件