This is actually an error message that was recently fixed. It happens when using sum on varchar [1]. You can see in the output of your intermediate query with the json blobs in it that the numeric values are actually surrounded by quotes, so they will need to be cast to int or double to have sum evaluated against them.
[1] - https://issues.apache.org/jira/browse/DRILL-3583 On Fri, 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) >
