转换为日期时间失败只在WHERE子句上发生？

Question

我在一些SQL服务器查询中遇到了问题。 原来我有一个表，其中包含“属性名称”和“属性值”字段，可以是任何类型，并存储为varchar。（是的...我知道。）

所有特定属性的日期似乎都存储在“YYYY-MM-DD hh:mm:ss”格式中（不确定百分之百，因为这里有数百万条记录），因此我可以毫无问题地执行以下代码：

select /*...*/ CONVERT(DATETIME, pa.Attribute_Value)
from 
    ProductAttributes pa
    inner join Attributes a on a.Attribute_ID = pa.Attribute_ID
where 
    a.Attribute_Name = 'SomeDate'

然而，如果我执行以下代码：

select /*...*/ CONVERT(DATETIME, pa.Attribute_Value)
from 
    ProductAttributes pa
    inner join Attributes a on a.Attribute_ID = pa.Attribute_ID
where 
    a.Attribute_Name = 'SomeDate'
    and CONVERT(DATETIME, pa.Attribute_Value) < GETDATE()

我将得到以下错误：从字符串转换日期和/或时间失败。

为什么它在where子句上失败而不是在select子句上？

另一个线索：

如果我使用存储在数据库中的实际Attribute_ID（PK）而不是通过Attribute_Name进行过滤，则可以正常工作，没有问题。

select /*...*/ CONVERT(DATETIME, pa.Attribute_Value)
from 
    ProductAttributes pa
    inner join Attributes a on a.Attribute_ID = pa.Attribute_ID
where 
    a.Attribute_ID = 15
    and CONVERT(DATETIME, pa.Attribute_Value) < GETDATE()

更新 感谢大家的回答。我发现很难选择一个正确的答案，因为每个人都指出了对理解问题有用的东西。它肯定与执行顺序有关。 事实证明，我的第一个查询工作正常，因为WHERE子句先执行，然后是SELECT。 我的第二个查询失败是由于同样的原因（因为属性没有被过滤，所以在执行相同的WHERE子句时转换失败）。 我的第三个查询有效，因为ID是索引的一部分（PK），所以它优先处理并首先根据该条件钻取结果。

谢谢！

- Alpha

---

你似乎假设了某种短路评估或保证WHERE子句中谓词的顺序。这并不是有保障的。当你在一个列中混合使用数据类型时，唯一安全的处理方式是使用CASE表达式。 - Martin Smith

@YetAnotherUser 刚刚检查了一下，列排序规则为 SQL_Latin1_General_CP1_CI_AS，所以我猜是这个原因，不过我没有找到其他大小写不同的值。好猜测！ - Alpha

@Aaron Bertrand 我同意，我也知道这一列中有一些非日期值，但我正在设置筛选器以仅显示日期时间（而且其中一个查询不会失败应该可以确认它们都可以正确转换为日期时间）。这并不那么糟糕，因为这些值来自自动化系统，所以我可以期望它们是有效的...但我也意识到了风险。 - Alpha

@Alpha - 不确定为什么你说你从那个角度抽象出来了。Attribute_Value包含非日期，你正在将该列转换为日期。你必须相信某种形式的预过滤是有保证的。 - Martin Smith

@Martin Smith，我刚刚读了Remus的文章，终于明白你说的话了。你是对的，这完全有道理。 - Alpha

Answer 1

你似乎认为WHERE子句中存在某种形式的短路评估或谓词保证的顺序。但这并不是有保障的。当一个列中含有混合数据类型时，处理它们的唯一安全方法是使用CASE表达式。

例如，使用以下方式：

CONVERT(DATETIME, 
      CASE WHEN ISDATE(pa.Attribute_Value) = 1 THEN pa.Attribute_Value END)

不是

CONVERT(DATETIME, pa.Attribute_Value)

- Martin Smith

Answer 2

如果转换出现在WHERE子句中，它可能会被评估比在投影列表中出现时要多得多的记录（值）。我以前在不同的上下文中谈过这个问题，请参见T-SQL functions do no imply a certain order of execution和On SQL Server boolean operator short-circuit。你的情况甚至更简单，但是类似，并且最终的根本原因是相同的：在处理像SQL这样的声明性语言时不要假设命令式执行顺序。

远远最好的解决方案是对数据进行清理并将列类型更改为DATETIME或DATETIME2类型。所有其他解决方法都会有一个或多个缺点，因此最好做正确的事情。

更新

经过更仔细的查看（抱歉，我在 @VLDB 会议期间只能偷偷看 SO），我意识到您有一个具有固有无类型语义的 EAV 存储（attribute_value 可以是字符串、日期、整数等）。我认为您最好使用存储中的 sql_variant，一直到客户端（即项目 sql_variant）。您可以在客户端中强制转换类型，所有客户端 API 都有从 sql_variant 中提取内部类型的方法，请参见 Using sql_variant Data（几乎所有客户端 API... Using the sql_variant datatype in CLR）。使用 sql_variant，您可以存储多种类型而不会出现通过字符串表示的问题，您可以使用 SQL_VARIANT_PROPERTY 检查存储值中的 BaseType 等内容，甚至可以执行检查约束以强制执行数据类型正确性。

- Remus Rusanu

---

我非常不愿意使用SQL_VARIANT，除非您在客户端进行所有演示、过滤和比较。在我们的EAV系统中，我们很快就放弃了SQL_VARIANT，转而使用每种类型的专用列。好吧，所以每行有两个NULL，但是您不必处理所有其他恶心的事情。为了公平起见，我在这里写了一些关于限制的博客文章：https://sqlblog.org/2009/10/12/bad-habits-to-kick-using-the-wrong-data-type ...如果列是SQL_VARIANT，您能展示一下您的查询吗？ - Aaron Bertrand

我理解你的观点。在 sql_variant EAV 结构上执行聚合操作会遇到类型转换问题，而专用列/类型可以轻松聚合值，因为它知道它们都在该类型的字段中，并且该类型不需要 CAST。这是一个有效的反对意见。 - Remus Rusanu

Answer 3

这与 SELECT 查询处理的顺序有关。 WHERE 子句在 SELECT 之前很长时间就被处理了。它必须确定要包含/排除哪些行。使用名称的子句必须使用扫描来调查所有行，其中一些不包含有效的日期/时间数据，而键可能会导致搜索，并且在该点不包括任何无效行。选择列表中的转换是最后执行的，显然此时它不会尝试转换无效的行。由于您正在将日期/时间数据与其他数据混合使用，因此可以考虑使用正确的数据类型将日期或数字数据存储在专用列中。同时，您可以通过以下方式推迟检查：

SELECT /* ... */
FROM
(
  SELECT /* ... */
    FROM ProductAttributes AS pa
    INNER JOIN dbo.Attributes AS a
    ON a.Attribute_ID = pa.Attribute_ID
    WHERE a.Attribute_Name = 'SomeDate'
    AND ISDATE (pa.Attribute_Value) = 1
) AS z
WHERE CONVERT(CHAR(8), AttributeValue, 112) < CONVERT(CHAR(8), GETDATE(), 112);

但更好的答案可能是尽可能使用Attribute_ID键而不是名称。

- Aaron Bertrand

---

这并不保证可行。SELECT列表中的计算标量可以在WHERE过滤器之前进行评估。例如，请参见此答案或此连接项。 - Martin Smith

不行，这个方法是错误的。你假设声明的顺序（子查询）意味着评估的顺序就像http://rusanu.com/2011/08/10/t-sql-functions-do-no-imply-a-certain-order-of-execution/中所述。查询优化器可以选择在attribute_name比较之前评估CONVERT，从而触发转换错误。 - Remus Rusanu

ISDATE()可能会被查询优化器移出子查询，在CONVERT之后进行评估。查询优化器可以自由地这样做。除非一个非常特定的索引设置了一个“陷阱”，否则不会发生这种情况。我曾经看到真实的产品支持案例，应用程序做了类似的事情，并在升级到新的SQL版本时出现了故障。我真的不知道有什么可靠的中庸之道，可以在OP中将强类型系统（项目DATETIME列）强加在灵活的EAV存储之上。sql_variant是我认为最接近的匹配（存储和项目sql_variant）。 - Remus Rusanu

但是谁在乎ISDATE()发生的位置呢？现在不再进行任何DATETIME转换，因此将不再出现错误-最坏的情况是，这将导致行在较不理想的位置被过滤。我们都同意最好的解决方案是更改数据类型，但这并不是唯一的答案，也不一定是可接受的答案。 - Aaron Bertrand

是的，如果删除了转换（即不再进行强类型投影），那么与不适当的CAST相关的所有问题都将消失。 - Remus Rusanu

Answer 4

看起来像是数据问题。使用两种不同的方法选择数据时，请查看数据，尝试查找不同的长度，然后选择不同集合中的项目并检查它们。 还要检查 null 值？（如果尝试将 null 转换为日期时间，我不确定会发生什么）

- kmcc049

---

将 null 转换为 datetime 会导致结果为 null。 - George Mastros

Answer 5

我认为问题是您的数据库中有错误的日期（显然）。

在您的第一个示例中，您没有检查WHERE子句中的日期，所有a.attribute.Name ='SomeDate'的日期都是有效的，因此它从未尝试转换错误的日期。

在您的第二个示例中，添加到WHERE子句会导致查询计划实际上转换所有这些日期并找到错误的日期，然后查看属性名称。

在您的第三个示例中，更改为使用Attribute_Id可能会更改查询计划，以便它只查找Id = 15的记录，然后检查这些记录是否具有有效的日期（也许Attribute_Id已经建立了索引，而Attribute_name没有）。

所以，您的确有一个错误的日期，但它不在任何Attribute_id = 15的记录中。

- PaulStock

Answer 6

你可以查看执行计划。可能第一个查询中的第二个条件（CONVERT(DATETIME, pa.Attribute_Value) < GETDATE()）会首先在所有行中评估，包括那些具有无效数据（非日期）的行，而在第二个查询的情况下，a.Attribute_ID = 15会首先进行评估。因此，排除了非日期值的行。

顺便提一句，第二个查询也可能更快，如果你在选择列表中没有来自Attributes 的任何内容，可以摆脱inner join Attributes a on a.Attribute_ID = pa.Attribute_ID。

在这方面，建议在还不太晚时摆脱EAV。

- nad2000

---

你可以尝试重新计算表格统计数据。如果 ProductAttributes 包含数百万行，那么对 CONVERT(DATETIME, pa.Attribute_Value) < GETDATE() 的评估效率非常低下。请尝试： ANALYZE TABLE ProductAttributes; ANALYZE TABLE Attributes;然后再次运行第一个查询。 - nad2000