Thank you Sherzod. I'll admit I did not know about those syntax rules, so
that changes my example a little bit. What about when the types are the
same, but the casting is not possible? For example, this code:
select case
when 1 = 0 then
cast('a' as int)
else 3
end
This will return 3 in MSSQL, but will result in the same error as before in
MonetDB even though it shouldn't get into the then statement.
Thank you,
John
On Tue, Dec 8, 2015 at 11:07 PM, Sherzod Mutalov
Hello John,
Wednesday, December 9, 2015, 2:19:38 AM, you wrote:
The issue happens when using a select case, the items in the when statement are validated even when the statement will never execute. This will cause issues with casting, that otherwise should never have executed.
For example, this sample code:
select case when false = true then cast('a' as int) else 'b' end
Results in the error:
Error: conversion of string 'a' to type int failed. SQLState: 22018 ErrorCode: 0
Even though the when statement should never be true and it shouldn't matter if it can't cast 'a' to an int.
Will always result in 'b', as expected. It is almost as if the query is not short circuiting correctly and checks every statement of the when statement. I was previously on release Jan-2014 SP1 and I did not have this issue. Has anybody else seen something like this? Is there a setting somewhere to turn off this when check? Apologies if this is a common/simple question, I am relatively new to MonetDB.
Some part of ANSI SQL-92 standard:
ANSI SQL-92 6.9 <case expression>
... Syntax Rules
... 7) The data type of a <case specification> is determined by applying Subclause 9.3, "Set operation result data types", to the data types of all <result expression>s in the <case specification>.
...
9.3 Set operation result data types
... Syntax Rules
... 2) All of the data types in DTS shall be comparable. ... 3) Case: a) If any of the data types in DTS is character string, then all data types in DTS shall be character string ...
c) If all of the data types in DTS are exact numeric, then the result data type is exact numeric with implementation-defined precision and with scale equal to the maximum of the scales of the data types in DTS.
d) If any data type in DTS is approximate numeric, then each data type in DTS shall be numeric and the result data type is approximate numeric with implementation-defined precision.
Also, I checked your SQL statement in MSSQL, Postgres 9.4, MySQL 5.6, SQLite 3, Oracle 12c. They are produces same error except MySQL and SQLite.
*-- Best regards, Sherzod * mailto:shmutalov@gmail.com
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list