[Info-ingres] Comparing nulls is beating me

[Martin Bowes]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180607/d3b76ee0/attachment.html>