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 <shmutalov@gmail.com> wrote:
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