PL/SQL Grammar rules are not sufficiently lenient

[Naros]

The table_properties rule is ordered in the PLSQL grammar, matching the Oracle docs:
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6__I2126725

The issue is that Oracle permits specifying a variety of these table properties in arbitrary orders that do not align with the current grammar. So, for example, if LogMiner or the DBMS_METADATA package were to construct a SQL statement, it's highly likely that the order of the table's property clauses will not adhere to the grammar's order.

I see a few ways we could address this so that the grammar remains logically correct, while also being lenient to satisfy these corner cases where the Oracle database engine permits such ambiguities. The question is to what extent we want to overcomplicate the grammar to make this work.

The first is the simplest in grammar, and that is, we treat all parts of the rule as a list that can be provided in any order using |, so that the order can be arbitrary rather than strict as it is today. The downside to this approach is that it opens the door for the SQL to contain multiple instances of the same clause, where that may otherwise be seen as invalid. It would then be on the listeners to validate whether this use case occurs and throw an exception.

The second approach is to use locals[] to define a series of member variables in the rule's context that are evaluated when one of the table_properties clauses matches, so that any future observations of such clauses throw an exception since they're only to be allowed once.

I see locals[] used in tiny portions of the codebase, which is why I am curious what the maintainers prefer.

From Debezium's perspective, we'd ideally like to be able to use as much of the upstream "as-is" as possible, as that reduces technical debt. For that to be possible, some of these rules need to adhere more to the database engine and less so to the rigid nature of the documentation.

Do any of the maintainers or users of the PL/SQL grammar have any input?

[kaby76]

The issue is that Oracle permits specifying a variety of these table properties in arbitrary orders that do not align with the current grammar.

Skip the Antlr grammar for a moment. You're saying that Oracle's implementation does not align with the doc?

[Naros]

Absolutely.

For example, in the Oracle documentation link, it shows (CACHE | NOCACHE) appearing before the parallel_clause for the table properties, but that's not enforced. Here's a valid SQL statement for Oracle 19/21/23 where the order is inverted, showing the parallel_clause prior to the CACHE|NOCACHE rule.

CREATE TABLE "DEBEZIUM"."BARRIER_GATE_MANAGE" (
    "ID" VARCHAR2(64 BYTE)  NOT NULL,
    "CREATE_BY" VARCHAR2(64 BYTE) ,
    "CREATE_DATE" DATE ,
    "UPDATE_BY" VARCHAR2(64 BYTE) ,
    "UPDATE_DATE" DATE ,
    "REMARKS" NVARCHAR2(255) ,
    "DEL_FLAG" VARCHAR2(64 BYTE) ,
    "BARRIER_GATE_NAME" VARCHAR2(64 BYTE) ,
    "BARRIER_GATE_CODE" VARCHAR2(64 BYTE) ,
    "DOOR_CODE" VARCHAR2(64 BYTE) ,
    "LIGHT_ALARM_ID" VARCHAR2(64 BYTE) ,
    "BARRIER_STATUS_ID" VARCHAR2(64 BYTE) ,
    "BARRIER_GATE_TYPE" VARCHAR2(1 BYTE) ,
    "DAHUA_ID" VARCHAR2(255 BYTE) ,
    "OLD_SYSTEM_ID" VARCHAR2(255 BYTE))
LOGGING NOCOMPRESS PCTFREE 10 INITRANS 1
STORAGE(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT)
PARALLEL 1 NOCACHE ENABLE ROW MOVEMENT;

[kaby76]

The Antlr grammar should be adjusted to correspond to the actual language defined by the implementation. The doc is likely scraped, incorrectly, from the implementation.

[Naros]

I understand, so that brings me back to my original question.

What should be responsible, if at all, for determining whether the grammar parsing fails if the supplied SQL contains two or more of a concrete rule (i.e. two parallel_clause fragments in the supplied SQL) where only one should be permitted in this arbitrary list of properties? Do we want to rely on locals[] in the grammar to manage this on a per-rule context basis, or leave that to the listener implementation and say that's outside the scope of the grammar to evaluate validity at that depth?

[kaby76]

Generally speaking, locals is not recommended in the .g4s because they are not "target agnostic". Those are in the syntax of the target language, and would then force forking of the grammar for all the targets, multiplying the headaches in maintenance.

Checking for duplicate parallel_clause, etc., might be possible in a semantic predicate with the assumption that the tree is partially constructed at the time of evaluation. The predicate would probably be best returning true inside an AdaptivePredict() call.