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.
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.