Issue #1717: InternalException when using MEMBER OF on a large list in a query

Hello,
we get when executing a query with MEMBER OF on a large list the following InternalException:

Caused by: com.objectdb.o.InternalException: merge2 com.btc.ep.coverage.bl.internal.dmos.CoverageRecordImpl:null => merger[2850057]-missing:223
    at com.objectdb.o.PBI.B(PBI.java:124)
    at com.objectdb.o.PBI.q(PBI.jave:97)
    at com.objectdb.o.OBI.Vp(OBI.java:244)
    (... more see attached Image)

Our query looks like this:

String recordClassName = CoverageRecordImpl.class.getName();
String propertyClassName = propertyClass.getName();
String query = "SELECT DISTINCT property.uid FROM " + propertyClassName + " AS property, "
    + recordClassName + " AS record "
    + "WHERE property.uid MEMBER OF record.properties AND property.scopeID MEMBER OF ?1";
TypedQuery<String> q = em.createQuery(query, String.class);
q.setParameter(1, scopeIDs); //scopeIDs is a Set<String>
List<String> resultArray = q.getResultList();

Basically, the query works as expected.
The above exception occurs only when the Set record.properties contains more than 20.000 elements.

The field 'properties' (query: record.properties) of CoverageRecordImpl is a list of String:

@Basic
private List<String> properties = new ArrayList<>();

The stack trace indicates an internal ObjectDB error, which is not specifically related to MEMBER OF query.

A similar issue was fixed a few years ago and since then this is the first report.

First, please check your database with the Doctor. If the error repeats, try to fix the database by running the Doctor in repair mode (even if no errors are found by the Doctor).

You may also disable an optimization that may be related to this error, by setting a system property before accessing ObjectDB:

    System.setProperty("com.objectdb.disable.optimization.small", "true");

Any further information regarding this issue may be helpful.

At first, I checked the database with the Doctor. -> no Problem.
I reproduced the exception by execute the Query in the explorer. Also with the outcome of the Doctor in repair mode.

But after many experiments I found out that the Query works, if I add an unless condition.

That means the following query don't works:
"SELECT DISTINCT property.uid FROM CovPropImpl AS property, CovRecImpl AS record WHERE property.uid MEMBER OF record.properties"

But after adding the condition 3<>4, which should always be true, the query works
"SELECT DISTINCT property.uid FROM CovPropImpl AS property, CovRecImpl AS record WHERE 3<>4 AND property.uid MEMBER OF record.properties"

I created (with a lot of effort) a 1-file example. I think it's best if you analyse the sample.

The example works if you reduce the count (line: 31) to 20.000  or  if you switch to the modified query "queryString_Ok" (line: 63)

Thank you very much for this test - it wouldn't be possible to understand and fix the issue without this help.

Build 2.6.3_04 should fix it.

The issue is caused because of a bug in handling query results that combine large objects with long primary keys. In such objects the keys may not be embedded in the object content but instead stored separately with references from the objects, and specific code in the query processing module was not aware to this format (which is not commonly used).

The database itself cannot be affected by the bug (so no need to repair it with the Doctor) but various queries can fail with versions of ObjectDB before the new build.

Note that although the new build fixes the exception - storing large objects with long primary keys is not very efficient, because non embeddable keys are slower. If possible, try to avoid primary keys longer than 32 bytes (in the test they seem to be 36 bytes).

Thanks for the fix.

Also thanks for the hint with long keys.
Can you give us a recommendation as we can represent the Key (UUID) most efficient?
Both in terms of memory usage of the database files, memory usage of the JVM and efficiency for queries.

We already need the key before the entities are persisted so that we can build even references.

a) Composite Primary Key  with two Longs
b) Embedded Primary Key with two Longs
c) Furthermore, as String (but reduced to a 22 char instead of actual 36 char)
  - Would it bring someting to specify the length with the Column annotation?
d) Or would you recommend something else?

Finally, how much performance improvement would bring a switch to a single long?

Your question was moved to a new thread in the forum.