Bug 1471815

Summary:	SQL Exception while sorting columns of events subtab of Hosts main tab
Product:	[oVirt] ovirt-engine	Reporter:	Tomas Jelinek <tjelinek>
Component:	Backend.Core	Assignee:	Eli Mesika <emesika>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Israel Pinto <ipinto>
Severity:	medium	Docs Contact:
Priority:	high
Version:	4.2.0	CC:	bugs, emesika, mgoldboi, mperina
Target Milestone:	ovirt-4.1.7	Flags:	rule-engine: ovirt-4.1+
Target Release:	4.1.7.1
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:		Doc Type:	No Doc Update
Doc Text:	undefined	Story Points:	---
Clone Of:		Environment:
Last Closed:	2017-11-13 12:26:01 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	Infra	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:

Description Tomas Jelinek 2017-07-17 13:38:07 UTC

In Hosts view pick a host. In the host details (e.g. former subtabs) pick the "events". Click e.g. correlation-id column header to sort by it. 
It will cause an SQL exception as shown below. 

Version: current master (89b1d2dfe122287def22c44d19ea02196e063603)

2017-07-17 15:35:08,193+02 ERROR [org.ovirt.engine.core.bll.SearchQuery] (default task-101) [b7080e63-180b-4a31-97af-b5c7745bd1c5] Query 'SearchQuery' failed: StatementCallback; bad SQL grammar [SELECT * FROM ((SELECT distinct audit_log.* FROM  audit_log   LEFT OUTER JOIN vds_with_tags ON audit_log.vds_id=vds_with_tags.vds_id    WHERE  vds_with_tags.vds_name LIKE 'h1'  AND not deleted)  ORDER BY correlation_id::varchar ASC NULLS FIRST,audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 100]; nested exception is org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
  Position: 205
2017-07-17 15:35:08,193+02 ERROR [org.ovirt.engine.core.bll.SearchQuery] (default task-101) [b7080e63-180b-4a31-97af-b5c7745bd1c5] Exception: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT * FROM ((SELECT distinct audit_log.* FROM  audit_log   LEFT OUTER JOIN vds_with_tags ON audit_log.vds_id=vds_with_tags.vds_id    WHERE  vds_with_tags.vds_name LIKE 'h1'  AND not deleted)  ORDER BY correlation_id::varchar ASC NULLS FIRST,audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 100]; nested exception is org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
  Position: 205
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99) [spring-jdbc.jar:4.3.9.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) [spring-jdbc.jar:4.3.9.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) [spring-jdbc.jar:4.3.9.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) [spring-jdbc.jar:4.3.9.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419) [spring-jdbc.jar:4.3.9.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:474) [spring-jdbc.jar:4.3.9.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:484) [spring-jdbc.jar:4.3.9.RELEASE]
	at org.ovirt.engine.core.dao.AuditLogDaoImpl.getAllWithQuery(AuditLogDaoImpl.java:105) [dal.jar:]
	at org.ovirt.engine.core.bll.SearchQuery.genericSearch(SearchQuery.java:337) [bll.jar:]
	at org.ovirt.engine.core.bll.SearchQuery.searchAuditLogEvents(SearchQuery.java:341) [bll.jar:]
	at org.ovirt.engine.core.bll.SearchQuery.executeQueryCommand(SearchQuery.java:176) [bll.jar:]
	at org.ovirt.engine.core.bll.QueriesCommandBase.executeCommand(QueriesCommandBase.java:106) [bll.jar:]
	at org.ovirt.engine.core.dal.VdcCommandBase.execute(VdcCommandBase.java:33) [dal.jar:]
	at org.ovirt.engine.core.bll.executor.DefaultBackendQueryExecutor.execute(DefaultBackendQueryExecutor.java:14) [bll.jar:]
	at org.ovirt.engine.core.bll.Backend.runQueryImpl(Backend.java:564) [bll.jar:]
	at org.ovirt.engine.core.bll.Backend.runQuery(Backend.java:533) [bll.jar:]
	at sun.reflect.GeneratedMethodAccessor74.invoke(Unknown Source) [:1.8.0_131]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_131]
	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_131]
	at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52)
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)
	at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.delegateInterception(Jsr299BindingsInterceptor.java:70) [wildfly-weld-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:80) [wildfly-weld-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:93) [wildfly-weld-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)
	at org.ovirt.engine.core.bll.interceptors.CorrelationIdTrackerInterceptor.aroundInvoke(CorrelationIdTrackerInterceptor.java:13) [bll.jar:]
	at sun.reflect.GeneratedMethodAccessor70.invoke(Unknown Source) [:1.8.0_131]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_131]
	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_131]
	at org.jboss.as.ee.component.ManagedReferenceLifecycleMethodInterceptor.processInvocation(ManagedReferenceLifecycleMethodInterceptor.java:89)
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43) [wildfly-ejb3-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)
	at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:73) [weld-core-impl-2.3.5.Final.jar:2.3.5.Final]
	at org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor.processInvocation(EjbRequestScopeActivationInterceptor.java:83) [wildfly-weld-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation(ConcurrentContextInterceptor.java:45) [wildfly-ee-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21)
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
	at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:52)
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.as.ejb3.component.singleton.SingletonComponentInstanceAssociationInterceptor.processInvocation(SingletonComponentInstanceAssociationInterceptor.java:53) [wildfly-ejb3-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInNoTx(CMTTxInterceptor.java:263) [wildfly-ejb3-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.as.ejb3.tx.CMTTxInterceptor.supports(CMTTxInterceptor.java:374) [wildfly-ejb3-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:243) [wildfly-ejb3-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41) [wildfly-ejb3-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.as.ejb3.component.invocationmetrics.WaitTimeInterceptor.processInvocation(WaitTimeInterceptor.java:47) [wildfly-ejb3-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:100) [wildfly-ejb3-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.as.ejb3.deployment.processors.StartupAwaitInterceptor.processInvocation(StartupAwaitInterceptor.java:22) [wildfly-ejb3-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.as.ejb3.component.interceptors.ShutDownInterceptorFactory$1.processInvocation(ShutDownInterceptorFactory.java:64) [wildfly-ejb3-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:67) [wildfly-ejb3-10.1.0.Final.jar:10.1.0.Final]
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50)
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.invocation.ContextClassLoaderInterceptor.processInvocation(ContextClassLoaderInterceptor.java:64)
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356)
	at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:636)
	at org.jboss.invocation.AccessCheckingInterceptor.processInvocation(AccessCheckingInterceptor.java:61)
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356)
	at org.jboss.invocation.PrivilegedWithCombinerInterceptor.processInvocation(PrivilegedWithCombinerInterceptor.java:80)
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
	at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:198)
	at org.jboss.as.ee.component.ViewDescription$1.processInvocation(ViewDescription.java:185)
	at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
	at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)
	at org.jboss.as.ee.component.ProxyInvocationHandler.invoke(ProxyInvocationHandler.java:73)
	at org.ovirt.engine.core.common.interfaces.BackendLocal$$$view3.runQuery(Unknown Source) [common.jar:]
	at org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl.runQuery(GenericApiGWTServiceImpl.java:90)
	at sun.reflect.GeneratedMethodAccessor81.invoke(Unknown Source) [:1.8.0_131]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_131]
	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_131]
	at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587)
	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:333)
	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303)
	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:373)
	at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) [jboss-servlet-api_3.1_spec-1.0.0.Final.jar:1.0.0.Final]
	at org.ovirt.engine.ui.frontend.server.gwt.GenericApiGWTServiceImpl.service(GenericApiGWTServiceImpl.java:78)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) [jboss-servlet-api_3.1_spec-1.0.0.Final.jar:1.0.0.Final]
	at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:85)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)
	at org.ovirt.engine.core.utils.servlet.HeaderFilter.doFilter(HeaderFilter.java:94) [utils.jar:]
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at org.ovirt.engine.ui.frontend.server.gwt.GwtCachingFilter.doFilter(GwtCachingFilter.java:132)
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at org.ovirt.engine.core.branding.BrandingFilter.doFilter(BrandingFilter.java:73) [branding.jar:]
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at org.ovirt.engine.core.utils.servlet.LocaleFilter.doFilter(LocaleFilter.java:66) [utils.jar:]
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
	at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
	at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
	at org.wildfly.extension.undertow.security.SecurityContextAssociationHandler.handleRequest(SecurityContextAssociationHandler.java:78)
	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
	at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:131)
	at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
	at io.undertow.security.handlers.AuthenticationConstraintHandler.handleRequest(AuthenticationConstraintHandler.java:53)
	at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
	at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
	at io.undertow.servlet.handlers.security.ServletSecurityConstraintHandler.handleRequest(ServletSecurityConstraintHandler.java:59)
	at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
	at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
	at io.undertow.security.handlers.NotificationReceiverHandler.handleRequest(NotificationReceiverHandler.java:50)
	at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
	at org.wildfly.extension.undertow.security.jacc.JACCContextIdHandler.handleRequest(JACCContextIdHandler.java:61)
	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
	at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:292)
	at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:81)
	at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:138)
	at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:135)
	at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
	at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
	at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
	at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
	at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
	at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
	at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:272)
	at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:81)
	at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:104)
	at io.undertow.server.Connectors.executeRootHandler(Connectors.java:202)
	at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:805)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_131]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_131]
	at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_131]
Caused by: org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
  Position: 205
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
	at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:397)
	at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:458) [spring-jdbc.jar:4.3.9.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408) [spring-jdbc.jar:4.3.9.RELEASE]
	... 142 more

Comment 3 Israel Pinto 2017-10-01 08:42:08 UTC

Verify with:
Version 4.1.7.2-0.1.el7

Steps:
Sort Events in host tab by correlation-id

PASS