Re: Peculiar results from a simple looking query.

From: Mark Bole <makbo_at_pacbell.net>
Date: Thu, 24 Feb 2005 20:04:06 GMT
Message-ID: <WqqTd.9029$Pz7.5035@newssvr13.news.prodigy.com>

Sybrand Bakker wrote:

> On 24 Feb 2005 09:45:47 -0800, netspam_at_shic.co.uk (Steve H) wrote:
> 
> 

>>I've stumbled upon a peculiar result when querying Oracle which I have
>>not experienced with other DBMS. Consider this example:
>>
>>create table atable(field varchar(20))
>>insert into atable values ('This is a test')
>>select field from atable where cast(field as varchar(2))='Th'
>>
>>Other DBMS return the single row:
>>
>>This is a test
>>
>>Oracle 9.2.10, however generates this result:
>>
>>Th
>>
>>Is this a widely known issue with Oracle? Can I change Oracle's
>>behaviour by tweaking configuration or applying a patch? Is this a
>>bug?

> 
> 
> I would recommend writing ordinary SQL
> such as
> select field from atable where field like 'Th%'
> and of course read the manuals 
> and stop shouting 'BUG' at he first thing you don't understand
> 
> Oracle != Sqlserver.
> 
> 
> --
> Sybrand Bakker, Senior Oracle DBA

I spent several minutes trying to understand this myself, and didn't reply at first since I couldn't come with anything, other than a variation on the above advice to use more straightforward SQL (SUBSTR function instead of CAST).

Why *does* Oracle change the datatype of a column in the SELECT clause due to use of a function in the WHERE clause? Couldn't think of any other situation where this happens, nothing on Metalink except an oblique reference to this issue when migrating from SQL Server.

select dump(field) from atable where substr(field,1,2) = 'Th';

DUMP(FIELD)

---

Typ=1 Len=14: 84,104,105,115,32,105,115,32,97,32,116,101,115,116

select dump(field) from atable where cast(field as varchar2(2))='Th';

DUMP(FIELD)

---

Typ=1 Len=2: 84,104

However, the following is a possible work-around:

  select field from atable
  where cast(to_char(field) as varchar2(2)) = 'Th';

FIELD

---

This is a test

-Mark Bole Received on Thu Feb 24 2005 - 14:04:06 CST