Axège

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