Hello all,
I've a strange result with sub queries.
Here the tables i use:
CREATE TABLE "TORPEDO"."EMPLACEMENT"
(
"EMP_C_CODE" Char (5) ASCII NOT NULL,
"EMP_C_LIBELLE" Varchar (25) ASCII,
"EMP_L_ACTIF" Boolean DEFAULT FALSE,
"EMP_L_MODIF" Boolean DEFAULT FALSE,
PRIMARY KEY ("EMP_C_CODE")
)
CREATE TABLE "TORPEDO"."EMP_ART"
(
"ART_C_REF" Char (12) ASCII NOT NULL,
"STO_C_CODE" Char (5) ASCII,
"SKU_C_CODE" Char (17) ASCII,
"SKU_C_CAB" Char (13) ASCII,
"EMP_C_CODE" Char (5) ASCII,
"EA_I_TYPE" Integer DEFAULT 0,
"ETP_C_CODE" Char (5) ASCII,
CONSTRAINT "TYPE D'ECLATEMENT" CHECK "EA_I_TYPE" IN (0,1,2),
FOREIGN KEY "REFERENCE" ("ART_C_REF") REFERENCES
"TORPEDO"."ARTICLE" ("ART_C_REF") ON DELETE CASCADE,
FOREIGN KEY "CODE STOCK" ("STO_C_CODE") REFERENCES
"TORPEDO"."STOCK" ("STO_C_CODE") ON DELETE CASCADE,
FOREIGN KEY "CODE EMPLACEMENT" ("EMP_C_CODE") REFERENCES
"TORPEDO"."EMPLACEMENT" ("EMP_C_CODE") ON DELETE CASCADE,
FOREIGN KEY "CODE ETAPE" ("ETP_C_CODE") REFERENCES
"TORPEDO"."ETAPE" ("ETP_C_CODE") ON DELETE RESTRICT
)
And the indexes for both table:
CREATE INDEX "EMP_ACTIF" ON "TORPEDO"."EMPLACEMENT"("EMP_L_ACTIF" ASC)
CREATE UNIQUE INDEX "PRIMAIRE" ON "TORPEDO"."EMP_ART"("ART_C_REF" ASC,
"STO_C_CODE" ASC, "SKU_C_CODE" ASC)
CREATE INDEX "SECONDAIRE" ON "TORPEDO"."EMP_ART"("EMP_C_CODE" ASC,
"STO_C_CODE" ASC)
If i run this query, i get many results:
select emp_c_code,emp_c_libelle from emplacement where emp_l_actif and
not exists (select emp_c_code from emp_art where sto_c_code = '1CEN' and
emplacement.emp_c_code = emp_art.emp_c_code)
But if i run this other one, which should return the same result, i
didn't get anything:
select emp_c_code,emp_c_libelle from emplacement where emp_l_actif and
emp_c_code not in (select emp_c_code from emp_art where sto_c_code =
'1CEN')
None of the indexes are marked as bad, i've just updated statictics on
both table.
Any idea ?
TIA.
Frédéric
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
