Filter Fails in Complex Query, Workaround Available

[mikhalov]

OrientDB Version: 3.2.33
Java Version: 21
OS: Windows

I encountered an issue in OrientDB when using a complex filter in a query. The test testComplicatedFilter() fails when trying to filter on a field in a connected vertex. However, the issue can be bypassed by either adding 1=1 and to the filter or by reversing the order of the query condition.

Tests to reproduce:

import com.orientechnologies.orient.core.db.ODatabaseSession;
import com.orientechnologies.orient.core.db.OrientDB;
import com.orientechnologies.orient.core.db.OrientDBConfig;
import com.orientechnologies.orient.core.db.record.OIdentifiable;
import com.orientechnologies.orient.core.id.ORID;
import com.orientechnologies.orient.core.metadata.schema.OSchema;
import com.orientechnologies.orient.core.record.OEdge;
import com.orientechnologies.orient.core.record.OVertex;
import com.orientechnologies.orient.core.sql.executor.OResult;

import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import static org.junit.jupiter.api.Assertions.assertEquals;

class OrientDBComplexFilterTest {

    private ODatabaseSession db;

    private OVertex newEntity1;

    private Iterable<OIdentifiable> vertexes;

    private OrientDB orientDB;

    @BeforeEach
    public void setup() {
        orientDB = new OrientDB("remote:localhost", OrientDBConfig.defaultConfig());
        db = orientDB.open("test", "root", "root");
        createClassesOrDeleteExistingInstanses();

        OVertex entity1 = db.newVertex("Entity");
        entity1.setProperty("name", "Entity1");
        db.save(entity1);

        newEntity1 = db.newVertex("NewEntity");
        newEntity1.setProperty("name", "NewEntity1");
        db.save(newEntity1);
        OVertex newEntity2 = db.newVertex("NewEntity");
        newEntity2.setProperty("name", "NewEntity2");
        entity1.addEdge(newEntity1, "NewConnectorEdge");
        db.save(newEntity2);

        OVertex entity2 = db.newVertex("Entity");
        entity2.setProperty("name", "Entity2");
        entity2.addEdge(newEntity2, "NewConnectorEdge");
        db.save(entity2);

        OVertex entity3 = db.newVertex("Entity");
        entity3.setProperty("name", "Entity3");
        db.save(entity3);

        final OVertex relatedEntity1 = db.newVertex("RelatedEntity");
        relatedEntity1.setProperty("name", "RelatedEntity1");
        db.save(relatedEntity1);
        relatedEntity1.addEdge(newEntity1, "ConnectorEdge");

        final OVertex relatedEntity2 = db.newVertex("RelatedEntity");
        relatedEntity2.setProperty("name", "RelatedEntity2");
        db.save(relatedEntity2);
        relatedEntity2.addEdge(newEntity2, "ConnectorEdge");

        OVertex relatedEntity3 = db.newVertex("RelatedEntity");
        relatedEntity3.setProperty("name", "RelatedEntity3");
        db.save(relatedEntity3);

        vertexes = Stream.of(relatedEntity1, relatedEntity2)
                .map(OVertex::getIdentity)
                .collect(Collectors.toSet());

        final OEdge connectorEdge11 = entity1.addEdge(relatedEntity1, "ConnectorEdge");
        final OEdge connectorEdge12 = entity1.addEdge(relatedEntity2, "ConnectorEdge");
        final OEdge connectorEdge23 = entity2.addEdge(relatedEntity3, "ConnectorEdge");
        final OEdge connectorEdge32 = entity3.addEdge(relatedEntity2, "ConnectorEdge");
        db.save(connectorEdge11);
        db.save(connectorEdge12);
        db.save(connectorEdge23);
        db.save(connectorEdge32);
    }

    @Test
    void testComplicatedFilterWithOneEqualsOne() {
        final String query = """
                SELECT expand(out('ConnectorEdge')[1=1 and in('NewConnectorEdge').name = 'Entity1']) as result
                FROM RelatedEntity""";
        try (var rs = db.query(query, vertexes).stream()) {
            final List<OResult> results = rs.filter(Objects::nonNull).toList();
            assertEquals(1, results.size());
            final ORID resultId = results.getFirst().getIdentity().orElseThrow();
            assertEquals(newEntity1.getIdentity(), resultId);
        }
    }

    @Test
    void testComplicatedFilterReverseHack() {
        final String query = """
                SELECT expand(out('ConnectorEdge')['Entity1' = in('NewConnectorEdge').name]) as result
                FROM RelatedEntity""";
        try (var rs = db.query(query, vertexes).stream()) {
            final List<OResult> results = rs.toList();
            assertEquals(1, results.size());
            final ORID resultId = results.getFirst().getIdentity().orElseThrow();
            assertEquals(newEntity1.getIdentity(), resultId);
        }
    }

    @Test
    void testComplicatedFilter() {
        final String query = """
                SELECT expand(out('ConnectorEdge')[in('NewConnectorEdge').name = 'Entity1']) as result
                FROM RelatedEntity""";
        try (var rs = db.query(query, vertexes).stream()) {
            final List<OResult> results = rs.toList();
            assertEquals(1, results.size());
            final ORID resultId = results.getFirst().getIdentity().orElseThrow();
            assertEquals(newEntity1.getIdentity(), resultId);
        }
    }

    @AfterEach
    public void tearDown() {
        db.command("DELETE VERTEX NewEntity");
        db.command("DELETE VERTEX RelatedEntity");
        db.command("DELETE VERTEX Entity");
        db.command("DELETE EDGE ConnectorEdge");
        db.command("DELETE EDGE NewConnectorEdge");

        db.close();
        orientDB.close();
    }

    private void createClassesOrDeleteExistingInstanses() {
        final OSchema schema = db.getMetadata().getSchema();
        if (!schema.existsClass("Entity")) {
            db.command("CREATE CLASS Entity EXTENDS V");
        } else {
            db.command("DELETE VERTEX Entity");
        }
        if (!schema.existsClass("RelatedEntity")) {
            db.command("CREATE CLASS RelatedEntity EXTENDS V");
        } else {
            db.command("DELETE VERTEX RelatedEntity");
        }
        if (!schema.existsClass("ConnectorEdge")) {
            db.command("CREATE CLASS ConnectorEdge EXTENDS E");
        } else {
            db.command("DELETE EDGE ConnectorEdge");
        }
        if (!schema.existsClass("NewConnectorEdge")) {
            db.command("CREATE CLASS NewConnectorEdge EXTENDS E");
        } else {
            db.command("DELETE EDGE NewConnectorEdge");
        }
        if (!schema.existsClass("NewEntity")) {
            db.command("CREATE CLASS NewEntity EXTENDS V");
        } else {
            db.command("DELETE VERTEX NewEntity");
        }
    }
}