Thread: Regarding select distinct ...query
Hello All,
I have a select query with select distinct....this query is giving
me error as "unable to identify ordering operator '<' for type
'unknown'"
when i used this query without distinct, it works fine.
What is actually happening?? Can anyone fed some light on
it...???
SELECT prod_chain.depot_sending as depot_sending,
prod_chain.depot_sending as description,
'' as part,
visual_sc.xpos as xpos,
visual_sc.ypos as ypos,
visual_sc.vlevel as depthlevel,
visual_sc.parent_hpos as hparent,
visual_sc.node_hpos as hnode,
visual_sc.visible as nodevisible,
visual_sc.nameshown as nameshown,
0 as parentindex
FROM prod_chain LEFT OUTER JOIN visual_sc
ON ( TRIM (trailing '' from prod_chain.depot_receiving ) =
TRIM( trailing '' from visual_sc.parent_depot ) ) and
( TRIM( trailing '' from prod_chain.depot_sending ) =
TRIM( trailing '' from visual_sc.child_depot ) ) WHERE
( ( TRIM( trailing '' from prod_chain.depot_receiving )
= '' ) AND
( TRIM( trailing '' from visual_sc.initiator_depot )
= '' ) AND
( TRIM( trailing '' from visual_sc.initiator_part ) = '' )
AND
( TRIM( trailing '' from visual_sc.user_id ) = '' ) AND
( TRIM( trailing '' from visual_sc.sc_type ) = 'B'
)AND
visual_sc.vlevel = 2 AND
visual_sc.parent_hpos = 0
)
Thanks and regards,
Prachi
"Prachi Jain" <prachijain3@rediffmail.com> writes:
> I have a select query with select distinct....this query is giving
> me error as "unable to identify ordering operator '<' for type
> 'unknown'"
Try casting the '' to some specific datatype, like varchar or text.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > "Prachi Jain" <prachijain3@rediffmail.com> writes: > > I have a select query with select distinct....this query is giving > > me error as "unable to identify ordering operator '<' for type > > 'unknown'" > > Try casting the '' to some specific datatype, like varchar or text. I ran into precisely the same issue here yesterday. This works perfectly fine in Oracle, does the standard really leave "select distinct 'foo'" undefined? -- greg
Greg Stark <gsstark@mit.edu> writes:
> I ran into precisely the same issue here yesterday. This works perfectly fine
> in Oracle, does the standard really leave "select distinct 'foo'" undefined?
The standard has no notion of datatype extensibility, so it's got no
problem with legislating that anything between single quotes is of type
CHAR(n). If we followed the spec closely on this point, you'd probably
need an explicit cast for *every* literal you wanted to be of a datatype
other than numeric (no quotes) or string (with quotes).
We do have some kluges in place to default to assuming that
string-looking literals are of type TEXT when we can't figure out
anything else, but those kluges didn't cover the SELECT DISTINCT 'foo'
case last time I looked. It's a fairly dangerous default assumption
IMHO, so I think it's appropriate to make it only in very narrowly
defined cases ...
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Greg Stark <gsstark@mit.edu> writes: > > I ran into precisely the same issue here yesterday. This works perfectly fine > > in Oracle, does the standard really leave "select distinct 'foo'" undefined? > > The standard has no notion of datatype extensibility, so it's got no > problem with legislating that anything between single quotes is of type > CHAR(n). If we followed the spec closely on this point, you'd probably > need an explicit cast for *every* literal you wanted to be of a datatype > other than numeric (no quotes) or string (with quotes). Well, if that what the spec says then I would imagine you should at least follow it in the absence of other information. I don't see what's "dangerous" about the assumption that valid standard SQL code should be interpreted as such. In the current situation we have a perfectly fine standard SQL query that runs fine on other databases but fails on postgres. And the only way to "fix" it for postgres is to add a non-standard cast to it that won't work on the other databases. I'm kind of surprised actually that it doesn't work the other way around. It seems like the standard datatypes ought to be the default for all constants and the kludges should only be necessary to automatically cast strings and numbers to other datatypes for programmer convenience. -- greg
What's a non-standard cast? select distinct cast('foo' as varchar) should work just about everywhere... Explicit
castsare never a bad idea afaics...
Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
(253) 798-3549
>>> Greg Stark <gsstark@mit.edu> 12/18/02 04:12PM >>>
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Greg Stark <gsstark@mit.edu> writes:
> > I ran into precisely the same issue here yesterday. This works perfectly fine
> > in Oracle, does the standard really leave "select distinct 'foo'" undefined?
>
> The standard has no notion of datatype extensibility, so it's got no
> problem with legislating that anything between single quotes is of type
> CHAR(n). If we followed the spec closely on this point, you'd probably
> need an explicit cast for *every* literal you wanted to be of a datatype
> other than numeric (no quotes) or string (with quotes).
Well, if that what the spec says then I would imagine you should at least
follow it in the absence of other information. I don't see what's "dangerous"
about the assumption that valid standard SQL code should be interpreted as
such.
In the current situation we have a perfectly fine standard SQL query that runs
fine on other databases but fails on postgres. And the only way to "fix" it
for postgres is to add a non-standard cast to it that won't work on the other
databases.
I'm kind of surprised actually that it doesn't work the other way around. It
seems like the standard datatypes ought to be the default for all constants
and the kludges should only be necessary to automatically cast strings and
numbers to other datatypes for programmer convenience.
--
greg
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Greg Stark <gsstark@mit.edu> writes:
> In the current situation we have a perfectly fine standard SQL query that runs
> fine on other databases but fails on postgres. And the only way to "fix" it
> for postgres is to add a non-standard cast to it that won't work on the other
> databases.
Which part of CAST('' AS CHARACTER VARYING(1)) doesn't work on your other
databases?
I am not here to defend the fact that SELECT DISTINCT 'foo' fails; in
fact fixing it is on my todo list I think [digs...] yeah here it is:
: SELECT DISTINCT 'bar' fails because there's no ordering op for unknown.
: Probably should coerce to text, much as we do for CASE and UNION.
: Msg 2/01/02 17:53
: Anywhere we are doing assignSortGroupRef, seems okay to modify tle to
: coerce to text. transformGroupClause, addTargetToSortList (but prepunion
: calls addAllTargetsToSortList; maybe need to do it at higher level?)
I'm just pointing out why it historically hasn't worked.
regards, tom lane
On 18 Dec 2002 19:12:40 -0500 Greg Stark <gsstark@mit.edu> wrote: > > In the current situation we have a perfectly fine standard SQL query that runs > fine on other databases but fails on postgres. And the only way to "fix" it > for postgres is to add a non-standard cast to it that won't work on the other > databases. > Most users on this list seem to prefer the Postgres specific odd variant of a cast (::datatype). This will fail on all other databases of course, so don't use it. Use the SQL2 function CAST(att AS datatype) instead. Suggestion: the cast variant '::' should be deprecated in the docs. Christoph Dalitz