Hello,
Can anyone explain why adding "2*" in the following query results in an error:
Original Query: sql>select (CAST(now()-'1970-01-01 00:00:00' as BIGINT)) as minuto,avg(CAST(N6_end - trStart as BIGINT)) as promedio from testquery where trStart>'2016-10-06 00:00:00.000000' GROUP BY minuto; +---------------+--------------------------+ | minuto | promedio | +===============+==========================+ | 1475770734000 | null | +---------------+--------------------------+
Query with "2*": sql>select 2*CAST(now()-'1970-01-01 00:00:00' as BIGINT) as minuto,avg(CAST(N6_end - trStart as BIGINT)) as promedio from testquery where trStart>'2016-10-06 00:00:00.000000' GROUP BY minuto; TypeException:user.s20_1[10]:'group.subgroupdone' undefined in: (X_15:any,r1_15:any,r2_15:any) := group.subgroupdone(X_14:hge); program contains errors
Explain: ------------------------------------------------------------ ------------------------------------------------------------ ---------------------+ | mal
| +=========================================================== ============================================================ ======================+ | function user.s22_1(A0:bte,A1:str,A2:str):void; | sql.mvc();
| | X_5:timestamp := mtime.current_timestamp();
| | A1;
| | calc.timestamp(X_6,7,1);
| | X_10:lng := mtime.diff(X_5,X_7);
| | calc.lng(X_10);
| | calc.hge(X_11);
| | A0;
| | X_14:hge := calc.*(X_12,X_13);
| | (X_15,r1_15,r2_15) := group.subgroupdone(X_14);
| | algebra.projection(r1_15,X_14);
| | bat.setKey(X_18,true);
| | X_21:bat[:oid] := sql.tid(X_4,"sys","testquery") ; | | X_24:bat[:timestamp] := sql.bind(X_4,"sys","testquery" ,"trstart",0); | | (X_27,r1_27) := sql.bind(X_4,"sys","testquery" ,"trstart",2); | | X_30:bat[:timestamp] := sql.bind(X_4,"sys","testquery" ,"trstart",1); | | sql.delta(X_24,X_27,r1_27,X_30);
| | algebra.projection(X_21,X_31);
| | X_33:bat[:timestamp] := mal.multiplex("calc","timestam p",X_32,7); | | A2;
| | calc.timestamp(X_36,7);
| | algebra.thetasubselect(X_33,X_37,">");
| | X_40:bat[:timestamp] := sql.bind(X_4,"sys","testquery" ,"n6_end",0); | | (X_42,r1_42) := sql.bind(X_4,"sys","testquery" ,"n6_end",2); | | X_44:bat[:timestamp] := sql.bind(X_4,"sys","testquery" ,"n6_end",1); | | sql.delta(X_40,X_42,r1_42,X_44);
| | algebra.projection(X_21,X_45);
| | algebra.projection(X_38,X_46);
| | algebra.projection(X_38,X_32);
| | X_49:bat[:lng] := mal.multiplex("mtime","diff",X _47,X_48); | | batcalc.lng(X_49);
| | batcalc.dbl(X_52);
| | X_54:bat[:dbl] := aggr.subavg(X_53,X_15,r1_15,tr ue,true); | | bat.new(nil:oid,nil:str);
| | bat.new(nil:oid,nil:str);
| | bat.new(nil:oid,nil:str);
| | bat.new(nil:oid,nil:int);
| | bat.new(nil:oid,nil:int);
| | bat.append(X_58,".L1");
| | bat.append(X_61,"minuto");
| | bat.append(X_62,"hugeint");
| | bat.append(X_63,72);
| | bat.append(X_65,0);
| | bat.append(X_66,"sys.L3");
| | bat.append(X_68,"promedio");
| | bat.append(X_70,"double");
| | bat.append(X_72,53);
| | bat.append(X_74,0);
| | sql.resultSet(X_76,X_78,X_80,X_82,X_84,X_19,X_54);
| | end user.s22_1;
| | # querylog.define("explain select 2*cast(now()-\'1970-01-01 00:00:00\' as bigint) as minuto,avg(cast(n6_end - trstart as bigint)) as prom | : edio from testquery where trstart>\'2016-10-06 00:00:00.000000\' group by minuto;","default_pipe") :
Environment: - MonetDB v11.23.7 (Jun2016-SP1) from release package - Centos and Ubuntu - Create table attached - The type of the columns in the query is timestamp - A previous versions of this email was rejected because the "create table" statement is too big for the 40Kbytes limit of this list.
Thanks Jose Cavieres