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','
BamBamR@...');
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]