The following bug has been logged online:
Bug reference: 6115
Logged by: Luc Filiatrault
Email address: lucfiliatrault@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