Date:         Fri, 15 Nov 2002 20:07:02 GMT
Reply-To:     julierog@ix.netcom.com
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Roger Lustig <trovato@BELLATLANTIC.NET>
Subject:      Re: Hot fix bundle error introduces SQL error
Content-Type: text/plain; charset=us-ascii; format=flowed

Stephen:

This is very strange, as they fixed a very similar bug once before.
Check out Hot Fix 82BA13, for the problem described in SN-004576:


    Using the IN operator in PROC SQL may produce incorrect results.  The
    problem occurs in the later passes of code generation optimization.
    For the problem to occur, the numerics being compared must be defined
    with less than the default 8 bytes.


81BA39 was the V8.1 fix for the same thing.

There was another nasty short-numeric bug in 8.0 and 8.1, which was
fixed with 81BA48 (SN-003430).  Time to check whether that one's back too!

Have you alerted TS to this new matter?

Best,

Roger Lustig


Stephen Dunn wrote:
> I thought people might be interested in a defect we have found in SQL. The
> SAS hot fix bundle for Windows 82bxwn03 introduced a major bug to SQL
> processing. If you have a query that involves a sub query that looks like:
>     select * from patches
>     where claimid in
>     (select claimid from changedclaims)
>     order by claimid
> it will return zero rows if the variables compared are numerics of less than
> 8 bytes. It this case claimid is integer valued and has length 6 in both
> tables. The error does not occur if you have not installed the hot fix.
>
> SAS suggest a workaround
>     select * from patches
>     where claimid in
>     (select round(claimid) from changedclaims)
>     order by claimid
>
> I would regard the defect as a major one - SAS have introduced an error so
> that a key SQL feature doesn't work correctly. The workaround only works if
> you know every place you use such code and that every comparision only
> involves integer values. In our case we would have to examine hundreds of
> SAS programs - not a practical suggestion. There is no uninstall feature for
> hot fixes unless you count manually renaming .dll files so we ended up doing
> a complete reinstall of SAS.
>
> Stephen Dunn
> Highway Insurance
> Tel: 01277 266253
> Fax: 01277 262828
> e-mail: stephen.dunn@highway-insurance.co.uk
>
>
>
> Member of the General Insurance Standards Council
>
> This email, together with any attachment(s), is for the exclusive
> and confidential information of the addressee(s).  Any other distribution,
> use or reproduction without the sender's prior consent is unauthorised
> and strictly prohibited.  If you receive this message in error, please
> notify the sender immediately and delete the message without making any
> copies.
> Any views or opinions expressed are the author's and do not necessarily
> represent those of Highway Insurance.  You should not rely on the content
> of this message unless satisfied it is from an authorised source.
> Highway Insurance reserves the right to monitor all email communications.