"SQL is a strongly typed language"
- nobody argues with that, that's a common fact everybody knows.
"so it is well within its right to require that a query be well-formed such that all inputs to functions (which COALESCE is in this context) are of the same type or raise a compile time error"
- they are not of the same type. That's the whole problem from the beginning, I present the values in different data types and it selects the "highest" based on some internal order and that's the type that is required for all the other value expressions. In this case the DB engine picks a datatype for me when I don't explicitly cast everything. Moreover, it throws an exception if it failed to evaluate the datatype from a value expression even through it's totally acceptable, it's just doesn't analyze the more complex stuff.
INTEGER is "higher" than CHARACTER VARYING, so the expressions will be validated to INTEGER and not to CHARACTER VARYING. Why, because the engine picked it this way for you.
Here is another example: SELECT COALESCE(NULL, '12', 2, 3 :: INTEGER, 1 :: NUMERIC, 1 :: INTEGER); Why is the result NUMERIC?
Anyway, the doc still doesn't give any info on these:
1) COALESCE scans the list of <value expression>s
2) COALESCE determines the highest data type in the list of <value expressions>s
3) COALESCE casting the first non-NULL to the highest data type
4) COALESCE evaluates every <value expression>s to the highest datatype selected in step (2). Correction from the last email I checked, the value expression is not evaluated itself but it is "analyzed" to the highest datatype or exception is thrown during various, not obvious conditions.
Thank you.
On Monday, April 29, 2019, 10:26:58 PM PDT, David G. Johnston <david.g.johnston@gmail.com> wrote:
but there is still a problem in here as you can see from this confusion. It evaluates the type of the <value expression>s.
SQL is a strongly typed language so it is well within its right to require that a query be well-formed such that all inputs to functions (which COALESCE is in this context) are of the same type or raise a compile time error.
The expression is still not evaluated for each row during runtime which is all the section on short-circuiting promises.