Hi Nikkos

Make sure you are running the most recent patch.

Try adding
WITH NOOJFLATTEN
to the end of the query.

Early last year I ran into a similar problem after upgrading to 11.0. The
SQL statement had aggregates and a NOT IN (subquery). Removing a simple
column made the problem go away. Changing NOT IN to a NOT EXISTS structure also worked around the bug.

A suggestion from the Support team also worked. Add
WITH NOOJFLATTEN
at the end of the query.

I fixed the issue with Patch P15516 which had a bunch of fixes for
aggregate queries and subselects.


Regarding my test case, the Support team wrote:


Reproducible in
- II 10.2.0 (a64.lnx/100) + p15427
- II 11.0.0 (a64.lnx/100) + p15509

but not in
- II 11.1.0 (a64.lnx/100) + 15508

Adding "WITH NOOJFLATTEN" to the query works around the issue in both 10.2
and 11.0.


Hope it helps.
Cheers

Paul


On Wed, Feb 10, 2021 at 7:25 PM Roy Hann <specially@processed.almost.meat> wrote:

nikosv wrote:

upgrading from II 10.0.1 (a64.lnx/100)NPTL to II 10.2.0 (a64.lnx/100)

seems to be breaking a SQL query by totally ignoring nulls.

The description I wrote is too big to fit in the forum's format plus I

also use screenshots.So can you please check my blog's item on the case to give me hints on what could be the issue with it?

https://perlingresprogramming.blogspot.com/2021/02/upgrading-to-newer-ingres-version.html

Is it a bug or am I just seeing ghosts?

I don't know if this is important but Q1 in your post does not parse.
There is an unbalanced parenthesis.

...and ( (d1.last_aa is null) or...

Roy

_______________________________________________
Info-ingres mailing list
Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres

<div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr">Hi Nikkos<div><br></div><div>Make sure you are running the most recent patch.</div><div><br></div><div>Try adding</div><div>  WITH NOOJFLATTEN</div><div>to the end
of the query.</div><div>  <br></div><div>Early last year I ran into a similar problem after upgrading to 11.0. The SQL statement had aggregates and a NOT IN (subquery). Removing a simple column made the problem go away.  Changing NOT IN to a NOT
EXISTS structure also worked around the bug.</div><div><br></div><div>A suggestion from the Support team also worked. Add</div><div>  WITH NOOJFLATTEN</div><div>at the end of the query.</div><div><br></div><div></div><div>I fixed the issue with Patch
P15516 which had a bunch of fixes for aggregate queries and subselects.</div><div><br></div><div><br></div><div>Regarding my test case, the Support team wrote:</div></div></div></div></div></div><blockquote style="margin:0 0 0 40px;border:none;padding:
0px"><div><div><div><div><div><div><br></div></div></div></div></div></div><div><div><div><div><div><div><span style="color:rgb(40,40,40);font-family:Montserrat,sans-serif;font-size:13px;background-color:rgb(249,249,249)">Reproducible in</span></div></

</div></div></div></div><div><div><div><div><div><div><span style="color:rgb(40,40,40);font-family:Montserrat,sans-serif;font-size:13px;background-color:rgb(249,249,249)">- II 10.2.0 (a64.lnx/100) + p15427</span></div></div></div></div></div></div><
<div><div><div><div><div><span style="color:rgb(40,40,40);font-family:Montserrat,sans-serif;font-size:13px;background-color:rgb(249,249,249)">- II 11.0.0 (a64.lnx/100) + p15509</span></div></div></div></div></div></div><div><div><div><div><div><div><

br style="box-sizing:border-box;font-family:Montserrat,sans-serif;color:rgb(40,40,40);font-size:13px;background-color:rgb(249,249,249)"></div></div></div></div></div></div><div><div><div><div><div><div><span style="color:rgb(40,40,40);font-family:
Montserrat,sans-serif;font-size:13px;background-color:rgb(249,249,249)">but not in</span></div></div></div></div></div></div><div><div><div><div><div><div><span style="color:rgb(40,40,40);font-family:Montserrat,sans-serif;font-size:13px;background-color:
rgb(249,249,249)">- II 11.1.0 (a64.lnx/100) + 15508</span></div></div></div></div></div></div><div><div><div><div><div><div><br style="box-sizing:border-box;font-family:Montserrat,sans-serif;color:rgb(40,40,40);font-size:13px;background-color:rgb(249,249,
249)"></div></div></div></div></div></div><div><div><div><div><div><div><span style="color:rgb(40,40,40);font-family:Montserrat,sans-serif;font-size:13px;background-color:rgb(249,249,249)">Adding &quot;WITH NOOJFLATTEN&quot; to the query works around the
issue in both 10.2 and 11.0.</span></div></div></div></div></div></div></blockquote><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div><span style="color:rgb(40,40,40);font-family:Montserrat,sans-serif;font-size:13px;
background-color:rgb(249,249,249)"><br></span></div><div><span style="color:rgb(40,40,40);font-family:Montserrat,sans-serif;font-size:13px;background-color:rgb(249,249,249)">Hope it helps.</span></div><div><span style="color:rgb(40,40,40);font-family:
Montserrat,sans-serif;font-size:13px;background-color:rgb(249,249,249)">Cheers </span></div><div><span style="color:rgb(40,40,40);font-family:Montserrat,sans-serif;font-size:13px;background-color:rgb(249,249,249)"><br></span></div><div><span style="
color:rgb(40,40,40);font-family:Montserrat,sans-serif;font-size:13px;background-color:rgb(249,249,249)">Paul</span></div><div><span style="color:rgb(40,40,40);font-family:Montserrat,sans-serif;font-size:13px;background-color:rgb(249,249,249)"><br></span><
/div></div></div></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Feb 10, 2021 at 7:25 PM Roy Hann &lt;specially@processed.almost.meat&gt; wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.
8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">nikosv wrote:<br>

&gt; upgrading from II 10.0.1 (a64.lnx/100)NPTL  to II 10.2.0 (a64.lnx/100) seems to be breaking a SQL query by totally ignoring nulls.<br>
&gt;<br>
&gt; The description I wrote is too big to fit in the forum&#39;s format plus I also use screenshots.So can you please check my blog&#39;s item on the case to give me hints on what could be the issue with it?<br>
&gt;<br>
&gt; <a href="https://perlingresprogramming.blogspot.com/2021/02/upgrading-to-newer-ingres-version.html" rel="noreferrer" target="_blank">https://perlingresprogramming.blogspot.com/2021/02/upgrading-to-newer-ingres-version.html</a><br>
&gt;<br>
&gt; Is it a bug or am I just seeing ghosts?<br>

I don&#39;t know if this is important but Q1 in your post does not parse.<br> There is an unbalanced parenthesis.<br>

...and ( (d1.last_aa is null) or...<br>

Roy<br>

_______________________________________________<br>
Info-ingres mailing list<br>
<a href="mailto:Info-ingres@lists.planetingres.org" target="_blank">Info-ingres@lists.planetingres.org</a><br>
<a href="https://lists.planetingres.org/mailman/listinfo/info-ingres" rel="noreferrer" target="_blank">https://lists.planetingres.org/mailman/listinfo/info-ingres</a><br>
</blockquote></div><br clear="all"><div><br></div></div>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Paul and thanks for replying.
I've tried WITH NOOJFLATTEN but it didn't work.
It's missing a patch most probably.How do I check the installation for applied patches?
thanks

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

It's missing a patch most probably.How do I check the installation for applied patches?

Is that the way?
ls -l --full-time ${II_SYSTEM}/ingres/lib/iimerge.a | awk '{print $6}'
gives:
2014-08-13

while

cat ${II_SYSTEM}/ingres/version.rel
gives
II 10.2.0 (a64.lnx/100)

What does this mean? That it has no patches applied ? And what does this iimerge.a trickery mean?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

To: info-ingres@lists.planetingres.org (Ingres and related product discussion forum)

On Feb 10, 2021, at 10:59 AM, nikosv <nikos.vaggalis@gmail.com> wrote:

It's missing a patch most probably.How do I check the installation for applied patches?

cat $II_SYSTEM/ingres/version.rel

is the proper way. it looks like you are running an unpatched 10.2. There were
a *lot* of fixes between the initial 10.2 release and the final patches, so it's quite
possible that your particular bug is fixed by a patch.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Karl,
if there had been patches applied , what would be the output of this command be? (as an example to know what I should be looking for)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

To: info-ingres@lists.planetingres.org (Ingres and related product discussion forum)

On Feb 10, 2021, at 11:16 AM, nikosv <nikos.vaggalis@gmail.com> wrote:

Hi Karl,
if there had been patches applied , what would be the output of this command be? (as an example to know what I should be looking for)

I'd expect to see a bunch of numbers listed.

II 10.2.0 (a64.lnx/100)
10234
11754
etc
etc

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Thanks. Somewhere I saw that there's also a version.dat file but can't find it. Is there any other way except the version.rel file?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)