Oracle: create <graph>_nulls_equal with the graph, so a statement that writes or a reduce compares lists holding a null item by item (port of drivine4j#270)

Start with drivine/backend/oracle/oracle_graph.py:57 and drivine/cypher/sql/oracle_cypher_compiler.py:502-510, then review the listed drivine4j#323 contract tests and spec cases. The work spans Oracle function creation, compiler behavior, permission fallback, and tests. Done means the stored function and fallback match the described behavior, with the listed Oracle tests and spec updates covered.

Port of liberation-data/drivine4j#270.

What happens here now: on Oracle, a Cypher statement that writes compiles to a PL/SQL block, and so does the step of a reduce. Neither can call the cy_nulls_equal function a query declares with WITH FUNCTION (PLS-00231). So two lists or maps that hold a null and differ as JSON compare as false there, where Cypher may give null: CREATE (n:CyWrite) WITH n, [null] AS a, [1] AS b SET n.eq = coalesce(a = b, 'null') RETURN n.eq gives false where Neo4j gives "null". See drivine/cypher/sql/oracle_cypher_compiler.py:502-510 (nulls_equal, if self.procedural or self._folding).

What drivine4j does now (liberation-data/drivine4j#323):

1. Store layout (shared contract). The Oracle graph is made with a function, <graph>_nulls_equal, created where it is missing or invalid, so a store made by an earlier version gains it. It is not recorded in _DrivineSchema, as the Postgres <graph>_find/_owns/_write functions aren't. The ensure block (here drivine/backend/oracle/oracle_graph.py:57 (ensuring)) gains, after the existing IF v_made = 0 THEN … END IF;:

    e_denied EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_denied, -1031);
...
    SELECT COUNT(*) INTO v_made FROM user_objects WHERE object_type = 'FUNCTION' AND object_name = '<GRAPH>_NULLS_EQUAL' AND status = 'VALID';
    IF v_made = 0 THEN
        BEGIN EXECUTE IMMEDIATE '<the DDL below, quoted>'; EXCEPTION WHEN e_denied THEN NULL; END;
    END IF;

The DDL must match byte for byte (lines joined with \n, no trailing newline), with <graph> the graph's name. It is CREATE OR REPLACE followed by the same text the query declares as cy_nulls_equal, renamed:

CREATE OR REPLACE FUNCTION <graph>_nulls_equal(cy_x JSON, cy_y JSON) RETURN NUMBER IS
    FUNCTION eq(a JSON_ELEMENT_T, b JSON_ELEMENT_T) RETURN NUMBER IS
        r NUMBER := 1;
        e NUMBER;
        la JSON_ARRAY_T;
        lb JSON_ARRAY_T;
        oa JSON_OBJECT_T;
        ob JSON_OBJECT_T;
        ks JSON_KEY_LIST;
        too_long EXCEPTION;
        PRAGMA EXCEPTION_INIT(too_long, -40478);
    BEGIN
        IF a.is_Null OR b.is_Null THEN RETURN NULL; END IF;
        IF a.is_Array AND b.is_Array THEN
            la := TREAT(a AS JSON_ARRAY_T);
            lb := TREAT(b AS JSON_ARRAY_T);
            IF la.get_Size <> lb.get_Size THEN RETURN 0; END IF;
            FOR i IN 0 .. la.get_Size - 1 LOOP
                e := eq(la.get(i), lb.get(i));
                IF e = 0 THEN RETURN 0; END IF;
                IF e IS NULL THEN r := NULL; END IF;
            END LOOP;
            RETURN r;
        END IF;
        IF a.is_Object AND b.is_Object THEN
            oa := TREAT(a AS JSON_OBJECT_T);
            ob := TREAT(b AS JSON_OBJECT_T);
            IF oa.get_Size <> ob.get_Size THEN RETURN 0; END IF;
            ks := oa.get_Keys;
            FOR i IN 1 .. ks.COUNT LOOP
                IF NOT ob.has(ks(i)) THEN RETURN 0; END IF;
                e := eq(oa.get(ks(i)), ob.get(ks(i)));
                IF e = 0 THEN RETURN 0; END IF;
                IF e IS NULL THEN r := NULL; END IF;
            END LOOP;
            RETURN r;
        END IF;
        IF a.is_Number AND b.is_Number THEN RETURN CASE WHEN a.to_Number = b.to_Number THEN 1 ELSE 0 END; END IF;
        IF a.is_String AND b.is_String THEN
            BEGIN
                RETURN CASE WHEN a.to_String = b.to_String THEN 1 ELSE 0 END;
            EXCEPTION WHEN too_long THEN
                RETURN CASE WHEN DBMS_LOB.COMPARE(a.to_Clob, b.to_Clob) = 0 THEN 1 ELSE 0 END;
            END;
        END IF;
        IF a.is_Boolean AND b.is_Boolean THEN RETURN CASE WHEN a.to_Boolean = b.to_Boolean THEN 1 ELSE 0 END; END IF;
        RETURN 0;
    END;
BEGIN
    RETURN eq(JSON_ELEMENT_T.parse(JSON_SERIALIZE(cy_x RETURNING CLOB)), JSON_ELEMENT_T.parse(JSON_SERIALIZE(cy_y RETURNING CLOB)));
END;

2. Compiler. nullsEqual always walks with the store's function, in reads too: it is as fast as the declared one, and the query needs no WITH FUNCTION block. Before the walk there is a fast path: two values with the same short JSON text are null, since one holds a null, so they need no walk:

CASE WHEN JSON_SERIALIZE(x RETURNING VARCHAR2(4000) NULL ON ERROR) = JSON_SERIALIZE(y RETURNING VARCHAR2(4000) NULL ON ERROR) THEN NULL ELSE (<graph>_nulls_equal(x, y) = 1) END

(JSON = and JSON_EQUAL can't be the fast path: they fail past about 32 KB, ORA-40180.) The compiler takes a flag, nullsEqualStored (default true), and reports whether the compiled statement calls the stored function.

3. Fallback. Making the function takes CREATE PROCEDURE, which the RESOURCE role grants. Without it, ensure still makes the graph (ORA-01031 is swallowed for the function only). When a compiled statement calls the function, the connection that compiles Cypher for Oracle first checks that the session can call it, with SELECT <graph>_nulls_equal(JSON('[1]'), JSON('[1]')) FROM DUAL. A yes is remembered per database, user and graph; a no is asked again next time. If the session can't call it, the statement is compiled again with nullsEqualStored = false, which is today's behaviour (a read declares cy_nulls_equal; a write or reduce step uses CASE WHEN <equals> THEN NULL ELSE FALSE END), and one warning is logged per graph.

Tests to port (drivine4j#323):

• CypherWriteContract lists and maps that hold a null and differ as JSON compare item by item in a statement that writes, and the Oracle-only a store without the function … until the graph is ensured again.
• CypherQueryContract the step of a reduce compares lists and maps that hold a null and differ as JSON item by item. The write case [null] = [1] no longer skips Oracle.
• CompiledSizeTest values that may hold a null are walked on Oracle by the store's function ….
• OracleFreshGraphTest the graph is made with the function ….
• Spec v0.6.14 (once tagged): writes.yaml drops the step's except: [oracle] and adds steps; queries.yaml adds "Values that hold a null, compared in the step of a reduce".