Implicit cast of literal in SQL statements
I've seen a few postings in multiple newsgroups saying that in 7.1.x and
up, literals in SQL statements are implicitly cast to strings.
For example in:
select distinct 'hello' from mytable;
the 'hello' is implicitly assumed to be 'hello'::text
However, in both 7.1.3, and a fresh build of 7.2b4 from cvs, (with all
regressions passing) I get:
mytest=# select distinct 'hello' from mytable;
ERROR: Unable to identify an ordering operator '<' for type 'unknown'
Use an explicit ordering operator or modify the query
an explicit 'hello'::text works fine.
I've spent a day looking through the code and can't really find any
obvious #define's or compile time flags that would be causing this
problem.
It looks like
Const *
make_const(Value *value)
{
...
case T_String:
val = DirectFunctionCall1(textin,
CStringGetDatum(strVal(value)));
typeid = UNKNOWNOID; /* will be coerced
later */
typelen = -1; /* variable len */
typebyval = false;
break;
...
}
does the damage, and it never gets 'coerced later', at least not before
transformDistinctClause(...) gets called, which is where the failure
happens (a few levels down).
does this really work for everybody else? Can someone point me to a
compile flag I may be missing, or the code that actually does the
implicit cast?
thanks
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Scott Royston <scroyston71@yahoo.com> writes:
I've seen a few postings in multiple newsgroups saying that in 7.1.x and
up, literals in SQL statements are implicitly cast to strings.
That's an oversimplification: the implicit coercion of unknown literals
only happens when looking for an operator or function to apply to them.
For an unprocessed result literal such as you describe, the type
never does get changed. Which is okay because type "unknown" does have
an output routine, which is all that's needed to emit the literal.
You may care to peruse the rules in
http://developer.postgresql.org/docs/postgres/typeconv.html
However, in both 7.1.3, and a fresh build of 7.2b4 from cvs, (with all
regressions passing) I get:
mytest=# select distinct 'hello' from mytable;
ERROR: Unable to identify an ordering operator '<' for type 'unknown'
Use an explicit ordering operator or modify the query
This is mildly annoying but I'm not sure that fixing it wouldn't
introduce greater annoyances. As an example of the pitfalls, consider:
regression=# select 1 union select '2';
?column?
----------
1
2
(2 rows)
regression=# select 1 union select '2'::text;
ERROR: UNION types "int4" and "text" not matched
The first example works because the right-hand SELECT's result is not
coerced to "text" before UNION can get its hands on it.
Possibly DISTINCT should be allowed to type-coerce unknown inputs to
text the same way that explicit operators and functions can. Offhand
I'm not sure if that's a good solution or not. There are related
cases to consider too, eg ORDER BY and GROUP BY.
regards, tom lane
I've seen a few postings in multiple newsgroups saying that in 7.1.x and
up, literals in SQL statements are implicitly cast to strings.
In some contexts, that statement is true, yes. The cases where this is
true is when the parser is trying to match literals with available
function calls. If there is a literal of unknown type, and if there is a
function which could take a string literal as input, then that function
is the one chosen.
does this really work for everybody else? Can someone point me to a
compile flag I may be missing, or the code that actually does the
implicit cast?
It looks like we are not handling the case where there is no explicit
function call, and there a string literal in the target list (so no
underlying column to infer a type from), and there is a subsequent
ordering operation. That might be fixable, but it may not be a useful
real world example afaict.
Do you have another example to illustrate the problem for a query which
one might actually need to use?
- Thomas
I've got some 'legacy' code that I'm dealing with - sometimes it will
receive query requests that are simply the union of two easier requests
it already knows the sql for.
These 'easier' queries have 'distincts' in them, and the code doesn't go
to the trouble of removing by hand when doing a union.
so the query ends up looking like:
SELECT DISTINCT firstName, middleName, lastName FROM completeNameTable
WHERE (...)
UNION
SELECT DISTINCT firstName, ' ', lastName FROM partialNameTable WHERE(...)
ugly, I know. ( and probably inefficient, I should check the plan )
thanks for the quick response
On Thursday, December 27, 2001, at 03:28 PM, Thomas Lockhart wrote:
I've seen a few postings in multiple newsgroups saying that in 7.1.x
and
up, literals in SQL statements are implicitly cast to strings.In some contexts, that statement is true, yes. The cases where this is
true is when the parser is trying to match literals with available
function calls. If there is a literal of unknown type, and if there is a
function which could take a string literal as input, then that function
is the one chosen.does this really work for everybody else? Can someone point me to a
compile flag I may be missing, or the code that actually does the
implicit cast?It looks like we are not handling the case where there is no explicit
function call, and there a string literal in the target list (so no
underlying column to infer a type from), and there is a subsequent
ordering operation. That might be fixable, but it may not be a useful
real world example afaict.Do you have another example to illustrate the problem for a query which
one might actually need to use?- Thomas
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com