Skip to content

SQL: Only records that match first part of big OR expression are deleted #2695

New issue
New issue
Closed
Bug
#2760
#2756
Closed
SQL: Only records that match first part of big OR expression are deleted#2695
Bug
#2760
#2756
Assignees
robfrank
Milestone
25.11.1

Description

@ruslan-butyk-fntext
ruslan-butyk-fntext
opened on Oct 23, 2025

ArcadeDB version: 25.9.1, 25.8.1-java17

Description:
Not all parts of complex OR expression are evaluated:

delete from hierarchy_duct_duct where ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?))

Actual behavior:
Only first ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) part was evaluated and only record(s) that match(es) that part was deleted.

Expected behavior: All parts of complex OR expression are evaluated and all relevant records are deleted.

Code example to reproduce the issue:

import com.arcadedb.ContextConfiguration;
import com.arcadedb.GlobalConfiguration;
import com.arcadedb.engine.ComponentFile.MODE;
import com.arcadedb.query.sql.executor.ResultSet;
import com.arcadedb.remote.RemoteDatabase;
import com.arcadedb.server.ArcadeDBServer;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

public class DeleteTest {
	private static final String DB_NAME = "testDB";
	private static final String ROOT_PASSWORD = "playwithdata";
	private static final String ROOT_DIR = "arcade_db_delete_test";
	private static final List<String> TMP_DIRS = List.of("backups", "log", "databases", "config");
	private static ArcadeDBServer SERVER = null;
	private static RemoteDatabase DATABASE = null;
	private static Path ROOT_PATH = null;

	// Schema
	private static final String VERTEX_DUCT = "duct";
	private static final String EDGE_HIERARCHY_DUCT_DUCT = "hierarchy_duct_duct";
	private static final String PROPERTY_INTERNAL_ID = "internal_id";
	private static final String PROPERTY_INTERNAL_FROM = "internal_from";
	private static final String PROPERTY_INTERNAL_TO = "internal_to";
	private static final String PROPERTY_SWAP = "swap";
	private static final String PROPERTY_ORDER_NUMBER = "order_number";
	private static final String PROPERTY_RID = "@rid";

	// Use case params
	private static final String DUCT_ELID = "duct_elid";
	private static final String SUB_DUCT_ELID_1 = "sub_duct_elid_1";
	private static final String SUB_DUCT_ELID_2 = "sub_duct_elid_2";
	private static final String SUB_DUCT_ELID_3 = "sub_duct_elid_3";
	private static final String SUB_DUCT_ELID_4 = "sub_duct_elid_4";
	private static final String SUB_DUCT_ELID_5 = "sub_duct_elid_5";
	private static final String SUB_DUCT_ELID_6 = "sub_duct_elid_6";
	private static final String OBJECT_OWNER = "owner";
	private static final String SWAP = "N";
	private static final String ORDER_NUMBER = "1";
	private static final List<String> ALL_DUCT_ELIDS = List.of(DUCT_ELID, SUB_DUCT_ELID_1, SUB_DUCT_ELID_2, SUB_DUCT_ELID_3, SUB_DUCT_ELID_4, SUB_DUCT_ELID_5, SUB_DUCT_ELID_6);
	private static final List<String> SUB_DUCT_ELIDS = List.of(SUB_DUCT_ELID_1, SUB_DUCT_ELID_2, SUB_DUCT_ELID_3, SUB_DUCT_ELID_4, SUB_DUCT_ELID_5, SUB_DUCT_ELID_6);

	public static void main(String[] args) {
		try {
			new Thread(() -> {
				prepareTestDirectory();
				startArcadeDBServer();
				initRemoteDatabase();
				setupDatabaseSchema();
				insertInitialData();
				executeTestCase();
			}).start();
			Thread.currentThread().join();
		} catch (Exception e) {
			System.err.print("\nERROR: " + e.getMessage());
		} finally {
			closeDatabase();
			stopServer();
		}
	}

	private static void setupDatabaseSchema() {
		System.out.print("\nSetting up database schema.");
		createVertexType(VERTEX_DUCT);
		createHierarchyEdgeType(EDGE_HIERARCHY_DUCT_DUCT);
	}

	private static void insertInitialData() {
		System.out.print("\nInserting initial data.");
		/**
		 *             duct
		 * 		      ||..|
		 * 		  sub_duct_1..6
		 */
		insertTestCaseVertices();
		insertTestCaseEdges();
	}

	private static void executeTestCase() {
		System.out.print("\nExecuting test case.");
		final String where = "((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR "
			+ "((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR "
			+ "((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR "
			+ "((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR "
			+ "((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR "
			+ "((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?))";
		deleteEdge(EDGE_HIERARCHY_DUCT_DUCT, where,
			List.of(DUCT_ELID, SUB_DUCT_ELID_1, SWAP, ORDER_NUMBER,
				DUCT_ELID, SUB_DUCT_ELID_2, SWAP, ORDER_NUMBER,
				DUCT_ELID, SUB_DUCT_ELID_3, SWAP, ORDER_NUMBER,
				DUCT_ELID, SUB_DUCT_ELID_4, SWAP, ORDER_NUMBER,
				DUCT_ELID, SUB_DUCT_ELID_5, SWAP, ORDER_NUMBER,
				DUCT_ELID, SUB_DUCT_ELID_6, SWAP, ORDER_NUMBER));
		final Map<String, String> allDuctIdMap = queryIdMap(VERTEX_DUCT, ALL_DUCT_ELIDS);
		createHierarchyEdge(SUB_DUCT_ELIDS.stream().map(subDuctElid -> new CreateHierarchyEdgeRecord(EDGE_HIERARCHY_DUCT_DUCT, allDuctIdMap.get(DUCT_ELID),  allDuctIdMap.get(subDuctElid), DUCT_ELID, subDuctElid, ORDER_NUMBER)).toList());
	}

	private static void insertTestCaseVertices() {
		createVertex(ALL_DUCT_ELIDS.stream().map(elid -> new CreateVertexRecord(VERTEX_DUCT, elid)).toList());
	}

	private static void insertTestCaseEdges() {
		// query vertex's rid(s)
		final Map<String, String> ductIdMap = queryIdMap(VERTEX_DUCT, ALL_DUCT_ELIDS);
		// duct <-..> sub_duct(s)
		final String ductRid = ductIdMap.get(DUCT_ELID);
		final List<CreateHierarchyEdgeRecord> records = new ArrayList<>();
		SUB_DUCT_ELIDS.forEach(subDuctElid -> records.add(new CreateHierarchyEdgeRecord(EDGE_HIERARCHY_DUCT_DUCT, ductRid, ductIdMap.get(subDuctElid), DUCT_ELID, subDuctElid, ORDER_NUMBER)));
		createHierarchyEdge(records);
	}

	private static Map<String, String> queryIdMap(final String label, final Collection<String> internalIds) {
		final List<String> properties = List.of(PROPERTY_RID, PROPERTY_INTERNAL_ID);
		final List<String> arguments = new ArrayList<>();
		final String placeholders = internalIds.stream() //
			.map(attr -> {
				arguments.add(attr);
				return "?";
			}).collect(Collectors.joining(", "));
		final String where = "%s in [%s]".formatted(PROPERTY_INTERNAL_ID, placeholders);
		return command("sql", "select %s from %s where %s".formatted(String.join(", ", properties), label, where), arguments)
			.stream() //
			.map(result -> Map.entry((String) result.getProperty(PROPERTY_INTERNAL_ID), (String) result.getProperty(PROPERTY_RID)))
			.collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
	}

	private static void createVertex(final List<CreateVertexRecord> records) {
		final List<InsertVertexRecord> insertRecords = records.stream().map(record -> new InsertVertexRecord(record.vertex,
			Map.of(PROPERTY_INTERNAL_ID, record.internal_id))).toList();
		insertVertex(insertRecords);
	}

	private record CreateVertexRecord(String vertex, String internal_id) {}

	private static void createHierarchyEdge(final List<CreateHierarchyEdgeRecord> records) {
		final List<CreateEdgeRecord> createEdgeRecords = records.stream().map(record -> new CreateEdgeRecord(record.edge, record.fromRid, record.toRid,
			Map.of(PROPERTY_INTERNAL_FROM, record.internal_from, PROPERTY_INTERNAL_TO, record.internal_to, PROPERTY_SWAP, SWAP, PROPERTY_ORDER_NUMBER, record.orderNumber))).toList();
		createEdge(createEdgeRecords);
	}

	private record CreateHierarchyEdgeRecord(String edge, String fromRid, String toRid, String internal_from, String internal_to, String orderNumber) {}

	private static void insertVertex(final List<InsertVertexRecord> records) {
		final List<String> arguments = new ArrayList<>();
		final List<String> commands = records.stream().map(record -> {
			arguments.addAll(record.properties.values());
			final String valuesPlaceholder = record.properties.values().stream().map(attr -> "?").collect(Collectors.joining(", "));
			return "insert into %s (%s) values (%s)".formatted(record.into, String.join(", ", record.properties.keySet()), valuesPlaceholder);
		}).toList();
		command("sqlscript", String.join("; ", commands), arguments);
	}

	private record InsertVertexRecord(String into, Map<String, String> properties) {}

	private static void createEdge(final List<CreateEdgeRecord> records) {
		final List<String> arguments = new ArrayList<>();
		final List<String> commands = records.stream().map(record -> {
			arguments.add(record.fromRid);
			arguments.add(record.toRid);
			final List<String> placeholders = record.properties.entrySet() //
				.stream() //
				.map(entry -> {
					arguments.add(entry.getValue());
					return "%s = ?".formatted(entry.getKey());
				}).toList();
			return "create edge %s from ? to ? set %s".formatted(record.edge, String.join(", ", placeholders));
		}).toList();
		command("sqlscript", String.join("; ", commands), arguments);
	}

	private record CreateEdgeRecord(String edge, String fromRid, String toRid, Map<String, String> properties) {}

	private static ResultSet deleteEdge(final String edge, final String where, final List<String> params) {
		return command("sql", "delete from %s where %s".formatted(edge, where), params);
	}

	private static void createVertexType(final String label) {
		command("sql", "create vertex type %s if not exists".formatted(label));
		command("sql", "alter type %s BucketSelectionStrategy `thread`".formatted(label));
		createIndexedStringProperty(label, PROPERTY_INTERNAL_ID, true);
	}

	private static void createHierarchyEdgeType(final String label) {
		createEdgeType(label);
		createStringProperty(label, PROPERTY_SWAP);
		createStringProperty(label, PROPERTY_ORDER_NUMBER);
		createIndexedStringProperty(label, PROPERTY_INTERNAL_FROM, false);
		createIndexedStringProperty(label, PROPERTY_INTERNAL_TO, false);
		createIndex(label, List.of(PROPERTY_INTERNAL_FROM, PROPERTY_INTERNAL_TO, PROPERTY_SWAP, PROPERTY_ORDER_NUMBER), true);
	}

	private static void createEdgeType(final String label) {
		command("sql", "create edge type %s if not exists".formatted(label));
		command("sql", "alter type %s BucketSelectionStrategy `thread`".formatted(label));
	}

	private static void createStringProperty(final String label, final String property) {
		createProperty(label, property, "STRING");
	}

	private static void createIndexedStringProperty(final String label, final String property, final boolean unique) {
		createStringProperty(label, property);
		createIndex(label, Collections.singletonList(property), unique);
	}

	private static void createProperty(final String label, final String property, final String type) {
		command("sql", "create property " + label + "." + property + " " + type);
	}

	private static void createIndex(final String label, final List<String> properties, final boolean unique) {
		command("sql", "create index on %s (%s) %s".formatted(label, String.join(",", properties), unique ? "UNIQUE" : "NOTUNIQUE"));
	}

	private static ResultSet command(final String language, final String command) {
		//		System.out.print("\nCommand: " + command);
		return DATABASE.command(language, command);
	}

	private static ResultSet command(final String language, final String command, Collection<String> parameters) {
		//		System.out.print("\nCommand: " + command + "; Parameters: " + parameters);
		return DATABASE.command(language, command, parameters.toArray());
	}

	private static void closeDatabase() {
		if (DATABASE != null && DATABASE.isOpen()) {
			DATABASE.close();
		}
		DATABASE = null;
	}

	private static void stopServer() {
		if (SERVER != null && SERVER.isStarted()) {
			SERVER.stop();
		}
		SERVER = null;
	}

	private static void prepareTestDirectory() {
		System.out.print("\nPreparing test directory.");
		ROOT_PATH = Paths.get(ROOT_DIR);
		clearTestDirectory(ROOT_PATH);
		createDirectoryIfNotExists(ROOT_PATH);
		createTmpDirectories(ROOT_PATH);
	}

	private static void createTmpDirectories(final Path rootPath) {
		TMP_DIRS.stream().map(rootPath::resolve).forEach(DeleteTest::createDirectoryIfNotExists);
	}

	private static void createDirectoryIfNotExists(final Path dir) {
		if (Files.exists(dir)) {
			return;
		}
		try {
			Files.createDirectory(dir);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	}

	private static void startArcadeDBServer() {
		System.out.print("\nStarting ArcadeDB server.");
		final ContextConfiguration config = new ContextConfiguration(
			Map.of(GlobalConfiguration.SERVER_ROOT_PASSWORD.getKey(), ROOT_PASSWORD, GlobalConfiguration.SERVER_ROOT_PATH.getKey(), ROOT_PATH.toAbsolutePath().toString()));
		SERVER = new ArcadeDBServer(config);
		SERVER.start();
		SERVER.createDatabase(DB_NAME, MODE.READ_WRITE);
	}

	private static void initRemoteDatabase() {
		System.out.print("\nInitializing remote database.");
		DATABASE = new RemoteDatabase("localhost", 2480, DB_NAME, "root", ROOT_PASSWORD);
	}

	private static void clearTestDirectory(final Path rootPath) {
		TMP_DIRS.stream() //
			.map(rootPath::resolve) //
			.flatMap(path -> {
				try {
					return Files.walk(path).sorted(Comparator.reverseOrder());
				} catch (IOException e) {
					System.err.println("Failed to enter: " + e.getMessage());
					return null;
				}
			}) //
			.filter(Objects::nonNull) //
			.forEach(path -> {
				try {
					Files.delete(path);
				} catch (IOException e) {
					System.err.println("Failed to delete: " + e.getMessage());
				}
			});
	}
}

Short explanation:
In the code above first, 6 edges were created then the delete operation with many ORs to delete those edges was executed and after those edges were recreated again.
At the end I've got DuplicatedKeyException because those edges weren't recreated successfully, because delete operation only deleted the first record that matches the first part of complex expression and didn't delete all the other records that match expression after OR.

You can see that in the web console:
before delete

Image

after delete

Image

5 more records remain untouched and only 1 was deleted.

Activity

  1. ruslan-butyk-fntext
    changed the title [-]SQL: Only first matched record is deleted when expression with many `OR`s is being evaluated[/-] [+]SQL: Only records that match first part of big `OR` expression are deleted[/+] on Oct 23, 2025

  2. gramian commented on Oct 23, 2025

    @gramian
    gramian
    on Oct 23, 2025
    Collaborator

    Hi,
    I tried a simplified case:

    CREATE DOCUMENT TYPE doc;
CREATE PROPERTY doc.num LONG;
INSERT INTO doc SET num = 1;
INSERT INTO doc SET num = 2;
INSERT INTO doc SET num = 3;

    with

    DELETE FROM doc WHERE (num = 1) OR (num = 2)

    and

    DELETE FROM doc WHERE ((num = 1) AND (num = 1)) OR ((num = 2) AND (num = 2))

    and it seems to work as expected.
    Maybe you could change this small example above to reproduce your error?

  3. gramian commented on Oct 23, 2025

    @gramian
    gramian
    on Oct 23, 2025
    Collaborator

    Also, could you print the where string?
    And, did you try wrapping the delete and the recreate of edges in separate transactions to avoid overlap?

  4. ruslan-butyk-fntext commented on Oct 23, 2025

    @ruslan-butyk-fntext
    ruslan-butyk-fntext
    on Oct 23, 2025
    Author

    Hello @gramian!
    Please uncomment print out in next methods from the example and run the code and you will get all the sql commands:

    private static ResultSet command(final String language, final String command) {
		//		System.out.print("\nCommand: " + command);
		return DATABASE.command(language, command);
	}

	private static ResultSet command(final String language, final String command, Collection<String> parameters) {
		//		System.out.print("\nCommand: " + command + "; Parameters: " + parameters);
		return DATABASE.command(language, command, parameters.toArray());
	}

    Here they are:

    Command: create vertex type duct if not exists
Command: alter type duct BucketSelectionStrategy `thread`
Command: create property duct.internal_id STRING
Command: create index on duct (internal_id) UNIQUE
Command: create edge type hierarchy_duct_duct if not exists
Command: alter type hierarchy_duct_duct BucketSelectionStrategy `thread`
Command: create property hierarchy_duct_duct.swap STRING
Command: create property hierarchy_duct_duct.order_number STRING
Command: create property hierarchy_duct_duct.internal_from STRING
Command: create index on hierarchy_duct_duct (internal_from) NOTUNIQUE
Command: create property hierarchy_duct_duct.internal_to STRING
Command: create index on hierarchy_duct_duct (internal_to) NOTUNIQUE
Command: create index on hierarchy_duct_duct (internal_from,internal_to,swap,order_number) UNIQUE
Command: insert into duct (internal_id) values (?); insert into duct (internal_id) values (?); insert into duct (internal_id) values (?); insert into duct (internal_id) values (?); insert into duct (internal_id) values (?); insert into duct (internal_id) values (?); insert into duct (internal_id) values (?); Parameters: [duct_elid, sub_duct_elid_1, sub_duct_elid_2, sub_duct_elid_3, sub_duct_elid_4, sub_duct_elid_5, sub_duct_elid_6]
Command: select @rid, internal_id from duct where internal_id in [?, ?, ?, ?, ?, ?, ?]; Parameters: [duct_elid, sub_duct_elid_1, sub_duct_elid_2, sub_duct_elid_3, sub_duct_elid_4, sub_duct_elid_5, sub_duct_elid_6]
Command: create edge hierarchy_duct_duct from ? to ? set order_number = ?, swap = ?, internal_from = ?, internal_to = ?; create edge hierarchy_duct_duct from ? to ? set order_number = ?, swap = ?, internal_from = ?, internal_to = ?; create edge hierarchy_duct_duct from ? to ? set order_number = ?, swap = ?, internal_from = ?, internal_to = ?; create edge hierarchy_duct_duct from ? to ? set order_number = ?, swap = ?, internal_from = ?, internal_to = ?; create edge hierarchy_duct_duct from ? to ? set order_number = ?, swap = ?, internal_from = ?, internal_to = ?; create edge hierarchy_duct_duct from ? to ? set order_number = ?, swap = ?, internal_from = ?, internal_to = ?; Parameters: [#1:0, #1:1, 1, N, duct_elid, sub_duct_elid_1, #1:0, #1:2, 1, N, duct_elid, sub_duct_elid_2, #1:0, #1:3, 1, N, duct_elid, sub_duct_elid_3, #1:0, #1:4, 1, N, duct_elid, sub_duct_elid_4, #1:0, #1:5, 1, N, duct_elid, sub_duct_elid_5, #1:0, #1:6, 1, N, duct_elid, sub_duct_elid_6]
Command: delete from hierarchy_duct_duct where ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)) OR ((internal_from = ?) AND (internal_to = ?) AND (swap = ?) AND (order_number = ?)); Parameters: [duct_elid, sub_duct_elid_1, N, 1, duct_elid, sub_duct_elid_2, N, 1, duct_elid, sub_duct_elid_3, N, 1, duct_elid, sub_duct_elid_4, N, 1, duct_elid, sub_duct_elid_5, N, 1, duct_elid, sub_duct_elid_6, N, 1]
Command: select @rid, internal_id from duct where internal_id in [?, ?, ?, ?, ?, ?, ?]; Parameters: [duct_elid, sub_duct_elid_1, sub_duct_elid_2, sub_duct_elid_3, sub_duct_elid_4, sub_duct_elid_5, sub_duct_elid_6]
Command: create edge hierarchy_duct_duct from ? to ? set order_number = ?, swap = ?, internal_from = ?, internal_to = ?; create edge hierarchy_duct_duct from ? to ? set order_number = ?, swap = ?, internal_from = ?, internal_to = ?; create edge hierarchy_duct_duct from ? to ? set order_number = ?, swap = ?, internal_from = ?, internal_to = ?; create edge hierarchy_duct_duct from ? to ? set order_number = ?, swap = ?, internal_from = ?, internal_to = ?; create edge hierarchy_duct_duct from ? to ? set order_number = ?, swap = ?, internal_from = ?, internal_to = ?; create edge hierarchy_duct_duct from ? to ? set order_number = ?, swap = ?, internal_from = ?, internal_to = ?; Parameters: [#1:0, #1:1, 1, N, duct_elid, sub_duct_elid_1, #1:0, #1:2, 1, N, duct_elid, sub_duct_elid_2, #1:0, #1:3, 1, N, duct_elid, sub_duct_elid_3, #1:0, #1:4, 1, N, duct_elid, sub_duct_elid_4, #1:0, #1:5, 1, N, duct_elid, sub_duct_elid_5, #1:0, #1:6, 1, N, duct_elid, sub_duct_elid_6]

    All the commands are executed in autocommit mode I don't start or commit transaction manually they all are executed in separate transactions, so there should be no overlap between delete and create.

  5. ruslan-butyk-fntext commented on Oct 24, 2025

    @ruslan-butyk-fntext
    ruslan-butyk-fntext
    on Oct 24, 2025
    Author

    I've done additional investigation and when separate deletes and add rebuild index * between each delete:

    Command: delete from hierarchy_duct_duct where internal_from = ? AND internal_to = ? AND swap = ? AND order_number = ?; Parameters: [duct_elid, sub_duct_elid_1, N, 1]
Command: rebuild index *
Command: delete from hierarchy_duct_duct where internal_from = ? AND internal_to = ? AND swap = ? AND order_number = ?; Parameters: [duct_elid, sub_duct_elid_2, N, 1]
Command: rebuild index *
Command: delete from hierarchy_duct_duct where internal_from = ? AND internal_to = ? AND swap = ? AND order_number = ?; Parameters: [duct_elid, sub_duct_elid_3, N, 1]
Command: rebuild index *
Command: delete from hierarchy_duct_duct where internal_from = ? AND internal_to = ? AND swap = ? AND order_number = ?; Parameters: [duct_elid, sub_duct_elid_4, N, 1]
Command: rebuild index *
Command: delete from hierarchy_duct_duct where internal_from = ? AND internal_to = ? AND swap = ? AND order_number = ?; Parameters: [duct_elid, sub_duct_elid_5, N, 1]
Command: rebuild index *
Command: delete from hierarchy_duct_duct where internal_from = ? AND internal_to = ? AND swap = ? AND order_number = ?; Parameters: [duct_elid, sub_duct_elid_6, N, 1]
Command: rebuild index *

    then it works.
    It looks like a problem with indexes (or composite indexes).

    Here is updated method from the example:

    private static void executeTestCase() {
		System.out.print("\nExecuting test case.");
		final List<List<String>> arguments = List.of(
			List.of(DUCT_ELID, SUB_DUCT_ELID_1, SWAP, ORDER_NUMBER),
			List.of(DUCT_ELID, SUB_DUCT_ELID_2, SWAP, ORDER_NUMBER),
			List.of(DUCT_ELID, SUB_DUCT_ELID_3, SWAP, ORDER_NUMBER),
			List.of(DUCT_ELID, SUB_DUCT_ELID_4, SWAP, ORDER_NUMBER),
			List.of(DUCT_ELID, SUB_DUCT_ELID_5, SWAP, ORDER_NUMBER),
			List.of(DUCT_ELID, SUB_DUCT_ELID_6, SWAP, ORDER_NUMBER)
		);
		arguments.forEach(args -> {
			deleteEdge(EDGE_HIERARCHY_DUCT_DUCT, "internal_from = ? AND internal_to = ? AND swap = ? AND order_number = ?", args);
			command("sql", "rebuild index *");
		});
		final Map<String, String> allDuctIdMap = queryIdMap(VERTEX_DUCT, ALL_DUCT_ELIDS);
		createHierarchyEdge(SUB_DUCT_ELIDS.stream().map(subDuctElid -> new CreateHierarchyEdgeRecord(EDGE_HIERARCHY_DUCT_DUCT, allDuctIdMap.get(DUCT_ELID),  allDuctIdMap.get(subDuctElid), DUCT_ELID, subDuctElid, ORDER_NUMBER)).toList());
	}

  6. gramian commented on Oct 24, 2025

    @gramian
    gramian
    on Oct 24, 2025
    Collaborator

    If you suspect the index to be the cause you could try the current repo head as since 25.9.1 two issues with the index have been fixed.

  7. ruslan-butyk-fntext commented on Oct 24, 2025

    @ruslan-butyk-fntext
    ruslan-butyk-fntext
    on Oct 24, 2025
    Author

    @gramian I've checked the latest head at main branch, and it doesn't work unfortunately.

  8. gramian commented on Oct 24, 2025

    @gramian
    gramian
    on Oct 24, 2025
    Collaborator

    Then it could be indeed the composite index.
    If the delete-recreate works without this composite index, then you could, as a workaround, add a property which either concatenates the composite index properties or computes a hash from the concatenation, and index this property with a non-composite index.

  9. ruslan-butyk-fntext commented on Oct 24, 2025

    @ruslan-butyk-fntext
    ruslan-butyk-fntext
    on Oct 24, 2025
    Author

    @gramian thanks for the hint. It looks like a general problem with indexes because when I remove composite key and add separate not unique indexes for each property it still deletes only first record, but when I remove indexes completely from that edge type it works fine, deletes all records as it should be.

  10. gramian commented on Oct 24, 2025

    @gramian
    gramian
    on Oct 24, 2025
    Collaborator

    Extending the example above:

    CREATE DOCUMENT TYPE doc;
CREATE PROPERTY doc.num LONG;
CREATE INDEX ON doc (num) NOTUNIQUE;
INSERT INTO doc SET num = 1;
INSERT INTO doc SET num = 2;
INSERT INTO doc SET num = 3;

    I still cannot reproduce this. But it is good to know that the index(es) seem responsible.

  11. robfrank
    self-assigned this
    on Oct 24, 2025
  12. robfrank
    added this to the 25.10.1 milestone on Oct 24, 2025
  13. robfrank
    added
    bug
    on Oct 24, 2025
  14. robfrank
    added the
    Bug
    issue type on Oct 24, 2025

  15. ruslan-butyk-fntext commented on Oct 24, 2025

    @ruslan-butyk-fntext
    ruslan-butyk-fntext
    on Oct 24, 2025
    Author

    @gramian indeed it does work with your example but for some reason it doesn't work with this example:

    CREATE VERTEX TYPE duct;
CREATE PROPERTY duct.id STRING;
CREATE INDEX ON duct (id) UNIQUE;
CREATE EDGE TYPE duct_duct;
CREATE PROPERTY duct_duct.from_id STRING;
CREATE INDEX ON duct_duct (from_id) NOTUNIQUE;
CREATE PROPERTY duct_duct.to_id STRING;
CREATE INDEX ON duct_duct (to_id) NOTUNIQUE;
CREATE PROPERTY duct_duct.swap STRING;
CREATE INDEX ON duct_duct (swap) NOTUNIQUE;
CREATE PROPERTY duct_duct.order_num STRING;
CREATE INDEX ON duct_duct (order_num) NOTUNIQUE;
CREATE INDEX ON duct_duct (from_id,to_id,swap,order_num) UNIQUE;

INSERT INTO duct (id) VALUES ('1'), ('2'), ('3');
CREATE EDGE duct_duct from #1:0 to #1:1 SET from_id='1', to_id='2', swap='N', order_num='1';
CREATE EDGE duct_duct from #1:0 to #1:2 SET from_id='1', to_id='3', swap='N', order_num='1';
DELETE FROM duct_duct WHERE (from_id='1' AND to_id='2' AND swap='N' AND order_num='1') OR (from_id='1' AND to_id='3' AND swap='N' AND order_num='1');
CREATE EDGE duct_duct from #1:0 to #1:1 SET from_id='1', to_id='2', swap='N', order_num='1';
CREATE EDGE duct_duct from #1:0 to #1:2 SET from_id='1', to_id='3', swap='N', order_num='1';

    I guess it has something to do with edges besides indexes

  16. 3 remaining items

  17. robfrank
    added 2 commits that reference this issue on Nov 5, 2025

    Fix issue #2695: DELETE with multiple OR conditions only deleting fir…

    b5fe173

    Fix issue #2695: DELETE with multiple OR conditions only deleting fir…

    6ddc42d
  18. robfrank
    linked a pull request that will close this issueReview and plan solution for GitHub issue #2756on Nov 5, 2025
  19. robfrank
    closed this as completedin #2756on Nov 5, 2025

  20. ruslan-butyk-fntext commented on Nov 6, 2025

    @ruslan-butyk-fntext
    ruslan-butyk-fntext
    on Nov 6, 2025
    Author

    The issue is not solved. This case is still reproducible when composite index is used with more than two properties on edge:

    CREATE VERTEX TYPE duct;
CREATE PROPERTY duct.id STRING;
CREATE INDEX ON duct (id) UNIQUE;
CREATE EDGE TYPE duct_duct;
CREATE PROPERTY duct_duct.from_id STRING;
CREATE INDEX ON duct_duct (from_id) NOTUNIQUE;
CREATE PROPERTY duct_duct.to_id STRING;
CREATE INDEX ON duct_duct (to_id) NOTUNIQUE;
CREATE PROPERTY duct_duct.swap STRING;
CREATE INDEX ON duct_duct (swap) NOTUNIQUE;
CREATE INDEX ON duct_duct (from_id,to_id,swap) UNIQUE;

INSERT INTO duct (id) VALUES ('1'), ('2'), ('3');
CREATE EDGE duct_duct from #1:0 to #1:1 SET from_id='1', to_id='2', swap='N';
CREATE EDGE duct_duct from #1:0 to #1:2 SET from_id='1', to_id='3', swap='N';
DELETE FROM duct_duct WHERE (from_id='1' AND to_id='2' AND swap='N') OR (from_id='1' AND to_id='3' AND swap='N');
CREATE EDGE duct_duct from #1:0 to #1:1 SET from_id='1', to_id='2', swap='N';
CREATE EDGE duct_duct from #1:0 to #1:2 SET from_id='1', to_id='3', swap='N';

    Running the code from description also throws an DuplicatedKeyException exception because records were not deleted.

  21. robfrank
    reopened this on Nov 6, 2025
  22. robfrank
    modified the milestones: 25.10.1, 25.11.1 on Nov 6, 2025

  23. robfrank commented on Nov 6, 2025

    @robfrank
    robfrank
    on Nov 6, 2025
    Collaborator

    First triage test. If I remove the 3 additional indexes all works fine:

            CREATE INDEX ON duct_duct (from_id) NOTUNIQUE;
        CREATE INDEX ON duct_duct (to_id) NOTUNIQUE;
        CREATE INDEX ON duct_duct (swap) NOTUNIQUE;

    That's not good.

  24. robfrank
    linked a pull request that will close this issuefix: improve delete functionality with multiple OR conditions and enh… #2760on Nov 6, 2025

  25. robfrank commented on Nov 6, 2025

    @robfrank
    robfrank
    on Nov 6, 2025
    Collaborator

    the PR should fix the problem. I created a new test with the scenario of the comment.
    I'll merge it later

  26. robfrank
    added a commit that references this issue on Nov 6, 2025

    #2695 fix: improve delete functionality with multiple OR conditions a…

    1cac4b8
  27. robfrank
    closed this as completedin #2760on Nov 6, 2025
  28. robfrank
    added a commit that references this issue on Nov 10, 2025

    #2695 fix: improve delete functionality with multiple OR conditions a…

    ce1ab3b
  29. tae898
    added a commit that references this issue on Jun 28, 2026

    ArcadeData#2695 fix: improve delete functionality with multiple OR co…

    27c7fc7
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

Bug

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions