Re: Weird results from WHERE clause ...

Stored value Chuck - we are currently making sure that we have the very latest PTFs and then will report to IBM if no resolution from that.

We did manage to blow up SQL completely by trying to use the working CTE (i.e. the BETWEEN version) to define a view so we’re sure it is a DB2 issue but need to get up-to-date to be sure that it is not already fixed.


Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On May 12, 2016, at 11:11 AM, CRPence <crpbottle@xxxxxxxxx> wrote:

On 12-May-2016 09:05 -0500, Jon Paris wrote:

<<SNIP>>

For example SELECT * from MyCTE where UniqueId between 50 and 50
produces the expected single row result with all columns correctly
populated.

On the other hand SELECT * from MyCTE where UniqueId = 50 also
produces a single row but all columns are empty/null. Needless to say
the results should be identical.

Originally this was run on a 6.1 system so it was moved to a pretty
current 7.1 box with identical results.

Anyone seen this or anything like it?

Is the UniqueId a generated value or a stored\physical value [e.g. in a primary or unique constraint key]? If generated, then how?

If not generated, then likely the change in implementation between the two queries using the different predicates [equal predicate vs BETWEEN predicate] is the origin for the issue; and presumably the query implementation of the variant producing the incorrect output would be the result of a defect to be reported to the service provider.

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.