查询ODPS数据报ORDER BY must be used with a LIMIT clause, please set odps.sql.validate.orderby.limit=false

在使用Java JDBC查询ODPS上的数据时，若使用了Order By，会遇到如下错误：

failed: ODPS-0130071:[1,307] Semantic analysis exception - ORDER BY must be used with a LIMIT clause, please set odps.sql.validate.orderby.limit=false to use it.

出错原因：没有加Limit，添加LIMIT 10000（limit的值根据业务自由设置）后解决问题。

 

阿里云官网解释如下：

 

order.by.no.limit

说明：MaxCompute默认order by后需要增加limit限制数量，因为order by是全量排序，没有limit时执行性能较低。

示例

• 错误写法

  select * from (select *
  from (select cast(login_user_cnt as int) as uv, '3' as shuzi
  from test_login_cnt where type = 'device' and type_name = 'mobile') v
  order by v.uv desc) v
  order by v.shuzi limit 20;

• 报错信息

  FAILED: ODPS-0130071:[4,1] Semantic analysis exception - ORDER BY must be used with a LIMIT clause

在子查询order by v.uv desc中增加limit。

另外，MaxCompute 1.0对于view的检查不够严格。比如在一个不需要检查limit的Project（odps.sql.validate.orderby.limit=false）中，创建了一个View。

create view table_view as select id from table_view order by id;

若访问此View：

select * from table_view;

MaxCompute 1.0不会报错，而MaxCompute 2.0会报如下错误信息：

FAILED: ODPS-0130071:[1,15] Semantic analysis exception - while resolving view xdj.xdj_view_limit - ORDER BY must be used with a LIMIT clause