Skip site navigation (1) Skip section navigation (2)

BUG #6115: Two similar object giving slightly different results

From: "Luc Filiatrault" <lucfiliatrault(at)videotron(dot)ca>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6115: Two similar object giving slightly different results
Date: 2011-07-12 19:25:09
Message-ID: 201107121925.p6CJP9gP022049@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      6115
Logged by:          Luc Filiatrault
Email address:      lucfiliatrault(at)videotron(dot)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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-07-12 19:54:18
Subject: Re: BUG #6115: Two similar object giving slightly different results
Previous:From: Jeff DavisDate: 2011-07-12 15:53:10
Subject: Re: Window function bug

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group