全部产品
云市场

使用coalesce函数只要超过一个expression,报错FAILED: ODPS-0130071:Semantic analysis exception

更新时间:2019-01-02 19:55:27

问题现象

使用coalesce函数只要超过一个expression,就会报错:

  1. FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 8:9 "$.table"

报错的sql如下:

  1. SELECT
  2. md5(concat(aid,bid)) as id
  3. ,aid
  4. , bid
  5. , sum(amountdue) as amountdue
  6. , coalesce(
  7. sum(regexp_count(get_json_object(extended_x, '$.table.tableParties'), '{')),
  8. decode(get_json_object(extended_x, '$.table'), NULL, 0, 1)
  9. ) as tableparty
  10. , decode(sum(headcount),null,0,sum(headcount) ) as headcount
  11. , 'a' as pt
  12. FROM e_orders
  13. where pt='20170425'
  14. group by aid, bid

问题原因

group by后面要把分组字段都要跟上,您的表达式:

  1. coalesce(
  2. sum(regexp_count(get_json_object(extended_x, '$.table.tableParties'), '{')),
  3. decode(get_json_object(extended_x, '$.table'), NULL, 0, 1)
  4. ) as tableparty
  5. , decode(sum(headcount),null,0,sum(headcount) ) as headcount

返回值实际上也是字段,需要把整个表达式写在group by后面。