sqlpostgresqlparsingapache-calcite

Casting operator :: not being parsed by Apache Calcite

I'm encountering a particular issue with a simple Apache Calcite planner I've set up using the following code:

SqlParser.Config parserConfig = SqlParser.configBuilder()
                .setCaseSensitive(false)
                .setUnquotedCasing(Casing.UNCHANGED)
                .setQuotedCasing(Casing.UNCHANGED)
                .setConformance(SqlConformanceEnum.BABEL)
                .build();

FrameworkConfig frameworkConfig = Frameworks.newConfigBuilder()
                .parserConfig(parserConfig)
                .operatorTable(SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
                        SqlLibrary.POSTGRESQL))
                .sqlValidatorConfig(SqlValidator.Config.DEFAULT.withIdentifierExpansion(true))
                .defaultSchema(connection.getRootSchema().getSubSchema(defaultSchema))
                .build();

Planner planner = Frameworks.getPlanner(frameworkConfig);

Now, I'm trying to parse the following query using the planner I created (SqlNode parsedQuery = planner.parse(sql);):

SELECT lineitem.l_shipdate :: text AS l_shipdate
FROM lineitem

The query executes without any problems in PostgreSQL. However, when attempting to parse it with Calcite, I encounter the following exception:

Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException: Encountered ":" at line 1, column 28.
Was expecting one of:
    "UESCAPE" ...
    <QUOTED_STRING> ...
    ")" ...
    "," ...
    "." ...
    "NOT" ...
    "IN" ...
    "<" ...
    "<=" ...
    ">" ...
    ">=" ...
    "=" ...
    "<>" ...
    "!=" ...
    "BETWEEN" ...
    "LIKE" ...
    "ILIKE" ...
    "RLIKE" ...
    "SIMILAR" ...
    "+" ...
    "-" ...
    "*" ...
    "/" ...
    "%" ...
    "||" ...
    "AND" ...
    "OR" ...
    "IS" ...
    "MEMBER" ...
    "SUBMULTISET" ...
    "CONTAINS" ...
    "OVERLAPS" ...
    "EQUALS" ...
    "PRECEDES" ...
    "SUCCEEDS" ...
    "IMMEDIATELY" ...
    "MULTISET" ...
    "[" ...
    "FORMAT" ...
    
    at org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:408)
    at org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:154)
    at org.apache.calcite.sql.parser.SqlParser.handleException(SqlParser.java:159)
    at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:174)
    at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:199)
    at org.apache.calcite.prepare.PlannerImpl.parse(PlannerImpl.java:216)
    at org.apache.calcite.tools.Planner.parse(Planner.java:50)
    at de.tub.dima.mascara.parser.Parser.getLogicalPlan(Parser.java:67)
    at de.tub.dima.mascara.Playground.main(Playground.java:34)

I've tried setting the conformance of the parser to multiple values, such as SqlConformanceEnum.BABEL, SqlConformanceEnum.BIG_QUERY, etc., but still without success. This issue is perplexing because this type of operator should be supported (as indicated in the documentation).

I appreciate any assistance you can provide. Thank you in advance!

EDIT: I removed the information about the UDF I created because is irrelevant to the problem of parsing the cast operator ::

Solution

  • Apache Calcite has three built-in parser implementations:

    The parser implementation can be chosen by calling one of the methods below:

    In this case you are using the Postgres cast operator '::' which was introduced in the Babel parser in CALCITE-2843 so in this case you have to pass SqlBabelParserImpl.FACTORY to one of the methods above.

    SqlConformance is a supplementary configuration that can fine tune the behavior of the parser & validator no matter which parser implementation is used underneath.