>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