The following bug has been logged online: Bug reference: 6115 Logged by: Luc Filiatrault Email address: lucfiliatra...@videotron.ca PostgreSQL version: 8.4.8 Operating system: (Debian 4.4.5-8) 4.4.5, 32-bit (Squeeze2) Description: Two similar object giving slightly different results Details:
On a business records database I run a query like this: copy (select name,addr,city,prov,sic4c,contact,email from (select name,addr,city,prov,sic4c,contact,email from email where prov like 'QC' ) as foo where (sic4c like '241' or sic4c like '291' or sic4c like '762' or sic4c like '781') except select name,addr,city,prov,sic4c,contact,email from email where ( name like '%Monsanto%' or name like '%Dow%' or name ilike '%helicopter%' or name ilike '%hydraulic%' or name ilike '%finance%' or name ilike '%financial%' or name ilike '%dvd%' or name ilike '%sewing%' or name ilike '%video%' or name ilike '%movie%' or name ilike '%crane%' or name ilike '%ceramic%') ) to '/var/lib/postgresql/Documents/Database/QueryResults/Today/testfile.csv'with null as '' csv header quote as '"'escape as '"'; *************** Since I need to update some flags according to this so I run to following : update email set wemail = 'on' where ( prov like 'QC' and (sic4c like '241' or sic4c like '291' or sic4c like '762' or sic4c like '781') and not (name like '%Monsanto%' or name like '%Dow%' or name ilike '%helicopter%' or name ilike '%hydraulic%' or name ilike '%finance%' or name ilike '%financial%' or name ilike '%dvd%' or name ilike '%sewing%' or name ilike '%video%' or name ilike '%movie%' or name ilike '%crane%' or name ilike '%ceramic%' )); ********************************************* The list of sic4 codes and the list of exclusion I used are longer: 29 sic4 codes and 73 exclusions. The 2 queries above are partial but good examples. The problem is that the number of rows are respectively 23 and 24. This is as per the 2 exact scripts above. Not a big discrepancy but if I run this with the full list of 29 sic4 codes and the 73 filtered names the discrepancy is 9 ( 578 and 587 row Returned /Affected) More.... if I remove the condition about prov like 'QC' the the whole country list is involved and I run them, I get 5856 rows for the first one and 5936 for the second, a discrepancy of 80. I tried other solutions like changing the order of the statements. It gave the same results. At last I have to say if I run the 2 query modified where the exclusions for the names are removed then the results are consistent at 712 for both of them with the list of 29 sic4 codes and the 73 filtered names with the limitation prov like 'QC'. I may find a bug Thanks -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs