Hi, Amihay, The problem is that ibis.cpp does not expect the incoming query to have an explicit group-by clause - the group-by operation is specified implicitly when any aggregation function appears in the select clause. Your query
SELECT returnflag,linestatus, sum(quantity) as sum_qty,sum(price*(1-discount)) as sum_disc_price,sum(price*(1-discount)*(1+tax)) as sum_charge, avg(quantity) as avg_qty, avg(price) as avg_price, avg(discount) as avg_disc, count(*) as count_order FROM lineitem WHERE shipdate <= 904608000 group by returnflag, linestatus ORDER BY returnflag,linestatus; needs to be SELECT returnflag,linestatus, sum(quantity) as sum_qty,sum(price*(1-discount)) as sum_disc_price,sum(price*(1-discount)*(1+tax)) as sum_charge, avg(quantity) as avg_qty, avg(price) as avg_price, avg(discount) as avg_disc, count(*) as count_order FROM lineitem WHERE shipdate <= 904608000 You can add back the ORDER By clause, but the Group By clause has to be omitted. John On 3/17/13 6:05 PM, amihay gonen wrote: > Hi all , > I'm trying to implement Q1 from TPCH, I've converted date to eopch , > but when trying to run the query i get warning (see bellow). > The results seems ok , but i don't understand why i get the warning. > > another question , I've convert dates to eopch , this is the simple > way to handle dates , but i wander if I'll seperate the dates to > different columns (year ,month,date ...) if it will be better in term > of fastbit performance. > > > > Query: > $ibis -o $out/q1.lst -q 'SELECT returnflag,linestatus, > sum(quantity) as sum_qty,sum(price*(1-discount)) as > sum_disc_price,sum(price*(1-discount)*(1+tax)) as sum_charge, > avg(quantity) as avg_qty, avg(price) as avg_price, > avg(discount) as avg_disc, count(*) as count_order FROM > lineitem WHERE shipdate <= 904608000 group by returnflag, > linestatus ORDER BY returnflag,linestatus;' -d $db/lineitem > > Result: > Constructed a part named lineitem > Warning -- ibis::whereParser encountered syntax error, unexpected name > string at location shipdate <= 904608000 group by > returnflag,linestatus :1.22-27 > Warning -- whereClause(shipdate <= 904608000 group by > returnflag,linestatus ) failed to parse the string into an expression > tree > filter::sift0(SELECT returnflag,linestatu ... FROM 1 data partition) > -- processing data partition lineitem > tableSelect -- select(returnflag,linestatus,sum(quantity) as > sum_qty,sum(price*(1-discount)) as > sum_disc_price,sum(price*(1-discount)*(1+tax)) as > sum_charge,avg(quantity) as avg_qty, avg(price) as avg_price, > avg(discount) as avg_disc, count(*) as count_order , shipdate <= > 904608000 group by returnflag,linestatus ) on table T-lineitem > produced a table with 4 rows and 9 columns > bord[_cKdz]::reorder -- duration: 0 sec(CPU), 4.2614e-05 sec(elapsed) > tableSelect:: complete evaluation of SELECT > returnflag,linestatus,sum(quantity) as sum_qty,sum(price*(1-discount)) > as sum_disc_price,sum(price*(1-discount)*(1+tax)) as > sum_charge,avg(quantity) as avg_qty, avg(price) as avg_price, > avg(discount) as avg_disc, count(*) as count_order FROM T-lineitem > WHERE shipdate <= 904608000 group by returnflag,linestatus ORDER BY > returnflag,linestatus; took 3.646 CPU seconds, 3.78151 elapsed seconds > /home/agonen/Code/fastbit-ibis1.3.5/examples/.libs/lt-ibis -- total > CPU time 3.675 s, total elapsed time 3.81539 s > > > > _______________________________________________ > FastBit-users mailing list > [email protected] > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users > _______________________________________________ FastBit-users mailing list [email protected] https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
