Re: Strange behaviour of Scalar Subquery

From: Mark Bole <makbo_at_pacbell.net>
Date: Wed, 05 Jan 2005 20:08:59 GMT
Message-ID: <vPXCd.7524$yV1.2937@newssvr14.news.prodigy.com>

tncc wrote:

> I have a table:
>
> create table c (
> adate date,
> pk number
> )
>
> assuming the columns having proper non-null values, the following update
> returns error:
>
> update c
> set adate=(select min(adate) from c) + 1;
>
> ora-00933 sql command not properly ended
>
> However, this update command executed successfully(i.e. I just swap the
> operands of + operator) !!
>
> update c
> set adate=1+(select min(adate) from c) ;
>
> xxx rows updated
>
> Anyone know why there is a difference? My guess may be the type of the
> scalar subquery may not be DATE (implicity converted to VARCHAR) in the
> first case because Oracle evaluates + operator from left to right and
> converts date to char implicitly. Am I correct?
>

But this works OK:

update c
set adate=(select min(adate + 1) from c)

I don't think it has anything to do with implicit conversion to VARCHAR2. However I agree that some odd datatypes are involved.

One web site I found http://www.dbazine.com/still2.shtml indicates that there are both internal and external datatypes, so Type 13 appears to be an "external" datatype. Only type 12 is documented by Oracle as an internal datatype. (The Pro*C/C++ Precompiler Programmer's Guide also discusses external types but doesn't shed any light on this example). Examples 2 and 3 below would indicate that your original problem should not happen...so it's anomalous behavior.

Examples:

select dump( (select min(adate) from c) ) from dual;

DUMP((SELECTMIN(ADATE)FROMC))

Typ=12 Len=7: 120,105,1,5,12,35,14

2. select dump( (select min(adate) from c) + 1 ) from dual;

DUMP((SELECTMIN(ADATE)FROMC)+1

Typ=13 Len=8: 213,7,1,6,11,34,13,0

3. select dump(1 + (select min(adate) from c) ) from dual;

DUMP(1+(SELECTMIN(ADATE)FROMC)

Typ=13 Len=8: 213,7,1,6,11,34,13,0

4. select dump(sysdate) from dual;

DUMP(SYSDATE)

Typ=13 Len=8: 213,7,1,5,12,4,45,0

-Mark Bole Received on Wed Jan 05 2005 - 14:08:59 CST