<div dir="ltr">Hi Marty,<div><br></div><div>What version of Ingres is this?</div><div><br></div><div>I just tested your queries on head-revs 10.2, 11.0 and main and got 2 rows for each query - as I would expect. </div><div><br></div><div>In other words, yes it's a bug, and apparently a fix. Raise an issue. </div><div><br></div><div>cheers</div><div>Paul</div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr">On Thu, 7 Jun 2018 at 09:21 Martin Bowes <<a href="mailto:martin.bowes@ndph.ox.ac.uk">martin.bowes@ndph.ox.ac.uk</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="EN-GB" link="blue" vlink="purple">
<div class="m_-4119404471036663205WordSection1">
<p class="MsoNormal">Hi All.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">What’s wrong with this query?<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Given a table named full_hesin_table defined as:<u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">declare global temporary table full_hesin_table(<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> eid integer4 not null not default,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> pid integer4 not null not default,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> aflag varchar(6) not null with default,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> min_sec varchar(16) with null with default,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New""> max_sec varchar(16) with null with default<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">) on commit preserve rows with norecovery;<u></u><u></u></span></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">It contains data:<u></u><u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">┌─────────────┬─────────────┬──────┬────────────────┬────────────────┐<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">│eid │pid │aflag │min_sec │max_sec │<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">├─────────────┼─────────────┼──────┼────────────────┼────────────────┤<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">│ 1│ 1│C8 │NULL │1 │<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">│ 2│ 1│C8 │NULL │1 │<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">│ 3│ 1│C8 │NULL │1 │<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">└─────────────┴─────────────┴──────┴────────────────┴────────────────┘<u></u><u></u></span></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">SELECT DISTINCT a.eid AS eid<u></u><u></u></p>
<p class="MsoNormal">FROM full_hesin_table a, full_hesin_table b<u></u><u></u></p>
<p class="MsoNormal">WHERE a.eid < b.eid<u></u><u></u></p>
<p class="MsoNormal"> AND a.pid = b.pid<u></u><u></u></p>
<p class="MsoNormal"> AND a.aflag = b.aflag<u></u><u></u></p>
<p class="MsoNormal"> AND (a.min_sec = b.min_sec OR (a.min_sec IS NULL AND b.min_sec IS NULL))<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">┌─────────────┐<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">│eid │<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">├─────────────┤<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">└─────────────┘<u></u><u></u></span></p>
<p class="MsoNormal">(0 rows)<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">But… <u></u><u></u></p>
<p class="MsoNormal">SELECT DISTINCT a.eid AS eid<u></u><u></u></p>
<p class="MsoNormal">FROM full_hesin_table a, full_hesin_table b<u></u><u></u></p>
<p class="MsoNormal">WHERE a.eid < b.eid<u></u><u></u></p>
<p class="MsoNormal">AND a.pid = b.pid<u></u><u></u></p>
<p class="MsoNormal">AND a.aflag = b.aflag<u></u><u></u></p>
<p class="MsoNormal">AND (a.max_sec = b.max_sec OR (a.max_sec IS NULL AND b.max_sec IS NULL))<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">┌─────────────┐<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">│eid │<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">├─────────────┤<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">│ 1│<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">│ 2│<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">└─────────────┘<u></u><u></u></span></p>
<p class="MsoNormal">(2 rows)<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">I would have expected the first query to return the same two rows as the second query.<u></u><u></u></p>
<p class="MsoNormal">Clearly the null data is beating me but I thought I’d allowed for it correctly using the predicate’ IS NULL’.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">I think it’s a bug. Anyone agree?<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Marty<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div>
_______________________________________________<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="http://lists.planetingres.org/mailman/listinfo/info-ingres" rel="noreferrer" target="_blank">http://lists.planetingres.org/mailman/listinfo/info-ingres</a><br>
</blockquote></div>