Ketan Sharma ketan780 at gmail.com
Mon Jul 22 10:15:28 CEST 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 <Stefan.Manegold at cwi.nl>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 <ketan780 at gmail.com>
> 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 <lsidir at gmail.com> 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 <ketan780 at gmail.com>
> 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 at monetdb.org
> > >> > http://mail.monetdb.org/mailman/listinfo/developers-list
> > >> >
> > >> _______________________________________________
> > >> developers-list mailing list
> > >> developers-list at monetdb.org
> > >> http://mail.monetdb.org/mailman/listinfo/developers-list
> > >
> > >
> > >
> > > _______________________________________________
> > > developers-list mailing list
> > > developers-list at monetdb.org
> > > http://mail.monetdb.org/mailman/listinfo/developers-list
> > >
> > _______________________________________________
> > developers-list mailing list
> > developers-list at monetdb.org
> > http://mail.monetdb.org/mailman/listinfo/developers-list
> >
>
> --
> | Stefan.Manegold at 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 at monetdb.org
> http://mail.monetdb.org/mailman/listinfo/developers-list
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/developers-list/attachments/20130722/633ac8d1/attachment.html>


More information about the developers-list mailing list