SQL problem on DB2 UDB 6.1 fixpak 6 on NT & Solaris

Duncan Gardner

SQL problem on DB2 UDB 6.1 fixpak 6 on NT & Solaris
We raised an ETR for this (PMR 09003) and there is now an APAR (IY15592) for
Solaris. We have been given a fix to FixPack6 which cures this problem and
I believe it will also be incorporated in FixPack 7.

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]
>
>
>
> visit the
> DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> can be
>
>
>
>
> visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the
> list can
>
>

========
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.
========