BUG #3778: Natural join with filter problem

Поиск
Список
Период
Сортировка
От Laurent HERVE
Тема BUG #3778: Natural join with filter problem
Дата
Msg-id 200711261052.lAQAqska087210@wwwmaster.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #3778: Natural join with filter problem  (Heikki Linnakangas <heikki@enterprisedb.com>)
Список pgsql-bugs
Дерево обсуждения 
The following bug has been logged online:

Bug reference:      3778
Logged by:          Laurent HERVE
Email address:      laurentjpherve@orange.fr
PostgreSQL version: 8.2.5
Operating system:   i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
(Ubuntu 4.1.2-0ubuntu4)
Description:        Natural join with filter problem
Details:

Dear all,
The join is correctly done when i do not use the numero_releve='2006-10'
filter. But when i use it, the join is not working. The explain plan gives
me a information about why :
"Index Scan using document_operation_fk_idx"
but for me it seems like a bug ...

Following all details :
\d lignes_operation

                                                     Table «
public.lignes_operation »
               Colonne                |         Type          |
                 Modificateurs
--------------------------------------+-----------------------+-------------
---------------------------------------------------------------
 code_banque                          | character varying(9)  | not null
 numero_guichet                       | character varying(22) | not null
 numero_compte                        | character varying(22) | not null
 numero_sequence                      | integer               | not null
default nextval('lignes_operation_numero_sequence_seq'::regclass)
 code_utilisateur                     | character varying(32) | not null
 code_devise                          | character(3)          | not null
 devise_base                          | character(3)          | not null
 date_valeur                          | date                  | not null
 code_document                        | integer               |
 lig_code_banque                      | character varying(9)  |
 lig_numero_guichet                   | character varying(22) |
 lig_numero_compte                    | character varying(22) |
 lig_numero_sequence                  | integer               |
 numero_carte                         | character(16)         |
 che_code_banque                      | character varying(9)  |
 che_numero_guichet                   | character varying(22) |
 che_numero_compte                    | character varying(22) |
 numero_chequier                      | integer               |
 numero_cheque                        | character varying(12) |
 moy_code_banque                      | character varying(9)  | not null
 code_moyen                           | character(6)          | not null
 debit_credit                         | character(1)          | not null
 code_partenaire                      | character varying(32) | not null
 code_credit                          | character varying(10) |
 numero_echeance_credit               | integer               |
 typ_code_banque                      | character varying(9)  | not null
 code_type_operation                  | character(6)          | not null
 rel_code_banque                      | character varying(9)  |
 rel_numero_guichet                   | character varying(22) |
 rel_numero_compte                    | character varying(22) |
 numero_releve                        | character varying(10) |
 code_ecart                           | integer               |
 code_statut_operation                | character(3)          | not null
default 'A'::bpchar
 date_operation                       | date                  | not null
default ('now'::text)::date
 detail_operation                     | ezm_ldesc             |
 sens_operation                       | character(1)          | not null
default 'D'::bpchar
 montant_operation                    | numeric(10,2)         | not null
default 0
 taxes_et_frais                       | numeric(10,2)         | not null
default 0
 montant_total                        | numeric(10,2)         | not null
default 0
 date_derniere_modification_operation | date                  | not null
default ('now'::text)::date
 montant_base                         | numeric(10,2)         | not null
default 0
 taux_de_change                       | double precision      | not null
default 1
 date_posted                          | date                  |
 transaction_id                       | ezm_trid              |
 reference_number                     | character varying(32) |
 sic                                  | numeric(6,0)          |
 hors_suivi_budget                    | boolean               | not null
default false
 generer_cat_partenaire               | boolean               | not null
default false
 credit_montant_echeance              | boolean               | not null
default false
 credit_montant_interets              | boolean               | not null
default false
 credit_montant_assurance             | boolean               | not null
default false
 credit_montant_autres                | boolean               | not null
default false
 credit_financement                   | boolean               | not null
default false
Index :
    « pk_lignes_operation » PRIMARY KEY, btree (code_banque,
numero_guichet, numero_compte, numero_sequence), tablespace « ezm_indexes
»
    « carte_operation_fk_idx » btree (numero_carte), tablespace «
ezm_indexes »
    « cheque_operation_fk_idx » btree (che_code_banque,
che_numero_guichet, che_numero_compte, numero_chequier, numero_cheque),
tablespace « ezm_indexes »
    « compte_ligne_operation_fk_idx » btree (code_banque, numero_guichet,
numero_compte), tablespace « ezm_indexes »
    « devise_base_operation_fk_idx » btree (code_devise), tablespace «
ezm_indexes »
    « devise_operation_fk_idx » btree (devise_base), tablespace «
ezm_indexes »
    « document_operation_fk_idx » btree (code_document), tablespace «
ezm_indexes »
    « lien_operation_echeance_credit_fk_idx » btree (code_credit,
numero_echeance_credit), tablespace « ezm_indexes »
    « ligne_operation_ecart2_fk_idx » btree (code_ecart), tablespace «
ezm_indexes »
    « moyen_operation_fk_idx » btree (moy_code_banque, code_moyen,
debit_credit), tablespace « ezm_indexes »
    « partenaire_operation_fk_idx » btree (code_partenaire), tablespace «
ezm_indexes »
    « releve_operation_fk_idx » btree (rel_code_banque,
rel_numero_guichet, rel_numero_compte, numero_releve), tablespace «
ezm_indexes »
    « statut_ligne_operation_fk_idx » btree (code_statut_operation),
tablespace « ezm_indexes »
    « type_ligne_operation_fk_idx » btree (typ_code_banque,
code_type_operation), tablespace « ezm_indexes »
    « utilisateur_operation_fk_idx » btree (code_utilisateur), tablespace
« ezm_indexes »
    « virement_operation_fk_idx » btree (lig_code_banque,
lig_numero_guichet, lig_numero_compte, lig_numero_sequence), tablespace «
ezm_indexes »
Contraintes de vérification :
    « ckc_code_statut_operation_lignes_operation » CHECK
(code_statut_operation = ANY (ARRAY['A'::bpchar, 'R'::bpchar, 'E'::bpchar,
'D'::bpchar, 'C'::bpchar, 'P'::bpchar, 'DEL'::bpchar]))
    « ckc_debit_credit_lignes_operation » CHECK (debit_credit = ANY
(ARRAY['C'::bpchar, 'D'::bpchar]))
    « ckc_sens_operation_lignes_operation » CHECK (sens_operation = ANY
(ARRAY['D'::bpchar, 'C'::bpchar]))
    « ckc_taux_de_change_lignes_operation » CHECK (taux_de_change >=
0::double precision)
    « ckt_lignes_operation » CHECK (montant_total = (montant_operation +
taxes_et_frais) AND montant_base = round((montant_total::double precision /
taux_de_change)::numeric, 2))
Contraintes de clés étrangères :
    « fk_lignes_operatio_carte_operation_cartes_bancaire » FOREIGN KEY
(numero_carte) REFERENCES cartes_bancaires(numero_carte) ON UPDATE RESTRICT
ON DELETE RESTRICT
    « fk_lignes_operatio_cheque_operation_cheques » FOREIGN KEY
(che_code_banque, che_numero_guichet, che_numero_compte, numero_chequier,
numero_cheque) REFERENCES cheques(code_banque, numero_guichet,
numero_compte, numero_chequier, numero_cheque) ON UPDATE RESTRICT ON DELETE
RESTRICT
    « fk_lignes_operatio_compte_ligne_operation_comptes » FOREIGN KEY
(code_banque, numero_guichet, numero_compte) REFERENCES comptes(code_banque,
numero_guichet, numero_compte) ON UPDATE RESTRICT ON DELETE RESTRICT
    « fk_lignes_operatio_devise_base_operation_devises » FOREIGN KEY
(code_devise) REFERENCES devises(code_devise) ON UPDATE RESTRICT ON DELETE
RESTRICT
    « fk_lignes_operatio_devise_operation_devises » FOREIGN KEY
(devise_base) REFERENCES devises(code_devise) ON UPDATE RESTRICT ON DELETE
RESTRICT
    « fk_lignes_operatio_document_operation_documents_lies » FOREIGN KEY
(code_document) REFERENCES documents_lies(code_document) ON UPDATE RESTRICT
ON DELETE RESTRICT
    « fk_lignes_operatio_lien_operation_echeance_cred_echeances_credi »
FOREIGN KEY (code_credit, numero_echeance_credit) REFERENCES
echeances_credit(code_credit, numero_echeance_credit) ON UPDATE RESTRICT ON
DELETE RESTRICT
    « fk_lignes_operatio_ligne_operation_ecart2_ecarts_rapproch » FOREIGN
KEY (code_ecart) REFERENCES ecarts_rapprochement(code_ecart) ON UPDATE
RESTRICT ON DELETE RESTRICT
    « fk_lignes_operatio_moyen_operation_moyens_de_paiem » FOREIGN KEY
(moy_code_banque, code_moyen, debit_credit) REFERENCES
moyens_de_paiement(code_banque, code_moyen, debit_credit) ON UPDATE RESTRICT
ON DELETE RESTRICT
    « fk_lignes_operatio_partenaire_operation_partenaires_ope » FOREIGN
KEY (code_partenaire) REFERENCES partenaires_operation(code_partenaire) ON
UPDATE RESTRICT ON DELETE RESTRICT
    « fk_lignes_operatio_releve_operation_releves_de_comp » FOREIGN KEY
(rel_code_banque, rel_numero_guichet, rel_numero_compte, numero_releve)
REFERENCES releves_de_compte(code_banque, numero_guichet, numero_compte,
numero_releve) ON UPDATE RESTRICT ON DELETE RESTRICT
    « fk_lignes_operatio_statut_ligne_operation_statuts_operati » FOREIGN
KEY (code_statut_operation) REFERENCES
statuts_operation(code_statut_operation) ON UPDATE RESTRICT ON DELETE
RESTRICT
    « fk_lignes_operatio_type_ligne_operation_types_operation » FOREIGN
KEY (typ_code_banque, code_type_operation) REFERENCES
types_operation(code_banque, code_type_operation) ON UPDATE RESTRICT ON
DELETE RESTRICT
    « fk_lignes_operatio_utilisateur_operation_utilisateurs » FOREIGN KEY
(code_utilisateur) REFERENCES utilisateurs(code_utilisateur) ON UPDATE
RESTRICT ON DELETE RESTRICT
    « fk_lignes_operatio_virement_operation_lignes_operatio » FOREIGN KEY
(lig_code_banque, lig_numero_guichet, lig_numero_compte,
lig_numero_sequence) REFERENCES lignes_operation(code_banque,
numero_guichet, numero_compte, numero_sequence) ON UPDATE RESTRICT ON DELETE
RESTRICT
Déclencheurs :
    tda_lignes_operation AFTER DELETE ON lignes_operation FOR EACH ROW
EXECUTE PROCEDURE tda_lignes_operation_proc()
    tia_lignes_operation AFTER INSERT ON lignes_operation FOR EACH ROW
EXECUTE PROCEDURE tia_lignes_operation_proc()
    tib_lignes_operation BEFORE INSERT ON lignes_operation FOR EACH ROW
EXECUTE PROCEDURE tib_lignes_operation_proc()
    tua_lignes_operation AFTER UPDATE ON lignes_operation FOR EACH ROW
EXECUTE PROCEDURE tua_lignes_operation_proc()
    tub_lignes_operation BEFORE UPDATE ON lignes_operation FOR EACH ROW
EXECUTE PROCEDURE tub_lignes_operation_proc()
    z_notifies_lignes_operation AFTER INSERT OR DELETE OR UPDATE ON
lignes_operation FOR EACH ROW EXECUTE PROCEDURE send_table_notifies()
Tablespace « ezm_data »

\d releves_de_compte

           Table « public.releves_de_compte »
    Colonne     |         Type          | Modificateurs
----------------+-----------------------+---------------
 code_banque    | character varying(9)  | not null
 numero_guichet | character varying(22) | not null
 numero_compte  | character varying(22) | not null
 numero_releve  | character varying(10) | not null
 code_document  | integer               |
 date_arrete    | date                  | not null
Index :
    « pk_releves_de_compte » PRIMARY KEY, btree (code_banque,
numero_guichet, numero_compte, numero_releve), tablespace « ezm_indexes »
    « document_releve_compte_fk_idx » btree (code_document), tablespace «
ezm_indexes »
    « releves_compte_fk_idx » btree (code_banque, numero_guichet,
numero_compte), tablespace « ezm_indexes »
Contraintes de clés étrangères :
    « fk_releves_de_comp_document_releve_compte_documents_lies » FOREIGN
KEY (code_document) REFERENCES documents_lies(code_document) ON UPDATE
RESTRICT ON DELETE RESTRICT
    « fk_releves_de_comp_releves_compte_comptes » FOREIGN KEY
(code_banque, numero_guichet, numero_compte) REFERENCES comptes(code_banque,
numero_guichet, numero_compte) ON UPDATE RESTRICT ON DELETE RESTRICT
Déclencheurs :
    tib_releves_de_compte BEFORE INSERT ON releves_de_compte FOR EACH ROW
EXECUTE PROCEDURE tib_releves_de_compte_proc()
Tablespace « ezm_data »


explain select * from lignes_operation natural inner join releves_de_compte
where numero_releve='2006-10';

                                                      QUERY PLAN


----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---------------------------------------------
 Nested Loop  (cost=0.00..26.73 rows=1 width=551)
   Join Filter: (((lignes_operation.code_banque)::text =
(releves_de_compte.code_banque)::text) AND
((lignes_operation.numero_guichet)::text =
(releves_de_compte.numero_guichet)::text) AND
((lignes_operation.numero_compte)::text =
(releves_de_compte.numero_compte)::text))
   ->  Seq Scan on releves_de_compte  (cost=0.00..10.15 rows=2 width=48)
         Filter: ('2006-10'::text = (numero_releve)::text)
   ->  Index Scan using document_operation_fk_idx on lignes_operation
(cost=0.00..8.27 rows=1 width=547)
         Index Cond: (lignes_operation.code_document =
releves_de_compte.code_document)
         Filter: ((numero_releve)::text = '2006-10'::text)
(7 lignes)


explain select * from lignes_operation natural inner join releves_de_compte;


                                                      QUERY PLAN



----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------------------
 Hash Join  (cost=18.39..459.81 rows=1 width=551)
   Hash Cond: (((lignes_operation.code_banque)::text =
(releves_de_compte.code_banque)::text) AND
((lignes_operation.numero_guichet)::text =
(releves_de_compte.numero_guichet)::text) AND
((lignes_operation.numero_compte)::text =
(releves_de_compte.numero_compte)::text) AND (lignes_operation.code_document
= releves_de_compte.code_document) AND
((lignes_operation.numero_releve)::text =
(releves_de_compte.numero_releve)::text))
   ->  Seq Scan on lignes_operation  (cost=0.00..301.83 rows=5583
width=547)
   ->  Hash  (cost=9.12..9.12 rows=412 width=48)
         ->  Seq Scan on releves_de_compte  (cost=0.00..9.12 rows=412
width=48)
(5 lignes)

                                            version

----------------------------------------------------------------------------
-------------------
 PostgreSQL 8.2.5 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
(Ubuntu 4.1.2-0ubuntu4)
(1 ligne)

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Steve Langasek
Дата:
Сообщение: Re: Test suite fails on alpha architecture
Следующее
От: Heikki Linnakangas
Дата:
Сообщение: Re: BUG #3778: Natural join with filter problem