--Error Report Jeroen Eitjes. J.eitjes@chem.leidenuniv.nl; eitjes@walras.nl
--Walras IT&TC

--First Create the tables.
CREATE TABLE services (
	servicenummer INTEGER PRIMARY KEY,
	omschrijving VARCHAR(100),
	releasedatum TIMESTAMP
);

CREATE TABLE clickvakjes (
	clicknummer INTEGER,
	servicenummer INTEGER REFERENCES services,
   code VARCHAR(5),
	omschrijving VARCHAR(100),
	PRIMARY KEY(clicknummer)   
);

CREATE TABLE gebruikers (
   gebruikersnummer INTEGER,
   registratiedatum TIMESTAMP,
   lastdatum TIMESTAMP,
   lastsessienummer INTEGER,
	PRIMARY KEY(gebruikersnummer)
);

CREATE TABLE clicks (
	clicknummer INTEGER REFERENCES clickvakjes,
   gebruikersnummer INTEGER REFERENCES gebruikers,
   sessienr INTEGER,
   volgnr INTEGER,
   statusnr INTEGER,
	datum TIMESTAMP,	
	PRIMARY KEY(clicknummer, gebruikersnummer, sessienr, volgnr)
);

CREATE TABLE statistiek (
   hits INTEGER
);

--Fill them with some relevant data to reproduce the error
INSERT INTO Services VALUES (
	1, 'Service 1', '5-3-2001 16:51:07'
);
INSERT INTO Services VALUES (
	2, 'Service 2', '5-3-2001 16:51:07'
);
INSERT INTO Services VALUES (
	3, 'Service 3', '5-3-2001 16:51:07'
);
INSERT INTO Services VALUES (
	4, 'Service 4', '5-3-2001 16:51:07'
);

INSERT INTO Gebruikers VALUES (
   1, '5-3-2001 16:51:07', '5-3-2001 16:51:07', 12
);
INSERT INTO Gebruikers VALUES (
	2, '5-3-2001 16:51:07', '5-3-2001 16:51:07', 13
);
INSERT INTO Gebruikers VALUES (
	3, '5-3-2001 16:51:07', '5-3-2001 16:51:07', 14
);

INSERT INTO Clickvakjes VALUES (
	1, 1, 'Cl1', 'Clicker 1'
);
INSERT INTO Clickvakjes VALUES (
	3, 1, 'Cl3', 'Clicker 3'
);
INSERT INTO Clickvakjes VALUES (
	4, 1, 'Cl1', 'Clicker 1'
);
INSERT INTO Clickvakjes VALUES (
	2, 1, 'Cl2', 'Clicker 2'
);
INSERT INTO Clickvakjes VALUES (
	5, 3, 'Cyclo', 'Cycloclicker'
);
INSERT INTO Clickvakjes VALUES (
	6, 4, 'Assym', 'AsymboClickker'
);
INSERT INTO Clickvakjes VALUES (
	7, 4, 'IrIX', 'IRIX 64 ProGen'
);
INSERT INTO Clickvakjes VALUES (
	8, 3, 'Alim', 'NeoAlim Clicker'	
);

INSERT INTO Clicks VALUES (
	3, 2, 1, 11, 1, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	3, 1, 2, 11, 2, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	3, 1, 3, 11, 3, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	3, 1, 4, 11, 4, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	5, 1, 5, 11, 5, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	5, 2, 6, 11, 6, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	6, 1, 7, 11, 7, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	3, 1, 8, 11, 8, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	3, 2, 9, 11, 9, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	3, 1, 10, 11, 10, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	3, 1, 11, 11, 11, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	2, 2, 12, 11, 12, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	2, 1, 13, 11, 13, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	2, 2, 14, 11, 14, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	2, 1, 15, 11, 15, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	2, 1, 16, 11, 16, '2001-03-05 16:51:07'
	);
INSERT INTO Clicks VALUES (
	2, 1, 17, 11, 17, '2001-03-05 16:51:07'
);
INSERT INTO Clicks VALUES (
	2, 1, 18, 11, 18, '2001-03-05 16:51:07'
);
INSERT INTO Clicks VALUES (
	2, 2, 19, 11, 19, '2001-03-05 16:51:07'
);

INSERT INTO statistiek VALUES (0);

--To show the error: Create a view and an identical temporary table

CREATE VIEW vwTrouble AS 
SELECT S.Omschrijving AS Service, G.Gebruikersnummer AS Gnummer
FROM Gebruikers G, Clicks C, Services S, Clickvakjes Cl
WHERE G.Gebruikersnummer = C.Gebruikersnummer AND
C.Clicknummer = Cl.Clicknummer AND Cl.Servicenummer =
S.Servicenummer AND C.datum >= '12-03-1990 12:15:51' AND C.datum <= '12-03-2020 12:15:57' AND
S.Servicenummer IN (2,1,4,3)
GROUP BY Gnummer, Service
;

--This statement surprises me sometime with a 'All my bits blew right of
--the end of the world' error.
SELECT S.Omschrijving AS Service, G.Gebruikersnummer AS Gnummer
INTO TEMPORARY TABLE tbTrouble
FROM Gebruikers G, Clicks C, Services S, Clickvakjes Cl
WHERE G.Gebruikersnummer = C.Gebruikersnummer AND
C.Clicknummer = Cl.Clicknummer AND Cl.Servicenummer =
S.Servicenummer AND C.datum >= '12-03-1990 12:15:51' AND C.datum <= '12-03-2020 12:15:57' AND
S.Servicenummer IN (2,1,4,3)
GROUP BY Gnummer, Service
;

--Now let's reproduce the nasty little bug
SELECT service, count(*) AS GebruikersAantal
FROM tbtrouble GROUP BY service;    --This work's out as expected...

SELECT service, count(*) AS GebruikersAantal
FROM vwtrouble GROUP BY service;    --Count(*)'s Wrong!

--Drop tables for sake of reproducibility
DROP TABLE Clicks;
DROP TABLE Clickvakjes;
DROP TABLE Services;
DROP TABLE Gebruikers;
DROP TABLE Statistiek;
DROP TABLE vwTrouble;
DROP TABLE tbTrouble;
