使用jOOQ解析带别名的Oracle DUAL表时遇到解析异常

问题场景 #

使用jOOQ 3.19.6开源版解析包含带别名的Oracle DUAL表SQL时，抛出relation "dual" does not exist错误；但移除DUAL表的别名后，解析可正常完成。当前业务场景是将Oracle方言SQL转换为PostgreSQL方言。

复现代码 #

业务查询代码

private Pair<String, String> getPerEchDate(Connection dbConn) throws Exception {
    String query="select  'dtPrEch' as lib , to_char(add_months(sysdate,12),'dd/mm/yyyy') as val from dual tab";
    Pair<String, String> rtn =null;
    try (final PreparedStatement pStmt = dbConn.prepareStatement(query); ResultSet rs= pStmt.executeQuery()) {
        while(rs.next()){
            rtn= Pair.of(rs.getString(1), rs.getString(2));
        }
        return rtn;
    } catch (SQLException | ParserException e1) {
        throw new Exception(e1);
    }
}

jOOQ配置

private Settings createSettings() {
    Settings settings = new Settings()
            .withParseDialect(SQLDialect.ORACLE)
            .withParseUnknownFunctions(ParseUnknownFunctions.IGNORE)
            .withTransformTableListsToAnsiJoin(true) // transform (+) to left outer join
            .withTransformUnneededArithmeticExpressions(TransformUnneededArithmeticExpressions.ALWAYS)
            .withTransformRownum(Transformation.ALWAYS)
            .withParamType(ParamType.INLINED)
            .withParamCastMode(ParamCastMode.DEFAULT)
            .withRenderOptionalAsKeywordForFieldAliases(RenderOptionalKeyword.ON)
            .withRenderOptionalAsKeywordForTableAliases(RenderOptionalKeyword.ON)
            .withRenderQuotedNames(RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED)
            .withRenderNameCase(RenderNameCase.UPPER)
            // Oracle reads empty strings as NULLs, while PostgreSQL treats them as empty.
            // Concatenating NULL values with non-NULL characters results in that character in Oracle, but NULL in PostgreSQL.
            // So this parameter whenever there's a concat it applies coalesce(x ,'')
            .withRenderCoalesceToEmptyStringInConcat(true);
    return settings;
}

原因分析 #

jOOQ对Oracle的DUAL表有特殊优化逻辑：

• 当DUAL表不带别名时，jOOQ能识别它是Oracle专属的单列表，转换到PostgreSQL方言时会自动移除FROM DUAL子句（因为PostgreSQL不需要DUAL表就能执行无表查询）。
• 当DUAL表带有别名时，jOOQ开源版的解析器会将其视为普通用户表，而非Oracle特殊表。转换到PostgreSQL时，会尝试查找名为dual的表，而PostgreSQL默认不存在该表，因此抛出relation "dual" does not exist错误。

解决/规避方案 #

方案1：移除DUAL表的别名（最简单） #

直接修改SQL，去掉DUAL的别名，jOOQ就能正常识别并转换为PostgreSQL兼容的SQL：

select 'dtPrEch' as lib , to_char(add_months(sysdate,12),'dd/mm/yyyy') as val from dual

方案2：自定义AST处理逻辑 #

通过jOOQ的VisitListener在SQL渲染阶段，识别带别名的DUAL表并进行替换：

public class DualAliasRemover extends DefaultVisitListener {
    @Override
    public void visitStart(VisitContext ctx) {
        if (ctx.queryPart() instanceof Table<?> table) {
            // 判断是否是名为dual的表且带有别名
            if ("DUAL".equalsIgnoreCase(table.getName()) && table.getAlias() != null) {
                // 替换为无别名的DUAL表
                ctx.queryPart(DSL.table("DUAL"));
            }
        }
        super.visitStart(ctx);
    }
}

然后在配置中添加该监听器：

Configuration configuration = new DefaultConfiguration()
        .set(SQLDialect.POSTGRES)
        .set(createSettings())
        .set(new DefaultExecuteListenerProvider(new DualAliasRemover()));

方案3：直接生成PostgreSQL兼容SQL #

如果SQL是固定的，可以手动改写为PostgreSQL不需要DUAL的形式：

select 'dtPrEch' as lib , to_char(add_months(current_date,12),'dd/mm/yyyy') as val

但这种方式只适用于不需要兼容Oracle的场景，若要跨方言适配，优先选择前两种方案。

内容的提问来源于stack exchange，提问作者Raoua KHATERCHI