---------- Forwarded message ----------
From: João Luiz de Souza Torres <joaoluiz.unifar@...>
Date: 01/04/2006 09:14
Subject: SQL error in the NOT IN operator => bug ?
To: firebird-support@yahoogroups.com, ib_sql@...

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*

*Metadata:
*
/* Table: PRODUTOALIQICMS, Owner: SYSDBA */

CREATE TABLE "PRODUTOALIQICMS"
(
"CODTABELAICMS" VARCHAR(5) CHARACTER SET WIN1252 NOT NULL COLLATE
PXW_INTL850,
"CODPRODUTO" VARCHAR(10) CHARACTER SET WIN1252 NOT NULL COLLATE
PXW_INTL850,
"CODALIQUOTAICMS" VARCHAR(6) CHARACTER SET WIN1252 NOT NULL COLLATE
PXW_INTL850,
"ST_EXPORT" VARCHAR(1) CHARACTER SET WIN1252 COLLATE PXW_INTL850,
"DTULTIMAATUALIZACAO" DATE,
"HRULTIMAATUALIZACAO" TIME,
PRIMARY KEY ("CODPRODUTO", "CODTABELAICMS", "CODALIQUOTAICMS")
);

/* Index definitions for PRODUTOALIQICMS */

CREATE INDEX "XIF114PRODUTOALIQICMS" ON "PRODUTOALIQICMS"("CODPRODUTO");
CREATE INDEX "XIF206PRODUTOALIQICMS" ON
"PRODUTOALIQICMS"("CODALIQUOTAICMS");
CREATE INDEX "XIF207PRODUTOALIQICMS" ON "PRODUTOALIQICMS"("CODTABELAICMS");
INSERT INTO "PRODUTOALIQICMS" ("CODTABELAICMS", "CODPRODUTO",
"CODALIQUOTAICMS", "ST_EXPORT", "DTULTIMAATUALIZACAO",
"HRULTIMAATUALIZACAO") VALUES ( '00102', '0000000230', '000001', NULL,
NULL, NULL);
INSERT INTO "PRODUTOALIQICMS" ("CODTABELAICMS", "CODPRODUTO",
"CODALIQUOTAICMS", "ST_EXPORT", "DTULTIMAATUALIZACAO",
"HRULTIMAATUALIZACAO") VALUES ( '00101', '0000000230', '000001', NULL,
NULL, NULL);

/* Table: TABELAICMS, Owner: SYSDBA */
CREATE TABLE "TABELAICMS"
(
"CODTABELAICMS" VARCHAR(5) CHARACTER SET WIN1252 NOT NULL COLLATE
PXW_INTL850,
"DESCRICAO" VARCHAR(30) CHARACTER SET WIN1252 COLLATE PXW_INTL850,
"ST_EXPORT" VARCHAR(1) CHARACTER SET WIN1252 COLLATE PXW_INTL850,
"DTULTIMAATUALIZACAO" DATE,
"HRULTIMAATUALIZACAO" TIME,
PRIMARY KEY ("CODTABELAICMS")
);
INSERT INTO "TABELAICMS" ("CODTABELAICMS", "DESCRICAO", "ST_EXPORT",
"DTULTIMAATUALIZACAO", "HRULTIMAATUALIZACAO") VALUES ( '00101', 'TABELA
DE ICMS PADRÃO', NULL, NULL, NULL);
INSERT INTO "TABELAICMS" ("CODTABELAICMS", "DESCRICAO", "ST_EXPORT",
"DTULTIMAATUALIZACAO", "HRULTIMAATUALIZACAO") VALUES ( '00102', 'SEGUNDA
TABELA DE ICMS', NULL, NULL, NULL);
INSERT INTO "TABELAICMS" ("CODTABELAICMS", "DESCRICAO", "ST_EXPORT",
"DTULTIMAATUALIZACAO", "HRULTIMAATUALIZACAO") VALUES ( '00103', 'MAIS UMA
TABELA ICMS', NULL, NULL, NULL);
INSERT INTO "TABELAICMS" ("CODTABELAICMS", "DESCRICAO", "ST_EXPORT",
"DTULTIMAATUALIZACAO", "HRULTIMAATUALIZACAO") VALUES ( '00104', 'OUTRA
TABELA DE ICMS', NULL, NULL, NULL);

/* Table: ALIQUOTAICMS, Owner: SYSDBA */

CREATE TABLE "ALIQUOTAICMS"
(
"CODALIQUOTAICMS" VARCHAR(6) CHARACTER SET WIN1252 NOT NULL COLLATE
PXW_INTL850,
"STTIPOALIQUOTA" VARCHAR(1) CHARACTER SET WIN1252 COLLATE PXW_INTL850,
"PERCICMSALIQ" NUMERIC(9, 2),
"ST_EXPORT" VARCHAR(1) CHARACTER SET WIN1252 COLLATE PXW_INTL850,
"DTULTIMAATUALIZACAO" DATE,
"HRULTIMAATUALIZACAO" TIME,
PRIMARY KEY ("CODALIQUOTAICMS")
);
INSERT INTO "ALIQUOTAICMS" ("CODALIQUOTAICMS", "STTIPOALIQUOTA",
"PERCICMSALIQ", "ST_EXPORT", "DTULTIMAATUALIZACAO",
"HRULTIMAATUALIZACAO") VALUES ( '000001', '1', '0', NULL, NULL, NULL);

INSERT INTO "ALIQUOTAICMS" ("CODALIQUOTAICMS", "STTIPOALIQUOTA",
"PERCICMSALIQ", "ST_EXPORT", "DTULTIMAATUALIZACAO",
"HRULTIMAATUALIZACAO") VALUES ( '000002', '2', '0', NULL, NULL, NULL);

INSERT INTO "ALIQUOTAICMS" ("CODALIQUOTAICMS", "STTIPOALIQUOTA",
"PERCICMSALIQ", "ST_EXPORT", "DTULTIMAATUALIZACAO",
"HRULTIMAATUALIZACAO") VALUES ( '000003', '3', '0', NULL, NULL, NULL);

INSERT INTO "ALIQUOTAICMS" ("CODALIQUOTAICMS", "STTIPOALIQUOTA",
"PERCICMSALIQ", "ST_EXPORT", "DTULTIMAATUALIZACAO",
"HRULTIMAATUALIZACAO") VALUES ( '000004', '4', '12', NULL, NULL,
NULL);
INSERT INTO "ALIQUOTAICMS" ("CODALIQUOTAICMS", "STTIPOALIQUOTA",
"PERCICMSALIQ", "ST_EXPORT", "DTULTIMAATUALIZACAO",
"HRULTIMAATUALIZACAO") VALUES ( '000005', '4', '17', NULL, NULL,
NULL);
INSERT INTO "ALIQUOTAICMS" ("CODALIQUOTAICMS", "STTIPOALIQUOTA",
"PERCICMSALIQ", "ST_EXPORT", "DTULTIMAATUALIZACAO",
"HRULTIMAATUALIZACAO") VALUES ( '000006', '4', '18', NULL, NULL,
NULL);
INSERT INTO "ALIQUOTAICMS" ("CODALIQUOTAICMS", "STTIPOALIQUOTA",
"PERCICMSALIQ", "ST_EXPORT", "DTULTIMAATUALIZACAO",
"HRULTIMAATUALIZACAO") VALUES ( '000007', '4', '19', NULL, NULL,
NULL);
Thanks,

--------------------
João Luiz de Souza Torres
Desenvolvimento de Soluções
Unifar Informática Ltda
www.unifar.com.br


[Non-text portions of this message have been removed]