# Create a new JavaTest database
DROP DATABASE JavaTest;
CREATE DATABASE JavaTest;

# Create tables with int auto_increment keys and foreign key constraints
USE JavaTest;
CREATE TABLE Series (
	SeriesNo int auto_increment not null primary key,
	SeriesName varchar(20) not null unique
);
CREATE TABLE Entity (
	EntityNo int auto_increment not null primary key,
	EntityName varchar(20) not null unique
);
CREATE TABLE Provenance (
	ProvenanceNo int auto_increment not null primary key,
	SeriesNo int null,
	EntityNo int null,
	CONSTRAINT FK_Series FOREIGN KEY (SeriesNo) REFERENCES Series (SeriesNo),
	CONSTRAINT FK_Entity FOREIGN KEY (SeriesNo) REFERENCES Entity (EntityNo)
);

# Add a few rows to the parent tables
INSERT INTO Series (SeriesName) VALUES ('First Series');
INSERT INTO Series (SeriesName) VALUES ('Second Series');
INSERT INTO Series (SeriesName) VALUES ('Third Series');
INSERT INTO Entity (EntityName) VALUES ('Brown');
INSERT INTO Entity (EntityName) VALUES ('Green');
INSERT INTO Entity (EntityName) VALUES ('Smith');
INSERT INTO Entity (EntityName) VALUES ('Jones');

# Add a row to the provenance table and then attempt to retrieve rows that have a null primary key
# The select statement returns the last provenance row entered instead of the empty set
# Running the SELECT statement a second time produces the correct result
INSERT INTO Provenance (SeriesNo, EntityNo) VALUES (2, 3);
EXPLAIN SELECT * FROM Provenance WHERE ProvenanceNo Is Null;