[jira] [Created] (OPTIQ-364) Validator rejects valid "WITH ... ORDER BY" query

["Julian Hyde (JIRA)" <ji...@apache.org>]

Julian Hyde created OPTIQ-364:
---------------------------------

             Summary: 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)