oracle - 比较两个日期字段时为 "Literal does not match format string"

Question

我这里发生了一个奇怪的行为，我希望有人能向我解释一下。

我在查询中有两个字段。一个是数字字段，通过 to_date('01/01/1960', 'mm/dd/yyyy') + somethingorother 转换为日期。另一种是文本字段，其中至少包含一个非日期值，该值通过 to_date(textField, 'mm/dd/rrrr') 转换为日期。如果我运行查询，它运行良好。但是，如果我将查询包含在 select * from ( ) where field1 > field2 中，它会出现“ORA-01861:文字与格式字符串不匹配”错误。如果我尝试在拉回文本字段的子查询的 where 子句中排除已知的非日期值，它没有帮助。

我知道如果没有代码这几乎是不可能弄清楚的，但我想知道是否有人可以向我解释为什么它在没有过滤器的情况下工作，但当我添加它时却爆炸了。谢谢。

Answer 1

普遍的问题是，由于 SQL 是一种基于集合的语言，Oracle 可以自由地按照它选择的任何顺序评估您的谓词。如果您有一个存储一些日期值和一些非日期值的 VARCAHR2 列，这意味着 Oracle 可以自由评估首先过滤掉所有非日期值的谓词或评估首先检查转换后的 DATE 值是否大于另一个的谓词。如果它碰巧在过滤掉非日期值之前计算了 DATE 不等式谓词 (field1 > field2)，您将得到一个错误。

SQL 基于集合的事实是使用错误的数据类型问题如此严重的主要原因之一——您永远无法确定查询之前总是会过滤掉不可转换的数据调用转换函数。即使你设置了抽象障碍，比如过滤掉无效数据的 View ，优化器可以自由地重新排序谓词，所以你可以很容易地发现你的查询最终打破了你的抽象障碍，或者你的查询大部分时间都有效，除非优化器碰巧选择了不同的执行计划。 Jonathan Gennick 有一篇非常有趣的文章 Subquery Madness谈到这个具体问题。

您可以编写自己的转换函数来忽略异常并在您的查询中使用它。例如，您可以创建一个函数

CREATE OR REPLACE FUNCTION my_to_date( p_date_str    IN VARCHAR2,
                                       p_format_mask IN VARCHAR2 )
  RETURN DATE
IS
  l_date DATE;
BEGIN
  l_date := to_date( p_date_Str, p_format_mask ); 
  RETURN l_date;
EXCEPTION
  WHEN OTHERS THEN
    RETURN null;
END;

然后在您的查询中使用该函数

SELECT *
  FROM (SELECT to_date('01/01/1960', 'mm/dd/yyyy') + somethingorother field1,
               my_to_date( textField, 'mm/dd/rrrr' ) field2
          FROM your_table
         WHERE some_condition)
 WHERE field1 > field2

这将起作用，因为它对任何字符串调用 my_to_date 都是有效的，无论它是否评估为有效的 DATE，因此您的查询不再依赖于顺序Oracle 选择哪个来评估谓词。