user2524707 user2524707 - 8 days ago 6
Java Question

Neo4j problems with type comparisons in "WHERE"

I am getting the following error when executing my Neo4j queries through java:

org.neo4j.graphdb.QueryExecutionException: Don't know how to compare that. Left: "0" (String); Right: 0 (Long)
at org.neo4j.kernel.impl.query.QueryExecutionKernelException.asUserException(QueryExecutionKernelException.java:35)
at org.neo4j.cypher.internal.javacompat.ExecutionResult.converted(ExecutionResult.java:399)
at org.neo4j.cypher.internal.javacompat.ExecutionResult.hasNext(ExecutionResult.java:232)
at main.java.com.bag.server.database.Neo4jDatabaseAccess.readObject(Neo4jDatabaseAccess.java:172)
at main.java.com.bag.server.TestServer.handleNodeRead(TestServer.java:259)
at main.java.com.bag.server.TestServer.appExecuteUnordered(TestServer.java:153)
at bftsmart.tom.server.defaultservices.DefaultRecoverable.executeUnordered(DefaultRecoverable.java:417)
at bftsmart.tom.ServiceReplica.receiveReadonlyMessage(ServiceReplica.java:214)
at bftsmart.tom.core.DeliveryThread.deliverUnordered(DeliveryThread.java:289)
at bftsmart.tom.core.TOMLayer.requestReceived(TOMLayer.java:290)
at bftsmart.communication.client.netty.NettyClientServerCommunicationSystemServerSide.channelRead0(NettyClientServerCommunicationSystemServerSide.java:184)
at bftsmart.communication.client.netty.NettyClientServerCommunicationSystemServerSide.channelRead0(NettyClientServerCommunicationSystemServerSide.java:61)
at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:105)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:292)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:278)
at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:277)
at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:264)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:292)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:278)
at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:962)
at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131)
at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:528)
at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:485)
at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:399)
at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:371)
at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:112)
at io.netty.util.concurrent.DefaultThreadFactory$DefaultRunnableDecorator.run(DefaultThreadFactory.java:137)
at java.lang.Thread.run(Thread.java:745)


But I am sure that I have the "0" as a String inside the Database.
And that I am entering it as a string in the query:

String.format(" WHERE r.%s <= %s OR n.%s IS NULL", "snapshotId", Long.toString(0), "snapshotId")

Answer

You're using string mangling instead of parameters, so you lose the type info (because it's just putting it all in a string). If you type the character '0' without quotes in Cypher, it is going to be interpreted as an Int. So instead of your current query,

WHERE r.snapshotId <= 0 or n.snapshotId IS NULL

you actually want it to say

WHERE r.snapshotId <= '0' or n.snapshotId IS NULL

with the quotes around the '0', so it treats it as a string.

The real solution, though, is instead of string mangling, write a query to say:

WHERE r.snapshotId <= {zero_string} or n.snapshotId IS NULL

and then pass a parameter zero_string that is set to Long.toString(0). That way the driver will handle types for you while packing, unpacking, and interpreting the data.

EDIT: Or if you really need the property name to be dynamic as well, pass it as a parameter too:

WHERE r[{zero_param}] <= {zero_string} or n[{zero_param}] IS NULL

UPDATE: You can modify this to work for multiple key-value pairs by passing in a Map and doing some iterative work. The naive way to do it would be like so:

WHERE ALL(k IN KEYS({map_param}) WHERE r[k] <= {map_param}[k] OR n[k] IS NULL)

but this is likely to be very slow on any scale, as I don't think the query planner will be able to optimize it. Try to narrow down your r matches on some other criteria before you apply this filter.

Comments