[teiid-issues] [JBoss JIRA] (TEIID-2202) Incorrect aggregate rewriting on DB2/AS400 using UDF

[Hisanobu Okuda (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2202?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12717793#comment-12717793 ] 

Hisanobu Okuda commented on TEIID-2202:
---------------------------------------

I confirmed the source query on EDS 5.3 :-

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

is valid on my DB2 v10.1/RHEL6.3. But it fails on some version of DB2/AS400.
                
> 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