ofbiz-notifications mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jacques Le Roux (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (OFBIZ-9676) Error of GROUP BY in MSSQL
Date Fri, 29 Sep 2017 06:10:00 GMT

    [ https://issues.apache.org/jira/browse/OFBIZ-9676?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16185396#comment-16185396
] 

Jacques Le Roux commented on OFBIZ-9676:
----------------------------------------

Thanks Wei,

So we have a double problem with r1804319 because of Dynamic View Entity as reported Pawan
Verma at OFBIZ-5701. I must say I'm less concerned by MSSQL but still it's an issue. I'll
revert r1804319, reopen OFBIZ-5701, close here and ask kieuanhvu to provide a better solution
if possible

> Error of GROUP BY in MSSQL
> --------------------------
>
>                 Key: OFBIZ-9676
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-9676
>             Project: OFBiz
>          Issue Type: Bug
>          Components: framework
>    Affects Versions: Trunk
>            Reporter: Wei Zhang
>
> To reproduce this error:
> 1. Create a view entity
> {code:borderStyle=solid}
>     <view-entity entity-name="OrderValue" package-name="org.apache.ofbiz.order.order">
>     <member-entity entity-alias="OI"   entity-name="OrderItem"/>
>     <alias entity-alias="OI" name="orderId" field="orderId" group-by="true"/>
>     <alias name="orderValue" function="sum">
>       <complex-alias operator="*">
>         <complex-alias-field entity-alias="OI" field="unitPrice"/>
>         <complex-alias-field entity-alias="OI" field="quantity"/>
>       </complex-alias>
>     </alias>
>   </view-entity>
> {code}
> 2. Find the view entity in groovy
> {code:borderStyle=solid}
>     EntityQuery.use(delegator).from("OrderValue").queryList()
> {code}
> 3. An error will occur as below
> {code}
> 2017-09-28 15:31:41,546 |jsse-nio-8443-exec-1 |GenericDelegator              |E| Failure
in findByCondition operation for entity [OrderValue]: org.apache.ofbiz.entity.GenericDataSourceException:
SQL Exception while executing the following:SELECT COUNT(1) FROM (SELECT COUNT(1)  FROM dbo.ORDER_ITEM
OI GROUP BY OI.ORDER_ID) TEMP_NAME (No column name was specified for column 1 of 'TEMP_NAME'.).
Rolling back transaction.
> org.apache.ofbiz.entity.GenericDataSourceException: SQL Exception while executing the
following:SELECT COUNT(1) FROM (SELECT COUNT(1)  FROM dbo.ORDER_ITEM OI GROUP BY OI.ORDER_ID)
TEMP_NAME (No column name was specified for column 1 of 'TEMP_NAME'.)
>         at org.apache.ofbiz.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:446)
~[ofbiz.jar:?]
>         at org.apache.ofbiz.entity.datasource.GenericDAO.selectCountByCondition(GenericDAO.java:1222)
~[ofbiz.jar:?]
>         at org.apache.ofbiz.entity.util.EntityListIterator.getResultSize(EntityListIterator.java:508)
~[ofbiz.jar:?]
>         at org.apache.ofbiz.entity.util.EntityListIterator.getCompleteList(EntityListIterator.java:417)
~[ofbiz.jar:?]
>         at org.apache.ofbiz.entity.GenericDelegator.findList(GenericDelegator.java:1587)
[ofbiz.jar:?]
>         at org.apache.ofbiz.entity.util.EntityQuery.query(EntityQuery.java:451) [ofbiz.jar:?]
>         at org.apache.ofbiz.entity.util.EntityQuery.queryList(EntityQuery.java:381) [ofbiz.jar:?]
>         at org.apache.ofbiz.entity.util.EntityQuery$queryList$2.call(Unknown Source)
[ofbiz.jar:?]
>         at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)
[groovy-all-2.4.12.jar:2.4.12]
>         at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)
[groovy-all-2.4.12.jar:2.4.12]
>         at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117)
[groovy-all-2.4.12.jar:2.4.12]
>         at uk.co.robertheath.mobile.FooServices.getFoo(FooServices.groovy:31) [script:?]
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_112]
>         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
~[?:1.8.0_112]
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
~[?:1.8.0_112]
>         at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_112]
>         at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:93) [groovy-all-2.4.12.jar:2.4.12]
>         at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:325) [groovy-all-2.4.12.jar:2.4.12]
>         at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1213) [groovy-all-2.4.12.jar:2.4.12]
>         at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1022) [groovy-all-2.4.12.jar:2.4.12]
>         at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:810) [groovy-all-2.4.12.jar:2.4.12]
>         at groovy.lang.GroovyObjectSupport.invokeMethod(GroovyObjectSupport.java:46)
[groovy-all-2.4.12.jar:2.4.12]
>         at groovy.lang.Script.invokeMethod(Script.java:80) [groovy-all-2.4.12.jar:2.4.12]
>         at org.apache.ofbiz.service.engine.GroovyEngine.serviceInvoker(GroovyEngine.java:112)
[ofbiz.jar:?]
>         at org.apache.ofbiz.service.engine.GroovyEngine.runSync(GroovyEngine.java:83)
[ofbiz.jar:?]
>         at org.apache.ofbiz.service.ServiceDispatcher.runSync(ServiceDispatcher.java:396)
[ofbiz.jar:?]
>         at org.apache.ofbiz.service.ServiceDispatcher.runSync(ServiceDispatcher.java:227)
[ofbiz.jar:?]
>         at org.apache.ofbiz.service.GenericDispatcherFactory$GenericDispatcher.runSync(GenericDispatcherFactory.java:88)
[ofbiz.jar:?]
>         at org.apache.ofbiz.webapp.event.CoreEvents.scheduleService(CoreEvents.java:316)
[ofbiz.jar:?]
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_112]
>         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
~[?:1.8.0_112]
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
~[?:1.8.0_112]
>         at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_112]
>         at org.apache.ofbiz.webapp.event.JavaEventHandler.invoke(JavaEventHandler.java:100)
[ofbiz.jar:?]
>         at org.apache.ofbiz.webapp.event.JavaEventHandler.invoke(JavaEventHandler.java:80)
[ofbiz.jar:?]
>         at org.apache.ofbiz.webapp.control.RequestHandler.runEvent(RequestHandler.java:730)
[ofbiz.jar:?]
>         at org.apache.ofbiz.webapp.control.RequestHandler.doRequest(RequestHandler.java:454)
[ofbiz.jar:?]
>         at org.apache.ofbiz.webapp.control.ControlServlet.doGet(ControlServlet.java:212)
[ofbiz.jar:?]
>         at org.apache.ofbiz.webapp.control.ControlServlet.doPost(ControlServlet.java:87)
[ofbiz.jar:?]
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) [javax.servlet-api-3.1.0.jar:3.1.0]
>         at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) [javax.servlet-api-3.1.0.jar:3.1.0]
>         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat-embed-websocket-8.0.39.jar:8.0.39]
>         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.ofbiz.webapp.control.ContextFilter.doFilter(ContextFilter.java:196)
[ofbiz.jar:?]
>         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.ofbiz.webapp.control.ControlFilter.doFilter(ControlFilter.java:157)
[ofbiz.jar:?]
>         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
[tomcat-catalina-8.5.16.jar:8.5.16]
>         at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
[tomcat-coyote-8.5.16.jar:8.5.16]
>         at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
[tomcat-coyote-8.5.16.jar:8.5.16]
>         at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
[tomcat-coyote-8.5.16.jar:8.5.16]
>         at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455)
[tomcat-coyote-8.5.16.jar:8.5.16]
>         at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
[tomcat-coyote-8.5.16.jar:8.5.16]
>         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
[?:1.8.0_112]
>         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
[?:1.8.0_112]
>         at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
[tomcat-util-8.5.16.jar:8.5.16]
>         at java.lang.Thread.run(Thread.java:745) [?:1.8.0_112]
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: No column name was specified
for column 1 of 'TEMP_NAME'.
>         at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
~[sqljdbc42.jar:?]
>         at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
~[sqljdbc42.jar:?]
>         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
~[sqljdbc42.jar:?]
>         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
~[sqljdbc42.jar:?]
>         at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505) ~[sqljdbc42.jar:?]
>         at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
~[sqljdbc42.jar:?]
>         at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
~[sqljdbc42.jar:?]
>         at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
~[sqljdbc42.jar:?]
>         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:297)
~[sqljdbc42.jar:?]
>         at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
~[commons-dbcp2-2.1.jar:2.1]
>         at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
~[commons-dbcp2-2.1.jar:2.1]
>         at org.apache.ofbiz.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:443)
~[ofbiz.jar:?]
>         ... 68 more
> {code}
> The expected query in MS SQL is 
> SELECT COUNT(1) FROM (SELECT COUNT(1) as cnt FROM dbo.ORDER_ITEM OI GROUP BY OI.ORDER_ID)
TEMP_NAME 
> Kind Regards,
> Wei



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message