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)
