Re: Strange effects of Cast

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Mon, 16 Feb 2009 19:28:19 -0600
Message-ID: <Tjoml.10757$8_3.4476_at_flpi147.ffdc.sbc.com>

Maxim Demenko wrote:
> Ken schrieb:

>> I am getting unexpected results from a Select statement which uses
>> Cast to tuncate strings.
>>
>> In the query below although I get the expected result in COL1 and COL2
>> COL3 and COL4 return only three characters. In fact if I swap COL1 and
>> COL2 in the query then all columns are three characters long.
>>
>> I have not been able to find any references to this problem. Has
>> anyone else come accross this behavior?
>>
>> Select
>>     Cast(NUM1 As Varchar2(7))            As COL1,
>>     Cast(NUM1 As Varchar2(3))            As COL2,
>>     Cast(NUM1 As Varchar2(9))            As COL3,
>>     NUM1                                 As COL4
>> From
>>     (
>>     Select
>>         '12345678901234567890'  As NUM1
>>     From
>>         Dual
>>     )
>>
>>
>> COL1    COL COL3      COL4
>> ------- --- --------- --------------------
>> 1234567 123 123       123
>> 1 row selected.
>>
>> select * from v$version;
>>
>> BANNER
>> ----------------------------------------------------------------
>> Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
>> PL/SQL Release 9.2.0.5.0 - Production
>> CORE    9.2.0.6.0    Production
>> TNS for 32-bit Windows: Version 9.2.0.5.0 - Production
>> NLSRTL Version 9.2.0.5.0 - Production

>
> It seems to be related to Bug 7154415.
> On 11.1.0.7 results are like what you are expecting to be:
>
> SQL> select * from v$version;
>
> BANNER
> --------------------------------------------------------------------------------
>
> Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
> PL/SQL Release 11.1.0.7.0 - Production
> CORE 11.1.0.7.0 Production
> TNS for Linux: Version 11.1.0.7.0 - Production
> NLSRTL Version 11.1.0.7.0 - Production
>
> SQL> Select
> 2 Cast(NUM1 As Varchar2(7)) As COL1,
> 3 Cast(NUM1 As Varchar2(3)) As COL2,
> 4 Cast(NUM1 As Varchar2(9)) As COL3,
> 5 NUM1 As COL4
> 6 From
> 7 (
> 8 Select
> 9 '12345678901234567890' As NUM1
> 10 From
> 11 Dual
> 12 )
> 13 /
>
> COL1 COL COL3 COL4
> ------- --- --------- --------------------
> 1234567 123 123456789 12345678901234567890
>
>
> Despite that, i agree with Charles, the proper tool for truncation of
> strings is the SUBSTR functions family, if you misuse the CAST for that
> purpose, you are buing side effects, such as implicit conversions etc.
>
> Best regards
>
> Maxim

He is, after all, running an obsolete, non-supported version... You get what you pay for?

in 10.2.0.1 XE - you get:
SQL> Select

   2       Cast(NUM1 As Varchar2(7))            As COL1,
   3       Cast(NUM1 As Varchar2(3))            As COL2,
   4       Cast(NUM1 As Varchar2(9))            As COL3,
   5       NUM1                                 As COL4
   6   From
   7       (
   8       Select
   9           '12345678901234567890'  As NUM1
  10       From
  11           Dual
  12       );

COL1    COL COL3      COL4
------- --- --------- --------------------
1234567 123 123       123

and thinking about it, I would tend to lean towards this actually being the correct result as you change NUM1 with the first and subsequent statements.

If you do perl or php or basic or whatever try this:

psuedo-code:

$x=12345678901234567890;
$x=left($x,7);
$col1=$x;
$x=left($x,3);
$col2=$x;
$x=left($x,9);
$col3=$x;
$col4=$x;
echo "col1		col2		col3		col4";
echo "-------------------------------------------------------";
echo $col1."		".$col2."		".$col3."		".$col4;

But as stated, this is fixed in some release after 10.2.0.1. Received on Mon Feb 16 2009 - 19:28:19 CST