Getting the following error when opening the statistics for a (own) system email:
Expression #1 of ORDER BY clause is not in SELECT list, references column ‘v.generated_date’ which is not in SELECT list; this is incompatible with DISTINCT
Query Error:
select distinct from_unixtime( floor( UNIX_TIMESTAMP( generated_date ) / 172800 ) * 172800 ) as expected_date from ( select addtime( adddate( from_unixtime( floor( UNIX_TIMESTAMP( {ts ‘2023-06-22 13:28:50’} ) / 172800 ) * 172800 ), t410000 + t31000 + t2100 + t110 + t0 ), hrs10000 + mins100 ) generated_date from ( select 0 as t0 union select 1 as t0 union select 2 as t0 union select 3 as t0 union select 4 as t0 union select 5 as t0 union select 6 as t0 union select 7 as t0 union select 8 as t0 union select 9 as t0 ) t0, ( select 0 as t1 union select 1 as t1 union select 2 as t1 union select 3 as t1 ) t1, ( select 0 as t2 ) t2, ( select 0 as t3 ) t3, ( select 0 as t4 ) t4, ( select 0 as hrs ) hrs, ( select 0 as mins ) mins ) v where generated_date between from_unixtime( floor( UNIX_TIMESTAMP( {ts ‘2023-06-22 13:28:50’} ) / 172800 ) * 172800 ) and {ts ‘2023-08-01 10:29:51’} order by generated_date
Stacktrace:
lucee.runtime.exp.DatabaseException: Expression #1 of ORDER BY clause is not in SELECT list, references column ‘v.generated_date’ which is not in SELECT list; this is incompatible with DISTINCT at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916) at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354) at lucee.runtime.type.util.QueryUtil.execute(QueryUtil.java:328) at lucee.runtime.type.QueryImpl.execute(QueryImpl.java:296) at lucee.runtime.type.QueryImpl.(QueryImpl.java:235) at lucee.runtime.tag.Query.executeDatasoure(Query.java:1135) at lucee.runtime.tag.Query._doEndTag(Query.java:700) at lucee.runtime.tag.Query.doEndTag(Query.java:566) at lucee.runtime.functions.query.QueryExecute.call(QueryExecute.java:86) at system.services.database.sqlrunner_cfc$cf.udfCall(/preside/system/services/database/SqlRunner.cfc:76) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:584) at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1950) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794) at system.services.metrics.timeseriesutils_cfc$cf$2ef.udfCall(/preside/system/services/metrics/TimeSeriesUtils.cfc:220) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:696) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:584) at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1931) at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787) at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1775) at system.services.email.emailtemplateservice_cfc$cf$2ep.udfCall3(/preside/system/services/email/EmailTemplateService.cfc:1072) at system.services.email.emailtemplateservice_cfc$cf$2ep.udfCall(/preside/system/services/email/EmailTemplateService.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:584) at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1950) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794) at system.handlers.admin.emailcenter_cfc$cf$2f0.udfCall(/preside/system/handlers/admin/EmailCenter.cfc:70) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:804) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794) at system.eventhandler_cfc$cf.udfCall(/coldbox/system/EventHandler.cfc:89) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:584) at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1950) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794) at system.coldboxmodifications.controller_cfc$cf.udfCall2(/preside/system/coldboxModifications/Controller.cfc:227) at system.coldboxmodifications.controller_cfc$cf.udfCall(/preside/system/coldboxModifications/Controller.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:804) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794) at system.web.controller_cfc$cf.udfCall3(/coldbox/system/web/Controller.cfc:831) at system.web.controller_cfc$cf.udfCall(/coldbox/system/web/Controller.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:804) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794) at system.web.controller_cfc$cf.udfCall3(/coldbox/system/web/Controller.cfc:560) at system.web.controller_cfc$cf.udfCall(/coldbox/system/web/Controller.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:804) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1794) at system.coldboxmodifications.controller_cfc$cf.udfCall1(/preside/system/coldboxModifications/Controller.cfc:153) at system.coldboxmodifications.controller_cfc$cf.udfCall(/preside/system/coldboxModifications/Controller.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:584) at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1950) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866) at