Shazaam. The cast fixes things. Thanks to Sudheesh and Jason for fast response.
On Fri, Aug 28, 2015 at 2:47 PM, Sudheesh Katkam <[email protected]> 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 <[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) > >
