Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

View filtering by virtual SQL column not working #407

Closed
teosarca opened this issue May 23, 2017 · 1 comment
Closed

View filtering by virtual SQL column not working #407

teosarca opened this issue May 23, 2017 · 1 comment
Assignees
@metas-lc
Labels
branch:master branch:release type:bug
Milestone
2017-21

Comments

@teosarca
Copy link
Member

teosarca commented May 23, 2017 •
edited
Loading

Test case:

  • open Billing candidates
  • filter by IsMaterialTracking
    => ERROR

image

image

Stacktrace:



2017-05-23 07:48:31.874 ERROR 3800 [                ][          ] --- [http-nio-8080-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.adempiere.exceptions.DBException: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "c_invoice_candidate"
  Hint: Perhaps you meant to reference the table alias "master".
  Position: 554
        SQL: INSERT INTO T_WEBUI_ViewSelection ( UUID, Line, Record_ID)
 SELECT
  ?
, row_number() OVER (ORDER BY (C_Invoice_Candidate_ID) ASC)
, C_Invoice_Candidate_ID
 FROM C_Invoice_Candidate master
 WHERE 1=1   AND
 /* security-client */ master.AD_Client_ID IN(0,1000000)
 /* security-org */  AND master.AD_Org_ID IN (0,1000000)
 /* security-record */ AND master.C_Invoice_Candidate_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 540270 AND AD_User_ID <> 2188223 AND IsActive = 'Y' )
 AND (
 /* filters */ (
/* default */(((CASE WHEN C_Invoice_Candidate.M_Material_Tracking_ID IS NULL THEN 'N' ELSE 'Y' END) = ?)))

)
 LIMIT ?
        SQL params: [540092-01eb2f01-e772-42ec-ad1e-d1de577aadc6, true, 5000]] with root cause

org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "c_invoice_candidate"
  Hint: Perhaps you meant to reference the table alias "master".
  Position: 554
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:135)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:1449)
        at org.adempiere.sql.impl.CPreparedStatementProxy.executeUpdate(CPreparedStatementProxy.java:128)
        at org.compiere.util.DB.executeUpdate(DB.java:907)
        at org.compiere.util.DB.executeUpdateEx(DB.java:1026)
        at org.compiere.util.DB.executeUpdateEx(DB.java:1009)
        at de.metas.ui.web.view.SqlViewRowIdsOrderedSelectionFactory.createOrderedSelection(SqlViewRowIdsOrderedSelectionFactory.java:96)
        at de.metas.ui.web.view.SqlViewDataRepository.createOrderedSelection(SqlViewDataRepository.java:125)
        at de.metas.ui.web.view.DefaultView.<init>(DefaultView.java:122)
        at de.metas.ui.web.view.DefaultView.<init>(DefaultView.java:67)
        at de.metas.ui.web.view.DefaultView$Builder.build(DefaultView.java:408)
        at de.metas.ui.web.view.SqlViewFactory.createView(SqlViewFactory.java:116)
        at de.metas.ui.web.view.IViewFactory.filterView(IViewFactory.java:44)
        at de.metas.ui.web.view.ViewsRepository.filterView(ViewsRepository.java:168)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
        at com.sun.proxy.$Proxy97.filterView(Unknown Source)
        at de.metas.ui.web.view.ViewRestController.filterView(ViewRestController.java:160)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.boot.web.filter.ApplicationContextHeaderFilter.doFilterInternal(ApplicationContextHeaderFilter.java:55)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at de.metas.ui.web.config.WebConfig$1.doFilter(WebConfig.java:82)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at de.metas.ui.web.config.ServletLoggingFilter.doFilter(ServletLoggingFilter.java:89)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at de.metas.ui.web.config.CORSFilter.doFilter(CORSFilter.java:85)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:110)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at de.metas.ui.web.config.CORSFilter.doFilter(CORSFilter.java:85)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:105)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.session.web.http.SessionRepositoryFilter.doFilterInternal(SessionRepositoryFilter.java:167)
        at org.springframework.session.web.http.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:80)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:106)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455)
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:748)
@teosarca teosarca added this to the 2017-21 milestone May 23, 2017
teosarca added a commit that referenced this issue May 23, 2017
@teosarca
fix view filtering by virtual SQL column not working
e1705e9 
#407
@teosarca teosarca changed the title Billing candidates: filtering by IsMaterialTracking not working View filtering by virtual SQL column not working May 23, 2017
@metas-lc metas-lc self-assigned this May 23, 2017
@metas-lc
Copy link

IT

  1. open Billing candidates
  2. filter by IsMaterialTracking
    => no results, since we don't have any
    => no errors OK

metas-ts added a commit to metasfresh/metasfresh that referenced this issue May 25, 2017
@metas-ts
complement ReleaseNotes.md
eae53b0 
[#409](metasfresh/metasfresh-webui-api-legacy#409) backend: change view attributes endpoint location
[#1639](#1635) cannot complete distribution order
[#760](metasfresh/metasfresh-webui-frontend-legacy#760) Fix the texts displayed while loading
[#787](metasfresh/metasfresh-webui-frontend-legacy#787) window endpoint: change "attribute" to "field"
 [#763](metasfresh/metasfresh-webui-frontend-legacy#763) grid: right click on a cell and zoom into
 * [#1617](#1617) Check if imported esr file is v11 file
* [#1615](#1615) Address lines is cut off on shipment jasper document
* [#1591](#1591) Password Field is missing in user window in WebUI
* [#1586](#1586) New Window for OrgType in WebUI
* [#1584](#1584) New Window for Org in WebUI
* [#1585](#1585) New Window Client in WebUI
* [#762](metasfresh/metasfresh-webui-frontend-legacy#762) Empty Filter vs. Default Value in Window Filtering
* [#778](metasfresh/metasfresh-webui-frontend-legacy#778) Yellow pulse effect w/o data change
* [#407](metasfresh/metasfresh-webui-api-legacy#407) View filtering by virtual SQL column not working
* [#412](metasfresh/metasfresh-webui-api-legacy#412) Provide API for sorting included tab
* [#411](metasfresh/metasfresh-webui-api-legacy#411) Provide "size" to layout element
* [#396](metasfresh/metasfresh-webui-api-legacy#396) Vendor Return Actions in WebUI
* [#772](metasfresh/metasfresh-webui-frontend-legacy#772) automatic Column sizing not showing all text although space would be available
* [#774](metasfresh/metasfresh-webui-frontend-legacy#774) Fix invalid "GET /window/{windowId}/{tabId} call
* [#764](metasfresh/metasfresh-webui-frontend-legacy#764) Long text boxes shall have a border around
* [#769](metasfresh/metasfresh-webui-frontend-legacy#769) Honor tab stale flag also when editing in advanced mode
* [#767](metasfresh/metasfresh-webui-frontend-legacy#767) Got same notification several times
^* [#766](metasfresh/metasfresh-webui-frontend-legacy#766) issue panel doesn't open
* [#765](metasfresh/metasfresh-webui-frontend-legacy#765) Date + Time Info in Notification window
[#378](metasfresh/metasfresh-webui-api-legacy#378) Document changes: provide the validStatus and saveStatus only when changed
* [#405](metasfresh/metasfresh-webui-api-legacy#405) Backend shall provide JSON document fields indexed by field's name
* [#1560](#1560) Add description fields to invoice window
* [#1577](#1577) Introduce /test/ping/notifications server troubleshooting endpoint

me-45
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@metas-lc metas-lc

Labels
branch:master branch:release type:bug
Projects
None yet
Milestone
2017-21
Development

No branches or pull requests
3 participants
@teosarca @metas-ts @metas-lc