Summary

A SQL query using IN :param with a collection parameter returns no rows when ArcadeDB chooses the indexed execution path.

The same collection parameter works when index usage is avoided, and equivalent forms using a literal list or expanded scalar parameters also work.

Environment Tested

• ArcadeDB server: 26.6.1-SNAPSHOT
• Server source commit: 716cfa523
• Java remote client jar used by the test: arcadedb-network-26.4.2.jar
• Query language: SQL
• Access path: RemoteDatabase.query(...)

Reproduction Database

I reproduced this against a fresh database created only for this issue.

Schema and data:

create vertex type IssueItem if not exists;
create property IssueItem.code integer;
create index on IssueItem (code) notunique;

insert into IssueItem set code = 1, name = 'one';
insert into IssueItem set code = 2, name = 'two';
insert into IssueItem set code = 3, name = 'three';

Literal SQL works as expected:

select code from IssueItem where code in [1, 2, 3] order by code;

Observed:

[1, 2, 3]

Java Reproduction

Using RemoteDatabase:

var db = new RemoteDatabase(
        "localhost",
        2480,
        "issue_in_param_index_repro",
        "root",
        "password"
);

var params = Map.of("codes", List.of(1L, 2L, 3L));

Failing Query

db.query(
        "sql",
        "select code from IssueItem where code in :codes order by code",
        params
);

Expected:

[1, 2, 3]

Actual:

[]

Same Parameter Works If Index Usage Is Avoided

db.query(
        "sql",
        "select code from IssueItem where (code + 0) in :codes order by code",
        params
);

Observed:

[1, 2, 3]

Expanded Scalar Parameters Work

db.query(
        "sql",
        "select code from IssueItem where code in [:c0, :c1, :c2] order by code",
        Map.of("c0", 1L, "c1", 2L, "c2", 3L)
);

Observed:

[1, 2, 3]

Positional Collection Parameter Also Fails

db.query(
        "sql",
        "select code from IssueItem where code in ? order by code",
        List.of(1L, 2L, 3L)
);

Observed:

[]

Why This Looks Like An Indexed Execution Path Issue

The collection parameter itself is usable by the non-indexed evaluator:

where (code + 0) in :codes

returns the expected rows.

The failure appears only when the indexed property is used directly:

where code in :codes

That suggests the planner/index path is treating the collection parameter differently from the normal IN evaluator.

Suspected Cause

The SQL IN condition is considered index-aware when the left side is an indexed field and the right side is an input parameter.

In the index execution path, FetchFromIndexStep.cartesianProduct(...) appears to expand only values that are Iterable:

if (value instanceof Iterable<?> iterable && !(value instanceof Identifiable)) {
    ...
}

However, HTTP parameter deserialization may produce primitive numeric arrays for JSON numeric arrays. Primitive arrays are multi-value values, but they are not Iterable.

If the indexed path receives a primitive array, it can treat the whole array as a single index key instead of expanding it into multiple index lookups.

The normal IN evaluator seems to work because it uses the multi-value utilities, which support arrays.

Expected Behavior

These forms should return the same rows:

where code in [1, 2, 3]

where code in :codes

where code in [:c0, :c1, :c2]

when codes is a Java collection/array containing 1, 2, and 3.