Hi,
Have détected a bug : a query doesn't return the good result, dépending of
the
order of the where clauses
Version of MaxDB : 7.5.0.26, Linux
Haven't checked if the problem was known and if it occurs in another
version.
//--------------------------------------------------------------------------
----
//-- Script of the tables
//--------------------------------------------------------------------------
----
create sequence SEQU_IDTC
//
create table IMPORT_DONNEE_TEXTE_COMMUN(
IDTC_ID D_LONG not null, -- |PK|
IDTC_TXT D_LIB, -- Libellé indexé, unique
IDTC_DTCRE D_DATETIME_DEFAULT, -- date/heure de création
IDTC_DTMOD D_DATETIME, -- Date/Heure de modification
primary key(IDTC_ID),
constraint IND_UNIQ_IDTC_IDTC_TXT unique (IDTC_TXT)
)
//
create sequence SEQU_COSO
//
create table CONTRAT_SOCIETE(
COSO_ID D_LONG, -- |PK|, générée par la séquence
SEQU_COSO
COGR_ID D_LONG not null, -- FK Contrat Groupe
COSO_CODESOC D_LIB not null, -- Code société pour rattachement
import
COSO_LIB D_LIB not null, -- Nom société
COSO_IDENT_ET D_LIB, -- Identifiant unique (pour France =
SIRET)
COSO_IDENT_ET_PR D_LIB, -- Identifiant groupe (pour France =
SIREN)
PAY_CODE D_PAY, -- FK Pays ATTENTION : PEUT ÊTRE
NULL (c'est pas D_R_PAY)
COSO_NIV D_LONG not null, -- Arbre : Niveau
COSO_BG D_LONG not null, -- Arbre : Bord gauche
COSO_BD D_LONG not null, -- Arbre : Bord droit
COSO_DTCRE D_DATETIME_DEFAULT, -- Date/Heure de création
COSO_DTMOD D_DATETIME, -- Date/Heure de modification
primary key(COSO_ID)
)
//
create index IND_COSO_FK_COGR_ID on CONTRAT_SOCIETE (COGR_ID)
//
create index IND_COSO_FK_PAY_CODE on CONTRAT_SOCIETE (PAY_CODE)
//
create index IND_COSO_COSO_NIV on CONTRAT_SOCIETE (COSO_NIV)
//
create index IND_COSO_COSO_BG on CONTRAT_SOCIETE (COSO_BG)
//
create index IND_COSO_COSO_BD on CONTRAT_SOCIETE (COSO_BD)
//--------------------------------------------------------------------------
----
//-- The query that return FALSE result
//-- le resultset containt ligns vit IMTR_ID <> 399
//--------------------------------------------------------------------------
----
SELECT *
FROM SA.IMPORT_DONNEE IMDO
WHERE (IMTR_ID = 399)
AND (IMDO_INTERCO IS NULL)
AND (
(IDTC_ID_XIDENT_ET, PAY_CODE) IN ( SELECT
IDTC.IDTC_ID,
COSO.PAY_COD
E
FROM
SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC
INNER JOIN ( SELECT
DISTINCT COSO_IDENT_ET,
PAY_CODE
FROM
SA.CONTRAT_SOCIETE
WHERE
COGR_ID = 2
AND
COSO_IDENT_ET IS NOT NULL
) COSO
ON COSO.COSO_IDENT_ET =
IDTC.IDTC_TXT)
) OR (
(IDTC_ID_XIDENT_ET_PR, PAY_CODE) IN ( SELECT
IDTC.IDTC_ID,
COSO.PAY_COD
E
FROM
SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC
INNER JOIN ( SELECT
DISTINCT COSO_IDENT_ET_PR,
PAY_CODE
FROM
SA.CONTRAT_SOCIETE
WHERE
COGR_ID = 2
AND
COSO_IDENT_ET_PR IS NOT NULL
) COSO
ON COSO.COSO_IDENT_ET_PR
= IDTC.IDTC_TXT)
)
//--------------------------------------------------------------------------
----
//-- When changing order or the where clause, when pushing "IMTR_ID = 399"
//-- after le IN clauses, it is ok,
//-- When deleting one of then IN clause, it is ok.
//--------------------------------------------------------------------------
----
SELECT *
FROM SA.IMPORT_DONNEE IMDO
WHERE (IMTR_ID = 399)
AND (IMDO_INTERCO IS NULL)
AND (
(IDTC_ID_XIDENT_ET, PAY_CODE) IN ( SELECT
IDTC.IDTC_ID,
COSO.PAY_COD
E
FROM
SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC
INNER JOIN ( SELECT
DISTINCT COSO_IDENT_ET,
PAY_CODE
FROM
SA.CONTRAT_SOCIETE
WHERE
COGR_ID = 2
AND
COSO_IDENT_ET IS NOT NULL
) COSO
ON COSO.COSO_IDENT_ET =
IDTC.IDTC_TXT)
) OR (
(IDTC_ID_XIDENT_ET_PR, PAY_CODE) IN ( SELECT
IDTC.IDTC_ID,
COSO.PAY_COD
E
FROM
SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC
INNER JOIN ( SELECT
DISTINCT COSO_IDENT_ET_PR,
PAY_CODE
FROM
SA.CONTRAT_SOCIETE
WHERE
COGR_ID = 2
AND
COSO_IDENT_ET_PR IS NOT NULL
) COSO
ON COSO.COSO_IDENT_ET_PR
= IDTC.IDTC_TXT)
)
Greets
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
