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

[Hisanobu Okuda (JIRA)]

Hisanobu Okuda created TEIID-2202:
-------------------------------------

             Summary: 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:
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

--
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