Re: weird error message - Mailing list pgsql-sql
| From | David G. Johnston |
|---|---|
| Subject | Re: weird error message |
| Date | |
| Msg-id | CAKFQuwYafhAJiqpt8ws2+-C507SyK55to9tv3x2QtN2LR++nTg@mail.gmail.com Whole thread Raw |
| In response to | Re: weird error message (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: weird error message
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-sql |
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I don't know that this is covered all that well in the documentation.
It'd be better to do something about it than document it.
Don't look at me :)
The core of
the problem is that if we don't resolve the type of an unknown literal
while processing the sub-SELECT's target list, it doesn't work to try
to make a conversion later.
Correct.
I think there's a rough consensus that it'd be okay to resolve unknown to
text at the time that the subquery is parsed, if there's no reason to
resolve it to something else.
I'm not sure that buys us a lot here...
>SELECT case when dt IS NOT NULL then dt else i end FROM (SELECT null AS dt, 1 AS i) q;
failed to find conversion function from unknown to integer
>SELECT case when dt IS NOT NULL then dt else i end FROM (SELECT '2'::text AS dt, 1 AS i) q;
SQL Error: ERROR: CASE types integer and text cannot be matched
There would be cases where that's not
really what you want, but it would be unsurprising for it to act that way.
The hard part is that we've historically allowed
INSERT INTO sometab SELECT 'foo', ...
to resolve 'foo' as the type of sometab's first column (and I think this
is required by SQL spec, actually). So some work would have to be done
to not break that behavior. But I think this could be managed by
explicitly passing down knowledge of the INSERT's target column types into
the parsing of the sub-SELECT, and then the rule could be "resolve an
unknown SELECT output column to whatever target type is provided by
context, or to TEXT if the context provides no target".
Don't know enough here to comment on "push down" options.
I'm not certain why we wouldn't just add implicit casts from unknown to other types. Then, we end up getting the same behavior when dealing with sub-selects as we do when unknown (untyped) literals are present directly within the main query.
IOW, instead of trying to carry type inference down to lower layers let ambiguity remain as vars travel up until an unknown encounters context which allows it be definitively typed. I'm sure there are some corner cases involved but in a trivial setup the end result of the two algorithms is the same.
I'm willing to go read where this option has been discussed and dismissed - just point me in the right direction please. Nothing comes to mind at the moment. While getting rid of implicit casting generally was a good idea this seems like the one area that warrants it - and in fact already has it locally.
David J.