[BUG] postgresql解析sql报错syntax error, expect ), actual ARRAY pos 234, line 6, column 59, token ARRAY

[dingzhenying]

Database Type

postgresql

Database Version

postgresql

Druid Version

1.2.24

JDK Version

1.8

Error SQL

SELECT a.id,
a.factory_id as factory_code,
b.bu_code,
a.auto_fields_info1,
jsonb_extract_path_text(a.auto_fields_info1, VARIADIC ARRAY['waterConsum'::text, 'submitValue'::text])::numeric AS water_usage,
jsonb_extract_path_text(a.auto_fields_info1, VARIADIC ARRAY['displacement'::text, 'submitValue'::text])::numeric AS drainage,
a.update_time AS stat_date
FROM holli_data.ods_ehs_env_pro_self_inspection_record a
LEFT JOIN holli_data.dim_factory b ON a.factory_id::text = b.factory_code::text
LEFT JOIN holli_data.dim_business_unit c ON b.bu_code::text = c.bu_code::text
WHERE a.auto_fields_info1 IS NOT NULL

Testcase Code

public static List getTableBySql(DbType dbType,String sql) {
List tableNameList = new ArrayList<>();
List stmtList = null;
try {
stmtList = SQLUtils.parseStatements(sql, dbType);
} catch (Exception e) {
e.printStackTrace();
LOG.error("sql解析异常:"+sql+" \n 异常信息:"+e);
return tableNameList;
}
SchemaStatVisitor visitor = new SchemaStatVisitor(dbType);
for (SQLStatement sqlStatement : stmtList) {
sqlStatement.accept(visitor);
//获取表名
Map<TableStat.Name, TableStat> tables = visitor.getTables();
LOG.info("解析sql的结果集:" , tables);
Set<TableStat.Name> tableNameSet = tables.keySet();
for (TableStat.Name name : tableNameSet) {
String tableName = name.getName();
if (StringUtils.isNotBlank(tableName)) {
tableNameList.add(tableName);
}
}
}
return tableNameList;
}

public static void main(String[] args) {
    String sql = "SELECT a.id,\n" +
            "    a.factory_id as factory_code,\n" +
            "    b.bu_code,\n" +
            "\t\ta.auto_fields_info1,\n" +
            "    jsonb_extract_path_text(a.auto_fields_info1, VARIADIC ARRAY['displacement'::text, 'submitValue'::text])::TEXT AS water_usage,\n" +
            "    jsonb_extract_path_text(a.auto_fields_info1, VARIADIC ARRAY['displacement'::text, 'submitValue'::text])::numeric AS drainage,\n" +
            "    a.update_time AS stat_date\n" +
            "   FROM holli_data.ods_ehs_env_pro_self_inspection_record a\n" +
            "     LEFT JOIN holli_data.dim_factory b ON a.factory_id::text = b.factory_code::text\n" +
            "     LEFT JOIN holli_data.dim_business_unit c ON b.bu_code::text = c.bu_code::text\n" +
            "  WHERE a.auto_fields_info1 IS NOT NULL";

    System.out.println(getTableBySql(DbType.postgresql,sql));
}

Stacktrace Info

No response

Error Info

com.alibaba.druid.sql.parser.ParserException: syntax error, expect ), actual ARRAY pos 234, line 6, column 59, token ARRAY
at com.alibaba.druid.sql.parser.SQLExprParser.accept(SQLExprParser.java:4724)
at com.alibaba.druid.sql.parser.SQLExprParser.methodRest(SQLExprParser.java:1972)
at com.alibaba.druid.sql.parser.SQLExprParser.parseSelectItemMethod(SQLExprParser.java:5700)
at com.alibaba.druid.sql.parser.SQLExprParser.parseSelectItem(SQLExprParser.java:5927)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseSelectList(SQLSelectParser.java:1112)
at com.alibaba.druid.sql.dialect.postgresql.parser.PGSelectParser.query(PGSelectParser.java:102)
at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:77)
at com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser.parseSelect(PGSQLStatementParser.java:615)
at com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser.parseSelect(PGSQLStatementParser.java:35)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:232)
at com.alibaba.druid.sql.SQLUtils.parseStatements(SQLUtils.java:656)
at com.alibaba.druid.sql.SQLUtils.parseStatements(SQLUtils.java:677)
at com.holli.data.assets.util.SQLParserUtil.getTableBySql(SQLParserUtil.java:138)
at com.holli.data.assets.util.SQLParserUtil.main(SQLParserUtil.java:176)