Re:

22 Jul 2013


      hi Lefteris,

this is my explain [querry]

-----------------------------------------------------------------------------------------+
| mal
              |
+=========================================================================================+
| function user.s3_6{autoCommit=true}(A0:int,A1:int):void;
               |
|     X_4 := nil:int;
              |
|     X_5:bat[:oid,:oid]  := nil:bat[:oid,:oid];
               |
|     X_21 := nil:bat[:oid,:int];
              |
|     X_28 := nil:bat[:oid,:str];
              |
|     X_37 := nil:bat[:oid,:str];
              |
|     X_57 := nil:bat[:oid,:oid];
              |
|     r1_70 := nil:bat[:oid,:oid];
               |
|     X_60 := nil:bat[:oid,:date];
               |
| barrier X_170 := language.dataflow();
              |
|     X_4 := sql.mvc();
              |
|     X_5:bat[:oid,:oid]  := sql.tid(X_4,"sys","esubstation");
               |
|     X_8 := sql.bind(X_4,"sys","esubstation","etime",0);
              |
|     (X_11,r1_11) := sql.bind(X_4,"sys","esubstation","etime",2);
               |
|     X_14 := sql.bind(X_4,"sys","esubstation","etime",1);
               |
|     X_16 := sql.delta(X_8,X_11,r1_11,X_14);
              |
|     X_17 := algebra.leftfetchjoin(X_5,X_16);
               |
|     X_18:bat[:oid,:str]  := batcalc.str(10,1,0,0,X_17,0);
              |
|     X_20:bat[:oid,:str]  := batstr.substring(X_18,A0,A1);
              |
|     X_21 := batcalc.int(X_20);
               |
|     X_22 := sql.bind(X_4,"sys","esubstation","loc_id",0);
              |
|     (X_24,r1_31) := sql.bind(X_4,"sys","esubstation","loc_id",2);
              |
|     X_26 := sql.bind(X_4,"sys","esubstation","loc_id",1);
              |
|     X_27 := sql.delta(X_22,X_24,r1_31,X_26);
               |
|     X_28 := algebra.leftfetchjoin(X_5,X_27);
               |
|     X_29 := sql.bind(X_4,"sys","esubstation","ss_id",0);
               |
|     (X_33,r1_42) := sql.bind(X_4,"sys","esubstation","ss_id",2);
               |
|     X_35 := sql.bind(X_4,"sys","esubstation","ss_id",1);
               |
|     X_36 := sql.delta(X_29,X_33,r1_42,X_35);
               |
|     X_37 := algebra.leftfetchjoin(X_5,X_36);
               |
|     X_38 := sql.bind(X_4,"sys","esubstation","edate",0);
               |
|     (X_41,r1_52) := sql.bind(X_4,"sys","esubstation","edate",2);
               |
|     X_44 := sql.bind(X_4,"sys","esubstation","edate",1);
               |
|     X_46 := sql.delta(X_38,X_41,r1_52,X_44);
               |
|     X_47 := algebra.leftfetchjoin(X_5,X_46);
               |
|     (X_48,r1_61,r2_61) := group.subgroup(X_47);
              |
|     (X_51,r1_64,r2_64) := group.subgroup(X_37,X_48);
               |
|     (X_54,r1_67,r2_67) := group.subgroup(X_28,X_51);
               |
|     (X_57,r1_70,r2_70) := group.subgroupdone(X_21,X_54);
               |
|     X_60 := algebra.leftfetchjoin(r1_70,X_47);
               |
|     language.pass(X_47);
               |
| exit X_170;
              |
|     X_62 := sql.append(X_4,"sys","esubstation_hour","edate",X_60);
               |
|     X_66 := algebra.leftfetchjoin(r1_70,X_21);
               |
|     X_67 := sql.append(X_62,"sys","esubstation_hour","ehour",X_66);
              |
|     X_69 := algebra.leftfetchjoin(r1_70,X_37);
               |
|     X_71 := sql.append(X_67,"sys","esubstation_hour","ss_id",X_69);
              |
|     X_73 := algebra.leftfetchjoin(r1_70,X_28);
               |
|     X_74 := sql.append(X_71,"sys","esubstation_hour","loc_id",X_73);
               |
|     X_98:bat[:oid,:int]  := nil:bat[:oid,:int];
              |
| barrier X_193 := language.dataflow();
              |
|     X_77 := sql.bind(X_74,"sys","esubstation","tot_active_power",0);
               |
|     (X_80,r1_128) :=
sql.bind(X_74,"sys","esubstation","tot_active_power",2);           |
|     X_83 := sql.bind(X_74,"sys","esubstation","tot_active_power",1);
               |
|     X_85 := sql.delta(X_77,X_80,r1_128,X_83);
              |
|     X_86 := algebra.leftfetchjoin(X_5,X_85);
               |
|     X_87:bat[:oid,:dbl]  := batcalc.dbl(4,X_86);
               |
|     X_89:bat[:oid,:dbl]  := aggr.subsum(X_87,X_57,r1_70,true,true);
              |
|     X_91:bat[:oid,:wrd]  := aggr.subcount(X_87,X_57,r1_70,true);
               |
|     X_93:bat[:oid,:bit]  := batcalc.==(X_91,0:wrd);
              |
|     X_95 := batcalc.dbl(X_91);
               |
|     X_96:bat[:oid,:dbl]  := batcalc.ifthenelse(X_93,nil:dbl,X_95);
               |
|     X_97:bat[:oid,:dbl]  := batcalc./(X_89,X_96);
              |
|     X_98:bat[:oid,:int]  := batcalc.int(X_97,5,4);
               |
|     language.pass(X_87);
               |
|     language.pass(X_91);
               |
| exit X_193;
              |
|     X_101 :=
sql.append(X_74,"sys","esubstation_hour","tot_active_power_h",X_98);       |
|     X_126:bat[:oid,:int]  := nil:bat[:oid,:int];
               |
| barrier X_200 := language.dataflow();
              |
|     X_104 :=
sql.bind(X_101,"sys","esubstation","phase_differ_voltage",0);              |
|     (X_108,r1_178) :=
sql.bind(X_101,"sys","esubstation","phase_differ_voltage",2);     |
|     X_111 :=
sql.bind(X_101,"sys","esubstation","phase_differ_voltage",1);              |
|     X_113 := sql.delta(X_104,X_108,r1_178,X_111);
              |
|     X_114 := algebra.leftfetchjoin(X_5,X_113);
               |
|     X_115:bat[:oid,:dbl]  := batcalc.dbl(6,X_114);
               |
|     X_117:bat[:oid,:dbl]  := aggr.subsum(X_115,X_57,r1_70,true,true);
              |
|     X_119:bat[:oid,:wrd]  := aggr.subcount(X_115,X_57,r1_70,true);
               |
|     X_121:bat[:oid,:bit]  := batcalc.==(X_119,0:wrd);
              |
|     X_123 := batcalc.dbl(X_119);
               |
|     X_124:bat[:oid,:dbl]  := batcalc.ifthenelse(X_121,nil:dbl,X_123);
              |
|     X_125:bat[:oid,:dbl]  := batcalc./(X_117,X_124);
               |
|     X_126:bat[:oid,:int]  := batcalc.int(X_125,8,6);
               |
|     language.pass(X_115);
              |
|     language.pass(X_119);
              |
| exit X_200;
              |
|     X_129 :=
sql.append(X_101,"sys","esubstation_hour","phase_differ_voltage_h",X_126); |
|     X_154:bat[:oid,:int]  := nil:bat[:oid,:int];
               |
| barrier X_207 := language.dataflow();
              |
|     X_132 := sql.bind(X_129,"sys","esubstation","stn_bat_voltage",0);
              |
|     (X_136,r1_228) :=
sql.bind(X_129,"sys","esubstation","stn_bat_voltage",2);          |
|     X_139 := sql.bind(X_129,"sys","esubstation","stn_bat_voltage",1);
              |
|     X_141 := sql.delta(X_132,X_136,r1_228,X_139);
              |
|     X_142 := algebra.leftfetchjoin(X_5,X_141);
               |
|     X_143:bat[:oid,:dbl]  := batcalc.dbl(5,X_142);
               |
|     X_145:bat[:oid,:dbl]  := aggr.subsum(X_143,X_57,r1_70,true,true);
              |
|     X_147:bat[:oid,:wrd]  := aggr.subcount(X_143,X_57,r1_70,true);
               |
|     X_149:bat[:oid,:bit]  := batcalc.==(X_147,0:wrd);
              |
|     X_151 := batcalc.dbl(X_147);
               |
|     X_152:bat[:oid,:dbl]  := batcalc.ifthenelse(X_149,nil:dbl,X_151);
              |
|     X_153:bat[:oid,:dbl]  := batcalc./(X_145,X_152);
               |
|     X_154:bat[:oid,:int]  := batcalc.int(X_153,8,5);
               |
|     language.pass(X_143);
              |
|     language.pass(X_57);
               |
|     language.pass(r1_70);
              |
|     language.pass(X_147);
              |
| exit X_207;
              |
|     X_157 :=
sql.append(X_129,"sys","esubstation_hour","stn_bat_voltage_h",X_154);      |
|     X_160 := aggr.count(X_154);
              |
|     sql.affectedRows(X_157,X_160,"");
              |
| end s3_6;

On Mon, Jul 22, 2013 at 1:38 PM, Stefan Manegold wrote:
...
Ketan,
could you please also share the schema of your enzensubstation table?
Does the select query (without insert part) work fine?
If not, could you please also share the EXPLAIN of the select part?
Thanks!
Stefan
----- Original Message -----
...
can you also please send the output of
explain [query]
where [query] is the insert query. So we can see which leftfetchjoin
complains? Run twice the query, one with explain and one alone and
send both outputs exactly as they appear.
Thank you
On Mon, Jul 22, 2013 at 10:37 AM, Ketan Sharma 
wrote:
...
hi Lefteris,
Thanks For help me...
MonetDB version that we are using is:  MonetDB v11.15.11
This is the Schema of my table:::
create table enzensubstation_hour(
edate date,
eHour int,
ss_id varchar(50),
loc_id varchar(50),
tot_active_power_h decimal(5,4),
phase_differ_voltage_h decimal(8,6),
stn_bat_voltage_h decimal(8,5)
);
insert into enzensubstation_hour
SELECT
edate,
CAST(SUBSTRING(etime,0,2) AS int) AS eHour,
ss_id,
loc_id,
avg(tot_active_power) as tot_active_power_h,
avg(phase_differ_voltage) as phase_differ_voltage_h,
avg(stn_bat_voltage) as stn_bat_voltage_h
FROM enzensubstation
group by
edate,
ehour,
ss_id,
loc_id
;
Thanks
Ketan Sharma
On Mon, Jul 22, 2013 at 12:55 PM, Lefteris  wrote:
...
Hi Ketan,
This does not sound right.
could you please tell us which MonetDB version are you using, also
could you give us the complete query, as well as the output of running
the query prefixed with "explain" ?
Also can you tell us the schema of the table and if you have any
constraints on it, such as primary key?
thank you,
lefteris
On Mon, Jul 22, 2013 at 9:06 AM, Ketan Sharma 
wrote:
...
...
Hi all,
I'm Ketan and I'm new to Monet DB.  I have successfully installed
MonetDB.
I have a table of size ~200MB which is to be inserted into another
MonetDB
Table.
But while inserting data using SELECT command, I am getting the
following
error:
#================================================
GDK reported error.
BATfetchjoin(tmpr_2257,tmp_
2241) does not hit always (|bn|=0 != 10=|l|) => can't use fetchjoin.
#================================================
Please do help me in getting this error solved.
Thanks.
_______________________________________________
developers-list mailing list
developers-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list
_______________________________________________
developers-list mailing list
developers-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list
_______________________________________________
developers-list mailing list
developers-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list
_______________________________________________
developers-list mailing list
developers-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list
--
| Stefan.Manegold@CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |
_______________________________________________
developers-list mailing list
developers-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list