Upgrading to a newer Ingres version breaks SQL query

[nikosv]

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?

[Roy Hann]

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

[nksvg]

typo when fiddling with the text for the article

[nikosv]

On Wednesday, February 10, 2021 at 11:28:01 AM UTC+2, nksvg wrote:
> typo when fiddling with the text for the article

fixed

[Paul White]

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

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

[nikosv]

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

[nikosv]

> 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?

[Karl Schendel]

> On Feb 10, 2021, at 10:59 AM, nikosv <nikos.v...@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.

[nikosv]

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)

[Karl Schendel]

> On Feb 10, 2021, at 11:16 AM, nikosv <nikos.v...@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

[nikosv]

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?