[MonetDB-users] Different results with same query

25 Apr 2012

      Hello,

Running the same query on the same data leeds to different results 
between postgresql and monetdb.
I have not been able to get a simple example to reproduce the problem. 
So here is what I observe:

the query:
select     temps_mois.rfoperdmo as c1,
     sum((case when dwhinv.dwhinv___rfodomide = 'RH'  and 
dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then 
dwhinv.dwhinvqte else 0 end)) as m0
from     rfoper_temps_mois as temps_mois,
     dwhinv as dwhinv,
     rfovsn as rfovsn_0,
     rrhamv as rrhamv_1,
     rrhcov as rrhcov_2,
     rfoadv as rfoadv_3
where     temps_mois.rfoper___rforefide = 'HPLUS'
     and dwhinv.dwhinv___rforefide = 'HPLUS'
     and dwhinv.dwhinv___rfodomide = 'RH'
     and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel'
     and dwhinv.dwhinvdtd = temps_mois.rfoperdtd
     and temps_mois.rfoperyea = '2011'
     and rfovsn___rforefide = 'HPLUS'
     and dwhinv.dwhinv___rfovsnide = rfovsn_0.rfovsnide
     and rfovsn_0.rfovsnide = '201111_reel'
     and rrhamv_1.rrhamv___rrhvemide='GRACOR'
     AND rrhamv_1.rrhamvrvs=1
     AND rrhamv_1.rrhamv___rforefide= 'HPLUS'
     and dwhinv.dwhinv___rrhempide = rrhamv_1.rrhamvinf
     and rrhamv_1.rrhamvsup = 'CEMP'
     and rrhcov_2.rrhcov___rrhvcoide='CONTRATS'
     AND rrhcov_2.rrhcovrvs=1
     AND rrhcov_2.rrhcov___rforefide= 'HPLUS'
     and dwhinv.dwhinv___rrhcntide = rrhcov_2.rrhcovinf
     and rrhcov_2.rrhcovsup = 'CONTRATS'
     and rfoadv_3.rfoadv___rfovdeide='STRC'
     AND rfoadv_3.rfoadvrvs=1
     AND rfoadv_3.rfoadv___rforefide= 'HPLUS'
     and dwhinv.dwhinv_p2rfodstide = rfoadv_3.rfoadvinf
     and rfoadv_3.rfoadvsup = 'HPLUS'
group by c1
order by c1

Postgresql result:
  c1 |       m0
----+-----------------
  01 | 7111.5376967750
  02 | 7100.9108821426
  03 | 7150.2597967742
  04 | 7151.4283666667
  05 | 7109.1641451610
  06 | 6976.2108421239
  07 | 6329.7404193564
  08 | 6302.9823032247
  09 | 6426.9459633351
  10 | 6519.6889580648
  11 | 6549.5235033402
  12 | 6492.5477161292
(12 lignes)

Monetdb result:
+------+----------------------+
| c1   | m0                   |
+======+======================+
| 12   |             1.000000 |
| 11   |             1.000000 |
| 10   |             2.000000 |
| 09   |             0.366667 |
| 08   |             1.000000 |
| 07   |             1.000000 |
| 06   |             1.000000 |
| 05   |             3.000000 |
| 04   |             4.000000 |
| 03   |            15.000000 |
| 02   |             9.964286 |
| 01   |             1.000000 |
+------+----------------------+
12 tuples (212.346ms)

As you see, all "m0" values are very different.

I have no idea of what is causing this but if I modify the query in 
order to simplify the CASE...WHEN...ELSE part of the select, it produce 
the expected result:
replacing sum((case when dwhinv.dwhinv___rfodomide = 'RH'  and 
dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then 
dwhinv.dwhinvqte else 0 end)) as m0
by sum((case when dwhinv.dwhinv___rfoindide = 
'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0

Its not exactly the same query but it must leeds to the same result due 
to my test datas (its the case in postgres)

I join to this email two files with the TRACES, respectively for the 
request with bad/correct result (ko.txt/ok.txt)

Config:
Ubuntu Server 11.04 x64
MonetDB v11.7.9 (Dec2011-SP2), MonetDB Database Server v1.6 
(Dec2011-SP2), MonetDB Database Server Toolkit v1.0 (Dec2011-SP2)

Thank you very much for your help!!

-- 
*Matthieu Guamis*
*Logo Axège http://www.axege.com/* 	/Axège//
23,rue Saint-Simon
63000 Clermont-Ferrand/

Tél: +33 (0)4 63 05 95 40
Fax: +33 (0)4.73.70.65.29
Email: matthieu.guamis@axege.com mailto:matthieu.guamis@axege.com