[ https://issues.apache.org/jira/browse/DRILL-2770?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14612563#comment-14612563 ]
Jinfeng Ni commented on DRILL-2770: ----------------------------------- Could you please re-run the query, and update the status? Also, please upload the 'countires.csv' file to this JIRA, as long as it does not contain sensitive data. As a general rule, if the issue is related to data, it makes sense to include some sample data. I run the queries against a different csv file, and seems got the expected results: {code} select max(columns[0]) from (select * from `countries.csv` offset 1) tmp order by tmp.columns[1]; {code} This is not a valid SQL query, since you put max(), yet there is no GROUP BY, but ORDER BY clause. Here is what I got for a similar query: {code} select max(columns[0]) from (select * from dfs.`/Users/jni/work/data/csv/nation.csv` offset 1) tmp order by tmp.columns[1]; SEVERE: org.apache.calcite.runtime.CalciteContextException: From line 1, column 109 to line 1, column 111: Expression 'tmp.*' is not being grouped Error: PARSE ERROR: From line 1, column 109 to line 1, column 111: Expression 'tmp.*' is not being grouped {code} Remove the oder by clause { {code} select max(columns[0]) from (select * from dfs.`/Users/jni/work/data/csv/nation.csv` offset 1) tmp; +---------+ | EXPR$0 | +---------+ | 3 | +---------+ 1 row selected (0.511 seconds) {code} > Aggregate query returns AssertionError: star should have been expanded > ---------------------------------------------------------------------- > > Key: DRILL-2770 > URL: https://issues.apache.org/jira/browse/DRILL-2770 > Project: Apache Drill > Issue Type: Bug > Components: Query Planning & Optimization > Affects Versions: 0.9.0 > Environment: | 393a8affdab9b93093a7afcc81d016e720d7781f | MD-192: > CONVERT_FROM in where clause | 25.03.2015 @ 17:57:28 EDT > Reporter: Khurram Faraaz > Assignee: Khurram Faraaz > Fix For: 1.2.0 > > > Aggregate query that should return maximum value, reports an AssertionError. > Test was performed on 4 node cluster on CentOS. > {code} > 0: jdbc:drill:> select max(columns[0]) from (select * from `countries.csv` > offset 1) tmp order by tmp.columns[1]; > Query failed: AssertionError: star should have been expanded > Error: exception while executing query: Failure while executing query. > (state=,code=0) > 0: jdbc:drill:> select max(tmp.columns[0]) from (select * from > `countries.csv` offset 1) tmp order by tmp.columns[1]; > Query failed: AssertionError: star should have been expanded > Error: exception while executing query: Failure while executing query. > (state=,code=0) > {code} > Aggregate query to get the maximum value from columns[0] > {code} > 0: jdbc:drill:> select max(columns[0]) from `countries.csv`; > +------------+ > | EXPR$0 | > +------------+ > | 302802,"VE","Venezuela","SA","http://en.wikipedia.org/wiki/Venezuela", | > +------------+ > 1 row selected (0.372 seconds) > {code} > query without the order by, returns results. Although it should have returned > just the maximum value in that column, we also see some other data returned > by the query. We do not see the assertion when the order by is removed. > {code} > 0: jdbc:drill:> select max(tmp.columns[0]) from (select * from > `countries.csv` offset 1) tmp; > +------------+ > | EXPR$0 | > +------------+ > | 302802,"VE","Venezuela","SA","http://en.wikipedia.org/wiki/Venezuela", | > +------------+ > 1 row selected (0.192 seconds) > {code} > Note that there is header information in the CSV file in the first row. > {code} > 0: jdbc:drill:> select * from `countries.csv` limit 2; > +------------+ > | columns | > +------------+ > | ["\"id\",\"code\",\"name\",\"continent\",\"wikipedia_link\",\"keywords\""] | > | > ["302672,\"AD\",\"Andorra\",\"EU\",\"http://en.wikipedia.org/wiki/Andorra\","] > | > +------------+ > 2 rows selected (0.14 seconds) > {code} > Snippet from CSV data file > {code} > [root@centos-01 airport_CSV_data]# head -10 countries.csv > "id","code","name","continent","wikipedia_link","keywords" > 302672,"AD","Andorra","EU","http://en.wikipedia.org/wiki/Andorra", > 302618,"AE","United Arab > Emirates","AS","http://en.wikipedia.org/wiki/United_Arab_Emirates","UAE" > 302619,"AF","Afghanistan","AS","http://en.wikipedia.org/wiki/Afghanistan", > 302722,"AG","Antigua and > Barbuda","NA","http://en.wikipedia.org/wiki/Antigua_and_Barbuda", > 302723,"AI","Anguilla","NA","http://en.wikipedia.org/wiki/Anguilla", > 302673,"AL","Albania","EU","http://en.wikipedia.org/wiki/Albania", > 302620,"AM","Armenia","AS","http://en.wikipedia.org/wiki/Armenia", > 302556,"AO","Angola","AF","http://en.wikipedia.org/wiki/Angola", > 302615,"AQ","Antarctica","AN","http://en.wikipedia.org/wiki/Antarctica", > {code} > Stack trace from drillbit.log > {code} > 2015-04-13 20:09:18,198 [2ad3dd91-0f3b-b882-8ae6-45f8ad208fb6:foreman] ERROR > o.a.drill.exec.work.foreman.Foreman - Error > 4eee026f-6235-45a8-84b1-dd8302edec3c: AssertionError: star should have been > expanded > org.apache.drill.exec.work.foreman.ForemanException: Unexpected exception > during fragment initialization: star should have been expanded > at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:213) > [drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) > [na:1.7.0_75] > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) > [na:1.7.0_75] > at java.lang.Thread.run(Thread.java:745) [na:1.7.0_75] > Caused by: java.lang.AssertionError: star should have been expanded > at org.eigenbase.sql.validate.AggChecker.visit(AggChecker.java:81) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.sql.validate.AggChecker.visit(AggChecker.java:31) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.sql.SqlIdentifier.accept(SqlIdentifier.java:222) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:107) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.sql.SqlOperator.acceptCall(SqlOperator.java:688) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.sql.validate.AggChecker.visit(AggChecker.java:139) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.sql.validate.AggChecker.visit(AggChecker.java:31) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.sql.SqlCall.accept(SqlCall.java:125) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:107) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.sql.SqlOperator.acceptCall(SqlOperator.java:688) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.sql.validate.AggChecker.visit(AggChecker.java:139) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.sql.validate.AggChecker.visit(AggChecker.java:31) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.sql.SqlCall.accept(SqlCall.java:125) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:155) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.AggregatingSelectScope.validateExpr(AggregatingSelectScope.java:164) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.OrderByScope.validateExpr(OrderByScope.java:100) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:3150) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.SqlValidatorImpl.validateOrderItem(SqlValidatorImpl.java:2965) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.SqlValidatorImpl.validateOrderList(SqlValidatorImpl.java:2949) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2829) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:85) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:785) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:774) > ~[optiq-core-0.9-drill-r20.jar:na] > at org.eigenbase.sql.SqlSelect.validate(SqlSelect.java:211) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:748) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:464) > ~[optiq-core-0.9-drill-r20.jar:na] > at > net.hydromatic.optiq.prepare.PlannerImpl.validate(PlannerImpl.java:174) > ~[optiq-core-0.9-drill-r20.jar:na] > at > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:157) > ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:133) > ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:145) > ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at > org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:773) > [drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:204) > [drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT] > ... 3 common frames omitted > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)