drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ted Dunning <ted.dunn...@gmail.com>
Subject Re: question about aggregation, possible regression
Date Fri, 28 Aug 2015 21:48:41 GMT
Shazaam.

The cast fixes things.  Thanks to Sudheesh and Jason for fast response.



On Fri, Aug 28, 2015 at 2:47 PM, Sudheesh Katkam <skatkam@maprtech.com>
wrote:

> The bad error message was a regression. It has been fixed, see DRILL-3583 <
> https://issues.apache.org/jira/browse/DRILL-3583>.
>
> > On Aug 28, 2015, at 2:36 PM, Ted Dunning <ted.dunning@gmail.com> wrote:
> >
> > The structure is as an id and a list of structures.  The structures have
> > fields called time, values and changes.  Values and changes each have
> > elements x and y.
> >
> > I can query, flatten and generally manipulate this data without much
> > trouble.  I have problem adding up some of the values.  I don't have this
> > problem with literal values leading me to believe that there is some odd
> > interaction with data that has been flattened or something like that.
> >
> > Does anybody have any thoughts here? is this just a silly regression?
> >
> > I have data as in https://dl.dropboxusercontent.com/u/36863361/xx.json
> >
> > For example,
> >
> > 0: jdbc:drill:> select count(*) from dfs.tdunning.`xx.json`;
> > +---------+
> > | EXPR$0  |
> > +---------+
> > | 100     |
> > +---------+
> > 1 row selected (0.301 seconds)
> > 0: jdbc:drill:> select count(*) from (select id, flatten(changes) c from
> > dfs.tdunning.`xx.json`);
> > +---------+
> > | EXPR$0  |
> > +---------+
> > | 373     |
> > +---------+
> > 0: jdbc:drill:> select x.id, x.c.`values`, x.c.changes from (select id,
> > flatten(changes) c from dfs.tdunning.`xx.json`) x limit 5;
> > +-----+----------------------+--------------------+
> > | id  |        EXPR$1        |       EXPR$2       |
> > +-----+----------------------+--------------------+
> > | 0   | {"x":"44","y":"38"}  | {"x":"1","y":"0"}  |
> > | 0   | {"x":"15","y":"38"}  | {"x":"1","y":"0"}  |
> > | 0   | {"x":"20","y":"38"}  | {"x":"1","y":"0"}  |
> > | 1   | {"x":"10","y":"83"}  | {"x":"1","y":"0"}  |
> > | 1   | {"x":"97","y":"83"}  | {"x":"1","y":"0"}  |
> > +-----+----------------------+--------------------+
> >
> > I can do grouping and some kinds of aggregates as well:
> >
> > 0: jdbc:drill:> select z.id, min(z.c.x) from (select x.id id,
> x.c.`values`
> > v, x.c.changes c from (select id, flatten(changes) c from
> > dfs.tdunning.`xx.json`) x limit 20) z group by id;
> > +-----+---------+
> > | id  | EXPR$1  |
> > +-----+---------+
> > | 0   | 1       |
> > | 1   | 0       |
> > | 2   | 0       |
> > | 3   | 0       |
> > | 4   | 1       |
> > +-----+---------+
> > 5 rows selected (0.436 seconds)
> >
> > BUT sum() breaks badly:
> >
> > 0: jdbc:drill:> select z.id, sum(z.c.x) from (select x.id id,
> x.c.`values`
> > v, x.c.changes c from (select id, flatten(changes) c from
> > dfs.tdunning.`xx.json`) x limit 20) z group by id;
> > java.lang.RuntimeException: java.sql.SQLException: SYSTEM ERROR:
> > CompileException: Line 71, Column 177: Unknown variable or type "logger"
> >
> > Fragment 0:0
> >
> > [Error Id: 560ec939-d709-4ca6-a916-34c8970ac364 on
> se-node10.se.lab:31010]
> > at sqlline.IncrementalRows.hasNext(IncrementalRows.java:73)
> > at
> >
> sqlline.TableOutputFormat$ResizingRowsProvider.next(TableOutputFormat.java:87)
> > at sqlline.TableOutputFormat.print(TableOutputFormat.java:118)
> > at sqlline.SqlLine.print(SqlLine.java:1583)
> > at sqlline.Commands.execute(Commands.java:852)
> > at sqlline.Commands.sql(Commands.java:751)
> > at sqlline.SqlLine.dispatch(SqlLine.java:738)
> > at sqlline.SqlLine.begin(SqlLine.java:612)
> > at sqlline.SqlLine.start(SqlLine.java:366)
> > at sqlline.SqlLine.main(SqlLine.java:259)
> > 0: jdbc:drill:>
> >
> > on the other hand, sum works with literals
> >
> > 0: jdbc:drill:> select * from (values (1,2),(1,3),(2,1),(1,1)) z (x,y);
> > +----+----+
> > | x  | y  |
> > +----+----+
> > | 1  | 2  |
> > | 1  | 3  |
> > | 2  | 1  |
> > | 1  | 1  |
> > +----+----+
> > 4 rows selected (0.256 seconds)
> > 0: jdbc:drill:> select x,sum(y) from (values (1,2),(1,3),(2,1),(1,1)) z
> > (x,y) group by x;
> > +----+---------+
> > | x  | EXPR$1  |
> > +----+---------+
> > | 1  | 6       |
> > | 2  | 1       |
> > +----+---------+
> > 2 rows selected (0.274 seconds)
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message