处理SQL标准中的错误值

I have a question about the SQL standard which I'm hoping a SQL language lawyer can help with.

我有一个关于SQL标准的问题,我希望SQL语言律师可以提供帮助。

Certain expressions just don't work. 62 / 0, for example. The SQL standard specifies quite a few ways in which expressions can go wrong in similar ways. Lots of languages deal with these expressions using special exceptional flow control, or bottom psuedo-values.

某些表达式不起作用。例如,62/0。 SQL标准指定了表达式可能以类似方式出错的几种方式。许多语言使用特殊的异常流控制或底部伪值来处理这些表达式。

I have a table, t, with (only) two columns, x and y each of type int. I suspect it isn't relevant, but for definiteness let's say that (x,y) is the primary key of t. This table contains (only) the following values:

我有一个表,t,(只)两列,x和y,每个都是int类型。我怀疑它不相关,但是为了明确,我们可以说(x,y)是t的主键。此表包含(仅)以下值:

What behavior is required by the SQL standard for SELECT expressions operating on this table which may involve division(s) by zero? Alternatively, if no one behavior is required, what behaviors are permitted?

对于在此表上运行的SELECT表达式,SQL标准需要什么行为,这可能涉及零除以?或者,如果不需要任何行为,允许哪些行为?

For example, what behavior is required for the following select statements?

例如,以下select语句需要什么行为?

The easy one:

简单的一个:

SELECT x, y, x / y AS quot
FROM t

A harder one:

更难的一个:

SELECT x, y, x / y AS quot
FROM t
WHERE y != 0

An even harder one:

更难的一个:

SELECT x, y, x / y AS quot
FROM t
WHERE x % 2 = 0

Would an implementation (say, one that failed to realize on a more complex version of this query that the restriction could be moved inside the extension) be permitted to produce a division by zero error in response to this query, because, say it attempted to divide 3 by 0 as part of the extension before performing the restriction and realizing that 3 % 2 = 1? This could become important if, for example, the extension was over a small table but the result--when joined with a large table and restricted on the basis of data in the large table--ended up restricting away all of the rows which would have required division by zero.

一个实现(比如,一个未能在该查询的更复杂版本上实现限制可以在扩展内移动的实现)是否允许在响应此查询时产生零错误除法,因为它说它试图在执行限制之前将3除以0作为扩展的一部分并实现3%2 = 1?例如,如果扩展名在一个小表上,但结果 - 当与一个大表连接并且基于大表中的数据限制时 - 结果限制了所有的行,这可能变得很重要。要求除以零。

If t had millions of rows, and this last query were performed by a table scan, would an implementation be permitted to return the first several million results before discovering a division by zero near the end when encountering one even value of x with a zero value of y? Would it be required to buffer?

如果t有数百万行,并且最后一次查询是通过表扫描执行的,那么当遇到x值为零的偶数值时,允许实现返回前几百万个结果,然后在结束时发现除零。你?是否需要缓冲?

There are even worse cases, ponder this one, which depending on the semantics can ruin boolean short-circuiting or require four-valued boolean logic in restrictions:

有更糟糕的情况,思考这个,这取决于语义可以破坏布尔短路或在限制中需要四值布尔逻辑:

SELECT x, y
FROM t
WHERE ((x / y) >= 2) AND ((x % 2) = 0)

If the table is large, this short-circuiting problem can get really crazy. Imagine the table had a million rows, one of which had a 0 divisor. What would the standard say is the semantics of:

如果表很大,这个短路问题可能会变得非常疯狂。想象一下,这个表有一百万行,其中一行有一个0除数。标准所说的是语义:

SELECT CASE 
       WHEN EXISTS 
            (
                SELECT x, y, x / y AS quot
                FROM t
            )
       THEN 1
       ELSE 0
       END AS what_is_my_value

It seems like this value should probably be an error since it depends on the emptiness or non-emptiness of a result which is an error, but adopting those semantics would seem to prohibit the optimizer for short-circuiting the table scan here. Does this existence query require proving the existence of one non-bottoming row, or also the non-existence of a bottoming row?

看起来这个值应该是一个错误,因为它取决于结果的空虚或非空虚,这是一个错误,但采用这些语义似乎会禁止优化器在这里短路表扫描。这种存在查询是否需要证明存在一个非触底行,或者是否存在触底行?

I'd appreciate guidance here, because I can't seem to find the relevant part(s) of the specification.

我很感激这里的指导,因为我似乎无法找到规范的相关部分。

1 个解决方案

#1

All implementations of SQL that I've worked with treat a division by 0 as an immediate NaN or #INF. The division is supposed to be handled by the front end, not by the implementation itself. The query should not bottom out, but the result set needs to return NaN in this case. Therefore, it's returned at the same time as the result set, and no special warning or message is brought up to the user.

我使用的所有SQL实现都将0除以NaN或#INF视为除法。该划分应由前端处理,而不是由实现本身处理。查询不应该触底,但在这种情况下结果集需要返回NaN。因此,它与结果集同时返回,并且没有向用户提出特殊警告或消息。

At any rate, to properly deal with this, use the following query:

无论如何,要正确处理这个问题,请使用以下查询:

select
   x, y, 
   case y 
       when 0 then null 
       else x / y 
   end as quot
from
   t

To answer your last question, this statement:

回答你的上一个问题,这句话:

SELECT x, y, x / y AS quot
FROM t

Would return this:

会回来这个:

x    y   quot
7    2    3.5
3    0    NaN
4    1      4
26   5    5.2
31   0    NaN
9    3      3

So, your exists would find all the rows in t, regardless of what their quotient was.

所以,你的存在会找到t中的所有行,而不管它们的商是什么。

Additionally, I was reading over your question again and realized I hadn't discussed where clauses (for shame!). The where clause, or predicate, should always be applied before the columns are calculated.

另外,我再次阅读你的问题并意识到我没有讨论过哪些条款(羞耻!)。在计算列之前,应始终应用where子句或谓词。

Think about this query:

想想这个查询:

select x, y, x/y as quot from t where x%2 = 0

If we had a record (3,0), it applies the where condition, and checks if 3 % 2 = 0. It does not, so it doesn't include that record in the column calculations, and leaves it right where it is.

如果我们有一个记录(3,0),它应用where条件,并检查是否3%2 = 0.它没有,所以它不包括列计算中的那个记录,并将它保留在它的正确位置。

1 个解决方案

#1

更多相关文章

随机推荐