Please don’t top-post
On Wednesday, January 4, 2023, Sayyid Ali Sajjad Rizavi <
sasrizavi@gmail.com> wrote:
Breaking working queries for this is not acceptable.
Good point, let's exclude Option 2.
This happens when possible so any remaining cases are not possible. Or, at least apparently not worth the effort it would take to make work.
Actually this doesn't happen when all of the values in that position are null. Or maybe I don't understand what you mean.
If we don't consider the effort it would take to make it work, do you think Option 1 would be good to have? Because when I
have an integer column in that position, I wouldn't want the unknown (null) values I supply to be resolved to `text` type.
The VALUES subquery has to produce its tabular output without being aware of how the outer query is going to use it. The second column of your values subquery lacks type information so the system chooses a default - text.
Dealing with types is one of the harder medium-hard problems in computer science…encountering this problem in real life has never seen me motivated enough to gripe about it rather than just add an explicit cast and move on. And I’ve been around long enough to know that the project is, and long has been, aware of the dull pain points in this area.
David J.