[Info-Ingres] Promiscuous constraints!

[Martin Bowes]

Hi All.

 

II 10.1.0 (a64.lnx/126)NPTL + 14348.

 

I have a view:

create view xxx as

SELECT G.id AS gid, G.gadgetno,

IFNULL(S.id, -1) AS reqid,

IFNULL(S.status, -1) AS status,

IFNULL(S.dtime, G.dtime) AS updated

FROM GADGET G

    JOIN (

            SELECT G.id AS gid, max(D.id) AS did

            FROM GADGET G

                    LEFT JOIN DISPATCH D ON D.gid = G.id

                GROUP BY 1

                ) GD ON GD.gid = G.id

        LEFT JOIN DISPATCH D ON D.id = GD.did

        LEFT JOIN REQUEST_STATUS S ON S.id = D.reqid;

 

Which is lovely.

 

We ask it the following questions.

select count(*) from xxx;

The answer is 447.

 

select count(*) from xxx where reqid is null; /* Should be zero by definition of the view (see highlight) */

The answer is 447.

 

select count(*) from xxx where reqid is not null;

The answer is 447.

 

Now the amusing part.

The table request_status has two constraints. A primary key constraint on column id, and a foreign key constraint on column id (it must exist in the request table … which is not involved in this join).

 

If I drop both (it must be both) constraints on the request_status table then select…where reqid is null returns zero!

 

If I recreate the constraints…the problem returns.

If I drop the f’ing constraints, modify the table and this time specify with unique_scope=statement and recreate the constraints the problem is fixed.

 

Any ideas?

 

Martin Bowes