Re: [Info-Ingres] When where clause *Really* alters what you select

Hi Steve et al,

It looks like a brand new bug. I'm very proud!

Thanks for playing.

Marty
>
> Hi Martin,
> I also get the same results on all three of the select count(*)
> selections.. HP-UX 11.11 II 2.6/0305 (hp2.us5/00) patch 11013
>
> On May 16, 1:54 pm, martin.bo...@ctsu.ox.ac.uk wrote:
> > Hi Everyone,
> >
> > I'm running II 9.0.4 (a64.lnx/105)NPTL patch12125.
> >
> > On a brand new database try this...Don't worry it won't crash
> > anything.
> >
> > sql your_new_db << SQL_END
> > \r
> > \continue
> > set autocommit on
> > \g
> > drop view named_object_view;
> > \p\g
> > create view named_object_view as
> > select reltid, trim(relid) as description from iirelation
> > union
> > select reltid, relowner as description from iirelation;
> >
> > \p\g
> > create view summary_view as
> > select reltid, n1.description, table_owner, table_type
> > from iitables left join named_object_view n1
> > on table_name = description;
> > \p\g
> > /* If the bug is present then the following counts will not add up!
> > ** There will be as many null cases as not null cases and each will
> > be ** equal to the total number of rows in the view! */ select
> > count(*) from summary_view; select count(*) from summary_view where
> > description is null; select count(*) from summary_view where
> > description is not null; \p\g /* even more fun... ** If the bug is
> > present then the 'where description is null' clause makes ** the
> > query return null for the descriptions the first query insists
> > aren't ** null. */ select reltid, description from summary_view
> > order by 1; select reltid, description from summary_view where
> > description is null order by 1; \p\g \q SQL_END
> >
> > Could people let me know if the selects are OK or not and on what
> > Ingres Versions they ran against. Anyone seen this before? Gotta Bug
> > number etc...
> >
> > Thanks Heaps.
> >
> > Martin Bowes
> > --
> > Random Farscape Quote #31:
> > John - I've either gotta stop pointing guns at people...or get a
> > bigger gun