The following script works on FB1.5 but not FB 2.0+
I'm not sure if I can attach a file so I'll also embed the sql script in
the email. I run the script using isql. The script creates the database so
the ods should be correct for each ver of FB.
cheers
Paul
/******Script Start ***************/
/*
Stripped out script to test subqueries in select stmt for FB 2.0
This works as expected on FB1.5, but not on 2.0
*/
CONNECT './testsq.fdb' USER 'sysdba' PASSWORD 'masterkey';
DROP DATABASE;
CREATE DATABASE './testsq.fdb' USER 'sysdba' PASSWORD 'masterkey';
SET SQL DIALECT 3;
CREATE TABLE Punter(
Id INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(80) NOT NULL CONSTRAINT Pu_Name_Unique UNIQUE,
Email VARCHAR(80) NOT NULL CONSTRAINT Email_Unique UNIQUE,
Alive SMALLINT DEFAULT 1
);
CREATE TABLE Issue(
Id INTEGER NOT NULL PRIMARY KEY,
Summary VARCHAR(256)
);
CREATE TABLE Product(
Id INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(20)
);
CREATE TABLE IssueProduct(
IssueId INTEGER NOT NULL CONSTRAINT IP_IssueId REFERENCES Issue(Id)
ON UPDATE CASCADE
ON DELETE CASCADE,
ProductId INTEGER NOT NULL CONSTRAINT IP_ProductId REFERENCES Product(Id)
ON UPDATE CASCADE
ON DELETE NO ACTION,
PRIMARY KEY (IssueId, ProductId)
);
CREATE TABLE ProductSubscription(
ProductId INTEGER NOT NULL CONSTRAINT PIL_ProductId REFERENCES
Product(Id)
ON UPDATE CASCADE
ON DELETE CASCADE,
UserId INTEGER NOT NULL CONSTRAINT PIL_UserId REFERENCES Punter(Id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (ProductId, UserId )
);
CREATE TABLE ISSUEBLACKLIST(
ISSUEID Integer NOT NULL CONSTRAINT IBL_ISSUEID REFERENCES ISSUE (ID) ON
UPDATE CASCADE ON DELETE CASCADE,
USERID Integer NOT NULL CONSTRAINT IBL_USERID REFERENCES PUNTER (ID) ON
UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (ISSUEID,USERID)
);
COMMIT;
/* Add the test records */
INSERT INTO Product( Id, Name) VALUES ( 2, 'testProduct' );
INSERT INTO Punter( Id, Name, Email ) VALUES ( 3, 'BamBam','
bamb...@yahoo.co.nz');
INSERT INTO ProductSubscription( ProductId, UserId) VALUES ( 2, 3);
INSERT INTO Issue( Id, Summary) VALUES (105, 'testIssue');
INSERT INTO IssueProduct( IssueId, ProductId) VALUES ( 105, 2);
Commit;
/* And now the offending query */
SHELL ECHO This *should* display a single result (and does on FB1.5):;
SELECT u.Id, u.Name, u.Email FROM Punter u
WHERE u.ALIVE = 1
/* they haven't blacklisted this issue */
AND u.Id not IN (SELECT bl.UserId FROM IssueBlacklist bl WHERE bl.IssueId
= 105)
/* Subsribed via the product */
AND u.id in (SELECT ps.UserId FROM ProductSubscription ps
JOIN IssueProduct ip ON (ps.ProductId
= ip.ProductId)
WHERE ip.IssueId = 105 ) ;
/* By commenting out one of the subqueries it now works on both 1.5 and 2.0
*/
SHELL ECHO This will display a single result:;
SELECT u.Id, u.Name, u.Email FROM Punter u
WHERE u.ALIVE = 1
/* they haven't blacklisted this issue */
/*AND u.Id not IN (SELECT bl.UserId FROM IssueBlacklist bl WHERE
bl.IssueId = 105) */
/* Subsribed via the product */
AND u.id in (SELECT ps.UserId FROM ProductSubscription ps
JOIN IssueProduct ip ON (ps.ProductId
= ip.ProductId)
WHERE ip.IssueId = 105 ) ;
SHELL ECHO END.;
/******Script End***************/
[Non-text portions of this message have been removed]
