Puzzling behaviour with SDO_RELATE contains

While upgrading the Oracle CI stack for GeoTools (https://github.com/geotools/geotools/pull/5121) updating from 18.4.XE to 21.3 XE and 23.7 Free I've run into some puzzling behaviour with a testcase that does a spatial join between polygons and points using CONTAINS.

Here are the extracted SQL statements that are used for setup and the test statement.

Setup

CREATE TABLE ft1 
(
    id INT, 
    geometry MDSYS.SDO_GEOMETRY, 
    intProperty INT, 
    doubleProperty FLOAT, 
    stringProperty VARCHAR(255), 
    PRIMARY KEY(id)
);

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('FT1','GEOMETRY',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.5), MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.5)), 4326);
INSERT INTO ft1 VALUES (0,MDSYS.SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(0.0,0.0,NULL),NULL,NULL), 0, 0.0,'zero');
INSERT INTO ft1 VALUES (1,MDSYS.SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(1.0,1.0,NULL),NULL,NULL), 1, 1.1,'one');
INSERT INTO ft1 VALUES (2,MDSYS.SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(2.0,2.0,NULL),NULL,NULL), 2, 2.2,'two');

CREATE INDEX FT1_GEOMETRY_IDX ON ft1(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ( 'layer_gtype=POINT' );

CREATE TABLE FTJOIN 
(
    id INT PRIMARY KEY, 
    name VARCHAR(255), 
    geom MDSYS.SDO_GEOMETRY, 
    join1intProperty INT
);

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID ) VALUES ('FTJOIN','GEOM',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-20,20,0.05), MDSYS.SDO_DIM_ELEMENT('Y',-20,20,0.05)), 4326);
INSERT INTO FTJOIN VALUES (0, 'zero', MDSYS.SDO_GEOMETRY(2003, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),  MDSYS.SDO_ORDINATE_ARRAY(-0.1,-0.1, -0.1,0.1, 0.1,0.1, 0.1,-0.1, -0.1,-0.1)), 0);
INSERT INTO FTJOIN VALUES (1, 'one', MDSYS.SDO_GEOMETRY(2003, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),  MDSYS.SDO_ORDINATE_ARRAY(-1.1,-1.1, -1.1,1.1, 1.1,1.1, 1.1,-1.1, -1.1,-1.1)), 1);
INSERT INTO FTJOIN VALUES (2, 'two', MDSYS.SDO_GEOMETRY(2003, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),  MDSYS.SDO_ORDINATE_ARRAY(-10,-10, -10,10, 10,10, 10,-10, -10,-10)), 2);
INSERT INTO FTJOIN VALUES (3, 'three', NULL, 3);
CREATE INDEX FTJOIN_GEOMETRY_IDX ON FTJOIN(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ( 'layer_gtype=POLYGON' );

Test

The Join query is expected to return 6 records, but returns 0 on both 21.3 and 23.7.

SELECT COUNT(*) 
FROM GEOTOOLS.FT1 A 
INNER JOIN GEOTOOLS.FTJOIN B ON SDO_RELATE(b.GEOM, a.GEOMETRY, 'mask=contains querytype=WINDOW') = 'TRUE';

Looking at the interaction matrix of all these features using the query below shows that there are no polygons that CONTAIN a point, but the points are INSIDE the polygons

SELECT
    point.ID as point_id,
    polygon.ID as polygon_id,
    SDO_CONTAINS(polygon.GEOM, point.GEOMETRY) as polygon_contains_point,
       SDO_INSIDE(point.GEOMETRY, polygon.GEOM) as point_inside_polygon,
       SDO_GEOM.relate(
               polygon.GEOM,
               'DETERMINE',
               point.GEOMETRY) as polygon_relates_to_point,
       SDO_GEOM.relate(
               point.GEOMETRY,
               'DETERMINE',
               polygon.GEOM) as point_relates_to_polygon
FROM 
    FT1 point 
INNER JOIN 
    FTJOIN polygon ON 'TRUE' 
ORDER BY 
    polygon_id, point_id;

Results:

+--------+----------+----------------------+--------------------+------------------------+------------------------+
|POINT_ID|POLYGON_ID|POLYGON_CONTAINS_POINT|POINT_INSIDE_POLYGON|POLYGON_RELATES_TO_POINT|POINT_RELATES_TO_POLYGON|
+--------+----------+----------------------+--------------------+------------------------+------------------------+
|0       |0         |FALSE                 |TRUE                |DISJOINT                |INSIDE                  |
|1       |0         |FALSE                 |FALSE               |DISJOINT                |DISJOINT                |
|2       |0         |FALSE                 |FALSE               |DISJOINT                |DISJOINT                |
|0       |1         |FALSE                 |TRUE                |DISJOINT                |INSIDE                  |
|1       |1         |FALSE                 |TRUE                |DISJOINT                |INSIDE                  |
|2       |1         |FALSE                 |FALSE               |DISJOINT                |DISJOINT                |
|0       |2         |FALSE                 |TRUE                |DISJOINT                |INSIDE                  |
|1       |2         |FALSE                 |TRUE                |DISJOINT                |INSIDE                  |
|2       |2         |FALSE                 |TRUE                |DISJOINT                |INSIDE                  |
|0       |3         |FALSE                 |FALSE               |null                    |null                    |
|1       |3         |FALSE                 |FALSE               |null                    |null                    |
|2       |3         |FALSE                 |FALSE               |null                    |null                    |
+--------+----------+----------------------+--------------------+------------------------+------------------------+

I've played with setting the min_resolution parameter as well as setting querytype=JOIN instead of WINDOW (though querytype no longer seems a valid parameter) to no avail.

Apart from concluding that there is a bug in the Oracle software, is there any other reason this might be failing in the newer Oracle versions? (as 18.4 works as expected and I think all the boxes regarding spatial indexes and primary keys are ticked)

I checked your geometries. (See 1st screenshot)
Table FTJOIN contains invalid geometries returning Oracle error code 13367. Check this blog post about validating and rectifying, if necessary, invalid geometries using SDO_UTIL.RECTIFY_GEOMETRY. Once rectified you´ll get the result you are expecting. (See 2nd+3rd screenshots)

Does it help?