[teiid-issues] [JBoss JIRA] (TEIID-2202) Incorrect aggregate rewriting on DB2/AS400 using UDF
Steven Hawkins (JIRA)
jira-events at lists.jboss.org
Thu Sep 13 08:26:33 EDT 2012
[ https://issues.jboss.org/browse/TEIID-2202?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12718229#comment-12718229 ]
Steven Hawkins commented on TEIID-2202:
---------------------------------------
The change between 5.2 and 5.3 is that we now use the ExecutionFactory.supportsFunctionsInGroupBy to know whether an inline view needs to be added if a grouping expression is not a simple column reference. The DB2ExecutionFactory is marked as supportsFunctionsInGroupBy unconditionally.
Can you determine if there is a database version setting that is appropriate here?
Or since db2 for i (previously db2/400) is a distinct product line do we need another translator (or at the least another property on the db2 execution) to capture the differences?
> 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