Subject: Implicit cast of literal in SQL statements

From: Scott Royston <[email-masked]>
Date: Thu, 27 Dec 2001 14:37:14 -0600

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

---

From: Tom Lane <[email-masked]>
Date: Thu, 27 Dec 2001 16:22:56 -0500

> 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

>          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

---

From: Thomas Lockhart <[email-masked]>
Date: Thu, 27 Dec 2001 21:28:53 +0000

> 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.

> compile flag I may be missing, or the code that actually does the

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

---

From: Scott Royston <[email-masked]>
Date: Thu, 27 Dec 2001 16:28:29 -0600

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:

> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>