3:16 a.m.
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
3:27 a.m.
[
https://issues.jboss.org/browse/TEIID-2202?page=com.atlassian.jira.plugin...
]
Hisanobu Okuda updated TEIID-2202:
----------------------------------
Description:
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}
was:
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
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
3:38 a.m.
[
https://issues.jboss.org/browse/TEIID-2202?page=com.atlassian.jira.plugin...
]
Hisanobu Okuda commented on TEIID-2202:
---------------------------------------
Query plan on EDS 5.3:-
{code}
USER COMMAND:
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 LIMIT 1000
----
PROCESSOR PLAN:
AccessNode(1) output=[examiner, exam_date, exam_date_code, num_claims, company_id, 1 AS
enterprise_id] 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,
parseDate_(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_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, CONVERT(g_0.COMPNO, INTEGER) AS
c_4 FROM FCC.MEDMAS AS g_0, FCC.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 >= curdate_('-11 months 1st day')) 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,
CONVERT(g_0.COMPNO, INTEGER)) AS v_0 LIMIT 1000
========
Source-specific command: 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}
The query to data source throws the exception:-
{code}
2012-08-23 16:51:07,392 DEBUG [org.teiid.PROCESSOR] (Worker310_QueryProcessorQueue12280)
Request Thread If/3D7bhPIX0.4 - error occurred
org.teiid.core.TeiidProcessingException: Error Code:-122 Message:FCC: Error Code:-122
Message:'[SQL0122] Column ADJUSR or expression in SELECT list not valid. Cause . . . .
. : One of the following has occurred: -- The statement contains column name ADJUSR and
an aggregate function in the SELECT clause and no GROUP BY clause is specified. -- Column
name ADJUSR is specified in the SELECT clause but not in the GROUP BY clause. -- An
expression is specified in the SELECT clause but not in the GROUP BY clause. -- A column
or expression that is specified in the ORDER BY clause, but not in the SELECT clause, does
not conform to the grouping rules listed above. Recovery . . . : Do one of the
following and try the request again: -- If a GROUP BY clause is required, make certain
that all columns or expressions in the SELECT list and ORDER BY clause are also in the
GROUP BY clause. -- If a GROUP BY clause is not needed, the SELECT list and ORDER BY
clause should not contain aggregate functions with column names.' error executing
statement(s): [Prepared Values: [] SQL: 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]
at
org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:430)
at
org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:250)
at
org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:271)
at
org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:280)
at
org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:176)
at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:139)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:147)
at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:375)
at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:288)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:216)
at
org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:240)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:122)
at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:292)
at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: [SQL0122] Column ADJUSR or expression in SELECT list not
valid. Cause . . . . . : One of the following has occurred: -- The statement contains
column name ADJUSR and an aggregate function in the SELECT clause and no GROUP BY clause
is specified. -- Column name ADJUSR is specified in the SELECT clause but not in the GROUP
BY clause. -- An expression is specified in the SELECT clause but not in the GROUP BY
clause. -- A column or expression that is specified in the ORDER BY clause, but not in the
SELECT clause, does not conform to the grouping rules listed above. Recovery . . . : Do
one of the following and try the request again: -- If a GROUP BY clause is required, make
certain that all columns or expressions in the SELECT list and ORDER BY clause are also in
the GROUP BY clause. -- If a GROUP BY clause is not needed, the SELECT list and ORDER BY
clause should not contain aggregate functions with column names.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:646)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:617)
at
com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1581)
at
com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:227)
at
com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:2063)
at
com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:2005)
at
org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:516)
at
org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:511)
at
org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:260)
at
org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:178)
at
org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:84)
... 13 more
{code}
On the contrast, the query plan on EDS 5.2 is:-
{code}
============================================================================
USER COMMAND:
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 LIMIT 1000
----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(1) output=[examiner, exam_date, exam_date_code, num_claims, company_id, 1 AS
enterprise_id] SELECT v_1.c_0, v_1.c_1, v_1.c_2, v_1.c_3, v_1.c_4 FROM (SELECT v_0.c_0,
parseDate_(v_0.c_1) AS c_1, v_0.c_1 AS c_2, COUNT(*) AS c_3, v_0.c_2 AS 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, 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_1, CONVERT(g_0.COMPNO, INTEGER) AS c_2 FROM FCC.MEDMAS AS g_0,
FCC.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 >= curdate_('-11 months 1st day')) AND (g_0.LINENO = 1) AND
(g_0.STATCD <> 'O')) AS v_0 GROUP BY v_0.c_0, v_0.c_1, v_0.c_2) AS v_1 LIMIT
1000
===============
2012-08-23 17:44:25,114 DEBUG [org.teiid.CONNECTOR] (Worker1129_QueryProcessorQueue18394)
Source-specific command: SELECT v_1.c_0, v_1.c_1, v_1.c_2, v_1.c_3, v_1.c_4 FROM (SELECT
v_0.c_0, date(substr(v_0.c_1+18000000,1,4) concat '-' concat substr(v_0.c_1,4,2)
concat '-' concat substr(v_0.c_1,6,2)) AS c_1, v_0.c_1 AS c_2, COUNT(*) AS c_3,
v_0.c_2 AS 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, 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_1, integer(g_0.COMPNO) AS c_2 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') AS v_0 GROUP BY v_0.c_0, v_0.c_1,
v_0.c_2) AS v_1 FETCH FIRST 1000 ROWS ONLY
{code}
It works fine on EDS 5.2.
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
3:40 a.m.
[
https://issues.jboss.org/browse/TEIID-2202?page=com.atlassian.jira.plugin...
]
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
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
7:26 a.m.
[
https://issues.jboss.org/browse/TEIID-2202?page=com.atlassian.jira.plugin...
]
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
11:56 a.m.
[
https://issues.jboss.org/browse/TEIID-2202?page=com.atlassian.jira.plugin...
]
Steven Hawkins resolved TEIID-2202.
-----------------------------------
Fix Version/s: 8.2
Resolution: Done
Added a boolean db2fori property on the db2 translator and updated the reference.
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
Fix For: 8.2
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
4462
days inactive
4484
days old
6 comments
2 participants
participants (2)
-
Hisanobu Okuda (JIRA) -
Steven Hawkins (JIRA)