search

MangoAutomation / ma-core-public

Mango Automation Core public code
Other
78 stars 50 forks source link

Cannot sort data points by tags (REST/RQL) #1635

Closed jazdw closed 3 years ago

jazdw commented 4 years ago

For request like /rest/latest/data-points?sort(tags.Campus)

Stack trace

Caused by: PreparedStatementCallback; bad SQL grammar [select distinct DP.ID, DP.XID, DP.NAME, DP.DATA, DP.DATASOURCEID, DP.DEVICENAME, DP.ENABLED, DP.LOGGINGTYPE, DP.INTERVALLOGGINGPERIODTYPE, DP.INTERVALLOGGINGPERIOD, DP.INTERVALLOGGINGTYPE, DP.TOLERANCE, DP.PURGEOVERRIDE, DP.PURGETYPE, DP.PURGEPERIOD, DP.DEFAULTCACHESIZE, DP.DISCARDEXTREMEVALUES, DP.ENGINEERINGUNITS, DP.ROLLUP, DP.DATATYPEID, DP.SETTABLE, DP.JSONDATA, DP.READPERMISSIONID, DP.EDITPERMISSIONID, DP.SETPERMISSIONID, DS.NAME, DS.XID, DS.DATASOURCETYPE from dataPoints DP join dataSources DS on DS.ID = DP.DATASOURCEID left outer join (select TAGS.DATAPOINTID, max(case when TAGS.TAGKEY = cast(? as varchar) then TAGS.TAGVALUE end) KEY0 from DATAPOINTTAGS TAGS group by TAGS.DATAPOINTID) TAGSPIVOT on TAGSPIVOT.DATAPOINTID = DP.ID order by TAGSPIVOT.KEY0 asc]; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Order by expression "TAGSPIVOT.KEY0" must be in the result list in this case; SQL statement:
select distinct DP.ID, DP.XID, DP.NAME, DP.DATA, DP.DATASOURCEID, DP.DEVICENAME, DP.ENABLED, DP.LOGGINGTYPE, DP.INTERVALLOGGINGPERIODTYPE, DP.INTERVALLOGGINGPERIOD, DP.INTERVALLOGGINGTYPE, DP.TOLERANCE, DP.PURGEOVERRIDE, DP.PURGETYPE, DP.PURGEPERIOD, DP.DEFAULTCACHESIZE, DP.DISCARDEXTREMEVALUES, DP.ENGINEERINGUNITS, DP.ROLLUP, DP.DATATYPEID, DP.SETTABLE, DP.JSONDATA, DP.READPERMISSIONID, DP.EDITPERMISSIONID, DP.SETPERMISSIONID, DS.NAME, DS.XID, DS.DATASOURCETYPE from dataPoints DP join dataSources DS on DS.ID = DP.DATASOURCEID left outer join (select TAGS.DATAPOINTID, max(case when TAGS.TAGKEY = cast(? as varchar) then TAGS.TAGVALUE end) KEY0 from DATAPOINTTAGS TAGS group by TAGS.DATAPOINTID) TAGSPIVOT on TAGSPIVOT.DATAPOINTID = DP.ID order by TAGSPIVOT.KEY0 asc [90068-200]
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate (SQLExceptionSubclassTranslator.java:93)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate (AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate (AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.translateException (JdbcTemplate.java:1443)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:633)
    at org.springframework.jdbc.core.JdbcTemplate.query (JdbcTemplate.java:669)
    at org.springframework.jdbc.core.JdbcTemplate.query (JdbcTemplate.java:700)
    at org.springframework.jdbc.core.JdbcTemplate.query (JdbcTemplate.java:712)
    at com.serotonin.db.DaoUtils.query (DaoUtils.java:257)
    at com.serotonin.m2m2.db.dao.AbstractBasicDao.customizedQuery (AbstractBasicDao.java:485)
    at com.serotonin.m2m2.db.dao.AbstractBasicDao.customizedQuery (AbstractBasicDao.java:464)
    at com.serotonin.m2m2.db.dao.AbstractBasicDao.customizedQuery (AbstractBasicDao.java:445)
    at com.infiniteautomation.mango.spring.service.AbstractBasicVOService.customizedQuery (AbstractBasicVOService.java:264)
    at com.infiniteautomation.mango.rest.latest.model.StreamedBasicVORqlQueryWithTotal$StreamedVOArray.writeArrayValues (StreamedBasicVORqlQueryWithTotal.java:130)
    at com.infiniteautomation.mango.rest.latest.mapping.JSONStreamedArraySerializer.serialize (JSONStreamedArraySerializer.java:25)
    at com.infiniteautomation.mango.rest.latest.mapping.JSONStreamedArraySerializer.serialize (JSONStreamedArraySerializer.java:19)
    at com.fasterxml.jackson.databind.ser.BeanPropertyWriter.serializeAsField (BeanPropertyWriter.java:727)
    at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeFields (BeanSerializerBase.java:722)
    at com.fasterxml.jackson.databind.ser.BeanSerializer.serialize (BeanSerializer.java:166)
    at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider._serialize (DefaultSerializerProvider.java:480)
    at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue (DefaultSerializerProvider.java:319)
    at com.fasterxml.jackson.databind.ObjectWriter$Prefetch.serialize (ObjectWriter.java:1433)
    at com.fasterxml.jackson.databind.ObjectWriter.writeValue (ObjectWriter.java:921)
    at org.springframework.http.converter.json.AbstractJackson2HttpMessageConverter.writeInternal (AbstractJackson2HttpMessageConverter.java:287)
    at org.springframework.http.converter.AbstractGenericHttpMessageConverter.write (AbstractGenericHttpMessageConverter.java:104)
    at org.springframework.web.servlet.mvc.method.annotation.AbstractMessageConverterMethodProcessor.writeWithMessageConverters (AbstractMessageConverterMethodProcessor.java:287)
    at org.springframework.web.servlet.mvc.method.annotation.RequestResponseBodyMethodProcessor.handleReturnValue (RequestResponseBodyMethodProcessor.java:181)
    at org.springframework.web.method.support.HandlerMethodReturnValueHandlerComposite.handleReturnValue (HandlerMethodReturnValueHandlerComposite.java:82)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle (ServletInvocableHandlerMethod.java:123)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod (RequestMappingHandlerAdapter.java:879)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal (RequestMappingHandlerAdapter.java:793)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle (AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch (DispatcherServlet.java:1040)
    at org.springframework.web.servlet.DispatcherServlet.doService (DispatcherServlet.java:943)
    at org.springframework.web.servlet.FrameworkServlet.processRequest (FrameworkServlet.java:1006)
    at org.springframework.web.servlet.FrameworkServlet.doGet (FrameworkServlet.java:898)
    at javax.servlet.http.HttpServlet.service (HttpServlet.java:687)
<snip>
Caused by: Order by expression "TAGSPIVOT.KEY0" must be in the result list in this case; SQL statement:
select distinct DP.ID, DP.XID, DP.NAME, DP.DATA, DP.DATASOURCEID, DP.DEVICENAME, DP.ENABLED, DP.LOGGINGTYPE, DP.INTERVALLOGGINGPERIODTYPE, DP.INTERVALLOGGINGPERIOD, DP.INTERVALLOGGINGTYPE, DP.TOLERANCE, DP.PURGEOVERRIDE, DP.PURGETYPE, DP.PURGEPERIOD, DP.DEFAULTCACHESIZE, DP.DISCARDEXTREMEVALUES, DP.ENGINEERINGUNITS, DP.ROLLUP, DP.DATATYPEID, DP.SETTABLE, DP.JSONDATA, DP.READPERMISSIONID, DP.EDITPERMISSIONID, DP.SETPERMISSIONID, DS.NAME, DS.XID, DS.DATASOURCETYPE from dataPoints DP join dataSources DS on DS.ID = DP.DATASOURCEID left outer join (select TAGS.DATAPOINTID, max(case when TAGS.TAGKEY = cast(? as varchar) then TAGS.TAGVALUE end) KEY0 from DATAPOINTTAGS TAGS group by TAGS.DATAPOINTID) TAGSPIVOT on TAGSPIVOT.DATAPOINTID = DP.ID order by TAGSPIVOT.KEY0 asc [90068-200]
    at org.h2.message.DbException.getJdbcSQLException (DbException.java:576)
    at org.h2.message.DbException.getJdbcSQLException (DbException.java:429)
    at org.h2.message.DbException.get (DbException.java:205)
    at org.h2.message.DbException.get (DbException.java:181)
    at org.h2.command.dml.Query.initExpression (Query.java:607)
    at org.h2.command.dml.Query.initOrder (Query.java:525)
    at org.h2.command.dml.Select.init (Select.java:1042)
    at org.h2.command.Parser.parseQuery (Parser.java:2627)
    at org.h2.command.Parser.parsePrepared (Parser.java:868)
    at org.h2.command.Parser.parse (Parser.java:843)
    at org.h2.command.Parser.parse (Parser.java:815)
    at org.h2.command.Parser.prepareCommand (Parser.java:738)
    at org.h2.engine.Session.prepareLocal (Session.java:657)
    at org.h2.engine.Session.prepareCommand (Session.java:595)
    at org.h2.jdbc.JdbcConnection.prepareCommand (JdbcConnection.java:1235)
    at org.h2.jdbc.JdbcPreparedStatement.<init> (JdbcPreparedStatement.java:76)
    at org.h2.jdbc.JdbcConnection.prepareStatement (JdbcConnection.java:352)
    at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement (JdbcTemplate.java:1556)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:615)
    at org.springframework.jdbc.core.JdbcTemplate.query (JdbcTemplate.java:669)
    at org.springframework.jdbc.core.JdbcTemplate.query (JdbcTemplate.java:700)
    at org.springframework.jdbc.core.JdbcTemplate.query (JdbcTemplate.java:712)
    at com.serotonin.db.DaoUtils.query (DaoUtils.java:257)
    at com.serotonin.m2m2.db.dao.AbstractBasicDao.customizedQuery (AbstractBasicDao.java:485)
    at com.serotonin.m2m2.db.dao.AbstractBasicDao.customizedQuery (AbstractBasicDao.java:464)
    at com.serotonin.m2m2.db.dao.AbstractBasicDao.customizedQuery (AbstractBasicDao.java:445)
    at com.infiniteautomation.mango.spring.service.AbstractBasicVOService.customizedQuery (AbstractBasicVOService.java:264)
    at com.infiniteautomation.mango.rest.latest.model.StreamedBasicVORqlQueryWithTotal$StreamedVOArray.writeArrayValues (StreamedBasicVORqlQueryWithTotal.java:130)
    at com.infiniteautomation.mango.rest.latest.mapping.JSONStreamedArraySerializer.serialize (JSONStreamedArraySerializer.java:25)
    at com.infiniteautomation.mango.rest.latest.mapping.JSONStreamedArraySerializer.serialize (JSONStreamedArraySerializer.java:19)
    at com.fasterxml.jackson.databind.ser.BeanPropertyWriter.serializeAsField (BeanPropertyWriter.java:727)
    at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeFields (BeanSerializerBase.java:722)
    at com.fasterxml.jackson.databind.ser.BeanSerializer.serialize (BeanSerializer.java:166)
    at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider._serialize (DefaultSerializerProvider.java:480)
    at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue (DefaultSerializerProvider.java:319)
    at com.fasterxml.jackson.databind.ObjectWriter$Prefetch.serialize (ObjectWriter.java:1433)
    at com.fasterxml.jackson.databind.ObjectWriter.writeValue (ObjectWriter.java:921)
    at org.springframework.http.converter.json.AbstractJackson2HttpMessageConverter.writeInternal (AbstractJackson2HttpMessageConverter.java:287)
    at org.springframework.http.converter.AbstractGenericHttpMessageConverter.write (AbstractGenericHttpMessageConverter.java:104)
    at org.springframework.web.servlet.mvc.method.annotation.AbstractMessageConverterMethodProcessor.writeWithMessageConverters (AbstractMessageConverterMethodProcessor.java:287)
    at org.springframework.web.servlet.mvc.method.annotation.RequestResponseBodyMethodProcessor.handleReturnValue (RequestResponseBodyMethodProcessor.java:181)
    at org.springframework.web.method.support.HandlerMethodReturnValueHandlerComposite.handleReturnValue (HandlerMethodReturnValueHandlerComposite.java:82)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle (ServletInvocableHandlerMethod.java:123)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod (RequestMappingHandlerAdapter.java:879)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal (RequestMappingHandlerAdapter.java:793)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle (AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch (DispatcherServlet.java:1040)
    at org.springframework.web.servlet.DispatcherServlet.doService (DispatcherServlet.java:943)
    at org.springframework.web.servlet.FrameworkServlet.processRequest (FrameworkServlet.java:1006)
    at org.springframework.web.servlet.FrameworkServlet.doGet (FrameworkServlet.java:898)
    at javax.servlet.http.HttpServlet.service (HttpServlet.java:687)
<snip>
jazdw commented 4 years ago

I suspect it was caused by this bug, fixed in 201807a8c76adeafc0180e586030a3cb7828e968

jazdw commented 4 years ago

OK this issue was NOT fixed by the above commit. It is in fact an artifact of adding SELECT DISTINCT to all our queries. Distinct is not needed for this query and we should look at selectively applying distinct only when needed.

terrypacker commented 3 years ago

Fixed by removing SELECT DISTINCT