Query with a combination of LIKE and an Integer causes an unexpected server error
Created by: Quazgar
I tried this search:
SELECT number from Record Box with Number like "262"
which caused an unexpected server error with the following message on the server:
Dez 07, 2018 12:06:20 PM caosdb.server.CaosDBComponent log
SCHWERWIEGEND: ERROR:REQUEST:d997d0eab035f696cac14e06223597f0
caosdb.server.query.Query$QueryException: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''262' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `integer_' at line 1
at caosdb.server.query.POV.apply(POV.java:320)
at caosdb.server.query.Query.sourceStrategy(Query.java:268)
at caosdb.server.query.Query.executeStrategy(Query.java:441)
at caosdb.server.query.Query.execute(Query.java:477)
at caosdb.server.jobs.core.ExecuteQuery.job(ExecuteQuery.java:43)
at caosdb.server.jobs.FlagJob.run(FlagJob.java:35)
at caosdb.server.jobs.ScheduledJob.run(Schedule.java:42)
at caosdb.server.jobs.Schedule.runJob(Schedule.java:120)
at caosdb.server.jobs.Schedule.runJobs(Schedule.java:102)
at caosdb.server.transaction.Transaction.execute(Transaction.java:126)
at caosdb.server.resource.transaction.EntityResource.httpGetInChildClass(EntityResource.java:113)
at caosdb.server.resource.AbstractCaosDBServerResource.httpGet(AbstractCaosDBServerResource.java:175)
at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.restlet.resource.ServerResource.doHandle(ServerResource.java:511)
at org.restlet.resource.ServerResource.get(ServerResource.java:723)
at org.restlet.resource.ServerResource.doHandle(ServerResource.java:603)
at org.restlet.resource.ServerResource.doNegotiatedHandle(ServerResource.java:662)
at org.restlet.resource.ServerResource.doConditionalHandle(ServerResource.java:348)
at org.restlet.resource.ServerResource.handle(ServerResource.java:1020)
at org.restlet.resource.Finder.handle(Finder.java:236)
at org.restlet.routing.Filter.doHandle(Filter.java:150)
at org.restlet.routing.Filter.handle(Filter.java:197)
at org.restlet.routing.Router.doHandle(Router.java:422)
at org.restlet.routing.Router.handle(Router.java:641)
at caosdb.server.CaosDBServer$2.handle(CaosDBServer.java:403)
at org.restlet.routing.Filter.doHandle(Filter.java:150)
at org.restlet.routing.Filter.handle(Filter.java:197)
at org.restlet.routing.Filter.doHandle(Filter.java:150)
at org.restlet.routing.Filter.handle(Filter.java:197)
at org.restlet.routing.Filter.doHandle(Filter.java:150)
at org.restlet.routing.Filter.handle(Filter.java:197)
at org.restlet.routing.Router.doHandle(Router.java:422)
at org.restlet.routing.Router.handle(Router.java:641)
at caosdb.server.CaosDBServer$3.handle(CaosDBServer.java:421)
at org.restlet.routing.Filter.doHandle(Filter.java:150)
at org.restlet.routing.Filter.handle(Filter.java:197)
at org.restlet.routing.Filter.doHandle(Filter.java:150)
at org.restlet.routing.Filter.handle(Filter.java:197)
at org.restlet.routing.Filter.doHandle(Filter.java:150)
at org.restlet.engine.application.StatusFilter.doHandle(StatusFilter.java:140)
at org.restlet.routing.Filter.handle(Filter.java:197)
at org.restlet.routing.Filter.doHandle(Filter.java:150)
at org.restlet.routing.Filter.handle(Filter.java:197)
at org.restlet.engine.CompositeHelper.handle(CompositeHelper.java:202)
at org.restlet.engine.application.ApplicationHelper.handle(ApplicationHelper.java:77)
at org.restlet.Application.handle(Application.java:385)
at org.restlet.routing.Filter.doHandle(Filter.java:150)
at org.restlet.routing.Filter.handle(Filter.java:197)
at org.restlet.routing.Router.doHandle(Router.java:422)
at org.restlet.routing.Router.handle(Router.java:641)
at org.restlet.routing.Filter.doHandle(Filter.java:150)
at org.restlet.routing.Filter.handle(Filter.java:197)
at org.restlet.routing.Router.doHandle(Router.java:422)
at org.restlet.routing.Router.handle(Router.java:641)
at org.restlet.routing.Filter.doHandle(Filter.java:150)
at org.restlet.engine.application.StatusFilter.doHandle(StatusFilter.java:140)
at org.restlet.routing.Filter.handle(Filter.java:197)
at org.restlet.routing.Filter.doHandle(Filter.java:150)
at org.restlet.routing.Filter.handle(Filter.java:197)
at org.restlet.engine.CompositeHelper.handle(CompositeHelper.java:202)
at org.restlet.Component.handle(Component.java:408)
at caosdb.server.CaosDBComponent.handle(CaosDBServer.java:727)
at org.restlet.Server.handle(Server.java:507)
at org.restlet.engine.connector.ServerHelper.handle(ServerHelper.java:63)
at org.restlet.engine.adapter.HttpServerHelper.handle(HttpServerHelper.java:143)
at org.restlet.ext.jetty.JettyServerHelper$WrappedServer.handle(JettyServerHelper.java:256)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''262' UNION ALL SELECT DISTINCT domain_id, entity_id, property_id FROM `integer_' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1826)
at com.mysql.cj.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1923)
at com.mysql.cj.jdbc.CallableStatement.executeQuery(CallableStatement.java:839)
at snaq.db.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:144)
at caosdb.server.query.POV.executeStmt(POV.java:432)
at caosdb.server.query.POV.apply(POV.java:316)
... 73 more
It seems to only occur for the comination of LIKE with an Integer on the RHS. The same query with "=" instead of LIKE works.
Imported comments:
By Henrik tom Wörden on 2019-12-17T16:03:57.060Z
closed
By Henrik tom Wörden on 2019-12-17T16:03:56.460Z
I can't reproduce this.
By Henrik tom Wörden on 2019-11-20T12:45:44.490Z
assigned to @henrik_indiscale and unassigned @timm.fitschen
By Quazgar on 2019-11-20T12:45:13.184Z
made the issue visible to everyone
By Timm Fitschen on 2019-09-11T15:03:28.545Z
changed due date to September 18, 2019
By Timm Fitschen on 2019-09-03T06:27:15.591Z
changed due date to September 11, 2019
By Timm Fitschen on 2019-08-29T09:48:59.099Z
changed due date to September 04, 2019
By Quazgar on 2019-08-13T09:20:53.677Z
I tried to reproduce this, and this is the XML response I got (no exception was thrown this time):
<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="https://localhost:10443/webinterface/webcaosdb.xsl" ?>
<Response username="admin" realm="PAM" srid="c0e43e0479ac4af2788ee4627ab58e8" timestamp="1565687921644" baseuri="https://localhost:10443" count="0">
<UserInfo username="admin" realm="PAM">
<Roles>
<Role>administration</Role>
</Roles>
</UserInfo>
<Error code="0" description="An error occured during the parsing of this query. Maybe you use a wrong syntax?" />
<Query string="SELECT date from Record with ID like "262"" results="0">
<ParseTree>
<ParsingError type="AttemptingFullContext">AtemptingFullContext conflict in query. Please report this with a bug ticket.</ParsingError>
<ParsingError type="Ambiguity">Ambiguity conflict in query. Please report this with a bug ticket.</ParsingError>
<ParsingError type="SyntaxError" line="1" character="32">mismatched input 'like' expecting {<EOF>, OPERATOR}</ParsingError>
</ParseTree>
<Role />
<Entity />
<TransactionBenchmark since="Tue Aug 13 09:18:41 UTC 2019" />
</Query>
</Response>
By Quazgar on 2019-08-13T09:19:47.929Z
assigned to @timm.fitschen and unassigned @quazgar