Axège

On Wed, Apr 25, 2012 at 12:27:05PM +0200, Matthieu Guamis wrote:

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

Could you also send the ddl statements, ie create table, such that
we could atleast repeat the query?

Niels

-- 
*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>

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and 
threat landscape has changed and how IT managers can respond. Discussions 
will include endpoint security, mobile security and the latest in malware 
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/

_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and 
threat landscape has changed and how IT managers can respond. Discussions 
will include endpoint security, mobile security and the latest in malware 
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users