Re: Weird results from WHERE clause ...

[Jon Paris <jon.paris@xxxxxxxxxxxxxx>]

Yes indeed Vern - it is a wondrous thing to behold!  I’m actually surprised it didn’t blow up trying to build it.

This CTE is HUGE and (for reasons I won’t get into) part of its function is to de-normalize the source tables using case statements to generate sets of 3 x 32 columns from 32 rows of 3 rows.

Not pretty.

 
Jon Paris

www.partner400.com
www.SystemiDeveloper.com

> On May 12, 2016, at 5:04 PM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx> wrote:
>
> Visual Explain might be very interesting - wonder if Jon used that yet?
>
> On 5/12/2016 2:56 PM, Carel wrote:
> > I do not know if this add to the solution.
> >
> > I once had a strange result with a CTE
> >
> >        WITH tmp AS( SELECT ...
> >
> > I changed it to
> >
> >        WITH tmp(fieldA, fieldB, ...) AS (SELECT ...
> >
> > it worked as it should be.
> >
> > Perhaps that may be a solution (depending the number of columns in the result set).
> >
> > Regards,
> >
> > Carel Teijgeler
> >
> > Op 12-5-2016 om 18:39 schreef Jon Paris:
> > > No.
> > >
> > > If we use the base table that uniqueId originates from we get the same results from both WHERE clauses as we should.
> > >
> > > As I noted earlier we have identified a PTF that may be an answer but we’re not convinced it will help because the same problem exists at V7.1 and that PTF does not have a V7.1 equivalent.
> > >
> > >
> > > Jon Paris
> > >
> > > www.partner400.com
> > > www.SystemiDeveloper.com
> > >
> > > > On May 12, 2016, at 11:13 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
> > > >
> > > > Does the behavior manifest on a very simple version?
> > > >
> > > > with mycte as (select * from mytble)
> > > > select * from mtcte
> > > > where key <...>
> > > >
> > > > What PTF levels?
> > > >
> > > >
> > > >
> > > > On Thu, May 12, 2016 at 10:59 AM, Jon Paris <jon.paris@xxxxxxxxxxxxxx>
> > > > wrote:
> > > >
> > > > > Trust me Charles there is no typo - no room for one really if you think
> > > > > about it.  Simply changing the where clause at the end of a VERY large CTE
> > > > > changes the result. Multiple people have looked at this and watched the
> > > > > change being made in the interests of convincing ourselves of our sanity.
> > > > >
> > > > > As
> > > > > Jon Paris
> > > > >
> > > > > www.partner400.com
> > > > > www.SystemiDeveloper.com
> > > > >
> > > > > > On May 12, 2016, at 10:09 AM, Charles Wilt <charles.wilt@xxxxxxxxx>
> > > > > wrote:
> > > > > > Jon,
> > > > > >
> > > > > > Probably need to see the actual code...guessing some sort of typo.
> > > > > >
> > > > > > A quick test of your paraphase code on my system returns the same single
> > > > > > row results.
> > > > > >
> > > > > > Charles
> > > > > >
> > > > > >
> > > > > >
> > > > > > On Thu, May 12, 2016 at 10:05 AM, Jon Paris <jon.paris@xxxxxxxxxxxxxx>
> > > > > > wrote:
> > > > > >
> > > > > > > OK SQL gurus - need your help here.  I’m pretty sure this has to be an
> > > > > SQL
> > > > > > > bug but if so I’d have expected it to have been reported before now and
> > > > > I
> > > > > > > can’t find anything on IBM’s site that sounds similar.
> > > > > > >
> > > > > > > Have a large complex CTE which produces a weird result if I use = in the
> > > > > > > WHERE compared with BETWEEN.
> > > > > > >
> > > > > > > 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?
> > > > > > >
> > > > > > >
> > > > > > > Jon Paris
> > > > > > >
> > > > > > > www.partner400.com
> > > > > > > www.SystemiDeveloper.com
> > > > > > >
> > > > > > > -- 
> > > > > > > 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.
> > > > > > -- 
> > > > > > 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.
> > > > >
> > > > > -- 
> > > > > 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.
> > > > -- 
> > > > 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.
>
> -- 
> 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.