[JBoss JIRA] (TEIID-4112) ORA-32039: recursive WITH clause must have column alias list
by Debbie Steigner (JIRA)
[ https://issues.jboss.org/browse/TEIID-4112?page=com.atlassian.jira.plugin... ]
Debbie Steigner updated TEIID-4112:
-----------------------------------
Description:
If running a WITH table AS(...) query to Oracle and the query schema name is the same as the subquery name i.e. EWI. you receive the Oracle error:
ORA-32039: recursive WITH clause must have column alias list
If you modify the query to
WITH EWI1 AS ....
then it works.
was:
If running a WITH table AS(...) query to Oracle and the query schema name is the same as the subquery name i.e. EWI. you receive the Oracle error:
ORA-32039: recursive WITH clause must have column alias list
If you modify the query to
WITH EWI1 AS ....
then it works.
Query:
======================================================
with EWI AS (
SELECT
A.cis_code,
B.ewi_id,
B.Action_Code,
B.Action_Comments,
B.Action_Date,
VP.LONG_NAME AS "PERSON_LONG_NAME",
B.Action_Taken_By_ECD_ID,
D.Notes,
D.Notes_Date,
C.EWI_Sub_Type_Code,
C.Narrative,
C.Last_Update_Date,
C.Last_Updated_By
FROM
"V_PARTY_STATUS" A inner join v_ewi_action B On A.cis_code = B.cis_code inner join v_ewi C ON B.ewi_id = C.ewi_id INNER JOIN V_PERSON VP ON B.Action_Taken_By_ECD_ID = VP.ECD_ID
AND B.ACTION_TAKEN_BY_RACFID = VP.WINDOWS_LOGON
AND B.ACTION_TAKEN_BY_DOMAIN = VP.DOMAIN left outer join v_ewi_notes D ON C.ewi_id = D.ewi_id
where
A.agreed_monitoring_code='ENHANCED_MONITORING'
AND A.party_status_code not in ('INAC',
'GARB')
AND B.Action_Code = 'ENHANCED_MONITORING'
AND B.task_status_code = 'FINISHED'
) select * from EWI
======================================================
> ORA-32039: recursive WITH clause must have column alias list
> ------------------------------------------------------------
>
> Key: TEIID-4112
> URL: https://issues.jboss.org/browse/TEIID-4112
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.2.6_2
> Reporter: Debbie Steigner
> Assignee: Steven Hawkins
>
> If running a WITH table AS(...) query to Oracle and the query schema name is the same as the subquery name i.e. EWI. you receive the Oracle error:
> ORA-32039: recursive WITH clause must have column alias list
> If you modify the query to
> WITH EWI1 AS ....
> then it works.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-4113) Impala translator - Incorrect aggregate replacement in query
by Don Krapohl (JIRA)
[ https://issues.jboss.org/browse/TEIID-4113?page=com.atlassian.jira.plugin... ]
Don Krapohl updated TEIID-4113:
-------------------------------
Description:
When PARSETIMESTAMP is included in a query aggregates in other columns may not be written correctly. Example is as below, sum(first_metric) is rewritten to anon_grp0.agg0 in the query sent to Impala.
Given the schema:
//source
SourceTable
the_attribute string(255),
first_metric long,
second_metric bigdecimal
another_attribute String(255)
//view
VirtualTable
the_attribute string(255),
first_metric long,
second_metric bigdecimal
another_attribute String(255)
// Teiid query
SELECT the_attribute,
cast(
sum(
first_metric
)
/
count(
distinct case when second_metric >= 0 then second_metric end
) as double
) as some_alias,
PARSETIMESTAMP(another_attribute, 'yyyy-MM-dd') as somedate
FROM VirtualTable
WHERE a_filter_val=111
GROUP BY the_attribute, another_attribute
//Query sent to impala
SELECT g_0.the_attribute AS c_0,
cast(
(
anon_grp0.agg0
/
cast(
anon_grp0.agg1 AS bigint
)
) AS double
) AS c_1,
g_0.another_attribute AS c_2
FROM VirtualTable
WHERE a_filter_val=111
GROUP BY g_0.the_attribute, g_0.another_attribute
was:
When PARSETIMESTAMP is included in a query aggregates in other columns may not be written correctly. Example is as below, sum(first_metric) is rewritten to anon_grp0.agg0 in the query sent to Impala.
Given the schema:
//source
SourceTable
the_attribute string(255),
first_metric long,
second_metric bigdecimal
another_attribute String(255)
//view
VirtualTable
the_attribute string(255),
first_metric long,
second_metric bigdecimal
another_attribute String(255)
// Teiid query
SELECT the_attribute,
cast(
sum(
first_metric
)
/
count(
distinct case when second_metric >= 0 then second_metric end
) as double
) as some_alias,
PARSETIMESTAMP(another_attribute, 'yyyy-MM-dd') as somedate
FROM VirtualTable
WHERE a_filter_val=111
GROUP BY the_attribute, another_attribute
//Query sent to impala
SELECT g_0.the_attribute AS c_0,
cast(
(
anon_grp0.agg0
/
cast(
anon_grp0.agg1 AS bigint
)
) AS double
) AS c_1,
g_0.another_attribute AS c_2
FROM VirtualTable
WHERE a_filter_val=111
GROUP BY g_0.the_attribute, g_0.date_key
> Impala translator - Incorrect aggregate replacement in query
> ------------------------------------------------------------
>
> Key: TEIID-4113
> URL: https://issues.jboss.org/browse/TEIID-4113
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.13.3
> Environment: Ubuntu Trusty
> Reporter: Don Krapohl
> Assignee: Steven Hawkins
> Labels: Impala_Translator
>
> When PARSETIMESTAMP is included in a query aggregates in other columns may not be written correctly. Example is as below, sum(first_metric) is rewritten to anon_grp0.agg0 in the query sent to Impala.
> Given the schema:
> //source
> SourceTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> //view
> VirtualTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> // Teiid query
> SELECT the_attribute,
> cast(
> sum(
> first_metric
> )
> /
> count(
> distinct case when second_metric >= 0 then second_metric end
> ) as double
> ) as some_alias,
> PARSETIMESTAMP(another_attribute, 'yyyy-MM-dd') as somedate
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY the_attribute, another_attribute
> //Query sent to impala
> SELECT g_0.the_attribute AS c_0,
> cast(
> (
> anon_grp0.agg0
> /
> cast(
> anon_grp0.agg1 AS bigint
> )
> ) AS double
> ) AS c_1,
> g_0.another_attribute AS c_2
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY g_0.the_attribute, g_0.another_attribute
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-4113) Impala translator - Incorrect aggregate replacement in query
by Don Krapohl (JIRA)
[ https://issues.jboss.org/browse/TEIID-4113?page=com.atlassian.jira.plugin... ]
Don Krapohl commented on TEIID-4113:
------------------------------------
Will do.
Should be able to produce the issue with the //Teiid query block that produces the output seen in the //Query sent to Impala block above. The Teiid query:
SELECT the_attribute,
cast(
sum(
first_metric
)
/
count(
distinct case when second_metric >= 0 then second_metric end
) as double
) as some_alias,
PARSETIMESTAMP(another_attribute, 'yyyy-MM-dd') as somedate
FROM VirtualTable
WHERE a_filter_val=111
GROUP BY the_attribute, another_attribute
Will work on the logs ASAP. I have to obfuscate, which takes a bit.
> Impala translator - Incorrect aggregate replacement in query
> ------------------------------------------------------------
>
> Key: TEIID-4113
> URL: https://issues.jboss.org/browse/TEIID-4113
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.13.3
> Environment: Ubuntu Trusty
> Reporter: Don Krapohl
> Assignee: Steven Hawkins
> Labels: Impala_Translator
>
> When PARSETIMESTAMP is included in a query aggregates in other columns may not be written correctly. Example is as below, sum(first_metric) is rewritten to anon_grp0.agg0 in the query sent to Impala.
> Given the schema:
> //source
> SourceTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> //view
> VirtualTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> // Teiid query
> SELECT the_attribute,
> cast(
> sum(
> first_metric
> )
> /
> count(
> distinct case when second_metric >= 0 then second_metric end
> ) as double
> ) as some_alias,
> PARSETIMESTAMP(another_attribute, 'yyyy-MM-dd') as somedate
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY the_attribute, another_attribute
> //Query sent to impala
> SELECT g_0.the_attribute AS c_0,
> cast(
> (
> anon_grp0.agg0
> /
> cast(
> anon_grp0.agg1 AS bigint
> )
> ) AS double
> ) AS c_1,
> g_0.another_attribute AS c_2
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY g_0.the_attribute, g_0.date_key
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-4113) Impala translator - Incorrect aggregate replacement in query
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4113?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4113:
---------------------------------------
There's not quite enough here to reproduce what's going on. Can you provide the view query expression? Or the plan debug log? With just a select * from SourceTable everything works as expected.
> Impala translator - Incorrect aggregate replacement in query
> ------------------------------------------------------------
>
> Key: TEIID-4113
> URL: https://issues.jboss.org/browse/TEIID-4113
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.13.3
> Environment: Ubuntu Trusty
> Reporter: Don Krapohl
> Assignee: Steven Hawkins
> Labels: Impala_Translator
>
> When PARSETIMESTAMP is included in a query aggregates in other columns may not be written correctly. Example is as below, sum(first_metric) is rewritten to anon_grp0.agg0 in the query sent to Impala.
> Given the schema:
> //source
> SourceTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> //view
> VirtualTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> // Teiid query
> SELECT the_attribute,
> cast(
> sum(
> first_metric
> )
> /
> count(
> distinct case when second_metric >= 0 then second_metric end
> ) as double
> ) as some_alias,
> PARSETIMESTAMP(another_attribute, 'yyyy-MM-dd') as somedate
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY the_attribute, another_attribute
> //Query sent to impala
> SELECT g_0.the_attribute AS c_0,
> cast(
> (
> anon_grp0.agg0
> /
> cast(
> anon_grp0.agg1 AS bigint
> )
> ) AS double
> ) AS c_1,
> g_0.another_attribute AS c_2
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY g_0.the_attribute, g_0.date_key
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-4113) Impala translator - Incorrect aggregate replacement in query
by Don Krapohl (JIRA)
Don Krapohl created TEIID-4113:
----------------------------------
Summary: Impala translator - Incorrect aggregate replacement in query
Key: TEIID-4113
URL: https://issues.jboss.org/browse/TEIID-4113
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 8.13.3
Environment: Ubuntu Trusty
Reporter: Don Krapohl
Assignee: Steven Hawkins
When PARSETIMESTAMP is included in a query aggregates in other columns may not be written correctly. Example is as below, sum(first_metric) is rewritten to anon_grp0.agg0 in the query sent to Impala.
Given the schema:
//source
SourceTable
the_attribute string(255),
first_metric long,
second_metric bigdecimal
another_attribute String(255)
//view
VirtualTable
the_attribute string(255),
first_metric long,
second_metric bigdecimal
another_attribute String(255)
// Teiid query
SELECT the_attribute,
cast(
sum(
first_metric
)
/
count(
distinct case when second_metric >= 0 then second_metric end
) as double
) as some_alias,
PARSETIMESTAMP(another_attribute, 'yyyy-MM-dd') as somedate
FROM VirtualTable
WHERE a_filter_val=111
GROUP BY the_attribute, another_attribute
//Query sent to impala
SELECT g_0.the_attribute AS c_0,
cast(
(
anon_grp0.agg0
/
cast(
anon_grp0.agg1 AS bigint
)
) AS double
) AS c_1,
g_0.another_attribute AS c_2
FROM VirtualTable
WHERE a_filter_val=111
GROUP BY g_0.the_attribute, g_0.date_key
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years
[JBoss JIRA] (TEIID-3911) Nearly all odata4 errors reported as internal server errors
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3911?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3911.
-----------------------------------
Resolution: Done
I think this should be sufficient for now. There may be some cases where we need to further refine the error codes, but those can be new issues.
> Nearly all odata4 errors reported as internal server errors
> -----------------------------------------------------------
>
> Key: TEIID-3911
> URL: https://issues.jboss.org/browse/TEIID-3911
> Project: Teiid
> Issue Type: Sub-task
> Components: OData
> Affects Versions: 8.12
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5
>
>
> From the TeiidServiceHandler we generally rethrow our exceptions as ODataApplicationExceptions that have a status code of 500 - however even using a different status code still results in a 500 error as the Olingo framework expects exception subclasses to be used (see ErrorHandler.handleException - a general application exception is always a 500 error).
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years