Re: Strange effects of Cast

From: ddf <oratune_at_msn.com>
Date: Tue, 17 Feb 2009 08:22:48 -0800 (PST)
Message-ID: <774e4c5d-5ba8-4405-97c9-16a2281e6db2_at_e24g2000vbe.googlegroups.com>

On Feb 16, 2:45 pm, Maxim Demenko <mdeme..._at_gmail.com> 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- Hide quoted text -
>
> - Show quoted text -

It appears the fix is in the 11.1.0.7 patchset, as 11.1.0.6 produces the same results as the OP provided:

SQL> with src as(

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

 12 From src;

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

SQL> set autotrace on
SQL> /

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

Using the query directly in the cast statement provides the desired output:

SQL> Select
  2 (

  3      Select
  4          '12345678901234567890'  As NUM1
  5      From
  6          Dual
  7      ) as COL4,
  8  cast((
  9      Select
 10          '12345678901234567890'  As NUM1
 11      From
 12          Dual
 13      ) as varchar2(7)) COL1,
 14  cast((
 15      Select
 16          '12345678901234567890'  As NUM1
 17      From
 18          Dual
 19      ) as varchar2(3)) COL2,
 20  cast((
 21      Select
 22          '12345678901234567890'  As NUM1
 23      From
 24          Dual
 25      ) as varchar2(9)) COL3

 26 From dual;

COL4                 COL1    COL COL3
-------------------- ------- --- ---------

12345678901234567890 1234567 123 123456789

SQL> David Fitzjarrell Received on Tue Feb 17 2009 - 10:22:48 CST