[CALCITE-364] Validator rejects valid "WITH ... ORDER BY" query

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.9.0-incubating
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 07/Aug/14 21:21
• Updated: 25/Aug/14 19:10
• Resolved: 08/Aug/14 00:12

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:

with q as (select * from emp)
select count(*) from q group by deptno order by count(*)

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".