sql - 当结果已被过滤以匹配我的日期格式（Oracle SQL）时，为什么这个 to_date 不起作用

Question

我有一个包含一列（VARCHAR2）的表'A'。该表包含一行包含文本“01/01/2021”和另一行包含文本“A”。

当我尝试过滤掉“A”然后 to_date 剩余值时，我得到“ORA-01858：在需要数字的地方发现了一个非数字字符”。我已经尝试了两种方式。

select *
from tbl
where col <> 'A'
and to_Date(col,'DD/MM/YYYY') = to_date('01/01/2020','DD/MM/YYYY');

select *
from (  select * 
        from tbl 
        where col <> 'A')
where to_Date(col,'DD/MM/YYYY') = to_date('01/01/2020','DD/MM/YYYY');

我可以理解为什么第一个可能不起作用，但在第二个示例中，to_date 应该只看到过滤后的数据（即“01/01/2020”）。

当我删除“A”的值时，该语句运行并且我得到了我的结果，所以它没有运行的原因似乎是因为它试图对“A”的值进行to_date，即使那应该是到时候就被过滤掉了。

我已经能够使用实际的 Oracle 表来复制它，但不幸的是，当我尝试使用 WITH AS 重现这些表时，查询有效并且没有遇到错误 - 另一个谜！

为什么这个查询不起作用？操作顺序似乎得到了满足（如果我使用 WITH AS，它就可以工作）。

标签： sqloracleoracle-sqldeveloper

Answer 1

Oracle（和其他数据库）没有义务在评估外部谓词之前评估应用于内联视图的谓词。实际上，从性能优化的角度来看，您经常希望优化器将选择性谓词从外部查询推送到视图、内联视图或子查询中。在这种情况下，查询是否抛出错误将取决于优化器选择的查询计划以及它实际首先评估的谓词。

作为一种快速破解方法，您可以更改内联视图以防止推送谓词。在这种情况下，a 的存在会rownum阻止优化器推送谓词。您还可以使用提示no_push_pred来尝试强制优化器使用您想要的计划

select *
from (  select t.*, rownum rn
        from tbl t
        where col <> 'A')
where to_Date(col,'DD/MM/YYYY') = to_date('01/01/2020','DD/MM/YYYY');

但是，这些快速技巧中的任何一个的问题是，某些未来版本的优化器可能有比您今天所知道的更多的选项，因此您将来可能会遇到问题。

更好的选择是重写查询，这样您就不必关心谓词的评估顺序。在这种情况下（取决于 Oracle 版本），这很容易，因为to_date允许您在出现转换错误时指定一个值

select *
from tbl
where col <> 'A'
and to_Date(col default null on conversion error,'DD/MM/YYYY') = 
      to_date('01/01/2020','DD/MM/YYYY');

如果您使用的是早期版本的 Oracle，或者to_date只是实际问题的一个示例，您可以创建一个执行相同操作的自定义函数。

create function safe_to_date( p_str in varchar2, p_fmt in varchar2 )
  return date
is
begin
  return to_date( p_str, p_fmt );
exception
  when value_error
  then
    return null;
end safe_to_date;
select *
from tbl
where col <> 'A'
and safe_to_date(col,'DD/MM/YYYY') = to_date('01/01/2020','DD/MM/YYYY');