Incorrect aggregate rewriting for count(*) in JBEDS 5.3
Issue
- 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 is a change from EDS 5.2 where it worked correctly.
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
Environment
- JBoss Enterprise Data Services (JBEDS) 5.3
Subscriber exclusive content
A Red Hat subscription provides unlimited access to our knowledgebase of over 48,000 articles and solutions.
Welcome! Check out the Getting Started with Red Hat page for quick tours and guides for common tasks.
