Re: BUG #17637: case-when branches taken even if they dont match, raising errors

>
> SQL is a strongly typed language and having a column of data that is
> conditionally in different data formats is not really compatible with that
> design.
>
I completely understand the argument of not touching the optimizer for
these cases, especially since I have a way to make it work which Tom
provided. But this is really not what is happening in the query.

I'm doing the equivalent of this piece of pseudo-code:

text v = '1234';
int8 id_type = 1;
bool bool_col;
int8 int_col;
if(id_type == 1) {
int_col = int8.parse(v);
} else {
int_col = null;
}
if(id_type == 2) {
bool_col = bool.parse(v);
} else {
bool_col = null;
}

And this query is executing both conditionals as if the condition is true
in both cases which is impossible, id_type can't be both values at the same
time. There is no dynamic typing whatsoever, it's all strongly typed, it's
just a very normal conditional operation.

Although this matter is way beyond the initial issue, which I consider
resolved since the 'materialized' keyword lets me make my original insert
just fine.

On Fri, Oct 14, 2022 at 10:28 AM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Fri, Oct 14, 2022 at 12:23 AM Facundo Etchezar <hctf90(at)gmail(dot)com>
> wrote:
>
>> I see. So is this behavior expected? The two snippets should work the
>> same when you look at them but one errors out and the other doesn't. I'm
>> thinking either both should work or both should error out.
>>
>
> While reasonable, this particular dynamic falls into a grey area. SQL is
> a strongly typed language and having a column of data that is conditionally
> in different data formats is not really compatible with that design. The
> CASE expression does allow for handling of this typically but that only
> works during execution - and in this case the problematic optimization is
> happening during parsing. More people write structurally correct
> inefficient queries than non-structurally correct ones and so the parsing
> time optimization stays as-is. As noted, you have a way to prohibit the
> optimization from revealing the design problem with your query.
>
> David J.
>
>
>