You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Running the same query on the same data leeds to different results.
select
temps_mois.rfoperyea as c0,
sum((case when dwhinv.dwhinv___rfodomide = 'PMSI' and dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' then dwhinv.dwhinvqte else 0 end)) as m0
from
rfoper_temps_mois as temps_mois,
dwhinv as dwhinv
where
temps_mois.rfoper___rforefide = dwhinv.dwhinv___rforefide and
dwhinv.dwhinv___rforefide = 'CHCL' and
dwhinv.dwhinv___rfodomide = 'PMSI' and
dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' and
dwhinv.dwhinvdtd = temps_mois.rfoperdtd
group by c0 order by c0
Replacing where clause "emps_mois.rfoper___rforefide = dwhinv.dwhinv___rforefide" by "temps_mois.rfoper___rforefide = 'CHCL'" which is the same because of the "dwhinv.dwhinv___rforefide = 'CHCL'":
select
temps_mois.rfoperyea as c0,
sum((case when dwhinv.dwhinv___rfodomide = 'PMSI' and dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' then dwhinv.dwhinvqte else 0 end)) as m0
from
rfoper_temps_mois as temps_mois,
dwhinv as dwhinv
where
temps_mois.rfoper___rforefide = 'CHCL' and -- OLD line: temps_mois.rfoper___rforefide = dwhinv.dwhinv___rforefide and
dwhinv.dwhinv___rforefide = 'CHCL' and
dwhinv.dwhinv___rfodomide = 'PMSI' and
dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' and
dwhinv.dwhinvdtd = temps_mois.rfoperdtd
group by c0 order by c0
admaxg@monetdb:~$ mclient --version
mclient, the MonetDB interactive terminal (Jul2012-SP2)
support for command-line editing compiled-in
character encoding: UTF-8
admaxg@monetdb:~$ monetdb version
MonetDB Database Server Toolkit v1.0 (Jul2012-SP2)
admaxg@monetdb:~$ monetdbd version
MonetDB Database Server v1.6 (Jul2012-SP2)
Those two queries must have the same result but only the first works as expected.
select temps_mois.rfoperyea as c0, sum((case when dwhinv.dwhinv___rfodomide = 'PMSI' and dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' then dwhinv.dwhinvqte else 0
end)) as m0
from rfoper_temps_mois as temps_mois, dwhinv as dwhinv
where temps_mois.rfoper___rforefide = dwhinv.dwhinv___rforefide and
dwhinv.dwhinv___rforefide = 'CHCL' and
dwhinv.dwhinv___rfodomide = 'PMSI' and
dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' and
dwhinv.dwhinvdtd = temps_mois.rfoperdtd
group by c0 order by c0
select temps_mois.rfoperyea as c0, sum((case when dwhinv.dwhinv___rfodomide = 'PMSI' and dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' then dwhinv.dwhinvqte else 0
end)) as m0
from rfoper_temps_mois as temps_mois, dwhinv as dwhinv
where temps_mois.rfoper___rforefide = 'CHCL' and
dwhinv.dwhinv___rforefide = 'CHCL' and
dwhinv.dwhinv___rfodomide = 'PMSI' and
dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' and
dwhinv.dwhinvdtd = temps_mois.rfoperdtd
group by c0 order by c0
Comment 17923
Date: 2012-11-08 15:47:47 +0100
From: @mlkersten
The problem does not appear on the default (=DEVELOPMENT) branch
sql>select temps_mois.rfoperyea as c0, sum((case when dwhinv.dwhinv___rfodomide =
more>'PMSI' and dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' then
more>dwhinv.dwhinvqte else 0
more>end)) as m0
more> from rfoper_temps_mois as temps_mois, dwhinv as dwhinv
more> where temps_mois.rfoper___rforefide = dwhinv.dwhinv___rforefide and
more> dwhinv.dwhinv___rforefide = 'CHCL' and
more> dwhinv.dwhinv___rfodomide = 'PMSI' and
more> dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' and
more> dwhinv.dwhinvdtd = temps_mois.rfoperdtd
more> group by c0 order by c0 ;
+------+----------------------+
| c0 | m0 |
+======+======================+
| 2009 | 14691.000000 |
| 2010 | 8454.000000 |
+------+----------------------+
2 tuples (36.442ms)
sql>select temps_mois.rfoperyea as c0, sum((case when dwhinv.dwhinv___rfodomide =
more>'PMSI' and dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' then
more>dwhinv.dwhinvqte else 0
more>end)) as m0
more> from rfoper_temps_mois as temps_mois, dwhinv as dwhinv
more> where temps_mois.rfoper___rforefide = 'CHCL' and
more> dwhinv.dwhinv___rforefide = 'CHCL' and
more> dwhinv.dwhinv___rfodomide = 'PMSI' and
more> dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' and
more> dwhinv.dwhinvdtd = temps_mois.rfoperdtd
more> group by c0 order by c0;
+------+----------------------+
| c0 | m0 |
+======+======================+
| 2009 | 14691.000000 |
| 2010 | 8454.000000 |
+------+----------------------+
2 tuples (31.012ms)
Date: 2012-11-05 11:30:23 +0100
From: matthieu.guamis
To: SQL devs <>
Version: 11.13.3 (Oct2012)
CC: matthieu.guamis, meduri, @mlkersten, @drstmane
Last updated: 2013-01-22 09:29:11 +0100
Comment 17876
Date: 2012-11-05 11:30:23 +0100
From: matthieu.guamis
User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:16.0) Gecko/20100101 Firefox/16.0
Build Identifier:
Same query, different results
Running the same query on the same data leeds to different results.
select
temps_mois.rfoperyea as c0,
sum((case when dwhinv.dwhinv___rfodomide = 'PMSI' and dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' then dwhinv.dwhinvqte else 0 end)) as m0
from
rfoper_temps_mois as temps_mois,
dwhinv as dwhinv
where
temps_mois.rfoper___rforefide = dwhinv.dwhinv___rforefide and
dwhinv.dwhinv___rforefide = 'CHCL' and
dwhinv.dwhinv___rfodomide = 'PMSI' and
dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' and
dwhinv.dwhinvdtd = temps_mois.rfoperdtd
group by c0 order by c0
+------+----------------------+
| c0 | m0 |
+======+======================+
| 2009 | 14691.000000 |
| 2010 | 8454.000000 |
+------+----------------------+
2 tuples (44.973ms)
Here is the explain out:
+------------------------------------------------------------------------------------------------+
| mal |
+================================================================================================+
| function user.s7_3{autoCommit=true}(A0:str,A1:str,A2:bte,A3:str,A4:str,A5:str):void; |
| X_167 := nil:bat[:oid,:str]; |
| X_205 := nil:bat[:oid,:lng]; |
| barrier X_311 := language.dataflow(); |
| X_8 := sql.mvc(); |
| X_9:bat[:oid,:str] := sql.bind(X_8,"axabas","rfoper_temps_mois","rfoper___rforefide",0); |
| X_14:bat[:oid,:str] := sql.bind(X_8,"axabas","rfoper_temps_mois","rfoper___rforefide",2); |
| X_16 := algebra.kdifference(X_9,X_14); |
| X_17 := algebra.kunion(X_16,X_14); |
| X_18:bat[:oid,:str] := sql.bind(X_8,"axabas","rfoper_temps_mois","rfoper___rforefide",1); |
| X_20 := algebra.kunion(X_17,X_18); |
| X_21:bat[:oid,:oid] := sql.bind_dbat(X_8,"axabas","rfoper_temps_mois",1); |
| X_22 := bat.reverse(X_21); |
| X_23 := algebra.kdifference(X_20,X_22); |
| X_24 := algebra.markT(X_23,40@0:oid); |
| X_25 := bat.reverse(X_23); |
| X_26 := algebra.markT(X_25,40@0:oid); |
| X_27 := bat.reverse(X_26); |
| X_28:bat[:oid,:wrd] := batcalc.hash(X_27); |
| X_29 := bat.reverse(X_24); |
| X_30:bat[:oid,:timestamp] := sql.bind(X_8,"axabas","rfoper_temps_mois","rfoperdtd",0); |
| X_32:bat[:oid,:timestamp] := sql.bind(X_8,"axabas","rfoper_temps_mois","rfoperdtd",2); |
| X_35 := algebra.kdifference(X_30,X_32); |
| X_36 := algebra.kunion(X_35,X_32); |
| X_37:bat[:oid,:timestamp] := sql.bind(X_8,"axabas","rfoper_temps_mois","rfoperdtd",1); |
| X_38 := algebra.kunion(X_36,X_37); |
| X_39 := algebra.leftjoin(X_29,X_38); |
| X_40:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_28,22:int,X_39); |
| X_41:bat[:oid,:str] := sql.bind(X_8,"axabas","dwhinv","dwhinv___rforefide",0); |
| X_45:bat[:oid,:str] := sql.bind(X_8,"axabas","dwhinv","dwhinv___rforefide",2); |
| X_47 := algebra.kdifference(X_41,X_45); |
| X_48 := algebra.kunion(X_47,X_45); |
| X_49:bat[:oid,:str] := sql.bind(X_8,"axabas","dwhinv","dwhinv___rforefide",1); |
| X_51 := algebra.kunion(X_48,X_49); |
| X_52:bat[:oid,:oid] := sql.bind_dbat(X_8,"axabas","dwhinv",1); |
| X_53 := bat.reverse(X_52); |
| X_54 := algebra.kdifference(X_51,X_53); |
| X_55:bat[:oid,:str] := sql.bind(X_8,"axabas","dwhinv","dwhinv___rfodomide",0); |
| X_57:bat[:oid,:str] := sql.bind(X_8,"axabas","dwhinv","dwhinv___rfodomide",2); |
| X_58 := algebra.kdifference(X_55,X_57); |
| X_59 := algebra.kunion(X_58,X_57); |
| X_60:bat[:oid,:str] := sql.bind(X_8,"axabas","dwhinv","dwhinv___rfodomide",1); |
| X_61 := algebra.kunion(X_59,X_60); |
| X_62 := algebra.kdifference(X_61,X_53); |
| X_63:bat[:oid,:str] := sql.bind(X_8,"axabas","dwhinv","dwhinv___rfoindide",0); |
| X_66 := algebra.uselect(X_63,A5); |
| X_67:bat[:oid,:str] := sql.bind(X_8,"axabas","dwhinv","dwhinv___rfoindide",2); |
| X_68 := algebra.kdifference(X_66,X_67); |
| X_69 := algebra.uselect(X_67,A5); |
| X_70 := algebra.kunion(X_68,X_69); |
| X_71:bat[:oid,:str] := sql.bind(X_8,"axabas","dwhinv","dwhinv___rfoindide",1); |
| X_73 := algebra.uselect(X_71,A5); |
| X_74 := algebra.kunion(X_70,X_73); |
| X_75 := algebra.kdifference(X_74,X_53); |
| X_76 := algebra.semijoin(X_62,X_75); |
| X_77 := algebra.uselect(X_76,A4); |
| X_78 := algebra.kdifference(X_77,X_57); |
| X_79 := algebra.semijoin(X_57,X_75); |
| X_80 := algebra.uselect(X_79,A4); |
| X_81 := algebra.kunion(X_78,X_80); |
| X_82 := algebra.semijoin(X_60,X_75); |
| X_83 := algebra.uselect(X_82,A4); |
| X_84 := algebra.kunion(X_81,X_83); |
| X_85 := algebra.kdifference(X_84,X_53); |
| X_86 := algebra.semijoin(X_54,X_85); |
| X_87 := algebra.uselect(X_86,A3); |
| X_88 := algebra.kdifference(X_87,X_45); |
| X_89 := algebra.semijoin(X_45,X_85); |
| X_90 := algebra.uselect(X_89,A3); |
| X_91 := algebra.kunion(X_88,X_90); |
| X_92 := algebra.semijoin(X_49,X_85); |
| X_93 := algebra.uselect(X_92,A3); |
| X_94 := algebra.kunion(X_91,X_93); |
| X_95 := algebra.kdifference(X_94,X_53); |
| X_96 := algebra.markT(X_95,0@0:oid); |
| X_97 := bat.reverse(X_96); |
| X_98 := algebra.kdifference(X_54,X_45); |
| X_99 := algebra.kunion(X_98,X_45); |
| X_100 := algebra.kunion(X_99,X_49); |
| X_101 := algebra.leftjoin(X_97,X_100); |
| X_102 := algebra.markT(X_101,40@0:oid); |
| X_103 := bat.reverse(X_101); |
| X_104 := algebra.markT(X_103,40@0:oid); |
| X_105 := bat.reverse(X_104); |
| X_106:bat[:oid,:wrd] := batcalc.hash(X_105); |
| X_107 := bat.reverse(X_102); |
| X_108:bat[:oid,:timestamp] := sql.bind(X_8,"axabas","dwhinv","dwhinvdtd",0); |
| X_113:bat[:oid,:timestamp] := sql.bind(X_8,"axabas","dwhinv","dwhinvdtd",2); |
| X_115 := algebra.kdifference(X_108,X_113); |
| X_116 := algebra.kunion(X_115,X_113); |
| X_117:bat[:oid,:timestamp] := sql.bind(X_8,"axabas","dwhinv","dwhinvdtd",1); |
| X_119 := algebra.kunion(X_116,X_117); |
| X_120 := algebra.leftjoin(X_97,X_119); |
| X_121 := algebra.leftjoin(X_107,X_120); |
| X_122:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_106,22:int,X_121); |
| X_123 := algebra.join(X_102,X_122); |
| X_124 := bat.reverse(X_123); |
| X_125:bat[:oid,:oid] := algebra.joinPath(X_24,X_40,X_124); |
| X_129 := bat.reverse(X_125); |
| X_130 := algebra.markT(X_129,4@0:oid); |
| X_131 := bat.reverse(X_130); |
| X_126 := algebra.markT(X_125,4@0:oid); |
| X_127 := bat.reverse(X_126); |
| X_128 := algebra.leftjoin(X_127,X_20); |
| X_132 := algebra.leftjoin(X_131,X_101); |
| X_133:bat[:oid,:bit] := batcalc.==(X_128,X_132); |
| X_134 := algebra.uselect(X_133,true:bit); |
| X_137 := algebra.semijoin(X_131,X_134); |
| X_138 := algebra.leftjoin(X_137,X_120); |
| X_135 := algebra.semijoin(X_127,X_134); |
| X_136 := algebra.leftjoin(X_135,X_38); |
| X_139:bat[:oid,:bit] := batcalc.==(X_136,X_138); |
| X_140 := algebra.uselect(X_139,true:bit); |
| X_141 := algebra.semijoin(X_135,X_140); |
| X_142 := bat.reverse(X_141); |
| X_143 := algebra.semijoin(X_137,X_140); |
| X_144 := algebra.join(X_142,X_143); |
| X_145 := algebra.markT(X_144,0@0:oid); |
| X_146 := bat.reverse(X_145); |
| X_147:bat[:oid,:str] := sql.bind(X_8,"axabas","rfoper_temps_mois","rfoperyea",0); |
| X_152:bat[:oid,:str] := sql.bind(X_8,"axabas","rfoper_temps_mois","rfoperyea",2); |
| X_154 := algebra.kdifference(X_147,X_152); |
| X_155 := algebra.kunion(X_154,X_152); |
| X_156:bat[:oid,:str] := sql.bind(X_8,"axabas","rfoper_temps_mois","rfoperyea",1); |
| X_158 := algebra.kunion(X_155,X_156); |
| X_159 := algebra.leftjoin(X_146,X_158); |
| (ext265,grp263) := group.done(X_159); |
| X_162 := bat.mirror(ext265); |
| X_163 := algebra.join(X_162,X_159); |
| X_168 := bat.reverse(X_144); |
| X_169 := algebra.markT(X_168,0@0:oid); |
| X_170 := bat.reverse(X_169); |
| X_310 := algebra.leftjoin(X_170,X_97); |
| X_171 := algebra.kdifference(X_62,X_57); |
| X_172 := algebra.kunion(X_171,X_57); |
| X_173 := algebra.kunion(X_172,X_60); |
| X_174:bat[:oid,:str] := algebra.leftjoin(X_310,X_173); |
| X_176:bat[:oid,:bit] := batcalc.==(X_174,A0); |
| X_177 := algebra.kdifference(X_63,X_67); |
| X_178 := algebra.kunion(X_177,X_67); |
| X_179 := algebra.kunion(X_178,X_71); |
| X_180:bat[:oid,:str] := algebra.leftjoin(X_310,X_179); |
| X_181:bat[:oid,:bit] := batcalc.==(X_180,A1); |
| X_185:bat[:oid,:lng] := sql.bind(X_8,"axabas","dwhinv","dwhinvqte",0); |
| X_189:bat[:oid,:lng] := sql.bind(X_8,"axabas","dwhinv","dwhinvqte",2); |
| X_192 := algebra.kdifference(X_185,X_189); |
| X_193 := algebra.kunion(X_192,X_189); |
| X_194:bat[:oid,:lng] := sql.bind(X_8,"axabas","dwhinv","dwhinvqte",1); |
| X_196 := algebra.kunion(X_193,X_194); |
| X_197:bat[:oid,:lng] := algebra.leftjoin(X_310,X_196); |
| X_182:bat[:oid,:bit] := batcalc.and(X_176,X_181); |
| X_183:bat[:oid,:bit] := batcalc.isnil(X_182); |
| X_184:bat[:oid,:bit] := batcalc.ifthenelse(X_183,false:bit,X_182); |
| X_198 := calc.lng(A2,18,6); |
| X_201:bat[:oid,:lng] := batcalc.ifthenelse(X_184,X_197,X_198); |
| X_202 := algebra.selectNotNil(X_201); |
| X_203:bat[:oid,:lng] := aggr.sum(X_202,grp263,X_162); |
| X_164 := algebra.sortTail(X_163); |
| X_165 := algebra.markT(X_164,0@0:oid); |
| X_166 := bat.reverse(X_165); |
| X_167 := algebra.leftjoin(X_166,X_163); |
| X_205 := algebra.leftjoin(X_166,X_203); |
| exit X_311; |
| X_206 := sql.resultSet(2,1,X_167); |
| sql.rsColumn(X_206,"axabas.temps_mois","c0","varchar",32,0,X_167); |
| sql.rsColumn(X_206,"axabas.","m0","decimal",18,6,X_205); |
| X_216 := io.stdout(); |
| sql.exportResult(X_216,X_206); |
| end s7_3; |
+------------------------------------------------------------------------------------------------+
169 tuples (22.405ms)
Replacing where clause "emps_mois.rfoper___rforefide = dwhinv.dwhinv___rforefide" by "temps_mois.rfoper___rforefide = 'CHCL'" which is the same because of the "dwhinv.dwhinv___rforefide = 'CHCL'":
select
temps_mois.rfoperyea as c0,
sum((case when dwhinv.dwhinv___rfodomide = 'PMSI' and dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' then dwhinv.dwhinvqte else 0 end)) as m0
from
rfoper_temps_mois as temps_mois,
dwhinv as dwhinv
where
temps_mois.rfoper___rforefide = 'CHCL' and -- OLD line: temps_mois.rfoper___rforefide = dwhinv.dwhinv___rforefide and
dwhinv.dwhinv___rforefide = 'CHCL' and
dwhinv.dwhinv___rfodomide = 'PMSI' and
dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' and
dwhinv.dwhinvdtd = temps_mois.rfoperdtd
group by c0 order by c0
+------+----------------------+
| c0 | m0 |
+======+======================+
| 2009 | 1.000000 |
| 2010 | 1.000000 |
+------+----------------------+
2 tuples (31.901ms)
Here is the explain out:
+------------------------------------------------------------------------------------------------+
| mal |
+================================================================================================+
| function user.s5_3{autoCommit=true}(A0:str,A1:str,A2:bte,A3:str,A4:str,A5:str,A6:str):void; |
| X_86 := nil:bat[:oid,:str]; |
| X_108 := nil:bat[:oid,:lng]; |
| barrier X_427 := language.dataflow(); |
| X_9 := sql.mvc(); |
| X_151:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rforefide",0,0,2); |
| X_158:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rforefide",2,0,2); |
| X_187 := algebra.kdifference(X_151,X_158); |
| X_189 := algebra.kunion(X_187,X_158); |
| X_17:bat[:oid,:oid] := sql.bind_dbat(X_9,"axabas","dwhinv",1); |
| X_18 := bat.reverse(X_17); |
| X_192 := algebra.kdifference(X_189,X_18); |
| X_160:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rfodomide",0,0,2); |
| X_163:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rfodomide",2,0,2); |
| X_195 := algebra.kdifference(X_160,X_163); |
| X_197 := algebra.kunion(X_195,X_163); |
| X_199 := algebra.kdifference(X_197,X_18); |
| X_165:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rfoindide",0,0,2); |
| X_202 := algebra.uselect(X_165,A6); |
| X_168:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rfoindide",2,0,2); |
| X_204 := algebra.kdifference(X_202,X_168); |
| X_206 := algebra.uselect(X_168,A6); |
| X_208 := algebra.kunion(X_204,X_206); |
| X_212 := algebra.kdifference(X_208,X_18); |
| X_216 := algebra.semijoin(X_199,X_212); |
| X_220 := algebra.uselect(X_216,A5); |
| X_223 := algebra.kdifference(X_220,X_163); |
| X_225 := algebra.semijoin(X_163,X_212); |
| X_229 := algebra.uselect(X_225,A5); |
| X_232 := algebra.kunion(X_223,X_229); |
| X_235 := algebra.kdifference(X_232,X_18); |
| X_240 := algebra.semijoin(X_192,X_235); |
| X_243 := algebra.uselect(X_240,A4); |
| X_248 := algebra.kdifference(X_243,X_158); |
| X_252 := algebra.semijoin(X_158,X_235); |
| X_254 := algebra.uselect(X_252,A4); |
| X_256 := algebra.kunion(X_248,X_254); |
| X_261 := algebra.kdifference(X_256,X_18); |
| X_266 := algebra.markT(X_261,4,0); |
| X_279 := bat.reverse(X_266); |
| X_170:bat[:oid,:timestamp] := sql.bind(X_9,"axabas","dwhinv","dwhinvdtd",0,0,2); |
| X_174:bat[:oid,:timestamp] := sql.bind(X_9,"axabas","dwhinv","dwhinvdtd",2,0,2); |
| X_286 := algebra.kdifference(X_170,X_174); |
| X_288 := algebra.kunion(X_286,X_174); |
| X_290 := algebra.leftjoin(X_279,X_288); |
| X_40:bat[:oid,:str] := sql.bind(X_9,"axabas","rfoper_temps_mois","rfoper___rforefide",0); |
| X_43 := algebra.uselect(X_40,A3); |
| X_44:bat[:oid,:str] := sql.bind(X_9,"axabas","rfoper_temps_mois","rfoper___rforefide",2); |
| X_45 := algebra.kdifference(X_43,X_44); |
| X_46 := algebra.uselect(X_44,A3); |
| X_47 := algebra.kunion(X_45,X_46); |
| X_48:bat[:oid,:str] := sql.bind(X_9,"axabas","rfoper_temps_mois","rfoper___rforefide",1); |
| X_49 := algebra.uselect(X_48,A3); |
| X_50 := algebra.kunion(X_47,X_49); |
| X_51:bat[:oid,:oid] := sql.bind_dbat(X_9,"axabas","rfoper_temps_mois",1); |
| X_53 := bat.reverse(X_51); |
| X_54 := algebra.kdifference(X_50,X_53); |
| X_55 := algebra.markT(X_54,0@0:oid); |
| X_56 := bat.reverse(X_55); |
| X_57:bat[:oid,:timestamp] := sql.bind(X_9,"axabas","rfoper_temps_mois","rfoperdtd",0); |
| X_60:bat[:oid,:timestamp] := sql.bind(X_9,"axabas","rfoper_temps_mois","rfoperdtd",2); |
| X_62 := algebra.kdifference(X_57,X_60); |
| X_63 := algebra.kunion(X_62,X_60); |
| X_64:bat[:oid,:timestamp] := sql.bind(X_9,"axabas","rfoper_temps_mois","rfoperdtd",1); |
| X_66 := algebra.kunion(X_63,X_64); |
| X_67 := algebra.leftjoin(X_56,X_66); |
| X_68 := bat.reverse(X_67); |
| X_297 := algebra.join(X_290,X_68); |
| X_301 := bat.reverse(X_297); |
| X_308 := algebra.markT(X_301,4,0); |
| X_319 := bat.reverse(X_308); |
| X_69:bat[:oid,:str] := sql.bind(X_9,"axabas","rfoper_temps_mois","rfoperyea",0); |
| X_72:bat[:oid,:str] := sql.bind(X_9,"axabas","rfoper_temps_mois","rfoperyea",2); |
| X_73 := algebra.kdifference(X_69,X_72); |
| X_74 := algebra.kunion(X_73,X_72); |
| X_75:bat[:oid,:str] := sql.bind(X_9,"axabas","rfoper_temps_mois","rfoperyea",1); |
| X_77 := algebra.kunion(X_74,X_75); |
| X_78 := algebra.leftjoin(X_56,X_77); |
| X_323 := algebra.leftjoin(X_319,X_78); |
| (X_327,X_328) := group.done(X_323); |
| X_335 := bat.mirror(X_327); |
| X_337 := algebra.leftjoin(X_335,X_323); |
| X_156:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rforefide",0,1,2); |
| X_159:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rforefide",2,1,2); |
| X_188 := algebra.kdifference(X_156,X_159); |
| X_190 := algebra.kunion(X_188,X_159); |
| X_193 := algebra.kdifference(X_190,X_18); |
| X_162:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rfodomide",0,1,2); |
| X_164:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rfodomide",2,1,2); |
| X_196 := algebra.kdifference(X_162,X_164); |
| X_198 := algebra.kunion(X_196,X_164); |
| X_200 := algebra.kdifference(X_198,X_18); |
| X_167:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rfoindide",0,1,2); |
| X_203 := algebra.uselect(X_167,A6); |
| X_169:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rfoindide",2,1,2); |
| X_205 := algebra.kdifference(X_203,X_169); |
| X_207 := algebra.uselect(X_169,A6); |
| X_211 := algebra.kunion(X_205,X_207); |
| X_213 := algebra.kdifference(X_211,X_18); |
| X_217 := algebra.semijoin(X_200,X_213); |
| X_221 := algebra.uselect(X_217,A5); |
| X_224 := algebra.kdifference(X_221,X_164); |
| X_228 := algebra.semijoin(X_164,X_213); |
| X_230 := algebra.uselect(X_228,A5); |
| X_233 := algebra.kunion(X_224,X_230); |
| X_236 := algebra.kdifference(X_233,X_18); |
| X_241 := algebra.semijoin(X_193,X_236); |
| X_246 := algebra.uselect(X_241,A4); |
| X_249 := algebra.kdifference(X_246,X_159); |
| X_253 := algebra.semijoin(X_159,X_236); |
| X_255 := algebra.uselect(X_253,A4); |
| X_257 := algebra.kunion(X_249,X_255); |
| X_262 := algebra.kdifference(X_257,X_18); |
| X_270 := algebra.markT(X_262,4,1); |
| X_282 := bat.reverse(X_270); |
| X_172:bat[:oid,:timestamp] := sql.bind(X_9,"axabas","dwhinv","dwhinvdtd",0,1,2); |
| X_175:bat[:oid,:timestamp] := sql.bind(X_9,"axabas","dwhinv","dwhinvdtd",2,1,2); |
| X_287 := algebra.kdifference(X_172,X_175); |
| X_289 := algebra.kunion(X_287,X_175); |
| X_291 := algebra.leftjoin(X_282,X_289); |
| X_298 := algebra.join(X_291,X_68); |
| X_303 := bat.reverse(X_298); |
| X_312 := algebra.markT(X_303,4,1); |
| X_320 := bat.reverse(X_312); |
| X_324 := algebra.leftjoin(X_320,X_78); |
| (X_329,X_330) := group.done(X_324); |
| X_338 := bat.mirror(X_329); |
| X_340 := algebra.leftjoin(X_338,X_324); |
| X_15:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rforefide",1); |
| X_194 := algebra.kdifference(X_15,X_18); |
| X_20:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rfodomide",1); |
| X_201 := algebra.kdifference(X_20,X_18); |
| X_22:bat[:oid,:str] := sql.bind(X_9,"axabas","dwhinv","dwhinv___rfoindide",1); |
| X_25 := algebra.uselect(X_22,A6); |
| X_215 := algebra.kdifference(X_25,X_18); |
| X_218 := algebra.semijoin(X_201,X_215); |
| X_222 := algebra.uselect(X_218,A5); |
| X_237 := algebra.kdifference(X_222,X_18); |
| X_26 := mat.pack(X_212,X_213,X_215); |
| X_27 := algebra.semijoin(X_20,X_26); |
| X_28 := algebra.uselect(X_27,A5); |
| X_238 := algebra.kdifference(X_28,X_18); |
| X_239 := algebra.kunion(X_237,X_238); |
| X_242 := algebra.semijoin(X_194,X_239); |
| X_247 := algebra.uselect(X_242,A4); |
| X_263 := algebra.kdifference(X_247,X_18); |
| X_273 := algebra.markT(X_263,4,2); |
| X_283 := bat.reverse(X_273); |
| X_37:bat[:oid,:timestamp] := sql.bind(X_9,"axabas","dwhinv","dwhinvdtd",1); |
| X_292 := algebra.leftjoin(X_283,X_37); |
| X_299 := algebra.join(X_292,X_68); |
| X_305 := bat.reverse(X_299); |
| X_314 := algebra.markT(X_305,4,2); |
| X_321 := bat.reverse(X_314); |
| X_325 := algebra.leftjoin(X_321,X_78); |
| (X_331,X_332) := group.done(X_325); |
| X_341 := bat.mirror(X_331); |
| X_343 := algebra.leftjoin(X_341,X_325); |
| X_30 := mat.pack(X_235,X_236,X_237,X_238); |
| X_31 := algebra.semijoin(X_15,X_30); |
| X_32 := algebra.uselect(X_31,A4); |
| X_265 := algebra.kdifference(X_32,X_18); |
| X_276 := algebra.markT(X_265,4,3); |
| X_285 := bat.reverse(X_276); |
| X_293 := algebra.leftjoin(X_285,X_37); |
| X_300 := algebra.join(X_293,X_68); |
| X_307 := bat.reverse(X_300); |
| X_316 := algebra.markT(X_307,4,3); |
| X_322 := bat.reverse(X_316); |
| X_326 := algebra.leftjoin(X_322,X_78); |
| (X_333,X_334) := group.done(X_326); |
| X_344 := bat.mirror(X_333); |
| X_345 := algebra.leftjoin(X_344,X_326); |
| X_346 := mat.pack(X_337,X_340,X_343,X_345); |
| (ext208,grp206) := group.done(X_346); |
| X_81 := bat.mirror(ext208); |
| X_350 := algebra.join(X_81,X_323); |
| X_351 := algebra.join(X_81,X_324); |
| X_352 := algebra.join(X_81,X_325); |
| X_353 := algebra.join(X_81,X_326); |
| X_349 := mat.pack(X_350,X_351,X_352,X_353); |
| X_82 := algebra.join(X_81,X_349); |
| X_355 := algebra.markT(X_297,4,0); |
| X_364 := bat.reverse(X_355); |
| X_423 := algebra.leftjoin(X_364,X_279); |
| X_371 := algebra.kdifference(X_199,X_163); |
| X_375 := algebra.kunion(X_371,X_163); |
| X_379:bat[:oid,:str] := algebra.leftjoin(X_423,X_375); |
| X_385 := batcalc.==(X_379,A0); |
| X_391 := algebra.kdifference(X_165,X_168); |
| X_393 := algebra.kunion(X_391,X_168); |
| X_398:bat[:oid,:str] := algebra.leftjoin(X_423,X_393); |
| X_403 := batcalc.==(X_398,A1); |
| X_176:bat[:oid,:lng] := sql.bind(X_9,"axabas","dwhinv","dwhinvqte",0,0,2); |
| X_181:bat[:oid,:lng] := sql.bind(X_9,"axabas","dwhinv","dwhinvqte",2,0,2); |
| X_408 := algebra.kdifference(X_176,X_181); |
| X_412 := algebra.kunion(X_408,X_181); |
| X_417:bat[:oid,:lng] := algebra.leftjoin(X_423,X_412); |
| X_358 := algebra.markT(X_298,4,1); |
| X_366 := bat.reverse(X_358); |
| X_424 := algebra.leftjoin(X_366,X_282); |
| X_373 := algebra.kdifference(X_200,X_164); |
| X_377 := algebra.kunion(X_373,X_164); |
| X_380:bat[:oid,:str] := algebra.leftjoin(X_424,X_377); |
| X_386 := batcalc.==(X_380,A0); |
| X_392 := algebra.kdifference(X_167,X_169); |
| X_394 := algebra.kunion(X_392,X_169); |
| X_400:bat[:oid,:str] := algebra.leftjoin(X_424,X_394); |
| X_404 := batcalc.==(X_400,A1); |
| X_178:bat[:oid,:lng] := sql.bind(X_9,"axabas","dwhinv","dwhinvqte",0,1,2); |
| X_185:bat[:oid,:lng] := sql.bind(X_9,"axabas","dwhinv","dwhinvqte",2,1,2); |
| X_410 := algebra.kdifference(X_178,X_185); |
| X_413 := algebra.kunion(X_410,X_185); |
| X_419:bat[:oid,:lng] := algebra.leftjoin(X_424,X_413); |
| X_360 := algebra.markT(X_299,4,2); |
| X_368 := bat.reverse(X_360); |
| X_425 := algebra.leftjoin(X_368,X_283); |
| X_381:bat[:oid,:str] := algebra.leftjoin(X_425,X_201); |
| X_387 := batcalc.==(X_381,A0); |
| X_382:bat[:oid,:str] := algebra.leftjoin(X_425,X_20); |
| X_388 := batcalc.==(X_382,A0); |
| X_401:bat[:oid,:str] := algebra.leftjoin(X_425,X_22); |
| X_405 := batcalc.==(X_401,A1); |
| X_98:bat[:oid,:lng] := sql.bind(X_9,"axabas","dwhinv","dwhinvqte",1); |
| X_420:bat[:oid,:lng] := algebra.leftjoin(X_425,X_98); |
| X_362 := algebra.markT(X_300,4,3); |
| X_370 := bat.reverse(X_362); |
| X_426 := algebra.leftjoin(X_370,X_285); |
| X_383:bat[:oid,:str] := algebra.leftjoin(X_426,X_201); |
| X_389 := batcalc.==(X_383,A0); |
| X_384:bat[:oid,:str] := algebra.leftjoin(X_426,X_20); |
| X_390 := batcalc.==(X_384,A0); |
| X_402:bat[:oid,:str] := algebra.leftjoin(X_426,X_22); |
| X_406 := batcalc.==(X_402,A1); |
| X_421:bat[:oid,:lng] := algebra.leftjoin(X_426,X_98); |
| X_100 := mat.pack(X_417,X_419,X_420,X_421); |
| X_88:bat[:oid,:bit] := mat.pack(X_385,X_386,X_387,X_388,X_389,X_390); |
| X_89:bat[:oid,:bit] := mat.pack(X_403,X_404,X_405,X_406); |
| X_90:bat[:oid,:bit] := batcalc.and(X_88,X_89); |
| X_91:bat[:oid,:bit] := batcalc.isnil(X_90); |
| X_92:bat[:oid,:bit] := batcalc.ifthenelse(X_91,false:bit,X_90); |
| X_101 := calc.lng(A2,18,6); |
| X_104:bat[:oid,:lng] := batcalc.ifthenelse(X_92,X_100,X_101); |
| X_105 := algebra.selectNotNil(X_104); |
| X_106:bat[:oid,:lng] := aggr.sum(X_105,grp206,X_81); |
| X_83 := algebra.sortTail(X_82); |
| X_84 := algebra.markT(X_83,0@0:oid); |
| X_85 := bat.reverse(X_84); |
| X_86 := algebra.leftjoin(X_85,X_82); |
| X_108 := algebra.leftjoin(X_85,X_106); |
| exit X_427; |
| X_109 := sql.resultSet(2,1,X_86); |
| sql.rsColumn(X_109,"axabas.temps_mois","c0","varchar",32,0,X_86); |
| sql.rsColumn(X_109,"axabas.","m0","decimal",18,6,X_108); |
| X_119 := io.stdout(); |
| sql.exportResult(X_119,X_109); |
| end s5_3; |
+------------------------------------------------------------------------------------------------+
257 tuples (26.460ms)
Do you
admaxg@monetdb:~$ mclient --version
mclient, the MonetDB interactive terminal (Jul2012-SP2)
support for command-line editing compiled-in
character encoding: UTF-8
admaxg@monetdb:~$ monetdb version
MonetDB Database Server Toolkit v1.0 (Jul2012-SP2)
admaxg@monetdb:~$ monetdbd version
MonetDB Database Server v1.6 (Jul2012-SP2)
admaxg@monetdb:~$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=12.04
DISTRIB_CODENAME=precise
DISTRIB_DESCRIPTION="Ubuntu 12.04.1 LTS"
Reproducible: Always
Steps to Reproduce:
I am trying to build a simple test case without success for now
Actual Results:
+------+----------------------+
| c0 | m0 |
+======+======================+
| 2009 | 1.000000 |
| 2010 | 1.000000 |
+------+----------------------+
Expected Results:
+------+----------------------+
| c0 | m0 |
+======+======================+
| 2009 | 14691.000000 |
| 2010 | 8454.000000 |
+------+----------------------+
Postgresql 9.1 computes the same result for this queries
Comment 17878
Date: 2012-11-06 10:25:12 +0100
From: matthieu.guamis
Here is the dump with the two tables to reproduce the problem:
http://ubuntuone.com/1LuEvyOcu2XtislVYpuM3g
Those two queries must have the same result but only the first works as expected.
select temps_mois.rfoperyea as c0, sum((case when dwhinv.dwhinv___rfodomide = 'PMSI' and dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' then dwhinv.dwhinvqte else 0
end)) as m0
from rfoper_temps_mois as temps_mois, dwhinv as dwhinv
where temps_mois.rfoper___rforefide = dwhinv.dwhinv___rforefide and
dwhinv.dwhinv___rforefide = 'CHCL' and
dwhinv.dwhinv___rfodomide = 'PMSI' and
dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' and
dwhinv.dwhinvdtd = temps_mois.rfoperdtd
group by c0 order by c0
select temps_mois.rfoperyea as c0, sum((case when dwhinv.dwhinv___rfodomide = 'PMSI' and dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' then dwhinv.dwhinvqte else 0
end)) as m0
from rfoper_temps_mois as temps_mois, dwhinv as dwhinv
where temps_mois.rfoper___rforefide = 'CHCL' and
dwhinv.dwhinv___rforefide = 'CHCL' and
dwhinv.dwhinv___rfodomide = 'PMSI' and
dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' and
dwhinv.dwhinvdtd = temps_mois.rfoperdtd
group by c0 order by c0
Comment 17923
Date: 2012-11-08 15:47:47 +0100
From: @mlkersten
The problem does not appear on the default (=DEVELOPMENT) branch
sql>select temps_mois.rfoperyea as c0, sum((case when dwhinv.dwhinv___rfodomide =
more>'PMSI' and dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' then
more>dwhinv.dwhinvqte else 0
more>end)) as m0
more> from rfoper_temps_mois as temps_mois, dwhinv as dwhinv
more> where temps_mois.rfoper___rforefide = dwhinv.dwhinv___rforefide and
more> dwhinv.dwhinv___rforefide = 'CHCL' and
more> dwhinv.dwhinv___rfodomide = 'PMSI' and
more> dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' and
more> dwhinv.dwhinvdtd = temps_mois.rfoperdtd
more> group by c0 order by c0 ;
+------+----------------------+
| c0 | m0 |
+======+======================+
| 2009 | 14691.000000 |
| 2010 | 8454.000000 |
+------+----------------------+
2 tuples (36.442ms)
sql>select temps_mois.rfoperyea as c0, sum((case when dwhinv.dwhinv___rfodomide =
more>'PMSI' and dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' then
more>dwhinv.dwhinvqte else 0
more>end)) as m0
more> from rfoper_temps_mois as temps_mois, dwhinv as dwhinv
more> where temps_mois.rfoper___rforefide = 'CHCL' and
more> dwhinv.dwhinv___rforefide = 'CHCL' and
more> dwhinv.dwhinv___rfodomide = 'PMSI' and
more> dwhinv.dwhinv___rfoindide = 'recN1_entrees_reel' and
more> dwhinv.dwhinvdtd = temps_mois.rfoperdtd
more> group by c0 order by c0;
+------+----------------------+
| c0 | m0 |
+======+======================+
| 2009 | 14691.000000 |
| 2010 | 8454.000000 |
+------+----------------------+
2 tuples (31.012ms)
Comment 17924
Date: 2012-11-08 16:38:24 +0100
From: @drstmane
I suspect the bug is already in the relational plan generation / optimization in Oct2012.
Comment 17971
Date: 2012-11-21 10:43:20 +0100
From: matthieu.guamis
(In reply to comment 3)
I tested it on Oct2012 compiled on Ubuntu 12.04 x64 and it works as expected
Comment 18311
Date: 2012-12-27 22:19:32 +0100
From: @mlkersten
I interpret your comment as a guidance to close the bug.
Comment 18369
Date: 2013-01-22 09:29:11 +0100
From: @sjoerdmullender
Oct2012-SP3 has been released.
The text was updated successfully, but these errors were encountered: