BUG #3426: Rows disappear from complex join - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      3426
Logged by:          David Flater
Email address:      dflater@nist.gov
PostgreSQL version: 8.2.4
Operating system:   GNU/Linux
Description:        Rows disappear from complex join
Details:

Hello,

I am getting NO DATA on queries that attempt to match one of the "outer"
rows generated by an outer join.

The following test script can be run on an empty database to demonstrate the
problem I am having.  The problem only appeared after I rewrote the view
BlankBallot to use a join instead of a subquery.  The contents of the old
and new versions of BlankBallot are identical according to select *, but the
behavior of queries that try to match specific rows has changed.

The results I get and the results I expected are in comments around the
three select statements at the end.

Thank you,
DWF

--------------- cut here ------------------

create table ReportingContext (
  Name  Text  primary key
);

create table Contest (
  ContestId      Integer  primary key,
  Description    Text     not null,
  N              Integer  not null check (N > 0),
  MaxWriteIns    Integer  not null check (MaxWriteIns between 0 and N),
  Rotate         Boolean  not null
);

create table Choice (
  ChoiceId     Integer  primary key,
  ContestId    Integer  not null references Contest,
  Name         Text     not null,
  IsWriteIn    Boolean  not null
);

create table BallotStyle (
  StyleId  Integer  primary key,
  Name     Text     not null
);

create table Ballot (
  BallotId  Integer  primary key,
  StyleId   Integer  not null references BallotStyle,
  Accepted  Boolean  not null
);

create table VoterInput (
  BallotId  Integer  references Ballot,
  ChoiceId  Integer  references Choice,
  Value     Integer  not null check (Value > 0),
  primary key (BallotId, ChoiceId)
);

create table BallotStyleContestAssociation (
  StyleId    Integer  references BallotStyle,
  ContestId  Integer  references Contest,
  primary key (StyleId, ContestId)
);

create table BallotStyleReportingContextAssociation (
  StyleId           Integer  references BallotStyle,
  ReportingContext  Text     references ReportingContext,
  primary key (StyleId, ReportingContext)
);

create table BallotReportingContextAssociation (
  BallotId          Integer  references Ballot,
  ReportingContext  Text     references ReportingContext,
  primary key (BallotId, ReportingContext)
);

create view ReportingContextAssociationMerge (BallotId, ReportingContext)
as
    select BallotId, ReportingContext
      from BallotReportingContextAssociation
  union
    select BallotId, ReportingContext
      from Ballot natural join BallotStyleReportingContextAssociation;

create view ReportingContextContestAssociation (ReportingContext, ContestId)
as
    select ReportingContext, ContestId
      from BallotStyleReportingContextAssociation
        natural join BallotStyleContestAssociation
  union
    select ReportingContext, ContestId
      from BallotReportingContextAssociation
        natural join Ballot
        natural join BallotStyleContestAssociation;

create view BlankBallot (BallotId, StyleId, Accepted) as
  select BallotId, StyleId, Accepted
    from Ballot
      natural left outer join VoterInput
    where Value is null;

create view BlankBallotCounts (ReportingContext, Read, Counted) as
  select Name, count(BallotId), count (nullif (Accepted, false))
    from BlankBallot
      natural join ReportingContextAssociationMerge
      right outer join ReportingContext on (Name = ReportingContext)
    group by Name;


insert into ReportingContext values
  ('Precinct 1'),
  ('District 1'),
  ('District 2');

insert into Contest (ContestId, Description, N,
                     MaxWriteIns, Rotate) values
  (1, 'President, vote for at most 1', 1, 0, false);

insert into Choice (ChoiceId, ContestId, Name, IsWriteIn) values
  (0,  1, 'Nada Zayro', false),
  (1,  1, 'Oona Won', false),
  (2,  1, 'Beeso Tu', false),
  (3,  1, 'Tayra Tree', false),
  (4,  1, 'Car Tay Fower', false);

insert into BallotStyle (StyleId, Name) values
  (1, 'District 1 Style'),
  (2, 'District 2 Style');

insert into BallotStyleContestAssociation (StyleId, ContestId) values
  (1, 1),
  (2, 1);

insert into BallotStyleReportingContextAssociation (StyleId,
                            ReportingContext) values
  (1, 'Precinct 1'),
  (1, 'District 1'),
  (2, 'Precinct 1'),
  (2, 'District 2');

insert into Ballot (BallotId, StyleId, Accepted) values
  (0, 1, true),
  (1, 2, true),
  (2, 1, true),
  (3, 2, true),
  (4, 1, true),
  (5, 2, true),
  (6, 1, true),
  (7, 2, true),
  (8, 1, true),
  (9, 2, true),
  (10, 1, true),
  (11, 2, true);

insert into VoterInput (BallotId, ChoiceId, Value) values
  (1, 1, 1),
  (2, 2, 1),
  (3, 2, 1),
  (4, 3, 1),
  (5, 3, 1),
  (6, 3, 1),
  (7, 4, 1),
  (8, 4, 1),
  (9, 4, 1),
  (10, 4, 1),
  (11, 0, 1),
  (11, 1, 1);


-- We have 3 rows, including one where reportingcontext = 'District 2'
select * from BlankBallotCounts;
-- I get:
--  reportingcontext | read | counted
-- ------------------+------+---------
--  Precinct 1       |    1 |       1
--  District 1       |    1 |       1
--  District 2       |    0 |       0
-- (3 rows)
-- As it should be.

-- So where is it now?
select * from BlankBallotCounts where reportingcontext = 'District 2';
-- I get:
--  reportingcontext | read | counted
-- ------------------+------+---------
-- (0 rows)
-- Was expecting to get one row.

-- Shouldn't the sum of the following two values be 3?  There are no NULLs
-- in the column ReportingContext.
select count(*) from BlankBallotCounts where reportingcontext = 'District
2';
-- I get 0, was expecting 1.
select count(*) from BlankBallotCounts where reportingcontext <> 'District
2';
-- I get 2, as it should be.