[alibaba/druid]带row_number() 的hive sql语句无法解析

sql="select id,number_id,parent_id,layer_id,alias,name from (select id,number_id,parent_id,layer_id,alias,name,row_number() over(distribute by number_id sort by create_time desc,id desc) rownum from hdw_ods.ods_my_coredata__dts_device_category where pdate ='') m where m.rownum = 1"

执行List stmtList = SQLUtils.parseStatements(sql, dbType);抛异常： com.alibaba.druid.sql.parser.ParserException: syntax error, expect ), actual IDENTIFIER pos 134, line 1, column 125, token IDENTIFIER distribute at com.alibaba.druid.sql.parser.SQLExprParser.accept(SQLExprParser.java:3960) at com.alibaba.druid.sql.parser.SQLExprParser.over(SQLExprParser.java:2288) at com.alibaba.druid.sql.parser.SQLExprParser.over(SQLExprParser.java:2165) at com.alibaba.druid.sql.parser.SQLExprParser.parseAggregateExpr(SQLExprParser.java:2140) at com.alibaba.druid.sql.parser.SQLExprParser.methodRest(SQLExprParser.java:1553) at com.alibaba.druid.sql.parser.SQLExprParser.parseSelectItem(SQLExprParser.java:5406) at com.alibaba.druid.sql.parser.SQLSelectParser.parseSelectList(SQLSelectParser.java:973) at com.alibaba.druid.sql.parser.SQLSelectParser.query(SQLSelectParser.java:430) at com.alibaba.druid.sql.parser.SQLSelectParser.query(SQLSelectParser.java:372) at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:61) at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSource(SQLSelectParser.java:1002) at com.alibaba.druid.sql.parser.SQLSelectParser.parseFrom(SQLSelectParser.java:993) at com.alibaba.druid.sql.parser.SQLSelectParser.query(SQLSelectParser.java:441) at com.alibaba.druid.sql.parser.SQLSelectParser.query(SQLSelectParser.java:372) at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:61) at com.alibaba.druid.sql.parser.SQLStatementParser.parseSelect(SQLStatementParser.java:4112) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:204) at com.alibaba.druid.sql.SQLUtils.parseStatements(SQLUtils.java:558)

yanmount

druid版本为1.2.1

yanmount

v1.1.20中貌似解析结果存在问题。

select id,number_id,parent_id,layer_id,alias,name
from (
     select 
           id,number_id,parent_id,layer_id,alias,name,
           row_number() over(distribute by number_id sort by create_time desc,id desc) rownum 
     from hdw_ods.ods_my_coredata__dts_device_category 
where pdate ='') m where m.rownum = 1

SELECT id, number_id, parent_id, layer_id, alias
    , name
FROM (
    SELECT id, number_id, parent_id, layer_id, alias
        , name, row_number() OVER (DISTRIBUTE BY number_idSORT BY number_id) AS rownum
    FROM hdw_ods.ods_my_coredata__dts_device_category
    WHERE pdate = ''
) m
WHERE m.rownum = 1

Parse 后 OVER 中的 SORT BY 子句内容变了。

... sort by create_time desc,id desc ...

... SORT BY number_id ...

单测用例：com/alibaba/druid/bvt/sql/hive/HiveSelectTest_45_issue_3987

zhangyibin1991

[alibaba/druid]带row_number() 的hive sql语句无法解析

回答