加入中断 WHERE 子句子查询

Question

我的数据库中有 3 个表。

• PARENT_A 具有"ID"主键列。
• PARENT_B有一个"ID"主键列。
• CHILD 具有"PARENT_A_ID"和"PARENT_B_ID"外键列。它还有一个"START_DATE"列，这是一个 VARCHAR（可悲的是，我无法更改这一点）。

现在，我有以下查询。

更新 - 我更新了子查询，使其更像我的实际代码。c2 上还有一个额外的限制，保证START_DATE是有效日期。

SELECT *
FROM PARENT_B pb
LEFT OUTER JOIN CHILD c1 ON c1.PARENT_B_ID = pb.ID
WHERE pb.ID IN
(
    SELECT c2.PARENT_B_ID
    FROM PARENT_A pa
    LEFT OUTER JOIN CHILD c2 ON c2.PARENT_A_ID = pa.ID
    WHERE TO_DATE(c2.START_DATE, 'mm/dd/yyyy') BETWEEN
          ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -12) AND
          (TRUNC(SYSDATE, 'MONTH') - 1)
    AND c2.HAS_VALID_DATE = 1
);

此查询失败。我得到了一个ORA-01843: not a valid month例外。但是，如果我删除第一个联接（查询的第 3 行），查询运行正常。

我不知道发生了什么。子查询本身运行良好，所有值都采用正确的日期格式。

有谁知道发生了什么？

Answer 1

几乎可以肯定的是，CHILD中至少有一些行无法使用mm/dd/yyyy格式将start_date字符串转换为日期。 由于 Oracle 可以按任何顺序计算谓词，因此我希望您在执行to_date之前，您碰巧在没有联接的情况下获得的计划是在执行之前消除这些坏行，而您碰巧通过联接获得的计划是在出于其他原因消除行之前评估to_date。 当然，明天，优化程序可以选择不同的计划，查询可能会与联接一起使用，如果没有它，查询可能会失败。 或者，您可能会发现查询成功返回第一行，然后在尝试提取行时引发 ORA-01843 错误。

除非可以修复数据，否则最佳选择通常是编写自己的字符串到日期转换例程，如果字符串无法转换为日期，则返回NULL。 类似的东西

CREATE OR REPLACE FUNCTION my_to_date( p_dt_str IN VARCHAR2, p_mask IN VARCHAR2 )
  RETURN DATE
  DETERMINISTIC
IS
  l_dt DATE;
BEGIN
  l_dt := to_date( p_dt_str, p_mask );
  RETURN l_dt;
EXCEPTION
  WHEN others THEN
    RETURN NULL;
END;

然后你的查询会说

WHERE my_to_date( c2.start_date, 'mm/dd/yyyy' ) between ...

您还可以使用此函数查找start_date无效的行

SELECT *
  FROM child
 WHERE start_date is not null
   AND my_to_date( start_date, 'mm/dd/yyyy' ) is null

乔纳森·根尼克（Jonathan Gennick）有一篇很棒的文章Subquery Madness，更详细地介绍了这个问题。 这也是一本有趣的读物。