Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Same query and different results #3176

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Same query and different results #3176

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working normal SQL

Comments

@monetdb-team
Copy link

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 suspect the bug is already in the relational plan generation / optimization
in Oct2012.

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.

@monetdb-team monetdb-team added bug Something isn't working normal SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Nov 9, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working normal SQL
Projects
None yet
Development

No branches or pull requests

2 participants