Repository navigation
Inconsistent behavior between Cypher vs SQL query with one field indexed while other not #1898
Description
This is related to #1888 which was closed as "question". But this scenario may not be just specific to my use-case.
ArcadeDB Version:
24.11.2
OS and JDK Version:
docker image
Expected behavior
Should fetch a record
Actual behavior
Did not fetched for SQL but Cypher query correct behavior.
Steps to reproduce
SQL script:
CREATE VERTEX TYPE Asset IF NOT EXISTS;
CREATE PROPERTY Asset.id IF NOT EXISTS STRING (mandatory true);
CREATE INDEX IF NOT EXISTS ON Asset (id) UNIQUE;
INSERT INTO Asset CONTENT {"id":"Engineering Workstation1", "addresses":"192.168.10.10,192.168.20.10"};
INSERT INTO Asset CONTENT {"id":"OT Chlorine Process #4"};
select from Asset where id='OT Chlorine Process #4' OR addresses LIKE '%OT Chlorine Process #4%'
works as expected, fetches correct record.
select from Asset where id='192.168.10.10' OR addresses LIKE '192.168.10.10'
no record but it should.
match(n:Asset)
where n.id='192.168.10.10' OR n.addresses CONTAINS '192.168.10.10'
return *
Cypher query fetches the correct record.
When I remove the index for id, the SQL statement works.
I tested under OrientDB, the same SQL statement works:
Activity
robfrank commentedon Jan 7, 2025
This is definitely a bug
lvca commentedon Jan 7, 2025
It looks like the OR condition is removed when the index is used. It must be something in the optimizer of the query planner that rewrite the query when indexes are being used.
#1898 complete review of SelectExecutionPlanner, re-enabled parts, so…
robfrank commentedon Jan 8, 2025
@jymcheong I prepared a PR that seems to solve the issue. I added a test similar to your code:
https://github.com/ArcadeData/arcadedb/pull/1900/files#diff-f3c848d4ae5605141cbbddcaa7ddfe16a0e447f7f850057d26d785dd834bb4d9
Even if, instead of a string, I'm using a list of string for the addresses, that should be your initial plan.
#1898 complete review of SelectExecutionPlanner
jymcheong commentedon Jan 9, 2025
@robfrank Thanks! Appreciate it! Yes list of string was initial plan.
chore(deps): bump jline.version from 4.1.0 to 4.1.2 [skip ci]
ArcadeData#1898 complete review of SelectExecutionPlanner, re-enabled…
ArcadeData#1898 complete review of SelectExecutionPlanner
chore(deps): bump jline.version from 4.1.0 to 4.1.2 [skip ci]