Thread: Fallback behavior for "UNKNOWN" types -- proposed change

The parser has some heuristics to try to match up existing functions and
operators when not all types are known apriori. We've had this
capability since v6.4, with some modest evolution since then.

Currently, if there is more than one function, say, which *could* match
the specified query, and if the arguments with unspecified types
(typically looking like a bare SQL9x string) come from different
"categories" of types (e.g. integer and string, or float and date) then
the parser throws an error about not finding the function.

I propose that we modify the heuristic slightly, so that if there are
function matches with arguments from different categories, and if one or
more of the possible matches comes from the "string" category, then that
category is preferred.

There are two good reasons for this, and one bad reason ;) :

1) the original query carries "string" semantics, so it is a reasonable
fallback interpretation for the query.

2) a string fallback will make things like
 select tstampfield at time zone 'pst' from t1;

and
 select tstampfield at time zone interval '-08:00' from t1;

possible (oh, btw, I've got patches to implement "at time zone..."),
where currently
 select tstampfield at time zone 'pst' from t1;

fails and requires that 'pst' be specified as "text 'pst'".

3) some braindead "compatibility tests" from some competing open-source
database projects have poorly designed queries which interpret this lack
of fallback as a lack of support for database features. So instead of
getting extra points for having *more* capabilities in a particular
area, they claim that we don't support anything in that area. Most
annoying, and it is not likely to change.

Comments? I've got code which implements the fallback for functions, and
presumably the same for operators will be easy to do...
                      - Thomas

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> I propose that we modify the heuristic slightly, so that if there are
> function matches with arguments from different categories, and if one or
> more of the possible matches comes from the "string" category, then that
> category is preferred.

I would suggest a slightly different rule, but maybe it comes out at the
same place in the end: if we can't find a unique match treating UNKNOWN
the way we do now, try again assuming it is TEXT (or at least string
category).  As you say, this is reasonable given that the original
literal looked like a string.

BTW, I have been thinking that numeric literals ought to be initially
assigned a new pseudo-type "UNKNOWNNUMERIC", which would eventually
get coerced to one specific numeric type along the same lines as type
assignment for string literals.  This looks like it might help deal
with the problems of float8 vs. numeric, etc.  Don't have a complete
proposal worked out yet, though.
        regards, tom lane

> I would suggest a slightly different rule, but maybe it comes out at the
> same place in the end: if we can't find a unique match treating UNKNOWN
> the way we do now, try again assuming it is TEXT (or at least string
> category).  As you say, this is reasonable given that the original
> literal looked like a string.

Yeah, it is the same thing in the end, since the *only* place I've
changed in the code is the block which used to bail out when seeing a
"category conflict".

I assumed you would have an opinion ;) If anyone else has concerns
before seeing the effects of the change in the development tree, speak
up! Of course, if we see troubles after commit, things can change or
revert...

Oh, and UNKNOWNNUMERIC sounds like a plausible concept too.
                    - Thomas