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 <[email protected]> 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)
