SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0

Duncan Gardner

SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0
Jef,

Thanks for spotting this. FixPack6 on Solaris also introduced this problem.
Anything more I say is likely to be libel .....

Not very happy
Duncan Gardner
DBA Team, Infrastructure Support Group
Western Australian Government Employees Superannuation Board
(08) 9263 4164
All my own views etc ....


> ----------
> From: Jakobs, Jef[SMTP:[login to unmask email]
> Reply To: DB2 Data Base Discussion List
> Sent: 22 December 2000 00:17
> To: [login to unmask email]
> Subject: Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0
>
> David,
>
> Maybe I should have explained that this query I mailed out is a simplified
> version of the real thing. What's behind it is the following query:
>
> The real thing looks like:
> SELECT REFUNDS.DATE_RECEIVED,
> REFUNDS.RECEIPT_NUMBER,
> REFUNDS.REFUND_TYPE,
> REFUNDS.REFUND_AMOUNT,
> REFUNDS.REFUND_ISSUED_DATE,
> REFUNDS.ADDRESS1,
> REFUNDS.REFUND_PAYEE,
> REFUNDS.ADDRESS2,
> REFUNDS.REFUND_REASON,
> REFUNDS.REFUND_CHECK_NO,
> REFUNDS.ADDRESS1,
> REFUNDS.ADDRESS2,
> REFUNDS.CITY,
> REFUNDS.STATE,
> REFUNDS.ZIP ,
> RECEIPT.USER_ID
> FROM REFUNDS , RECEIPT
> WHERE REFUNDS.DATE_RECEIVED = RECEIPT.DATE_RECEIVED AND
> REFUNDS.RECEIPT_NUMBER = RECEIPT.RECEIPT_NUMBER
> AND
> (REFUNDS.DATE_RECEIVED >= :start_date AND
> REFUNDS.DATE_RECEIVED <= :end_date) AND (
> :user_arg = 9999 OR
> RECEIPT.USER_ID = :user_arg ) AND (
> :refund_arg = 'A' OR
> REFUNDS.REFUND_TYPE = :refund_arg) AND
> ((:paid_flag = 1 AND
> REFUNDS.REFUND_CHECK_NO IS NULL) OR
> (:paid_flag = 2 AND
> REFUNDS.REFUND_CHECK_NO IS NOT NULL) OR
> (:paid_flag = 3))
> ORDER BY REFUNDS.DATE_RECEIVED ASC,
> REFUNDS.RECEIPT_NUMBER ASC
>
> Since I couldn't ask anybody to run that dragon of a query without
> creating
> tables, inserting data etc, I simplified it. It still shows wrong results,
> but now anybody (on version 6.1 of DB2 UDB) can run it.
>
> Think I tracked it down to an enhancement:
> http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/aparli
> b.
> d2w/display_apar_details?aparno=JR15040
>
> Funny thing is that when I change "(1=1 or 1=2)" into "(1=2 or 1=1)" it
> works:
>
> --------------------------- Command entered ----------------------------
> select *
> from sysibm.sysdummy1
> where (1=1 or 1=2)
> and (1=1 or (1=2 and 2=2))
> ------------------------------------------------------------------------
> IBMREQD
> -------
> 0 record(s) selected.
>
> --------------------------- Command entered ----------------------------
> select *
> from sysibm.sysdummy1
> where (1=2 or 1=1)
> and (1=1 or (1=2 and 2=2))
> ------------------------------------------------------------------------
> IBMREQD
> -------
> Y
> 1 record(s) selected.
>
>
> Thanks for all the feedback.
> At first I thought I was going crazy
> (and could not interpret simple queries anymore).
>
> What our developers like to do, is turn part of the where statement off
> with
> false predicates, that way they only need one (powerbuilder) window,
> instead
> of two. Now I don't like that, but in this case, it should work...
>
>
> Jef Jakobs
>
> -----Original Message-----
> From: Seibert, Dave [mailto:[login to unmask email]
> Sent: Wednesday, December 20, 2000 7:18 PM
> To: [login to unmask email]
> Subject: Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0
>
>
> Hi Jef,
> From the inconsistent results I see in the follow-on posts, it appears
> to
> be a bug or maybe just a new feature.
>
> But I have to ask:
>
> what is the objective of SQL like that?
>
> thanks
>
> David Seibert
> Compuware Corporation File-AID Product Architect
> [login to unmask email]
>
>
>
> the
>
>
>
>
>
>
>
>

========
Standard disclaimer:
Any recipient of this communication acknowledges that:
* the Government Employees Superannuation Board accepts no responsibility for the contents nor the validity of this communication; and
* they do not rely on any view given unless it is properly authorised.
========