>Existence Predicates NOT IN and ALL May Be Slow
>
>Firebird and, before that, InterBase, have produced incorrect results

for the logical existence predicates ALL and NOT IN for many years.

>That problem has been corrected in Firebird 2.0, but the change means

that indexes on the inner tables cannot be used and performance

>may be slow compared to the same query's performance in V.1.5. “Inner

tables” are the tables used in the subquery argument inside an

>ALL or NOT IN expression.
>
>So my questions:
>
>This only applies when there is a JOIN in the SQL statement correct?
>In other words it does not apply to a stored procedure like the

following does it?

Hi Mike!

On www.firebirdsql.org/manual/nullguide-predicates.html, I find

"Bug alert

All Firebird versions before 2.0 contain a bug that causes [NOT] IN to
return the wrong result if an index is active on the subselect and one
of the following conditions is true:

A is NULL and the subselect doesn't return any NULLs, or
A is not NULL and the subselect result set doesn't contain A but does
contain NULL(s).
Please realise that an index may be active even if it has not been
created explicitly, namely if a key is defined on A.

Example: Table TA has a column A with values { 3, 8 }. Table TB has a
column B containing { 2, 8, 1, NULL }. The expressions:

A [not] in ( select B from TB )
should both return NULL for A = 3, because of the NULL in B. But if B is
indexed, IN returns false and NOT IN returns true. As a result, the query

select A from TA where A not in ( select B from TB )
returns a dataset with one record – containing the field with value 3 –
while it should have returned an empty set. Other errors may also occur,
e.g. if you use “NOT IN” in an IF, CASE or WHILE statement."

which I assume to be the error you're talking about. So, yes, it can
apply to cases like the procedure you describe.

One of the first things I learnt when starting with InterBase/Firebird,
was to never use IN (<subselect>) - it took far longer than I expected.
This was either with InterBase 5.6 or Firebird 0.9.4, but I've never had
a need for IN (<subselect>) since I learnt about EXISTS. So, I'd
recommend you to change your SP to:

SET TERM ^^ ;
CREATE PROCEDURE SPS_ATTORNEY_STATE (
V_ATTORNEY_ID Integer)
returns (
R_ASSOCIATED SmallInt,
R_ATTORNEY_ID Integer,
R_STATE_CODE Char(2),
R_STATE_NAME VarChar(35),
R_CREATE_DATE TimeStamp,
R_CREATE_USER SmallInt,
R_MODIFY_DATE TimeStamp,
R_MODIFY_USER SmallInt,
R_COURT_NO VarChar(10))
AS
/*
Author : Michael Tuttle
Date : 10/02/2006
Purpose :
*/
begin
FOR SELECT CAST(1 AS SMALLINT) AS ASSOCIATED,
A.ATTORNEY_ID,
A.STATE_CODE,
S.NAME AS STATE_NAME,
A.CREATE_DATE,
A.CREATE_USER,
A.MODIFY_DATE,
A.MODIFY_USER,
A.COURT_NO
FROM ATTORNEY_STATE A
JOIN STATE S ON S.STATE_CODE = A.STATE_CODE
WHERE A.ATTORNEY_ID = :V_ATTORNEY_ID
UNION
SELECT CAST(0 AS SMALLINT) AS ASSOCIATED,
CAST(:V_ATTORNEY_ID AS INTEGER) AS ATTORNEY_ID,
S.STATE_CODE,
S.NAME AS STATE_NAME,
CAST(NULL AS TIMESTAMP) AS CREATE_DATE,
CAST(NULL AS SMALLINT) AS CREATE_USER,
CAST(NULL AS TIMESTAMP) AS MODIFY_DATE,
CAST(NULL AS SMALLINT) AS MODIFY_USER,
CAST(NULL AS VARCHAR(10)) AS COURT_NO
FROM STATE S
WHERE NOT EXISTS (SELECT *
FROM ATTORNEY_STATE A2
WHERE A2.ATTORNEY_ID = :V_ATTORNEY_ID
AND A2.STATE_CODE = S.STATE_CODE)
INTO :R_ASSOCIATED, :R_ATTORNEY_ID, :R_STATE_CODE, :R_STATE_NAME,
:R_CREATE_DATE, :R_CREATE_USER, :R_MODIFY_DATE, :R_MODIFY_USER,
:R_COURT_NO DO
BEGIN
SUSPEND;
END
end
^^
SET TERM ; ^^

regardless of whether you have performance problems or not. Firebird
have greatly improved since version 0.9.4 and NOT IN may or may not be
slow with 2.5, but it is so simple to replace IN with EXISTS, and
IN(<subselect>) is never quicker than EXISTS (it can be equally quick),
so I see no reason for ever using IN (<subselect>).

Unfortunately, I have no answer to whether or not this bug have given
you incorrect results on older Firebird versions or whether or not you
ought to modify your old code.

By the way, in your particular case, I think I'd consider changing the
procedure a bit more, but that eliminates the subselect and hence, your
original question:

SET TERM ^^ ;
CREATE PROCEDURE SPS_ATTORNEY_STATE (
V_ATTORNEY_ID Integer)
returns (
R_ASSOCIATED SmallInt,
R_ATTORNEY_ID Integer,
R_STATE_CODE Char(2),
R_STATE_NAME VarChar(35),
R_CREATE_DATE TimeStamp,
R_CREATE_USER SmallInt,
R_MODIFY_DATE TimeStamp,
R_MODIFY_USER SmallInt,
R_COURT_NO VarChar(10))
AS
/*
Author : Michael Tuttle
Date : 10/02/2006
Purpose :
*/
begin
FOR SELECT CAST(IIF(A.STATE_CODE IS NULL, 0, 1) AS SMALLINT),
:V_ATTORNEY_ID,
S.STATE_CODE,
S.NAME,
A.CREATE_DATE,
A.CREATE_USER,
A.MODIFY_DATE,
A.MODIFY_USER,
A.COURT_NO
FROM STATE S
LEFT JOIN ATTORNEY_STATE A
ON S.STATE_CODE = A.STATE_CODE
AND A.ATTORNEY_ID = :V_ATTORNEY_ID
INTO :R_ASSOCIATED, :R_ATTORNEY_ID, :R_STATE_CODE, :R_STATE_NAME,
:R_CREATE_DATE, :R_CREATE_USER, :R_MODIFY_DATE, :R_MODIFY_USER,
:R_COURT_NO DO
BEGIN
SUSPEND;
END
end
^^
SET TERM ; ^^

HTH,
Set