SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0

Jef Jakobs

SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0
I've run into a strange problem running a very simple sql-statement on a DB2
UDB 6.1 database
(fixpak 6 on NT 4.0):

select *
from sysibm.sysdummy1
where (1 = 1 or 1=2)
and (1 = 1 or (1=2 and 2=2))


The problem is that this query retrieves no rows...
Selecting from sysibm.sysdummy1 itself (without the where clause) does work.
I get one row.

Now
select *
from sysibm.sysdummy1
where (1 = 1 or 1=2)

and
select *
from sysibm.sysdummy1
where (1 = 1 or (1=2 and 2=2))

Both do also retrieve a row (as expected). When combined with an "and"
statement I don't select anything anymore. :(

When I run this on DB2 UDB 5.2 (against a table with one row), I do get a
result (one row) for the whole query.

My question is if anybody has run into this behavior, or can explain to me
what is going on.
That way I know if I need to report this to IBM as a bug (or not).

Thanks!


Jef Jakobs



Eric Pearson

Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0
(in response to Jef Jakobs)
This works correctly on DB2/390 V5.1.
I will try it at home on UDB PDE V7 running on Win2K
and UDB EEE Beta on Linux.

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Jakobs, Jef [mailto:[login to unmask email]
Sent: Tuesday, December 19, 2000 2:34 PM
To: [login to unmask email]
Subject: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0


I've run into a strange problem running a very simple sql-statement on a DB2
UDB 6.1 database
(fixpak 6 on NT 4.0):

select *
from sysibm.sysdummy1
where (1 = 1 or 1=2)
and (1 = 1 or (1=2 and 2=2))


The problem is that this query retrieves no rows...
Selecting from sysibm.sysdummy1 itself (without the where clause) does work.
I get one row.

Now
select *
from sysibm.sysdummy1
where (1 = 1 or 1=2)

and
select *
from sysibm.sysdummy1
where (1 = 1 or (1=2 and 2=2))

Both do also retrieve a row (as expected). When combined with an "and"
statement I don't select anything anymore. :(

When I run this on DB2 UDB 5.2 (against a table with one row), I do get a
result (one row) for the whole query.

My question is if anybody has run into this behavior, or can explain to me
what is going on.
That way I know if I need to report this to IBM as a bug (or not).

Thanks!


Jef Jakobs








Kirk Hampton

Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0
(in response to Eric Pearson)
I got this to return one result row on both UDB for OS/390 v6.1,
and UDB EEE v6.1 on Win/NT 4.0 at FP3.





"Pearson, Eric L," <[login to unmask email]> on 12/19/2000 01:41:32 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Kirk Hampton/Texas Utilities)
Subject: Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0



This works correctly on DB2/390 V5.1.
I will try it at home on UDB PDE V7 running on Win2K
and UDB EEE Beta on Linux.

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Jakobs, Jef [mailto:[login to unmask email]
Sent: Tuesday, December 19, 2000 2:34 PM
To: [login to unmask email]
Subject: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0


I've run into a strange problem running a very simple sql-statement on a DB2
UDB 6.1 database
(fixpak 6 on NT 4.0):

select *
from sysibm.sysdummy1
where (1 = 1 or 1=2)
and (1 = 1 or (1=2 and 2=2))


The problem is that this query retrieves no rows...
Selecting from sysibm.sysdummy1 itself (without the where clause) does work.
I get one row.

Now
select *
from sysibm.sysdummy1
where (1 = 1 or 1=2)

and
select *
from sysibm.sysdummy1
where (1 = 1 or (1=2 and 2=2))

Both do also retrieve a row (as expected). When combined with an "and"
statement I don't select anything anymore. :(

When I run this on DB2 UDB 5.2 (against a table with one row), I do get a
result (one row) for the whole query.

My question is if anybody has run into this behavior, or can explain to me
what is going on.
That way I know if I need to report this to IBM as a bug (or not).

Thanks!


Jef Jakobs













Jef Jakobs

Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0
(in response to Kirk Hampton)
Thanks Eric,

I've narrowed it down a bit.

Getting results with DB2 UDB 6.1 (NT 4.0) without fixpak
Also getting results with fixpak 3, 4 and 5 (don't have 1 & 2).

As soon as I install fixpak 6 I'm dead.
Must be an optimizer thingy.

Will inform IBM (and fallback to FP5)
So far I've only hurt our development group...

Thanks!


Jef Jakobs
-----Original Message-----
From: Kirk Hampton [mailto:[login to unmask email]
Sent: Tuesday, December 19, 2000 3:30 PM
To: [login to unmask email]
Subject: Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0


I got this to return one result row on both UDB for OS/390 v6.1,
and UDB EEE v6.1 on Win/NT 4.0 at FP3.





"Pearson, Eric L," <[login to unmask email]> on 12/19/2000 01:41:32 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Kirk Hampton/Texas Utilities)
Subject: Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0



This works correctly on DB2/390 V5.1.
I will try it at home on UDB PDE V7 running on Win2K
and UDB EEE Beta on Linux.

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Jakobs, Jef [mailto:[login to unmask email]
Sent: Tuesday, December 19, 2000 2:34 PM
To: [login to unmask email]
Subject: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0


I've run into a strange problem running a very simple sql-statement on a DB2
UDB 6.1 database
(fixpak 6 on NT 4.0):

select *
from sysibm.sysdummy1
where (1 = 1 or 1=2)
and (1 = 1 or (1=2 and 2=2))


The problem is that this query retrieves no rows...
Selecting from sysibm.sysdummy1 itself (without the where clause) does work.
I get one row.

Now
select *
from sysibm.sysdummy1
where (1 = 1 or 1=2)

and
select *
from sysibm.sysdummy1
where (1 = 1 or (1=2 and 2=2))

Both do also retrieve a row (as expected). When combined with an "and"
statement I don't select anything anymore. :(

When I run this on DB2 UDB 5.2 (against a table with one row), I do get a
result (one row) for the whole query.

My question is if anybody has run into this behavior, or can explain to me
what is going on.
That way I know if I need to report this to IBM as a bug (or not).

Thanks!


Jef Jakobs


















David Seibert

Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0
(in response to Jef Jakobs)
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]



Jef Jakobs

Re: SQL problem on DB2 UDB 6.1 fixpak 6 on NT 4.0
(in response to David Seibert)
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/aparlib.
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]