[MonetDB-users] Different results with same query
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
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
-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
Hello, Here is the traces of the statements: ok.txt http://ubuntuone.com/60VFHU17sk6vlciQO2hhu0 ko.txt http://ubuntuone.com/4v3RjVE6G12qB3ELWx0jXH I can't send the DDL statements but I will try to build a more simple use case to reproduce the problem. *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 Le 25/04/2012 14:11, Niels Nes a écrit :
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ègehttp://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
Hello, Few months later(sorry)... Ididnot get a very simple test case butthe problem can be reproduced with a simpler query and few datas. I filled the bug 3176 http://bugs.monetdb.org/show_bug.cgi?id=3176 with a dump and the queries to illustrate it. Regards *Matthieu Guamis* *Logo Axège http://www.axege.com/* /Axège// 22 bis, rue des Chazots 63170 Aubière/ 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 Le 25/04/2012 16:11, Matthieu Guamis a écrit :
Hello,
Here is the traces of the statements: ok.txt http://ubuntuone.com/60VFHU17sk6vlciQO2hhu0 ko.txt http://ubuntuone.com/4v3RjVE6G12qB3ELWx0jXH
I can't send the DDL statements but I will try to build a more simple use case to reproduce the problem.
*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
Le 25/04/2012 14:11, Niels Nes a écrit :
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ègehttp://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
------------------------------------------------------------------------------ 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
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Hi all, What this bug means is that two semantically identical SQL requests give two different results in MonetDB: - in request A, we have a statement saying: where a.col = 'VAL' and b.col = 'VAL' - in request B, we have: where a.col = b.col and a.col = 'VAL' Both should give the same result... but they don't. Another variant is that removing a "where a.another = 'AVALUE' " from the request makes it work as expected (the data in the table happens to always respect this statement). So simplifying slightly the request makes MonetDB compute the right result... The bug report now contains a dump of the tables that are used in the requests, so this can be easily reproduced... Any idea anyone ? Franck Full Disclosure: I am working with Matthieu on the same project. The requests are generated by Mondrian ROLAP engine, and work as expected on both Postgresql and Oracle. _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Franck Routier -
Matthieu Guamis -
Niels Nes