Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #3778: Natural join with filter problem

BUG #3778: Natural join with filter problem

From
"Laurent HERVE"
Date:
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)

Re: BUG #3778: Natural join with filter problem

From
Heikki Linnakangas
Date:
Laurent HERVE wrote:
> 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 ...

I don't see anything wrong here. How exactly is the join not working?
Are you getting unexpected results? What are you getting and what did
you expect to happen?

> 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)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: BUG #3778: Natural join with filter problem

From
Heikki Linnakangas
Date:
Please keep the list CC'd.

Laurent HERVE wrote:
> in fact, i don't know why the column "code_document" is used because it
> is not in the primary key of the tables.
> So as this column is nullable, it gives unexpected results.
>
> I thought only the columns on the primary key are taken into account
> when building a join. Maybe i misunderstood something in how postgresql
> builds the join.

Ah, no. According to the docs:

     NATURAL is shorthand for a USING list that mentions all columns in
the two tables that have the same names.

Because there's a column called code_document in both tables, you'll
have to use an INNER JOIN to get what you want. If you want to eliminate
the duplicate columns from the result set like a NATURAL JOIN does, you
can use INNER JOIN ... USING (list of columns).

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: BUG #3778: Natural join with filter problem

From
Laurent HERVE
Date:
ok thank you... and sorry for not having checked enough time in the
documentation.
anyway, i think a kind of JOIN ... USING (primary key columns) could be
useful.
if you change a primary key, because of design choice, you won't have to
rewrite all rules and views for example, just simple execute would be
required. This is why i used NATURAL.
ok, i will use the INNER JOIN ... USING as suggested.
regards
laurent Herve

Le lundi 26 novembre 2007 à 12:55 +0000, Heikki Linnakangas a écrit :
> Please keep the list CC'd.
>
> Laurent HERVE wrote:
> > in fact, i don't know why the column "code_document" is used because it
> > is not in the primary key of the tables.
> > So as this column is nullable, it gives unexpected results.
> >
> > I thought only the columns on the primary key are taken into account
> > when building a join. Maybe i misunderstood something in how postgresql
> > builds the join.
>
> Ah, no. According to the docs:
>
>      NATURAL is shorthand for a USING list that mentions all columns in
> the two tables that have the same names.
>
> Because there's a column called code_document in both tables, you'll
> have to use an INNER JOIN to get what you want. If you want to eliminate
> the duplicate columns from the result set like a NATURAL JOIN does, you
> can use INNER JOIN ... USING (list of columns).
>

Re: BUG #3778: Natural join with filter problem

From
Heikki Linnakangas
Date:
Laurent HERVE wrote:
> ok thank you... and sorry for not having checked enough time in the
> documentation.
> anyway, i think a kind of JOIN ... USING (primary key columns) could be
> useful.
> if you change a primary key, because of design choice, you won't have to
> rewrite all rules and views for example, just simple execute would be
> required. This is why i used NATURAL.

Yeah, I see what you mean. NATURAL JOIN is part of the SQ standard, so
we're just following that. Matching by foreign keys would make more
sense to me as well.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com