My apologies if much of this is already assumed knowledge by most -hackers...I'm trying to learn from observation instead of, largely, reading code in a foreign language.
On Wed, Apr 22, 2015 at 6:40 PM, Jeff Davis <pg...@j-davis.com> wrote: > Moving thread to -hackers. > > On Wed, Apr 8, 2015 at 11:18 PM, Jeff Davis <pg...@j-davis.com> wrote: > > That example was just for illustration. My other example didn't require > > creating a table at all: > > > > > > > SELECT a=b FROM (SELECT ''::text, ' ') x(a,b); > > > > it's fine with me if we want that to fail, but I don't think we're > > failing in the right place, or with the right error message. > > > > I'm not clear on what rules we're applying such that the above query > > should fail, but: > > > > > > SELECT ''::text=' '; > > > should succeed. Unknown literals are OK, but unknown column references > > are not? If that's the rule, can we catch that earlier, and throw an > > error like 'column reference "b" has unknown type'? > But the fact that column "b" has the data type "unknown" is only a warning - not an error. This seems to be a case of the common problem (or, at least recently mentioned) where type conversion only deals with data and not context. http://www.postgresql.org/message-id/CADx9qBmVPQvSH3+2cH4cwwPmphW1mE18e=wumlfuc-qz-t7...@mail.gmail.com Additional hinting regarding the column containing the offending data would be welcomed by the community - but I suspect it is a non-trivial endeavor. > Is the behavior of unknown literals vs. unknown column references > documented anywhere? I tried looking here: > http://www.postgresql.org/docs/devel/static/typeconv.html, but it > doesn't seem to make the distinction between how unknown literals vs. > unknown column references are handled. > > My understanding until now has been that unknown types are a > placeholder while still inferring the types. But that leaves a few > questions: > > 1. Why do we care whether the unknown is a literal or a column reference? > Apparently the difference is in when non-implicit casts can be used for coercion - or, rather, when input functions can be used instead of casting functions. in SELECT ' '::text = 'a' the explicit cast between the implicit unknown and text is used while going through the subquery forces the planner to locate an implicit cast between the explicit unknown and text. The following fails no matter what you try because no casts exist from unknown to integer: SELECT a::int=b FROM (SELECT '1', 1) x(a,b); but this too works - which is why the implicit cast concept above fails (I'm leaving it since the thought process may help in understanding): SELECT 1 = '1'; >From which I infer that an unknown literal is allowed to be fed directly into a type's input function to facilitate a direct coercion. Writing this makes me wish for more precise terminology...is there something already established here? "untyped" versus "unknown" makes sense to me. untyped literals only exist within the confines of a single node and can be passed through a type's input function to make them take on a type. If the untyped reference passes through the node without having been assigned an explicit type it is assigned the unknown type. 2. Unknown column references seem basically useless, because we will > almost always encounter the "failure to find conversion" error, so why > do we allow them? > At this point...backward compatibility? I do get a warning in psql (9.3.6) from your original -bugs example create table a(u) as select '1'; WARNING: "column "u" has type "unknown" DETAIL: Proceeding with relation creation anyway. Related question: was there ever a time when the above failed instead of just supplying a warning? My git-fu is not super strong but the above warning was last edited by Tom Lane back in 2003 (d8528630) though it was just a refactor - the warning was already present. I suppose after 12 years the "why" doesn't matter so much... create table b(i int); insert into b select u from a; ERROR: failed to find conversion function from unknown to integer Text appears to have a cast defined: SELECT u::text FROM a; > 3. If unknowns are just a placeholder, why do we return them to the > client? What do we expect the client to do with it? We do? I suspect that it is effectively treated as if it were text by client libraries. My gut reaction is if you feel strongly enough to add some additional documentation or warnings/hints/details related to this topic they probably would get put in; but disallowing "unknown" as first-class type is likely to fail to pass a cost-benefit evaluation. Distinguishing between "untyped" literals and "unknown type" literals seems promising concept to aid in understanding the difference in the face of not being able (or wanting) to actually change the behavior. David J.
