[teiid-issues] [JBoss JIRA] (TEIID-2202) Incorrect aggregate rewriting on DB2/AS400 using UDF
Hisanobu Okuda (JIRA)
jira-events at lists.jboss.org
Wed Sep 12 04:38:32 EDT 2012
[ https://issues.jboss.org/browse/TEIID-2202?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12717791#comment-12717791 ]
Hisanobu Okuda commented on TEIID-2202:
---------------------------------------
Query plan on EDS 5.3:-
{code}
USER COMMAND:
SELECT examiner, exam_date, exam_date_code, num_claims, company_id, 1 AS enterprise_id FROM (SELECT examiner, parseDate_(exam_date_code) AS exam_date, exam_date_code, company_id, COUNT(*) AS num_claims FROM (SELECT CASE WHEN (ME4.ADJUSR <> ' ') AND (ME4.APRUSR <> ' ') THEN CASE WHEN ME4.APRDAT >= ME4.ADJDAT THEN ME4.APRUSR ELSE ME4.ADJUSR END WHEN (ME4.APRUSR <> ' ') AND (ME4.ADJUSR = ' ') THEN ME4.APRUSR WHEN (ME4.APRUSR = ' ') AND (ME4.ADJUSR <> ' ') THEN ME4.ADJUSR ELSE MED.ADJUSR END AS examiner, CASE WHEN (ME4.ADJUSR <> ' ') AND (ME4.APRUSR <> ' ') THEN CASE WHEN ME4.APRDAT >= ME4.ADJDAT THEN ME4.APRDAT ELSE ME4.ADJDAT END WHEN (ME4.APRUSR <> ' ') AND (ME4.ADJUSR = ' ') THEN ME4.APRDAT WHEN (ME4.APRUSR = ' ') AND (ME4.ADJUSR <> ' ') THEN ME4.ADJDAT ELSE MED.LSTCHG END AS exam_date_code, CONVERT(MED.COMPNO, INTEGER) AS company_id FROM FCC.MEDMAS AS MED INNER JOIN FCC.ME4MAS AS ME4 ON MED.BRANCD = ME4.BRANCD AND MED.BATDAT = ME4.BATDAT AND MED.BATSEQ = ME4.BATSEQ AND MED.SEQNUM = ME4.SEQNUM WHERE (MED.SYSDAT >= curdate_('-11 months 1st day')) AND (MED.LINENO = 1) AND (MED.STATCD <> 'O') AND ((ME4.ADJUSR <> ' ') OR (ME4.APRUSR <> ' ') OR (MED.ADJUSR <> ' '))) AS Event GROUP BY Event.examiner, Event.exam_date_code, Event.company_id) AS Count_By_Examiner_ID LIMIT 1000
----
PROCESSOR PLAN:
AccessNode(1) output=[examiner, exam_date, exam_date_code, num_claims, company_id, 1 AS enterprise_id] SELECT v_0.c_0, v_0.c_1, v_0.c_2, v_0.c_3, v_0.c_4 FROM (SELECT CASE WHEN (g_1.ADJUSR <> ' ') AND (g_1.APRUSR <> ' ') THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRUSR ELSE g_1.ADJUSR END WHEN (g_1.APRUSR <> ' ') AND (g_1.ADJUSR = ' ') THEN g_1.APRUSR WHEN (g_1.APRUSR = ' ') AND (g_1.ADJUSR <> ' ') THEN g_1.ADJUSR ELSE g_0.ADJUSR END AS c_0, parseDate_(CASE WHEN (g_1.ADJUSR <> ' ') AND (g_1.APRUSR <> ' ') THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN (g_1.APRUSR <> ' ') AND (g_1.ADJUSR = ' ') THEN g_1.APRDAT WHEN (g_1.APRUSR = ' ') AND (g_1.ADJUSR <> ' ') THEN g_1.ADJDAT ELSE g_0.LSTCHG END) AS c_1, CASE WHEN (g_1.ADJUSR <> ' ') AND (g_1.APRUSR <> ' ') THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN (g_1.APRUSR <> ' ') AND (g_1.ADJUSR = ' ') THEN g_1.APRDAT WHEN (g_1.APRUSR = ' ') AND (g_1.ADJUSR <> ' ') THEN g_1.ADJDAT ELSE g_0.LSTCHG END AS c_2, COUNT(*) AS c_3, CONVERT(g_0.COMPNO, INTEGER) AS c_4 FROM FCC.MEDMAS AS g_0, FCC.ME4MAS AS g_1 WHERE (g_0.BRANCD = g_1.BRANCD) AND (g_0.BATDAT = g_1.BATDAT) AND (g_0.BATSEQ = g_1.BATSEQ) AND (g_0.SEQNUM = g_1.SEQNUM) AND ((g_1.ADJUSR <> ' ') OR (g_1.APRUSR <> ' ') OR (g_0.ADJUSR <> ' ')) AND (g_0.SYSDAT >= curdate_('-11 months 1st day')) AND (g_0.LINENO = 1) AND (g_0.STATCD <> 'O') GROUP BY CASE WHEN (g_1.ADJUSR <> ' ') AND (g_1.APRUSR <> ' ') THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRUSR ELSE g_1.ADJUSR END WHEN (g_1.APRUSR <> ' ') AND (g_1.ADJUSR = ' ') THEN g_1.APRUSR WHEN (g_1.APRUSR = ' ') AND (g_1.ADJUSR <> ' ') THEN g_1.ADJUSR ELSE g_0.ADJUSR END, CASE WHEN (g_1.ADJUSR <> ' ') AND (g_1.APRUSR <> ' ') THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN (g_1.APRUSR <> ' ') AND (g_1.ADJUSR = ' ') THEN g_1.APRDAT WHEN (g_1.APRUSR = ' ') AND (g_1.ADJUSR <> ' ') THEN g_1.ADJDAT ELSE g_0.LSTCHG END, CONVERT(g_0.COMPNO, INTEGER)) AS v_0 LIMIT 1000
========
Source-specific command: SELECT v_0.c_0, v_0.c_1, v_0.c_2, v_0.c_3, v_0.c_4 FROM (SELECT CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRUSR ELSE g_1.ADJUSR END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRUSR WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJUSR ELSE g_0.ADJUSR END AS c_0, date(substr(CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRDAT WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJDAT ELSE g_0.LSTCHG END+18000000,1,4) concat '-' concat substr(CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRDAT WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJDAT ELSE g_0.LSTCHG END,4,2) concat '-' concat substr(CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRDAT WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJDAT ELSE g_0.LSTCHG END,6,2)) AS c_1, CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRDAT WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJDAT ELSE g_0.LSTCHG END AS c_2, COUNT(*) AS c_3, integer(g_0.COMPNO) AS c_4 FROM MEDMAS AS g_0, ME4MAS AS g_1 WHERE g_0.BRANCD = g_1.BRANCD AND g_0.BATDAT = g_1.BATDAT AND g_0.BATSEQ = g_1.BATSEQ AND g_0.SEQNUM = g_1.SEQNUM AND (g_1.ADJUSR <> ' ' OR g_1.APRUSR <> ' ' OR g_0.ADJUSR <> ' ') AND g_0.SYSDAT >= 2110901 AND g_0.LINENO = 1 AND g_0.STATCD <> 'O' GROUP BY CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRUSR ELSE g_1.ADJUSR END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRUSR WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJUSR ELSE g_0.ADJUSR END, CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRDAT WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJDAT ELSE g_0.LSTCHG END, integer(g_0.COMPNO)) AS v_0 FETCH FIRST 1000 ROWS ONLY
{code}
The query to data source throws the exception:-
{code}
2012-08-23 16:51:07,392 DEBUG [org.teiid.PROCESSOR] (Worker310_QueryProcessorQueue12280) Request Thread If/3D7bhPIX0.4 - error occurred
org.teiid.core.TeiidProcessingException: Error Code:-122 Message:FCC: Error Code:-122 Message:'[SQL0122] Column ADJUSR or expression in SELECT list not valid. Cause . . . . . : One of the following has occurred: -- The statement contains column name ADJUSR and an aggregate function in the SELECT clause and no GROUP BY clause is specified. -- Column name ADJUSR is specified in the SELECT clause but not in the GROUP BY clause. -- An expression is specified in the SELECT clause but not in the GROUP BY clause. -- A column or expression that is specified in the ORDER BY clause, but not in the SELECT clause, does not conform to the grouping rules listed above. Recovery . . . : Do one of the following and try the request again: -- If a GROUP BY clause is required, make certain that all columns or expressions in the SELECT list and ORDER BY clause are also in the GROUP BY clause. -- If a GROUP BY clause is not needed, the SELECT list and ORDER BY clause should not contain aggregate functions with column names.' error executing statement(s): [Prepared Values: [] SQL: SELECT v_0.c_0, v_0.c_1, v_0.c_2, v_0.c_3, v_0.c_4 FROM (SELECT CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRUSR ELSE g_1.ADJUSR END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRUSR WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJUSR ELSE g_0.ADJUSR END AS c_0, date(substr(CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRDAT WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJDAT ELSE g_0.LSTCHG END+18000000,1,4) concat '-' concat substr(CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRDAT WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJDAT ELSE g_0.LSTCHG END,4,2) concat '-' concat substr(CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRDAT WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJDAT ELSE g_0.LSTCHG END,6,2)) AS c_1, CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRDAT WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJDAT ELSE g_0.LSTCHG END AS c_2, COUNT(*) AS c_3, integer(g_0.COMPNO) AS c_4 FROM MEDMAS AS g_0, ME4MAS AS g_1 WHERE g_0.BRANCD = g_1.BRANCD AND g_0.BATDAT = g_1.BATDAT AND g_0.BATSEQ = g_1.BATSEQ AND g_0.SEQNUM = g_1.SEQNUM AND (g_1.ADJUSR <> ' ' OR g_1.APRUSR <> ' ' OR g_0.ADJUSR <> ' ') AND g_0.SYSDAT >= 2110901 AND g_0.LINENO = 1 AND g_0.STATCD <> 'O' GROUP BY CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRUSR ELSE g_1.ADJUSR END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRUSR WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJUSR ELSE g_0.ADJUSR END, CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRDAT WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJDAT ELSE g_0.LSTCHG END, integer(g_0.COMPNO)) AS v_0 FETCH FIRST 1000 ROWS ONLY]
at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:430)
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:250)
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:271)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:280)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:176)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:139)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:147)
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:375)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:288)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:216)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:240)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:122)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:292)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: [SQL0122] Column ADJUSR or expression in SELECT list not valid. Cause . . . . . : One of the following has occurred: -- The statement contains column name ADJUSR and an aggregate function in the SELECT clause and no GROUP BY clause is specified. -- Column name ADJUSR is specified in the SELECT clause but not in the GROUP BY clause. -- An expression is specified in the SELECT clause but not in the GROUP BY clause. -- A column or expression that is specified in the ORDER BY clause, but not in the SELECT clause, does not conform to the grouping rules listed above. Recovery . . . : Do one of the following and try the request again: -- If a GROUP BY clause is required, make certain that all columns or expressions in the SELECT list and ORDER BY clause are also in the GROUP BY clause. -- If a GROUP BY clause is not needed, the SELECT list and ORDER BY clause should not contain aggregate functions with column names.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:646)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:617)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1581)
at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:227)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:2063)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:2005)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:516)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:511)
at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:260)
at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:178)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:84)
... 13 more
{code}
On the contrast, the query plan on EDS 5.2 is:-
{code}
============================================================================
USER COMMAND:
SELECT examiner, exam_date, exam_date_code, num_claims, company_id, 1 AS enterprise_id FROM (SELECT examiner, parseDate_(exam_date_code) AS exam_date, exam_date_code, company_id, COUNT(*) AS num_claims FROM (SELECT CASE WHEN (ME4.ADJUSR <> ' ') AND (ME4.APRUSR <> ' ') THEN CASE WHEN ME4.APRDAT >= ME4.ADJDAT THEN ME4.APRUSR ELSE ME4.ADJUSR END WHEN (ME4.APRUSR <> ' ') AND (ME4.ADJUSR = ' ') THEN ME4.APRUSR WHEN (ME4.APRUSR = ' ') AND (ME4.ADJUSR <> ' ') THEN ME4.ADJUSR ELSE MED.ADJUSR END AS examiner, CASE WHEN (ME4.ADJUSR <> ' ') AND (ME4.APRUSR <> ' ') THEN CASE WHEN ME4.APRDAT >= ME4.ADJDAT THEN ME4.APRDAT ELSE ME4.ADJDAT END WHEN (ME4.APRUSR <> ' ') AND (ME4.ADJUSR = ' ') THEN ME4.APRDAT WHEN (ME4.APRUSR = ' ') AND (ME4.ADJUSR <> ' ') THEN ME4.ADJDAT ELSE MED.LSTCHG END AS exam_date_code, CONVERT(MED.COMPNO, INTEGER) AS company_id FROM FCC.MEDMAS AS MED INNER JOIN FCC.ME4MAS AS ME4 ON MED.BRANCD = ME4.BRANCD AND MED.BATDAT = ME4.BATDAT AND MED.BATSEQ = ME4.BATSEQ AND MED.SEQNUM = ME4.SEQNUM WHERE (MED.SYSDAT >= curdate_('-11 months 1st day')) AND (MED.LINENO = 1) AND (MED.STATCD <> 'O') AND ((ME4.ADJUSR <> ' ') OR (ME4.APRUSR <> ' ') OR (MED.ADJUSR <> ' '))) AS Event GROUP BY Event.examiner, Event.exam_date_code, Event.company_id) AS Count_By_Examiner_ID LIMIT 1000
----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(1) output=[examiner, exam_date, exam_date_code, num_claims, company_id, 1 AS enterprise_id] SELECT v_1.c_0, v_1.c_1, v_1.c_2, v_1.c_3, v_1.c_4 FROM (SELECT v_0.c_0, parseDate_(v_0.c_1) AS c_1, v_0.c_1 AS c_2, COUNT(*) AS c_3, v_0.c_2 AS c_4 FROM (SELECT CASE WHEN (g_1.ADJUSR <> ' ') AND (g_1.APRUSR <> ' ') THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRUSR ELSE g_1.ADJUSR END WHEN (g_1.APRUSR <> ' ') AND (g_1.ADJUSR = ' ') THEN g_1.APRUSR WHEN (g_1.APRUSR = ' ') AND (g_1.ADJUSR <> ' ') THEN g_1.ADJUSR ELSE g_0.ADJUSR END AS c_0, CASE WHEN (g_1.ADJUSR <> ' ') AND (g_1.APRUSR <> ' ') THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN (g_1.APRUSR <> ' ') AND (g_1.ADJUSR = ' ') THEN g_1.APRDAT WHEN (g_1.APRUSR = ' ') AND (g_1.ADJUSR <> ' ') THEN g_1.ADJDAT ELSE g_0.LSTCHG END AS c_1, CONVERT(g_0.COMPNO, INTEGER) AS c_2 FROM FCC.MEDMAS AS g_0, FCC.ME4MAS AS g_1 WHERE (g_0.BRANCD = g_1.BRANCD) AND (g_0.BATDAT = g_1.BATDAT) AND (g_0.BATSEQ = g_1.BATSEQ) AND (g_0.SEQNUM = g_1.SEQNUM) AND ((g_1.ADJUSR <> ' ') OR (g_1.APRUSR <> ' ') OR (g_0.ADJUSR <> ' ')) AND (g_0.SYSDAT >= curdate_('-11 months 1st day')) AND (g_0.LINENO = 1) AND (g_0.STATCD <> 'O')) AS v_0 GROUP BY v_0.c_0, v_0.c_1, v_0.c_2) AS v_1 LIMIT 1000
===============
2012-08-23 17:44:25,114 DEBUG [org.teiid.CONNECTOR] (Worker1129_QueryProcessorQueue18394) Source-specific command: SELECT v_1.c_0, v_1.c_1, v_1.c_2, v_1.c_3, v_1.c_4 FROM (SELECT v_0.c_0, date(substr(v_0.c_1+18000000,1,4) concat '-' concat substr(v_0.c_1,4,2) concat '-' concat substr(v_0.c_1,6,2)) AS c_1, v_0.c_1 AS c_2, COUNT(*) AS c_3, v_0.c_2 AS c_4 FROM (SELECT CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRUSR ELSE g_1.ADJUSR END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRUSR WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJUSR ELSE g_0.ADJUSR END AS c_0, CASE WHEN g_1.ADJUSR <> ' ' AND g_1.APRUSR <> ' ' THEN CASE WHEN g_1.APRDAT >= g_1.ADJDAT THEN g_1.APRDAT ELSE g_1.ADJDAT END WHEN g_1.APRUSR <> ' ' AND g_1.ADJUSR = ' ' THEN g_1.APRDAT WHEN g_1.APRUSR = ' ' AND g_1.ADJUSR <> ' ' THEN g_1.ADJDAT ELSE g_0.LSTCHG END AS c_1, integer(g_0.COMPNO) AS c_2 FROM MEDMAS AS g_0, ME4MAS AS g_1 WHERE g_0.BRANCD = g_1.BRANCD AND g_0.BATDAT = g_1.BATDAT AND g_0.BATSEQ = g_1.BATSEQ AND g_0.SEQNUM = g_1.SEQNUM AND (g_1.ADJUSR <> ' ' OR g_1.APRUSR <> ' ' OR g_0.ADJUSR <> ' ') AND g_0.SYSDAT >= 2110901 AND g_0.LINENO = 1 AND g_0.STATCD <> 'O') AS v_0 GROUP BY v_0.c_0, v_0.c_1, v_0.c_2) AS v_1 FETCH FIRST 1000 ROWS ONLY
{code}
It works fine on EDS 5.2.
> Incorrect aggregate rewriting on DB2/AS400 using UDF
> ----------------------------------------------------
>
> Key: TEIID-2202
> URL: https://issues.jboss.org/browse/TEIID-2202
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector, Query Engine
> Affects Versions: 7.7.1
> Reporter: Hisanobu Okuda
> Assignee: Steven Hawkins
>
> When I run the following query, the count(*) in the middle inline view, gets rewritten as a sum and a count in the most inner inline view. This has something to do with my the parseDate_ UDF. This is a change from EDS 5.2 where it worked correctly. I've included logs from both for contrast.
> Query:
> {code}
> SELECT
> examiner,
> exam_date,
> exam_date_code,
> num_claims,
> company_id,
> 1 AS enterprise_id
> FROM
> (
> SELECT
> examiner,
> parseDate_(exam_date_code) AS exam_date,
> exam_date_code,
> company_id,
> COUNT(*) AS num_claims
> FROM
> (
> SELECT
> CASE
> WHEN (ME4.ADJUSR <> ' ')
> AND (ME4.APRUSR <> ' ')
> THEN
> CASE
> WHEN ME4.APRDAT >= ME4.ADJDAT
> THEN ME4.APRUSR
> ELSE ME4.ADJUSR
> END
> WHEN (ME4.APRUSR <> ' ')
> AND (ME4.ADJUSR = ' ')
> THEN ME4.APRUSR
> WHEN (ME4.APRUSR = ' ')
> AND (ME4.ADJUSR <> ' ')
> THEN ME4.ADJUSR
> ELSE MED.ADJUSR
> END AS examiner,
> CASE
> WHEN (ME4.ADJUSR <> ' ')
> AND (ME4.APRUSR <> ' ')
> THEN
> CASE
> WHEN ME4.APRDAT >= ME4.ADJDAT
> THEN ME4.APRDAT
> ELSE ME4.ADJDAT
> END
> WHEN (ME4.APRUSR <> ' ')
> AND (ME4.ADJUSR = ' ')
> THEN ME4.APRDAT
> WHEN (ME4.APRUSR = ' ')
> AND (ME4.ADJUSR <> ' ')
> THEN ME4.ADJDAT
> ELSE MED.LSTCHG
> END AS exam_date_code,
> CONVERT(MED.COMPNO, INTEGER) AS company_id
> FROM
> FCC.MEDMAS AS MED
> INNER JOIN
> FCC.ME4MAS AS ME4
> ON
> MED.BRANCD = ME4.BRANCD
> AND MED.BATDAT = ME4.BATDAT
> AND MED.BATSEQ = ME4.BATSEQ
> AND MED.SEQNUM = ME4.SEQNUM
> WHERE
> (MED.SYSDAT >= curdate_('-11 months 1st day'))
> AND (MED.LINENO = 1)
> AND (MED.STATCD <> 'O')
> AND ((ME4.ADJUSR <> ' ')
> OR (ME4.APRUSR <> ' ')
> OR (MED.ADJUSR <> ' '))) AS Event
> GROUP BY
> Event.examiner,
> Event.exam_date_code,
> Event.company_id) AS Count_By_Examiner_ID
> {code}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the teiid-issues
mailing list