Re: Peculiar results from a simple looking query.

From: Mark Bole <makbo_at_pacbell.net>
Date: Fri, 25 Feb 2005 15:10:05 GMT
Message-ID: <hdHTd.9293$Pz7.6721@newssvr13.news.prodigy.com>

Turkbear wrote:

> "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote:
> 
>

>>"Pedro Lopes" <pedro.lopes_at_netvisao.pt> wrote in message
>>news:newscache$xoyfci$yyb$1_at_newsfront4.netvisao.pt...
>>

[...]
>>
>>nice addition, pedro!
>>
>>yes, the OP should open a TAR. but I for one appreciate knowing that there
>>is a problem with the syntax that he's trying and would welcome more
>>'heads-up' like this. plus, i'm quite sure we are all aware that oracle
>>support isn't always the best at trying out alleged bugs, so i don't think
>>it's too bad an idea to post a test case here. and, as an instructor and
>>consultant, i certainly want to increase my knowledge of what things don't
>>quite work, whether or not its a feature or syntax that i generally use
>>
>>all in all, the OP's issue was not really all that different from your
>>recent attempts to obfuscate number columns -- from which we all learned
>>that 10g release 2 will have it built in
>>
>>++ mcs
>>

> 
> 
> 
> Let's reexamine what CAST does in the context of a query:
> 
> The Conversion requested by the CAST statement is in effect throughout the life of that query and applies to that affected
> column however many times after the cast it is referenced. In effect you have created a virtual field named the same as the
> original, uncasted, one and the query is accessing that virtual one - hence 2 characters long, like it was redefined.
> 
> 
>

What is a CAST "statement" and is there any context other than a query when you would use it? (now I'm getting into that HJR "precise" mode... ;-) ) Here's what the documentation says for CAST and several dozen other conversion functions it is grouped with:

"Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses. [...] Conversion functions convert a value from one datatype to another. [...] The SQL conversion functions are: [...] CAST [...]"

Nowhere is the totally unique and unexpected behavior of the CAST function mentioned, in the documentation or Metalink, so it is either a software bug or a very egregious, long-standing documentation bug.

I competely agree with mcs' comment. All the OP asked was whether or not anyone could explain or confirm what he saw, and whether there was a workaround (both of which have been accomplished by now). Somehow the presumed side issue of this being a problem when migrating from SQL Server (which *is* in fact documented in Metalink 210262.1) became the touchstone of this thread. That is not a problem, but the fact is there is something wrong with the behavior of CAST or the way it is documented, and nothing contributed so far has shown otherwise.

-Mark Bole Received on Fri Feb 25 2005 - 09:10:05 CST