BUG #3778: Natural join with filter problem

The following bug has been logged online:

Bug reference: 3778
Logged by: Laurent HERVE
Email address: laurentjpherve(at)orange(dot)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 vrification :
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 cls trangres :
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
Dclencheurs :
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 cls trangres :
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
Dclencheurs :
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)