--- In firebird-support@yahoogroups.com, "João Luiz de Souza Torres"
wrote:

> Dear Sirs,
>
> We are a problem in our system using "Firebird 1.5.3.4870".
> We are thinking about a "SQL Bug" in the Firebird.
> We ask for your help for testing. The SQL and the metadata are
> below.
>
> *Firebird tested versions:*
> 1.5.1.4481 => *OK*
> 1.5.2.4731 => *ERROR*
> 1.5.3.4870 => *ERROR*
>
> *SQL:*
> *SQL-BEGIN*
> Select TAB.codtabelaicms, TAB.Descricao
> from TabelaICMS TAB
> where TAB.codtabelaicms not in
> (select TABELAICMS.codtabelaicms from PRODUTOALIQICMS prodaliq
> left outer join TabelaICMS TABELAICMS on
> (TABELAICMS.codtabelaicms = prodaliq.CODTABELAICMS)
> left outer join ALIQUOTAICMS aliq on
> (aliq.codaliquotaicms = prodaliq.codaliquotaicms)
> where prodaliq.codproduto = '0000000230')
> *SQL-END*

Hi!

Logically, it doesn't make sense to use outer joins in a subselect,
and you should never use the table name as an alias to a table (at
least not when referenced twice). If I recall correctly (I'm not
certain), there is/was an error with NOT IN, but that was with
"multilevel" subselects which are more complex than your case. In your
case, I would say the reason Firebird complains is one of the two
"errors" in your query (maybe Firebird is tolerant enough to handle
outer joins in subselects, I don't know, but ambiguous table names is
a no-no). Moreover, most of your query may be eliminated, so the query
you seem to want is as simple as

Select TAB.codtabelaicms, TAB.Descricao
from TabelaICMS TAB
where TAB.codtabelaicms not in
(select prodaliq.CODTABELAICMS from PRODUTOALIQICMS prodaliq
where prodaliq.codproduto = '0000000230')

but I would recommend you to change this to

Select TAB.codtabelaicms, TAB.Descricao
from TabelaICMS TAB
where not exists(
select * from PRODUTOALIQICMS prodaliq
where TAB.codtabelaicms = prodaliq.codtabelaicms and
prodaliq.codproduto = '0000000230')

(though some disagree with me in that using NOT EXISTS in general is a
better approach than using NOT IN <subselect>).

HTH,
Set