Search code examples
javasqljooqsql-parser

Parsing Issue with jOOQ when Using Alias on Oracle's DUAL Table

I recently encountered a parsing issue while using jOOQ to parse SQL queries containing Oracle's DUAL table with an alias. Here's a simplified version of the query that reproduces the issue:

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 Settings:

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;
    }

To provide more context, here are the relevant jOOQ settings I used:

  1. Database Configuration:
    Database source dialect: Oracle
    Destination dialect: PostgreSQL
  2. jOOQ version: 3.19.6
  3. jOOQ edition: Open Source.

Interestingly, the query works perfectly fine when the alias for the DUAL table is omitted. However, once an alias like tab is added to DUAL, the parsing fails, resulting in errors relation "dual" does not exist.

I expected jOOQ to successfully parse the query(oracle dialect) and generate the corresponding jOOQ representation(PostgreSQL). However, I encountered a parsing error related to the DUAL table alias. I tried adjusting the query and removing the alias, and in those cases, jOOQ was able to parse the query without any issues.

I'm seeking suggestions or insights on why jOOQ struggles to parse DUAL with an alias and how to resolve or work around this issue. Any assistance, solutions, or recommendations would be greatly appreciated. Thank you.

Solution

  • As of jOOQ 3.19, the jOOQ parser currently only recognises single, unaliased DUAL tables, such as:

    SELECT 1 FROM dual

    It doesn't recognise aliased DUAL tables or DUAL tables in JOIN expressions, or table lists, e.g.

    SELECT 1 FROM dual a;
SELECT 1 FROM dual, dual;

    I've created an issue to address this:

    Workaround if using the parser API

    You can work around this by post-processing your parsed Select object via the Query Object Model (QOM) API, if you're using the Parser API directly, e.g.

    // There are various ways to do this:
Query query = parser.parseQuery(sql);
if (query instanceof Select<?> s && s.$from().stream().anyMatch(...)) {
    query = ...;
}

    Workaround if using the ParsingConnection

    If you're using the ParsingConnection, given your example from your question, then you can probably implement a ParseListener, which intercepts all parsings of tables. It's not easy to remove the DUAL table from the query this way, but you can at least transform it to something equivalent, such as (SELECT 'X' DUMMY) AS DUAL

    DSLContext ctx = DSL.using(connection);
Configuration configuration = ctx.configuration();

configuration.set(ParseListener.onParseTable(c -> {
    if (c.parseKeywordIf("DUAL"))
        return select(DSL.inline("DUMMY").as("X")).asTable("DUAL");

    return null;
}));

try (Connection c = ctx.parsingConnection();
    Statement s = c.createStatement();
    ResultSet rs = s.executeQuery("select 1 from dual t, dual")
) {
    while (rs.next())
        System.out.println(rs.getString(1));
}

    As you can then see from the logs:

    Translating from : select 1 from dual t, dual                
Translating to   : select 1 from (select 'DUMMY' "X") t, (select 'DUMMY' "X") "DUAL"