3:40 a.m.
[
https://issues.jboss.org/browse/TEIID-2202?page=com.atlassian.jira.plugin...
]
Hisanobu Okuda edited comment on TEIID-2202 at 9/12/12 4:40 AM:
----------------------------------------------------------------
I confirmed the source query on EDS 5.3 :-
{code}
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}
is valid on my DB2 v10.1/RHEL6.3 via both of CLI and Thin JDBC driver. But it fails on
some version of DB2/AS400.
was (Author: hisanobu.okuda):
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