I'm trying to query record filtering on the date (documentation for date)
select * from InstallationFee where infinite OR (date() >= dateFrom and date() <= dateTo)
The query returns the following error in the studio:
java.lang.ClassCastException: com.orientechnologies.orient.core.sql.filter.OSQLFilterItemField cannot be cast to com.orientechnologies.orient.core.sql.filter.OSQLFilterCondition
Here is the stack trace in the JAVA api
com.orientechnologies.orient.core.exception.OCommandExecutionException: Error on execution of command: sql.select * from InstallationFee where (infinite OR date() >= dateFrom and date() <= dateTo)
at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.executeCommand(OAbstractPaginatedStorage.java:1190)
at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.command(OAbstractPaginatedStorage.java:1173)
at com.orientechnologies.orient.core.sql.query.OSQLQuery.run(OSQLQuery.java:71)
at com.orientechnologies.orient.core.sql.query.OSQLSynchQuery.run(OSQLSynchQuery.java:85)
at com.orientechnologies.orient.core.query.OQueryAbstract.execute(OQueryAbstract.java:33)
at com.orientechnologies.orient.server.network.protocol.binary.ONetworkProtocolBinary.command(ONetworkProtocolBinary.java:1178)
at com.orientechnologies.orient.server.network.protocol.binary.ONetworkProtocolBinary.executeRequest(ONetworkProtocolBinary.java:385)
at com.orientechnologies.orient.server.network.protocol.binary.OBinaryNetworkProtocolAbstract.execute(OBinaryNetworkProtocolAbstract.java:216)
at com.orientechnologies.common.thread.OSoftThread.run(OSoftThread.java:65)
Caused by: java.lang.ClassCastException: com.orientechnologies.orient.core.sql.filter.OSQLFilterItemField cannot be cast to com.orientechnologies.orient.core.sql.filter.OSQLFilterCondition
at com.orientechnologies.orient.core.sql.OFilterAnalyzer.analyzeUnion(OFilterAnalyzer.java:195)
at com.orientechnologies.orient.core.sql.OFilterAnalyzer.analyzeOrFilterBranch(OFilterAnalyzer.java:80)
at com.orientechnologies.orient.core.sql.OFilterAnalyzer.analyzeMainCondition(OFilterAnalyzer.java:58)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.searchForIndexes(OCommandExecutorSQLSelect.java:1454)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.searchInClasses(OCommandExecutorSQLSelect.java:765)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLResultsetAbstract.assignTarget(OCommandExecutorSQLResultsetAbstract.java:194)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.assignTarget(OCommandExecutorSQLSelect.java:438)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.executeSearch(OCommandExecutorSQLSelect.java:420)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLSelect.execute(OCommandExecutorSQLSelect.java:391)
at com.orientechnologies.orient.core.sql.OCommandExecutorSQLDelegate.execute(OCommandExecutorSQLDelegate.java:64)
at com.orientechnologies.orient.core.storage.impl.local.OAbstractPaginatedStorage.executeCommand(OAbstractPaginatedStorage.java:1184)
I tried a different approaches in the studio, but all resulted with the error presented above. Here are a few:
Using sysdate
select * from InstallationFee where infinite OR (sysdate("yyyy-MM-dd") >= dateFrom and sysdate("yyyy-MM-dd") <= dateTo)
Using between (documentation)
select * from InstallationFee where infinite OR date("yyyy-MM-dd") between dateFrom and dateTo)
Am I missing something? or Is there a way around this problem?
I believe the error comes from:
... where infinite ...
It should be:
... where infinite = true ...