[Info-ingres] Comparing nulls is beating me

[Paul Mason]

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> 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
> 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/951cc982/attachment.html>