7:51 a.m.
[
https://issues.jboss.org/browse/TEIID-3681?page=com.atlassian.jira.plugin...
]
Mark Tawk commented on TEIID-3681:
----------------------------------
Teiid 8.11.3
Here is the plan
<?xml version='1.0' encoding='UTF-8'?><node
name="ProjectNode"><property name="Relational Node
ID"><value>3</value></property><property name="Output
Columns"><value>Profile_fqS_030915_CID
(string)</value><value>SubQuery Trx
(timestamp)</value><value>CalculatedField2
(long)</value><value>Trxmo3U_CalculatedField1
(bigdecimal)</value><value>Trxmo3U_CalculatedField
(bigdecimal)</value><value>Trxmo3U_CalculatedField11
(long)</value><value>Trxmo3U_CalculatedField2
(bigdecimal)</value><value>Trxmo3U_CalculatedField12
(long)</value><value>Trxmo3U_CalculatedField3
(bigdecimal)</value></property><property
name="Statistics"><value>Node Output Rows:
0</value><value>Node Next Batch Process Time: 0</value><value>Node
Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process
Time: 0</value><value>Node Next Batch Calls: 1</value><value>Node
Blocks: 1</value></property><property name="Cost
Estimates"><value>Estimated Node Cardinality:
10.0</value></property><property name="Child 0"><node
name="AccessNode"><property name="Relational Node
ID"><value>4</value></property><property name="Output
Columns"><value>CID (string)</value><value>expr
(timestamp)</value><value>TransactionDate
(timestamp)</value><value>Trxmo3U_CalculatedField12
(bigdecimal)</value><value>Trxmo3U_CalculatedField3
(bigdecimal)</value><value>Trxmo3U_CalculatedField11
(long)</value><value>Trxmo3U_CalculatedField2
(bigdecimal)</value><value>Trxmo3U_CalculatedField1
(long)</value><value>Trxmo3U_CalculatedField
(bigdecimal)</value></property><property
name="Statistics"><value>Node Output Rows:
0</value><value>Node Next Batch Process Time: 0</value><value>Node
Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process
Time: 0</value><value>Node Next Batch Calls: 1</value><value>Node
Blocks: 1</value></property><property name="Cost
Estimates"><value>Estimated Node Cardinality:
10.0</value></property><property
name="Query"><value>SELECT g_0.CID AS c_0, (SELECT
MAX(g_4.TransactionDate) FROM implify_data_BanksModel.implify_data_Banks.Trx_fil_030915 AS
g_4 WHERE g_4.CID = g_0.CID) AS c_1, g_1.TransactionDate AS c_2, v_0.c_2 AS c_3, v_0.c_3
AS c_4, v_0.c_4 AS c_5, v_0.c_5 AS c_6, v_0.c_6 AS c_7, v_0.c_7 AS c_8 FROM
(implify_data_BanksModel.implify_data_Banks.Profile_fqS_030915 AS g_0 LEFT OUTER JOIN
implify_data_BanksModel.implify_data_Banks.Trx_fil_030915 AS g_1 ON g_0.CID = g_1.CID)
LEFT OUTER JOIN (SELECT g_2.Trx_fil_030915_CID AS c_0, (SELECT MAX(g_3.TransactionDate)
FROM implify_data_BanksModel.implify_data_Banks.Trx_fil_030915 AS g_3 WHERE g_3.CID =
g_0.CID) AS c_1, AVG((1.0 * convert(g_2.CalculatedField1, bigdecimal))) AS c_2, AVG((1.0 *
g_2.CalculatedField)) AS c_3, MAX(g_2.CalculatedField1) AS c_4, MAX(g_2.CalculatedField)
AS c_5, MIN(g_2.CalculatedField1) AS c_6, MIN(g_2.CalculatedField) AS c_7 FROM
implify_data_BanksModel.implify_data_Banks.Trxmo3U AS g_2 WHERE g_2.Trx_fil_030915_Channel
IN ('ATM', 'Branch', 'P.O.S.') GROUP BY g_2.Trx_fil_030915_CID) AS
v_0 ON g_0.CID = v_0.c_0 LIMIT 10</value></property><property
name="Model
Name"><value>implify_data_BanksModel</value></property></node></property><property
name="Select Columns Subplan 0"><node
name="AccessNode"><property name="Relational Node
ID"><value>2</value></property><property name="Output
Columns"><value>Trx_fil_030915_TransactionDa
(timestamp)</value></property><property name="Cost
Estimates"><value>Estimated Node Cardinality:
1.0</value></property><property
name="Query"><value>SELECT MAX(g_0.TransactionDate) FROM
implify_data_BanksModel.implify_data_Banks.Trx_fil_030915 AS g_0 WHERE g_0.CID =
Profile_fqS_030915.CID</value></property><property name="Model
Name"><value>implify_data_BanksModel</value></property></node></property><property
name="Select Columns"><value>Profile_fqS_030915.CID AS
Profile_fqS_030915_CID</value><value>(SELECT MAX(g_0.TransactionDate) FROM
implify_data_BanksModel.implify_data_Banks.Trx_fil_030915 AS g_0 WHERE g_0.CID =
Profile_fqS_030915.CID) AS "SubQuery
Trx"</value><value>TIMESTAMPDIFF(SQL_TSI_DAY, Trx.TransactionDate,
{ts'2015-04-02 00:00:00.0'}) AS
CalculatedField2</value><value>Trxmo3UMEoebyXTVyguosX_Sub.Trxmo3U_CalculatedField12
AS
Trxmo3U_CalculatedField1</value><value>Trxmo3UMEoebyXTVyguosX_Sub.Trxmo3U_CalculatedField3
AS
Trxmo3U_CalculatedField</value><value>Trxmo3UMEoebyXTVyguosX_Sub.Trxmo3U_CalculatedField11
AS
Trxmo3U_CalculatedField11</value><value>Trxmo3UMEoebyXTVyguosX_Sub.Trxmo3U_CalculatedField2
AS
Trxmo3U_CalculatedField2</value><value>Trxmo3UMEoebyXTVyguosX_Sub.Trxmo3U_CalculatedField1
AS
Trxmo3U_CalculatedField12</value><value>Trxmo3UMEoebyXTVyguosX_Sub.Trxmo3U_CalculatedField
AS Trxmo3U_CalculatedField3</value></property><property name="Data
Bytes Sent"><value>0</value></property><property
name="Planning
Time"><value>15</value></property></node>
Subquery field added in wrong place in generated JDBC SQL
---------------------------------------------------------
Key: TEIID-3681
URL: https://issues.jboss.org/browse/TEIID-3681
Project: Teiid
Issue Type: Bug
Reporter: Mark Tawk
Assignee: Steven Hawkins
I'm using Teiid 8.11.3 and h2 translator:
I have a query that contains a subquery field in the select statement and a join over
another subquery. In the JDBC query executed, i found that the subquery field was added
into the subquery join which is giving an error in execution.
Here is my query:
SELECT
"Profile_fqS_030915"."CID" as "Profile_fqS_030915_CID"
,
(
SELECT
MAX("Trx_fil_030915_sub"."TransactionDate") as
"Trx_fil_030915_TransactionDa"
FROM
"implify_data_BanksModel"."implify_data_Banks"."Trx_fil_030915"
"Trx_fil_030915_sub"
WHERE
("Profile_fqS_030915"."CID" =
"Trx_fil_030915_sub"."CID") ) as "SubQuery Trx" ,
TIMESTAMPDIFF(SQL_TSI_DAY, "Trx"."TransactionDate", PARSETIMESTAMP(
'2015-04-02 00:00:00.000', 'yyyy-MM-dd HH:mm:ss.SSS' )) as
"CalculatedField2"
,
"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_CalculatedField12" as
"Trxmo3U_CalculatedField1"
,
"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_CalculatedField3" as
"Trxmo3U_CalculatedField"
,
"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_CalculatedField11" as
"Trxmo3U_CalculatedField11"
,
"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_CalculatedField2" as
"Trxmo3U_CalculatedField2"
,
"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_CalculatedField1" as
"Trxmo3U_CalculatedField12"
,
"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_CalculatedField" as
"Trxmo3U_CalculatedField3"
FROM
"implify_data_BanksModel"."implify_data_Banks"."Profile_fqS_030915"
"Profile_fqS_030915"
LEFT JOIN
"implify_data_BanksModel"."implify_data_Banks"."Trx_fil_030915"
"Trx"
ON
"Profile_fqS_030915"."CID" = "Trx"."CID"
LEFT JOIN
(
SELECT
"Trxmo3U_sub"."Trx_fil_030915_CID" as
"Trxmo3U_Trx_fil_030915_CID"
,
MIN("Trxmo3U_sub"."CalculatedField1") as
"Trxmo3U_CalculatedField1"
,
MIN("Trxmo3U_sub"."CalculatedField") as
"Trxmo3U_CalculatedField"
,
MAX("Trxmo3U_sub"."CalculatedField") as
"Trxmo3U_CalculatedField2"
,
MAX("Trxmo3U_sub"."CalculatedField1") as
"Trxmo3U_CalculatedField11"
,
AVG(1.0 * "Trxmo3U_sub"."CalculatedField") as
"Trxmo3U_CalculatedField3"
,
AVG(1.0 * "Trxmo3U_sub"."CalculatedField1") as
"Trxmo3U_CalculatedField12"
FROM
"Domain1BusinessModel"."Trxmo3U" "Trxmo3U_sub"
WHERE
( ( ("Trxmo3U_sub"."Trx_fil_030915_Channel" IN ('ATM'
,
'Branch'
,
'P.O.S.') ) ) )
GROUP BY
"Trxmo3U_sub"."Trx_fil_030915_CID")
"Trxmo3UMEoebyXTVyguosX_Sub" ON
"Profile_fqS_030915"."CID" =
"Trxmo3UMEoebyXTVyguosX_Sub"."Trxmo3U_Trx_fil_030915_CID" LIMIT 0,
10
Here is the executed JDBC query:
SELECT
g_0."CID" AS c_0,
(
SELECT
MAX(g_4."TransactionDate")
FROM
"implify_data_Banks"."Trx_fil_030915" AS g_4
WHERE
g_4."CID" = g_0."CID") AS c_1 ,
g_1."TransactionDate" AS c_2,
v_0.c_2 AS c_3,
v_0.c_3 AS c_4,
v_0.c_4 AS c_5,
v_0.c_5 AS c_6,
v_0.c_6 AS c_7,
v_0.c_7 AS c_8
FROM
("implify_data_Banks"."Profile_fqS_030915" AS g_0
LEFT OUTER JOIN
"implify_data_Banks"."Trx_fil_030915" AS g_1
ON
g_0."CID" = g_1."CID")
LEFT OUTER JOIN
(
SELECT
g_2."Trx_fil_030915_CID" AS c_0
,
*_(
SELECT
MAX(g_3."TransactionDate")
FROM
"implify_data_Banks"."Trx_fil_030915" AS g_3
WHERE
g_3."CID" = g_0."CID") AS c_1_* ,
AVG((1.0 * cast(g_2."CalculatedField1" AS decimal))) AS c_2 ,
AVG((1.0 * g_2."CalculatedField")) AS c_3 ,
MAX(g_2."CalculatedField1") AS c_4 ,
MAX(g_2."CalculatedField") AS c_5 ,
MIN(g_2."CalculatedField1") AS c_6 ,
MIN(g_2."CalculatedField") AS c_7
FROM
"implify_data_Banks"."Trxmo3U" AS g_2
WHERE
g_2."Trx_fil_030915_Channel" IN ('ATM', 'Branch',
'P.O.S.')
GROUP BY
g_2."Trx_fil_030915_CID") AS v_0 ON g_0."CID" = v_0.c_0 LIMIT 10
Error:
Please try again! TEIID30504 implify_data_BanksModelVDB: 42122 TEIID11008:TEIID11004
Error executing statement(s): [Prepared Values: []
Note that if i remove TIMESTAMPDIFF from the main query, the query executes without a
problem and the subquery field is no longer added in the join subquery
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
3881
days inactive
3881
days old
0 comments
1 participants
participants (1)
-
Mark Tawk (JIRA)