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.
