[
https://issues.apache.org/jira/browse/OPTIQ-364?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated OPTIQ-364:
------------------------------
Description:
The validator rejects a valid "WITH ... ORDER BY" query, giving the error
"Aggregate expression is illegal in ORDER BY clause of non-aggregating SELECT".
Example:
{code:sql}
with q as (select * from emp)
select count(*) from q group by deptno order by count(*)
{code}
The validator is doing a rewrite on the SqlOrderBy and is adding a wrapper
SqlSelect. But its result, "select * from (...) order by count( * )" is not
valid. We should instead generate "select * from (...) order by 1".
was:
The validator rejects a valid "WITH ... ORDER BY" query, giving the error
"Aggregate expression is illegal in ORDER BY clause of non-aggregating SELECT".
Example:
{code:sql}
with q as (select * from emp)
select count(*) from q group by deptno order by count(*)
{code}
The validator is doing a rewrite on the SqlOrderBy and is adding a wrapper
SqlSelect. But its result, "select * from (...) order by count(*)" is not
valid. We should instead generate "select * from (...) order by 1".
> Validator rejects valid "WITH ... ORDER BY" query
> -------------------------------------------------
>
> Key: OPTIQ-364
> URL: https://issues.apache.org/jira/browse/OPTIQ-364
> Project: Optiq
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Julian Hyde
>
> The validator rejects a valid "WITH ... ORDER BY" query, giving the error
> "Aggregate expression is illegal in ORDER BY clause of non-aggregating
> SELECT". Example:
> {code:sql}
> with q as (select * from emp)
> select count(*) from q group by deptno order by count(*)
> {code}
> The validator is doing a rewrite on the SqlOrderBy and is adding a wrapper
> SqlSelect. But its result, "select * from (...) order by count( * )" is not
> valid. We should instead generate "select * from (...) order by 1".
--
This message was sent by Atlassian JIRA
(v6.2#6252)
