[
https://issues.apache.org/jira/browse/OOZIE-1997?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14125762#comment-14125762
]
Bowen Zhang commented on OOZIE-1997:
------------------------------------
A simple fix to "GET_COORD_JOBS_OLDER_FOR_MATERILZATION" query in
CoordinatorJobBean.java is needed.
> Oozie cannot materialize coordinator job with sqlserver in windows
> ------------------------------------------------------------------
>
> Key: OOZIE-1997
> URL: https://issues.apache.org/jira/browse/OOZIE-1997
> Project: Oozie
> Issue Type: Bug
> Reporter: Bowen Zhang
> Assignee: Bowen Zhang
> Priority: Blocker
> Fix For: 4.1.0
>
>
> The error of " An aggregate may not appear in the WHERE clause unless it is
> in a subquery contained in a HAVING clause or a select list, and the column
> being aggregated is an outer reference" is specific to sqlserver since it's
> stricter on subquery syntax. This is introduced by
> https://issues.apache.org/jira/browse/OOZIE-1527. As a result of this change,
> coordinator job will not work in windows environment.
> {code}
> 2014-09-06 00:06:06,144 ERROR
> CoordMaterializeTriggerService$CoordMaterializeTriggerRunnable:540 -
> SERVER[ONPREM-HCAT1] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[-] ACTION[-]
> Exception while attempting to materialize coordinator jobs, An aggregate may
> not appear in the WHERE clause unless it is in a subquery contained in a
> HAVING clause or a select list, and the column being aggregated is an outer
> reference. {prepstmnt 486918974 SELECT TOP 50 t0.id, t0.last_modified_time
> FROM COORD_JOBS t0 WHERE (t0.start_time <= ? AND (t0.status = ? OR t0.status
> = ? OR t0.status = ?) AND (t0.next_matd_time < ? OR t0.next_matd_time IS
> NULL) AND (t0.next_matd_time IS NULL OR t0.end_time > t0.next_matd_time AND
> (t0.pause_time IS NULL OR t0.pause_time > t0.next_matd_time)) AND
> t0.mat_throttling > (SELECT COUNT(t0.id) FROM COORD_ACTIONS t1 WHERE
> (t1.job_id = t0.id AND t1.status = ?))) ORDER BY t0.last_modified_time ASC}
> [code=147, state=S0001]
> <openjpa-2.2.2-r422266:1468616 fatal general error>
> org.apache.openjpa.persistence.PersistenceException: An aggregate may not
> appear in the WHERE clause unless it is in a subquery contained in a HAVING
> clause or a select list, and the column being aggregated is an outer
> reference. {prepstmnt 486918974 SELECT TOP 50 t0.id, t0.last_modified_time
> FROM COORD_JOBS t0 WHERE (t0.start_time <= ? AND (t0.status = ? OR t0.status
> = ? OR t0.status = ?) AND (t0.next_matd_time < ? OR t0.next_matd_time IS
> NULL) AND (t0.next_matd_time IS NULL OR t0.end_time > t0.next_matd_time AND
> (t0.pause_time IS NULL OR t0.pause_time > t0.next_matd_time)) AND
> t0.mat_throttling > (SELECT COUNT(t0.id) FROM COORD_ACTIONS t1 WHERE
> (t1.job_id = t0.id AND t1.status = ?))) ORDER BY t0.last_modified_time ASC}
> [code=147, state=S0001]
> FailedObject: select w.id from CoordinatorJobBean w where w.startTimestamp <=
> :matTime AND (w.statusStr = 'PREP' OR w.statusStr = 'RUNNING' or w.statusStr
> = 'RUNNINGWITHERROR') AND (w.nextMaterializedTimestamp < :matTime OR
> w.nextMaterializedTimestamp IS NULL) AND (w.nextMaterializedTimestamp IS NULL
> OR (w.endTimestamp > w.nextMaterializedTimestamp AND (w.pauseTimestamp IS
> NULL OR w.pauseTimestamp > w.nextMaterializedTimestamp))) and w.matThrottling
> > ( select count(w.id) from CoordinatorActionBean a where a.jobId = w.id and
> a.statusStr = 'WAITING') order by w.lastModifiedTimestamp [java.lang.String]
> at
> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4962)
> at
> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4922)
> at
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
> at
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:118)
> at
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:70)
> at
> org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:155)
> at
> org.apache.openjpa.kernel.QueryImpl$PackingResultObjectProvider.handleCheckedException(QueryImpl.java:2111)
> at
> org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:40)
> at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1251)
> at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1007)
> at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:863)
> at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:794)
> at
> org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:542)
> at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:286)
> at
> org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:302)
> at
> org.apache.oozie.service.JPAService.executeGetList(JPAService.java:481)
> at
> org.apache.oozie.executor.jpa.CoordJobQueryExecutor.getList(CoordJobQueryExecutor.java:348)
> at
> org.apache.oozie.service.CoordMaterializeTriggerService$CoordMaterializeTriggerRunnable.materializeCoordJobs(CoordMaterializeTriggerService.java:158)
> at
> org.apache.oozie.service.CoordMaterializeTriggerService$CoordMaterializeTriggerRunnable.runCoordJobMatLookup(CoordMaterializeTriggerService.java:148)
> at
> org.apache.oozie.service.CoordMaterializeTriggerService$CoordMaterializeTriggerRunnable.run(CoordMaterializeTriggerService.java:94)
> at
> org.apache.oozie.service.SchedulerService$2.run(SchedulerService.java:174)
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
> at
> java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:351)
> at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:178)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:722)
> Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: An aggregate
> may not appear in the WHERE clause unless it is in a subquery contained in a
> HAVING clause or a select list, and the column being aggregated is an outer
> reference. {prepstmnt 486918974 SELECT TOP 50 t0.id, t0.last_modified_time
> FROM COORD_JOBS t0 WHERE (t0.start_time <= ? AND (t0.status = ? OR t0.status
> = ? OR t0.status = ?) AND (t0.next_matd_time < ? OR t0.next_matd_time IS
> NULL) AND (t0.next_matd_time IS NULL OR t0.end_time > t0.next_matd_time AND
> (t0.pause_time IS NULL OR t0.pause_time > t0.next_matd_time)) AND
> t0.mat_throttling > (SELECT COUNT(t0.id) FROM COORD_ACTIONS t1 WHERE
> (t1.job_id = t0.id AND t1.status = ?))) ORDER BY t0.last_modified_time ASC}
> [code=147, state=S0001]
> at
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:219)
> at
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:203)
> at
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$700(LoggingConnectionDecorator.java:59)
> at
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeQuery(LoggingConnectionDecorator.java:1118)
> at
> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:268)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeQuery(JDBCStoreManager.java:1801)
> at
> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:258)
> at
> org.apache.openjpa.jdbc.sql.SelectImpl.executeQuery(SelectImpl.java:499)
> at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:424)
> at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:382)
> at
> org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:94)
> at
> org.apache.openjpa.kernel.QueryImpl$PackingResultObjectProvider.open(QueryImpl.java:2070)
> at
> org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:34)
> ... 21 more
> NestedThrowables:
> com.microsoft.sqlserver.jdbc.SQLServerException: An aggregate may not appear
> in the WHERE clause unless it is in a subquery contained in a HAVING clause
> or a select list, and the column being aggregated is an outer reference.
> at
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
> at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
> at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
> at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4615)
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
> at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:283)
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
> at
> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:270)
> at
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeQuery(LoggingConnectionDecorator.java:1116)
> at
> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:268)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeQuery(JDBCStoreManager.java:1801)
> at
> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:258)
> at
> org.apache.openjpa.jdbc.sql.SelectImpl.executeQuery(SelectImpl.java:499)
> at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:424)
> at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:382)
> at
> org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:94)
> at
> org.apache.openjpa.kernel.QueryImpl$PackingResultObjectProvider.open(QueryImpl.java:2070)
> at
> org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:34)
> at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1251)
> at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1007)
> at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:863)
> at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:794)
> at
> org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:542)
> at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:286)
> at
> org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:302)
> at
> org.apache.oozie.service.JPAService.executeGetList(JPAService.java:481)
> at
> org.apache.oozie.executor.jpa.CoordJobQueryExecutor.getList(CoordJobQueryExecutor.java:348)
> at
> org.apache.oozie.service.CoordMaterializeTriggerService$CoordMaterializeTriggerRunnable.materializeCoordJobs(CoordMaterializeTriggerService.java:158)
> at
> org.apache.oozie.service.CoordMaterializeTriggerService$CoordMaterializeTriggerRunnable.runCoordJobMatLookup(CoordMaterializeTriggerService.java:148)
> at
> org.apache.oozie.service.CoordMaterializeTriggerService$CoordMaterializeTriggerRunnable.run(CoordMaterializeTriggerService.java:94)
> at
> org.apache.oozie.service.SchedulerService$2.run(SchedulerService.java:174)
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
> at
> java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:351)
> at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:178)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:722)
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
