Support DB2 selectivity-Keyword in Query-Parser

[marco135]

Use case:

We want to parse custom SQL using the parse function. Since users can write the SQL freely, we have no influence on the keywords used. Currently, the parsing of the query fails when the keyword selectivity is used.

Possible solution you'd like to see:

Here is a minimal test setup with DB2 standard tables:

DefaultConfiguration config = new DefaultConfiguration();
config.setSQLDialect(SQLDialect.DB2_11);
DSLContext dslContextDb2 = new DefaultDSLContext(config);
String queryString =
                "select distinct TBCREATOR "
                        + "from sysibm.syscolumns "
                        + "WHERE  TBCREATOR  like 'SYS%' "
                        + "selectivity 0.5 ";
Query query = dslContextDb2.parser().parseQuery(queryString);

The following error is currently thrown

org.jooq.impl.ParserException: Unexpected clause: [1:80] ...m sysibm.syscolumns WHERE  TBCREATOR  like 'SYS%' [*]selectivity 1 
	at org.jooq.impl.DefaultParseContext.exception(ParserImpl.java:14129)
	at org.jooq.impl.DefaultParseContext.done(ParserImpl.java:14315)
	at org.jooq.impl.DefaultParseContext.lambda$parseQuery0$2(ParserImpl.java:936)
	at org.jooq.impl.DefaultParseContext.wrap(ParserImpl.java:14322)
	at org.jooq.impl.DefaultParseContext.parseQuery0(ParserImpl.java:936)
	at org.jooq.impl.ParserImpl.parseQuery(ParserImpl.java:753)
	at org.jooq.impl.ParserImpl.parseQuery(ParserImpl.java:748)

Possible workarounds:

I have tried to implement a workaround via ParseListener, but I have not been able to do it.

Versions:

• jOOQ: 3.17.1
• Java: 1.8
• Database (include vendor): DB2 v.11.5.7.0
• OS:
• JDBC Driver (include name if unofficial driver):

[lukaseder]

Thanks a lot for your feature request. Parsing custom operators is indeed a bit tricky right now using the ParseListener. There's a pending feature request for this: #12339. We'll have to sort out operator precedence for that, so there are still a few open questions.

The simplest workaround for you right now is to implement ParseListener::parseStart and just regex-replace any (?i:SELECTIVITY \d\.\d) regular expression.

[marco135]

That's right, simply replacing the expression with a regex is certainly a workaround for now. Thank you!

[lukaseder]

Starting from jOOQ 3.18, we'll parse this and just ignore the operator. As such, operator precedence might not be too important. It's not quite clear from the documentation what the precedence is...