Hello all, I have a perplexing problem which I cannot figure out. I have a somewhat complex query that is returning two identical rows, where only one row exists in the table. If I run a simpler query, I receive the one row as desired. This is on a windows vista development machine with Postgres 8.3.7 as localhost. Simple query: select * from f_dossier where dono = 'NT003-011' Query results: "NT003";"NT003-011";"Accord de confidentialité";"";"S.E.C. Repro inc.";10;? Explain analyze: "Index Scan using dono_idx on f_dossier (cost=0.00..8.27 rows=1 width=622) (actual time=0.049..0.051 rows=1 loops=1)" " Index Cond: ((dono)::text = 'NT003-011'::text)" "Total runtime: 0.189 ms" Somewhat complex query: SELECT do_sequence::text AS Id, DOCLNO ,CASE WHEN do_nom2 = '' OR do_nom2 IS NULL THEN cl_nom2 ELSE do_nom2 END AS DO_NOM2 ,DONO,DOCODE,DO_SEQUENCE,DO_PADVE,DO_PADVE_MAJ,DO_OUVERTURE,DO_FERMETURE_DOS ,DO_NO_FERMETURE,DO_NOM_ASSURE,DO_ADRESSE_EVENEMENT,DO_CATEG,DO_QUOTE_CLIENT ,DO_AVOC1 ,DO_CODE_INFO,DO_TYPE_RECLAMATION,DO_ETUDE_REFER,DO_NOTE,DO_SITE,DO_TYPE_FAC T,DO_NOCOU ,DO_DATE_EVENEMENT,DO_MONT_RECOURS,DO_DERNI_ACT,DO_REF_CLIENT,DO_FERMETURE_P ROV ,DO_USER_CHAR1, coalesce(Code1,'')::text AS Code1, coalesce(Code2,'')::text AS Code2 , coalesce(Code3,'')::text AS Code3, coalesce(Code4,'')::text AS Code4 , Associe, Nom, translate(do_padve,E'\x0d',E'\x20') AS Objet--, Site , ''::text AS MandatExt , '##' || coalesce(Nom,'') ||'##' || coalesce(DONO,'') || '##' || CASE WHEN do_nom2 = '' OR do_nom2 IS NULL THEN cl_nom2 ELSE do_nom2 END || '####' || coalesce(Code1,'') || '##' || coalesce(Code2,'') || '##' || coalesce(Code3,'') || '##' || coalesce(Code4,'') || '##' || coalesce(DO_NOM_ASSURE,'') || '##' || coalesce(DO_ADRESSE_EVENEMENT,'') || '##' || coalesce(DO_NOTE,'') || '##' || coalesce(Site,'') || '##' || coalesce(DO_PADVE,'') || '##' || coalesce(DO_NOCOU,'') || '##' || coalesce(DO_REF_CLIENT,'') AS Recherche FROM f_dossier LEFT JOIN (SELECT co_code, co_description as code1 FROM f_code_cour) AS codecour ON do_categ=co_code LEFT JOIN (SELECT qc_description as code2 , qc_code FROM f_quote_client) as quoteclient ON do_quote_client=qc_code LEFT JOIN (SELECT av_code, av_nom || ', ' || av_prenom AS Associe FROM f_avocat) AS avocat ON do_avoc1=av_code LEFT JOIN (SELECT cl_no, cl_nom2, cl_ville AS Ville, cl_code_postal AS CodePostal, cl_naissance , CASE WHEN cl_sexe=1 THEN 'M' WHEN cl_sexe=2 THEN 'F' ELSE 'N/A' END AS sexe , cl_code3, cl_code4, cl_code6, cl_internet AS Courriel1, cl_internet2 AS Courriel2 ,CASE WHEN cl_prenom='' OR cl_prenom IS NULL THEN cl_nom ELSE cl_nom || ', ' || cl_prenom END AS Nom FROM f_client) AS client1 ON doclno=cl_no --LEFT JOIN (SELECT si_nom AS site, si_id FROM f_site) AS site ON do_site=si_id LEFT JOIN (SELECT qc_code as qccode3, qc_description as code3 from f_quote_client) as tempcode3 on do_code_info = qccode3 LEFT JOIN (SELECT qc_code as qccode4, qc_description as code4 from f_quote_client) as tempcode4 on do_type_reclamation = qccode4 where dono = 'NT003-011' order by dono; Query results: "10";"NT003";"Louis Bonneville";"NT003-011";? "10";"NT003";"Louis Bonneville";"NT003-011";? Explain analyze: "Nested Loop Left Join (cost=0.00..256.51 rows=1 width=307) (actual time=0.470..13.167 rows=2 loops=1)" " Join Filter: ((f_dossier.do_quote_client)::text = (public.f_quote_client.qc_code)::text)" " -> Nested Loop Left Join (cost=0.00..255.38 rows=1 width=305) (actual time=0.386..13.059 rows=1 loops=1)" " Join Filter: ((f_dossier.do_categ)::text = (f_code_cour.co_code)::text)" " -> Nested Loop Left Join (cost=0.00..254.13 rows=1 width=296) (actual time=0.333..13.002 rows=1 loops=1)" " Join Filter: ((f_dossier.do_avoc1)::text = (f_avocat.av_code)::text)" " -> Nested Loop Left Join (cost=0.00..250.05 rows=1 width=281) (actual time=0.306..12.796 rows=1 loops=1)" " Join Filter: ((f_dossier.doclno)::text = (f_client.cl_no)::text)" " -> Nested Loop Left Join (cost=0.00..22.61 rows=1 width=206) (actual time=0.149..0.164 rows=1 loops=1)" " Join Filter: ((f_dossier.do_code_info)::text = (public.f_quote_client.qc_code)::text)" " -> Nested Loop Left Join (cost=0.00..21.56 rows=1 width=204) (actual time=0.124..0.135 rows=1 loops=1)" " Join Filter: ((f_dossier.do_type_reclamation)::text = (public.f_quote_client.qc_code)::text)" " -> Nested Loop Left Join (cost=0.00..20.52 rows=1 width=202) (actual time=0.092..0.100 rows=1 loops=1)" " Join Filter: ((f_dossier.do_site)::text = (f_site.si_id)::text)" " -> Index Scan using dono_idx on f_dossier (cost=0.00..8.27 rows=1 width=134) (actual time=0.063..0.069 rows=1 loops=1)" " Index Cond: ((dono)::text = 'NT003-011'::text)" " -> Seq Scan on f_site (cost=0.00..11.00 rows=100 width=74) (actual time=0.003..0.003 rows=0 loops=1)" " -> Seq Scan on f_quote_client (cost=0.00..1.02 rows=2 width=4) (actual time=0.006..0.010 rows=2 loops=1)" " -> Seq Scan on f_quote_client (cost=0.00..1.02 rows=2 width=4) (actual time=0.003..0.007 rows=2 loops=1)" " -> Seq Scan on f_client (cost=0.00..175.22 rows=2321 width=61) (actual time=0.027..9.045 rows=2321 loops=1)" " -> Seq Scan on f_avocat (cost=0.00..3.48 rows=48 width=18) (actual time=0.008..0.096 rows=48 loops=1)" " -> Seq Scan on f_code_cour (cost=0.00..1.11 rows=11 width=11) (actual time=0.006..0.023 rows=11 loops=1)" " -> Seq Scan on f_quote_client (cost=0.00..1.02 rows=2 width=4) (actual time=0.003..0.006 rows=2 loops=1)" "Total runtime: 13.738 ms" I have rebuilt all indexes to no avail and would love to know how to solve this. I?ll be happy to provide any additional information. I?m currently on the digest version and also will be unavailable until Tuesday, so please do not consider this an emergency. Thanks for your time, Mark Watson -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
