• [Info-ingres] Upgrading to a newer Ingres version breaks SQL query

    From Paul White@21:1/5 to Ingres and related product discussi on Wed Feb 10 23:39:13 2021
    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)
  • From nikosv@21:1/5 to All on Wed Feb 10 06:39:31 2021
    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)
  • From nikosv@21:1/5 to All on Wed Feb 10 07:59:35 2021
    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)
  • From Karl Schendel@21:1/5 to nikosv on Wed Feb 10 11:10:46 2021
    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)
  • From nikosv@21:1/5 to All on Wed Feb 10 08:16:28 2021
    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)
  • From Karl Schendel@21:1/5 to nikosv on Wed Feb 10 11:25:14 2021
    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)
  • From nikosv@21:1/5 to All on Wed Feb 10 08:47:30 2021
    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)