[Info-ingres] Comparing nulls is beating me

[Martin Bowes]

I’ve got this on both:
II 10.2.0 (a64.lnx/100) +p15162
II 11.0.0 (a64.lnx/100) + p15325

I’ll raise a bug.

Marty

From: Paul Mason [mailto:latepaul at gmail.com]
Sent: 07 June 2018 09:40
To: Martin Bowes
Cc: info-ingres at lists.planetingres.org
Subject: Re: [Info-ingres] Comparing nulls is beating me

Hi Marty,

What version of Ingres is this?

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.

In other words, yes it's a bug, and apparently a fix. Raise an issue.

cheers
Paul



On Thu, 7 Jun 2018 at 09:21 Martin Bowes <martin.bowes at ndph.ox.ac.uk<mailto:martin.bowes at ndph.ox.ac.uk>> wrote:
Hi All.

What’s wrong with this query?

Given a table named full_hesin_table defined as:
declare global temporary table full_hesin_table(
        eid      integer4    not null not default,
        pid      integer4    not null not default,
        aflag    varchar(6)  not null with default,
        min_sec  varchar(16) with null with default,
        max_sec  varchar(16) with null with default
) on commit preserve rows with norecovery;

It contains data:
┌─────────────┬─────────────┬──────┬────────────────┬────────────────┐
│eid          │pid          │aflag │min_sec         │max_sec         │
├─────────────┼─────────────┼──────┼────────────────┼────────────────┤
│            1│            1│C8    │NULL            │1               │
│            2│            1│C8    │NULL            │1               │
│            3│            1│C8    │NULL            │1               │
└─────────────┴─────────────┴──────┴────────────────┴────────────────┘

SELECT DISTINCT a.eid AS eid
FROM full_hesin_table a, full_hesin_table b
WHERE a.eid < b.eid
  AND a.pid = b.pid
  AND a.aflag = b.aflag
  AND (a.min_sec = b.min_sec OR (a.min_sec IS NULL  AND  b.min_sec IS NULL))

┌─────────────┐
│eid          │
├─────────────┤
└─────────────┘
(0 rows)

But…
SELECT DISTINCT a.eid AS eid
FROM full_hesin_table a, full_hesin_table b
WHERE a.eid < b.eid
AND a.pid = b.pid
AND a.aflag = b.aflag
AND (a.max_sec = b.max_sec OR (a.max_sec IS NULL  AND  b.max_sec IS NULL))

┌─────────────┐
│eid          │
├─────────────┤
│            1│
│            2│
└─────────────┘
(2 rows)

I would have expected the first query to return the same two rows as the second query.
Clearly the null data is beating me but I thought I’d allowed for it correctly using the predicate’ IS NULL’.

I think it’s a bug. Anyone agree?

Marty

_______________________________________________
Info-ingres mailing list
Info-ingres at lists.planetingres.org<mailto:Info-ingres at lists.planetingres.org>
http://lists.planetingres.org/mailman/listinfo/info-ingres
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180607/3bd28d71/attachment.html>