On Fri, Oct 14, 2022 at 12:23 AM Facundo Etchezar <hctf90@gmail.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.
