foundation/eclipselink.extension.oracle.spatial.test/src/it/java/org/eclipse/persistence/testing/tests/jpa/jgeometry/SpatialJPQLTestSuite.java

/*
 * Copyright (c) 2011, 2020 Oracle and/or its affiliates. All rights reserved.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0,
 * or the Eclipse Distribution License v. 1.0 which is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
 */

// Contributors:
//     03/08/2010 Andrei Ilitchev
//       Bug 300512 - Add FUNCTION support to extended JPQL
package org.eclipse.persistence.testing.tests.jpa.jgeometry;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;

import junit.framework.Test;
import junit.framework.TestSuite;

import oracle.spatial.geometry.JGeometry;

import org.eclipse.persistence.config.QueryHints;
import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;
import org.eclipse.persistence.testing.models.jpa.structconverter.JGeometryTableCreator;
import org.eclipse.persistence.testing.models.jpa.structconverter.SimpleSpatial;
import org.eclipse.persistence.testing.models.spatial.jgeometry.wrapped.Spatial;

import org.eclipse.persistence.testing.framework.TestProblemException;

/**
 * Test validating custom jpql spatial functions.
 * It is conversion to jpql of Query_SpatialExpOp_ExpGeom_Tests.
 *
 * These tests pass an expression for the first geometry and bin in a JGeometry
 * as a STRUCT for the second one.
 * SQL samples from C:\oracle\db\10.2\md\demo\examples\eginsert.sql
 * Note: Table re-named from TEST81 to JPA_JGEOMETRY
 * fields we renamed from {GID, GEOMETRY} to {ID, JGEOMETRY}
 */
public class SpatialJPQLTestSuite extends JUnitTestCase {

    public static final String STRUCT_CONVERTER_PU = "structConverter";

    public boolean supported;

    public SpatialJPQLTestSuite() {
        super();
    }

    public SpatialJPQLTestSuite(String name){
        super(name);
    }

    public static Test suite() {
        TestSuite suite = new TestSuite();
        suite.setName("SpatialJPQLTestSuite");
        suite.addTest(new SpatialJPQLTestSuite("testSetup"));
        suite.addTest(new SpatialJPQLTestSuite("testSDORelateRectangle"));
        suite.addTest(new SpatialJPQLTestSuite("testSDORelateRectangle2"));
        suite.addTest(new SpatialJPQLTestSuite("testSDORelateCircle"));
        suite.addTest(new SpatialJPQLTestSuite("testSDORelateArbitraryLine"));
        suite.addTest(new SpatialJPQLTestSuite("testSDOFilterRectangle"));
        suite.addTest(new SpatialJPQLTestSuite("testSDOFilterRectangleNullParams"));
        suite.addTest(new SpatialJPQLTestSuite("testSDOFilterCircle"));
        suite.addTest(new SpatialJPQLTestSuite("testSDOFilterArbitraryLine"));
        suite.addTest(new SpatialJPQLTestSuite("testSDOWithinDistanceRectangle"));
        suite.addTest(new SpatialJPQLTestSuite("testSDOWithinDistanceRectangleUsingMaxResolution"));
        suite.addTest(new SpatialJPQLTestSuite("testSDOWithinDistanceCircle"));
        suite.addTest(new SpatialJPQLTestSuite("testSDOWithinDistanceArbitraryLine"));
        suite.addTest(new SpatialJPQLTestSuite("testSDOWithinDistanceNullParamsMatchingCircle1004"));
        suite.addTest(new SpatialJPQLTestSuite("testSDOWithinDistanceNullParamsNotMatching"));
        suite.addTest(new SpatialJPQLTestSuite("testSdoBinding"));
        return suite;
    }

    public void testSetup() {
        supported = getServerSession(STRUCT_CONVERTER_PU).getPlatform().isOracle();
        if(!supported) {
            return;
        }
        clearCache(STRUCT_CONVERTER_PU);
        getServerSession(STRUCT_CONVERTER_PU).executeNonSelectingSQL("DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'JPA_JGEOMETRY'");
        new JGeometryTableCreator().replaceTables(JUnitTestCase.getServerSession(STRUCT_CONVERTER_PU));
        getServerSession(STRUCT_CONVERTER_PU).executeNonSelectingSQL("INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME, COLUMN_NAME, DIMINFO) VALUES('JPA_JGEOMETRY', 'JGEOMETRY'," +
                " mdsys.sdo_dim_array(mdsys.sdo_dim_element('X', -100, 100, 0.005), mdsys.sdo_dim_element('Y', -100, 100, 0.005)))");

        getServerSession(STRUCT_CONVERTER_PU).executeNonSelectingSQL("CREATE INDEX jpa_test_idx on JPA_JGEOMETRY(jgeometry) indextype is mdsys.spatial_index parameters ('mdsys.sdo_level=5 sdo_numtiles=6')");
        populate();
    }

    protected void populate() {
        SampleGeometries samples = new SampleGeometries();
        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        beginTransaction(em);
        try {
            List<SimpleSpatial> simpleSpatials = samples.simpleJpaPopulation();
            for(SimpleSpatial ss : simpleSpatials) {
                em.persist(ss);
            }
            commitTransaction(em);
            assertEquals(simpleSpatials.size(), ((Long)em.createQuery("SELECT COUNT(ss) FROM SimpleSpatial ss").getSingleResult()).intValue());
        } catch (Exception ex) {
            if(isTransactionActive(em)) {
                rollbackTransaction(em);
            }
            throw new TestProblemException("Populate failed", ex);
        } finally {
            closeEntityManager(em);
            clearCache(STRUCT_CONVERTER_PU);
        }
    }

    /**
     * SDO_RELATE using a dynamic rectangular window with lower left
     * and upper right coordinates of {(1,1), (20,20)}
     */
    public void testSDORelateRectangle() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where mdsys.sdo_relate(jgeometry, " +
            "mdsys.sdo_geometry(3,null,null, mdsys.sdo_elem_info_array(1,3,3),  " +
            "mdsys.sdo_ordinate_array(1,1, 20, 20)), " +
            "'MASK=ANYINTERACT QUERYTYPE=WINDOW') = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry rectangle =
            JGeometry.createLinearPolygon(new double[] { 1, 1, 1, 20, 10, 20,
                                                         20, 1, 1, 1 }, 2, 0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_RELATE', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", rectangle);
        query.setParameter("params", "MASK=ANYINTERACT QUERYTYPE=WINDOW");
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    /**
     * SDO_RELATE using a dynamic rectangular window with lower left
     * and upper right coordinates of {(1,1), (20,20)}
     * Build jpql string with all mdsys.sdo_ functions found in the control query.
     */
    public void testSDORelateRectangle2() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where mdsys.sdo_relate(jgeometry, " +
            "mdsys.sdo_geometry(3,null,null, mdsys.sdo_elem_info_array(1,3,3),  " +
            "mdsys.sdo_ordinate_array(1,1, 20, 20)), " +
            "'MASK=ANYINTERACT QUERYTYPE=WINDOW') = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        String otherGeometry = "FUNC('mdsys.sdo_geometry', 3, null, null, FUNC('mdsys.sdo_elem_info_array', 1, 3, 3), FUNC('mdsys.sdo_ordinate_array', 1, 1, 20, 20))";

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_RELATE', ss.JGeometry, "+otherGeometry+", :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("params", "MASK=ANYINTERACT QUERYTYPE=WINDOW");
        query.setHint(QueryHints.BIND_PARAMETERS, "false");
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    /**
     * SDO_RELATE using a with a circle of radius 10 around (0,0)
     */
    public void testSDORelateCircle() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where mdsys.sdo_relate(jgeometry, " +
            "mdsys.sdo_geometry(3,null,null, " +
            "mdsys.sdo_elem_info_array(1,3,4), " +
            "mdsys.sdo_ordinate_array(-10,0, 0, 10, 10, 0)), " +
            "'MASK=ANYINTERACT QUERYTYPE=WINDOW') = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry circle = JGeometry.createCircle(-10, 0, 0, 10, 10, 0, 0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_RELATE', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", circle);
        query.setParameter("params", "MASK=ANYINTERACT QUERYTYPE=WINDOW");
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    /**
     * SDO_RELATE using an arbitrary line string {(10,10), (20, 20), (30, 30), (45,45)}
     */
    public void testSDORelateArbitraryLine() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where mdsys.sdo_relate(" +
            "jgeometry, mdsys.sdo_geometry(2,null,null, " +
            "mdsys.sdo_elem_info_array(1,2,1), " +
            "mdsys.sdo_ordinate_array(10,10, 20,20, 30,30, 45,45)), " +
            "'MASK=ANYINTERACT QUERYTYPE=WINDOW') = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry line =
            JGeometry.createLinearLineString(new double[] { 10, 10, 20, 20, 30,
                                                            30, 45, 45 }, 2,
                                             0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_RELATE', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", line);
        query.setParameter("params", "MASK=ANYINTERACT QUERYTYPE=WINDOW");
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    /**
     * SDO_Filter using a dynamic rectangular window with lower left
     * and upper right coordinates of {(1,1), (20,20)}
     */
    public void testSDOFilterRectangle() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where mdsys.sdo_filter(" +
            "jgeometry, mdsys.sdo_geometry(3,null,null, " +
            "mdsys.sdo_elem_info_array(1,3,3), " +
            "mdsys.sdo_ordinate_array(1,1, 20, 20)), " +
            "'QUERYTYPE=WINDOW') = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry rectangle =
            JGeometry.createLinearPolygon(new double[] { 1, 1, 1, 20, 10, 20,
                                                         20, 1, 1, 1 }, 2, 0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_FILTER', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", rectangle);
        query.setParameter("params", "QUERYTYPE=WINDOW");
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    public void testSDOFilterRectangleNullParams() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where mdsys.sdo_filter(" +
            "jgeometry, mdsys.sdo_geometry(3,null,null, " +
            "mdsys.sdo_elem_info_array(1,3,3), " +
            "mdsys.sdo_ordinate_array(1,1, 20, 20)), " +
            "NULL) = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry rectangle =
            JGeometry.createLinearPolygon(new double[] { 1, 1, 1, 20, 10, 20,
                                                         20, 1, 1, 1 }, 2, 0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_FILTER', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", rectangle);
        query.setParameter("params", null);
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    /**
     * SDO_FILTER using a with a circle of radius 10 around (0,0)
     */
    public void testSDOFilterCircle() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where mdsys.sdo_filter(" +
            "jgeometry, mdsys.sdo_geometry(3,null,null, " +
            "mdsys.sdo_elem_info_array(1,3,4), " +
            "mdsys.sdo_ordinate_array(-10,0, 0, 10, 10, 0)), " +
            "'QUERYTYPE=WINDOW') = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry circle = JGeometry.createCircle(-10, 0, 0, 10, 10, 0, 0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_FILTER', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", circle);
        query.setParameter("params", "QUERYTYPE=WINDOW");
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    /**
     * SDO_FILTER using an arbitrary line string {(10,10), (20, 20), (30, 30), (45,45)}
     */
    public void testSDOFilterArbitraryLine() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where mdsys.sdo_filter(" +
            "jgeometry, mdsys.sdo_geometry(2,null,null, " +
            "mdsys.sdo_elem_info_array(1,2,1), " +
            "mdsys.sdo_ordinate_array(10,10, 20,20, 30,30, 45,45)), " +
            "'QUERYTYPE=WINDOW') = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry line =
            JGeometry.createLinearLineString(new double[] { 10, 10, 20, 20, 30,
                                                            30, 45, 45 }, 2,
                                             0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_FILTER', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", line);
        query.setParameter("params", "QUERYTYPE=WINDOW");
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    /**
     * SDO_WITHIN_DISTANCE using a dynamic rectangular window with lower left
     * and upper right coordinates of {(1,1), (20,20)}
     */
    public void testSDOWithinDistanceRectangle() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where mdsys.sdo_within_distance(" +
            "jgeometry, mdsys.sdo_geometry(3,null,null, mdsys.sdo_elem_info_array(1,3,3), " +
            "mdsys.sdo_ordinate_array(1,1, 20, 20)), " +
            "'DISTANCE=10') = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry rectangle =
            JGeometry.createLinearPolygon(new double[] { 1, 1, 1, 20, 10, 20,
                                                         20, 1, 1, 1 }, 2, 0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_WITHIN_DISTANCE', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", rectangle);
        query.setParameter("params", "DISTANCE=10");
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    public void testSDOWithinDistanceRectangleUsingMaxResolution() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where mdsys.sdo_within_distance(" +
            "jgeometry, mdsys.sdo_geometry(3,null,null, mdsys.sdo_elem_info_array(1,3,3), " +
            "mdsys.sdo_ordinate_array(1,1, 20, 20)), " +
            "'DISTANCE=10 MAX_RESOLUTION=5') = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry rectangle =
            JGeometry.createLinearPolygon(new double[] { 1, 1, 1, 20, 10, 20,
                                                         20, 1, 1, 1 }, 2, 0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_WITHIN_DISTANCE', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", rectangle);
        query.setParameter("params", "DISTANCE=10 MAX_RESOLUTION=5");
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    /**
     * SDO_WITHIN_DISTANCE using a with a circle of radius 10 around (0,0)
     */
    public void testSDOWithinDistanceCircle() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where " + "mdsys.sdo_within_distance(jgeometry, " +
            "mdsys.sdo_geometry(3,null,null, " +
            "mdsys.sdo_elem_info_array(1,3,4), " +
            "mdsys.sdo_ordinate_array(-10,0, 0, 10, 10, 0)), " +
            "'DISTANCE=10') = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry circle = JGeometry.createCircle(-10, 0, 0, 10, 10, 0, 0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_WITHIN_DISTANCE', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", circle);
        query.setParameter("params", "DISTANCE=10");
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    /**
     * SDO_WITHIN_DISTANCE using an arbitrary line string {(10,10), (20, 20), (30, 30), (45,45)}
     */
    public void testSDOWithinDistanceArbitraryLine() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where " + "mdsys.sdo_within_distance(jgeometry, " +
            "mdsys.sdo_geometry(2,null,null, " +
            "mdsys.sdo_elem_info_array(1,2,1), " +
            "mdsys.sdo_ordinate_array(10,10, 20,20, 30,30, 45,45)), " +
            "'DISTANCE=10') = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry line =
            JGeometry.createLinearLineString(new double[] { 10, 10, 20, 20, 30,
                                                            30, 45, 45 }, 2,
                                             0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_WITHIN_DISTANCE', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", line);
        query.setParameter("params", "DISTANCE=10");
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    /**
     * SDO_WITHIN_DISTANCE with NULL params matching a known circle geometry (1004)
     */
    public void testSDOWithinDistanceNullParamsMatchingCircle1004() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where " + "mdsys.sdo_within_distance(jgeometry, mdsys.sdo_geometry(3, " +
            "NULL, null, mdsys.sdo_elem_info_array(1,3,4), " +
            "mdsys.sdo_ordinate_array(1, 0, 0, 1, 0, -1)), " +
            "NULL) = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry circle = JGeometry.createCircle(1, 0, 0, 1, 0, -1, 0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_WITHIN_DISTANCE', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", circle);
        query.setParameter("params", null);
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    /**
     * SDO_WITHIN_DISTANCE with NULL params not matching existing
     */
    public void testSDOWithinDistanceNullParamsNotMatching() throws Exception {
        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where " + "mdsys.sdo_within_distance(jgeometry, mdsys.sdo_geometry(3, " +
            "NULL, null, mdsys.sdo_elem_info_array(1,3,4), " +
            "mdsys.sdo_ordinate_array(10, 0, 0, 10, 0, -10)), " +
            "NULL) = 'TRUE' ORDER BY ID";

        SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql);

        JGeometry circle = JGeometry.createCircle(10, 0, 0, 10, 0, -10, 0);

        EntityManager em = createEntityManager(STRUCT_CONVERTER_PU);
        Query query = em.createQuery("SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_WITHIN_DISTANCE', ss.JGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC");
        query.setParameter("otherGeometry", circle);
        query.setParameter("params", null);
        List<Spatial> results = query.getResultList();

        String compareResult = reader.compare(results);

        assertNull(compareResult, compareResult);
    }

    /**
     * SDO_RELATE using a dynamic rectangular window with lower left
     * and upper right coordinates of {(1,1), (20,20)}
     * The test explores different binding options of the original sdo sql from testSDORelateRectangle.
     * The observed behaviour - attempt to bind the third parameter (the second null in "mdsys.sdo_geometry(3,null,null,..."
     * cause exception.
     * That's the reason why jpql query in testSDORelateRectangle2 test can't use binding -
     * it results in exactly the same exception.
     */
    public void testSdoBinding() throws Exception {
        // could be used for debugging
        boolean shouldPrintPassed = false;
        boolean shouldPrintFailed = false;
        boolean shouldPrintExceptionStackTrace = false;

        // Commented out below the original sql - it is run in bindNone case
/*        String sql =
            "select ID, JGEOMETRY from JPA_JGEOMETRY where mdsys.sdo_relate(jgeometry, " +
            "mdsys.sdo_geometry(3,null,null, mdsys.sdo_elem_info_array(1,3,3),  " +
            "mdsys.sdo_ordinate_array(1,1, 20, 20)), " +
            "'MASK=ANYINTERACT QUERYTYPE=WINDOW') = 'TRUE' ORDER BY ID";*/

        // parameter values
        Object[] values = {3, null, null, 1, 3, 3, 1, 1, 20, 20, "MASK=ANYINTERACT QUERYTYPE=WINDOW", "TRUE"};
        // indicates which parameter should be bound (true), which should not (false)
        // passes
        boolean[] bindNone = {false, false, false, false, false, false, false, false, false, false, false, false};
        // fails
        boolean[] bindAll = {true, true, true, true, true, true, true, true, true, true, true, true};
        // fails
        boolean[] bindOnlyThird = {false, false, true, false, false, false, false, false, false, false, false, false};
        // passes
        boolean[] bindAllButThird = {true, true, false, true, true, true, true, true, true, true, true, true};
        boolean[][] binds = {bindNone, bindAll, bindAllButThird };
        Map<boolean[], String> passed = new HashMap();
        Map<boolean[], String> failed = new HashMap();
        for(int i=0; i < binds.length; i++) {
            boolean[] bind = binds[i];
            String sql =
                "select ID, JGEOMETRY from JPA_JGEOMETRY where mdsys.sdo_relate(jgeometry, " +
                "mdsys.sdo_geometry("+(bind[0]?"#0":"3")+","+(bind[1]?"#1":"null")+","+(bind[2]?"#2":"null")+", mdsys.sdo_elem_info_array("+(bind[3]?"#3":"1")+","+(bind[4]?"#4":"3")+","+(bind[5]?"#5":"3")+"),  " +
                "mdsys.sdo_ordinate_array("+(bind[6]?"#6":"1")+","+(bind[7]?"#7":"1")+", "+(bind[8]?"#8":"20")+", "+(bind[9]?"#9":"20")+")), " +
                (bind[10]?"#10":"'MASK=ANYINTERACT QUERYTYPE=WINDOW'")+") = "+(bind[11]?"#11":"'TRUE'")+" ORDER BY ID";
            List<String> argumentNames = new ArrayList(values.length);
            List argumentValues = new ArrayList(values.length);
            for(int j=0; j < values.length; j++) {
                if(bind[j]) {
                    argumentNames.add(Integer.toString(j));
                    argumentValues.add(values[j]);
                }
            }
            try {
                SQLReader reader = new SQLReader(getServerSession(STRUCT_CONVERTER_PU), sql, argumentNames, argumentValues);
                if(shouldPrintPassed) {
                    System.out.println("passed:");
                    System.out.println(sql);
                }
                passed.put(bind, sql);
            } catch (Exception ex) {
                if(shouldPrintExceptionStackTrace) {
                    ex.printStackTrace();
                }
                if(shouldPrintFailed) {
                    System.out.println("FAILED:");
                    System.out.println(sql);
                }
                failed.put(bind, sql);
            }
        }

        // Observed sdo behaviour - binding the third parameter causes failure
        String errorMsg = "";
        Iterator<Map.Entry<boolean[],String>> it = failed.entrySet().iterator();
        while(it.hasNext()) {
            Map.Entry<boolean[],String> entry = it.next();
            boolean[] bind = entry.getKey();
            if(!bind[2]) {
                errorMsg += entry.getValue() + '\n';
            }
        }
        if(errorMsg.length() > 0) {
            errorMsg = "Unexpected failures: \n" + errorMsg;
            fail(errorMsg);
        }
    }
}