[
https://issues.apache.org/jira/browse/DRILL-1450?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14157507#comment-14157507
]
Julian Hyde commented on DRILL-1450:
------------------------------------
Maybe previous Calcite (formerly known as Optiq) versions had a problem with
missing ORDER BY, but it's OK now:
{code:sql}
select *, count(*) over (partition by deptno) as c from emp;
ENAME | DEPTNO | GENDER | C
-------+--------+--------+---
Adam | 50 | M | 2
Alice | 30 | F | 2
Bob | 10 | M | 2
Eric | 20 | M | 1
Eve | 50 | F | 2
Grace | 60 | F | 1
Jane | 10 | F | 2
Susan | 30 | F | 2
Wilma | | F | 1
(9 rows)
{code}
If there are other issues with Calcite please log them.
> Window function when window definition does not have order by clause would
> hit assertion error.
> ------------------------------------------------------------------------------------------------
>
> Key: DRILL-1450
> URL: https://issues.apache.org/jira/browse/DRILL-1450
> Project: Apache Drill
> Issue Type: Bug
> Reporter: Jinfeng Ni
>
> This query will hit as
> Q1:
> select sum(position_id) over w from cp.`employee.json` window w as (
> partition by position_id)
> Stack trace:
> java.lang.AssertionError
> at
> org.eigenbase.sql.validate.SqlValidatorUtil.lookup(SqlValidatorUtil.java:242)
> ~[optiq-core-0.9-drill-r3.jar:na]
> at
> org.eigenbase.sql.SqlIdentifier.getMonotonicity(SqlIdentifier.java:261)
> ~[optiq-core-0.9-drill-r3.jar:na]
> at
> org.eigenbase.sql.validate.SelectScope.getMonotonicity(SelectScope.java:149)
> ~[optiq-core-0.9-drill-r3.jar:na]
> at org.eigenbase.sql.SqlWindow.isTableSorted(SqlWindow.java:340)
> ~[optiq-core-0.9-drill-r3.jar:na]
> at org.eigenbase.sql.SqlWindow.validate(SqlWindow.java:561)
> ~[optiq-core-0.9-drill-r3.jar:na]
> The cause of this issue is the star column in schema-less system. Drill
> currently will by default add "*" to a schema-less table. In optiq, * would
> be expanded to a list of regular columns if table is schema-aware, but does
> not expand for schema-less table (That's the behavior starting from optiq
> 0.9-drill-r2). That would cause issue in some optiq code logic, since it
> does not expect * ( should already be expanded).
> For Q1, we could remove the logic of adding * by default to schema-less
> table, and it would work fine. However, if the query explicitly ask for *,
> then still, run into similar problem:
> Q2:
> select *, sum(position_id) over w from cp.`employee.json` window w as (
> partition by position_id)
> Therefore, the fix had better to be in Optiq.
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
