exception thrown when using @rid < ? in prepared statement and providing rid value as String #741
Description
ArcadeDB Version: ArcadeDB Server v23.1.1-SNAPSHOT (build 0ba5399/1672916179785/main)
JDK Version: openjdk version "11.0.12" 2021-07-20 LTS
OpenJDK Runtime Environment Corretto-11.0.12.7.1 (build 11.0.12+7-LTS)
OpenJDK 64-Bit Server VM Corretto-11.0.12.7.1 (build 11.0.12+7-LTS, mixed mode)
OS: CentOS release 6.9 (Final)
Expected behavior
when the rid is used in the WHERE clause of a simple (i.e. not prepared) sql statement, it can be provided as a String literal:
SELECT from Order WHERE @rid = '#3:0' ORDER BY @rid DESC LIMIT 10
It is expected that it is possible to pass the rid as a String parameter to an sql prepared statement.
The reason is that there are cases in which the positional parameters are determined dynamically (e.g. when the user selects filter criteria from a UI). Since Database.query and Database.command accept an array of Object instances representing positional parameters, the most straightforward solution for this use case is to put all the parameters into an array of Object, without worrying about the type of the individual parameters.
Actual behavior
If the rid is provided as a String positional parameter to an sql prepared statement, it produces an unexpected and inconsistent behavior:
- [@]rid < ? causes a class cast exception
- [@]rid > ? produces no result even when it should
- [@]rid = ? works as expected
If the rid is provided as a RID object, the prepared statement behaves correctly with <, > and =.
Steps to reproduce
The attached Test class reproduces the problem.
This is the output I have obtained (I have removed some irrelevant ArcadeDB start-up messages):
2023-01-05 16:21:45.372 INFO [ArcadeDBServer] <ArcadeDB_0> ArcadeDB Server v23.1.1-SNAPSHOT (build 0ba53990ff4ef597a5171cd9eac6e41b304d8cb9/1672916179785/main) is starting up...
2023-01-05 16:21:45.373 INFO [ArcadeDBServer] <ArcadeDB_0> Starting ArcadeDB Server with plugins [] ...
2023-01-05 16:21:45.425 INFO [ArcadeDBServer] <ArcadeDB_0> - JMX Metrics Started...
2023-01-05 16:21:45.515 INFO [HttpServer] <ArcadeDB_0> - Starting HTTP Server (host=0.0.0.0 port=2480-2489)...
2023-01-05 16:21:45.543 INFO [undertow] starting server: Undertow - 2.3.2.Final
2023-01-05 16:21:45.548 INFO [xnio] XNIO version 3.8.8.Final
2023-01-05 16:21:45.557 INFO [nio] XNIO NIO Implementation Version 3.8.8.Final
2023-01-05 16:21:45.934 INFO [threads] JBoss Threads version 3.5.0.Final
2023-01-05 16:21:45.980 INFO [ArcadeDBServer] <ArcadeDB_0> Available query languages: [mongo, gremlin, java, cypher, js, graphql, sql]
2023-01-05 16:21:45.981 INFO [ArcadeDBServer] <ArcadeDB_0> ArcadeDB Server started in 'development' mode (CPUs=8 MAXRAM=3.93GB)
2023-01-05 16:21:45.982 INFO [ArcadeDBServer] <ArcadeDB_0> Studio web tool available at http://localhost:2480
created bucket O202203
insert record: sqlString = UPDATE BUCKET:O202203 SET processor = ?, vstart = ?, vstop = ?, status = ? UPSERT RETURN AFTER WHERE processor = ? AND vstart = ? AND vstop = ?, result = {"@rid":"#3:0","@type":"Order","@cat":"d","vstart":"20220319_002905.423534","vstop":"20220319_003419.571172","processor":"SIR1LRM-7.1","status":"PENDING"}
insert record: sqlString = UPDATE BUCKET:O202203 SET processor = ?, vstart = ?, vstop = ?, status = ? UPSERT RETURN AFTER WHERE processor = ? AND vstart = ? AND vstop = ?, result = {"@rid":"#3:1","@type":"Order","@cat":"d","vstart":"20220319_002624.404379","vstop":"20220319_002826.525650","processor":"SIR1LRM-7.1","status":"PENDING"}
Test 1: select with @rid < #3:1 (as String) using prepared statement
sqlString = SELECT from Order WHERE @rid < ? ORDER BY @rid DESC LIMIT 10
class java.lang.String cannot be cast to class com.arcadedb.database.Identifiable (java.lang.String is in module java.base of loader 'bootstrap'; com.arcadedb.database.Identifiable is in unnamed module of loader 'app')
Test 2: select with @rid > #3:0 (as String) using prepared statement
sqlString = SELECT from Order WHERE @rid > ? ORDER BY @rid DESC LIMIT 10
result:
Test 3: select with @rid = #3:0 (as String) using prepared statement
sqlString = SELECT from Order WHERE @rid = ? ORDER BY @rid DESC LIMIT 10
result: @rid = #3:0, processor = SIR1LRM-7.1, vstart = 20220319_002905.423534, vstop = 20220319_003419.571172, pstart = null, pstop = null, status = PENDING, node = null
Test 4: select with @rid < #3:1 using simple statement
sqlString = SELECT from Order WHERE @rid < #3:1 ORDER BY @rid DESC LIMIT 10
result: @rid = #3:0, processor = SIR1LRM-7.1, vstart = 20220319_002905.423534, vstop = 20220319_003419.571172, pstart = null, pstop = null, status = PENDING, node = null
Test 5: select with @rid = '#3:0' using simple statement
sqlString = SELECT from Order WHERE @rid = '#3:0' ORDER BY @rid DESC LIMIT 10
result: @rid = #3:0, processor = SIR1LRM-7.1, vstart = 20220319_002905.423534, vstop = 20220319_003419.571172, pstart = null, pstop = null, status = PENDING, node = null
Test 6: select with @rid < #3:1 (as RID object) using prepared statement
sqlString = SELECT from Order WHERE @rid < ? ORDER BY @rid DESC LIMIT 10
result: @rid = #3:0, processor = SIR1LRM-7.1, vstart = 20220319_002905.423534, vstop = 20220319_003419.571172, pstart = null, pstop = null, status = PENDING, node = null
2023-01-05 16:21:46.107 INFO [ArcadeDBServer] <ArcadeDB_0> Shutting down ArcadeDB Server...
2023-01-05 16:21:46.108 INFO [undertow] stopping server: Undertow - 2.3.2.Final
2023-01-05 16:21:46.402 INFO [ArcadeDBServer] <ArcadeDB_0> - Stop JMX Metrics
2023-01-05 16:21:46.402 INFO [ArcadeDBServer] <ArcadeDB_0> ArcadeDB Server is down