[JBoss JIRA] (TEIID-3269) Oracle pushdown time formatting incorrect
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3269?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3269.
-----------------------------------
Fix Version/s: (was: 8.9.1)
Resolution: Done
The matching logic needed to be updated with how it prevented invalid matches, such as F or u.
So this is only partially resolved in 8.9.1 and a new commit will be needed for 8.7.x
> Oracle pushdown time formatting incorrect
> -----------------------------------------
>
> Key: TEIID-3269
> URL: https://issues.jboss.org/browse/TEIID-3269
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.7.1, 8.9.1, 8.10
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.7.1, 8.10
>
>
> Oracle formatting cannot control the number of digits displayed in the same way as Java formatting, needs to AM instead of PM, and needs better handling for ERA.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-3299) Errors when calculating bytes sent.
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3299?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3299:
----------------------------------
Summary: Errors when calculating bytes sent. (was: Client will hang when there is a serialization error)
Description:
TEIID-3147 caused several regressions:
The client will appear to hang if we are determining the data bytes sent and there is a serialization error (which is not common). This is because the receiver has already been cleared but an exception is thrown.
Also if generated keys are requested, then an update will fail on the client side with an npe as the delayed deserialization logic will be used, but no attempt will have been made to reconstruct the results.
was:The client will appear to hang if we are determining the data bytes sent (TEIID-3147) and there is a serialization error (which is not common). This is because the receiver has already been cleared but an exception is thrown.
> Errors when calculating bytes sent.
> -----------------------------------
>
> Key: TEIID-3299
> URL: https://issues.jboss.org/browse/TEIID-3299
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.9
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.10
>
>
> TEIID-3147 caused several regressions:
> The client will appear to hang if we are determining the data bytes sent and there is a serialization error (which is not common). This is because the receiver has already been cleared but an exception is thrown.
> Also if generated keys are requested, then an update will fail on the client side with an npe as the delayed deserialization logic will be used, but no attempt will have been made to reconstruct the results.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-3287) Auto generated REST war does not query parameters
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3287?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-3287.
---------------------------------
Labels: Beta2 (was: )
Fix Version/s: 8.10
Resolution: Done
The Query Parameter support for GET is added. Also, PATH parameter support for POST, DELETE, PUT also added.
{code}
paramType = "Form based";
if (request-type == "GET") paramType= "Query based";
if (uri.defined(path) paramType = "Path based"
{code}
> Auto generated REST war does not query parameters
> -------------------------------------------------
>
> Key: TEIID-3287
> URL: https://issues.jboss.org/browse/TEIID-3287
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Reporter: Ramesh Reddy
> Assignee: Ramesh Reddy
> Labels: Beta2
> Fix For: 8.10
>
>
> Currently the REST war that gets deployed automatically based on metadata does not support query parameters defined on the procedure. The same can accomplished using path parameters.
> This must also support query parameters
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-3299) Client will hang when there is a serialization error
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-3299:
-------------------------------------
Summary: Client will hang when there is a serialization error
Key: TEIID-3299
URL: https://issues.jboss.org/browse/TEIID-3299
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 8.9
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 8.10
The client will appear to hang if we are determining the data bytes sent (TEIID-3147) and there is a serialization error (which is not common). This is because the receiver has already been cleared but an exception is thrown.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-3289) Join subquery containing ifnull in join condition
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3289?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3289.
-----------------------------------
Resolution: Won't Fix
Will add a specific ki on the timestamp to string conversion for mysql, but for now will leave the pushdown in place.
> Join subquery containing ifnull in join condition
> -------------------------------------------------
>
> Key: TEIID-3289
> URL: https://issues.jboss.org/browse/TEIID-3289
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> I have join subquery linked to main query via 2 fields in the query below.
> To be able to join null values between main query and subquery, I have added ifnull to join fields with a random value.
> If I run my query directly over mysql, the field CalculatedField1 return values whenever the field RESOLUTIONDATE is null in the main query.
> But when executed in Teiid, the query returns always null for the field CalculatedField1.
> When looking at the jdbc queries executed by Teiid, I found that ifnull is used for the main query fields but not for the subquery fields, which may be causing a link problem between main query and subquery.
> I have tried to put the ifnull into the subquery but still getting jdbc subquery fields without the ifnull function.
> SELECT DISTINCT
> jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue.CREATED AS jiraissue_CREATED,
> CalculatedField1_Sub.CalculatedField1 AS CalculatedField1
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue
> LEFT JOIN
> (SELECT DISTINCT
> TIMESTAMPDIFF(
> SQL_TSI_MINUTE,
> jiraissue_sub.CREATED,
> PARSETIMESTAMP('2014-10-03 00:00:00',
> 'yyyy-MM-dd HH:mm:ss'))
> AS CalculatedField1,
> jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue_sub.CREATED AS jiraissue_CREATED
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue_sub
> WHERE ((((jiraissue_sub.RESOLUTIONDATE IS NULL)))))
> CalculatedField1_Sub
> ON (IFNULL(jiraissue.RESOLUTIONDATE, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE,
> -2147483648))
> AND (IFNULL(jiraissue.CREATED, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_CREATED,
> -2147483648))
> Teiid JDBC queries:
> SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0, g_0.`CREATED` AS c_1, ifnull(date_format(g_0.`RESOLUTIONDATE`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_2, ifnull(date_format(g_0.`CREATED`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_3 FROM `tjiradb`.`jiraissue` AS g_0 ORDER BY c_2, c_3
> SELECT g_0.`CREATED`, g_0.`RESOLUTIONDATE` FROM `tjiradb`.`jiraissue` AS g_0 WHERE g_0.`RESOLUTIONDATE` IS NULL
> Teiid Execution plan
>
> <?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 43</value><value>Node Cumulative Process Time: 49</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Child 0"><node name="SortNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 13</value><value>Node Cumulative Next Batch Process Time: 42</value><value>Node Cumulative Process Time: 48</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="JoinNode"><property name="Output Columns"><value>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 10</value><value>Node Blocks: 6</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.RESOLUTIONDATE AS c_0, g_0.CREATED AS c_1, IFNULL(convert(g_0.RESOLUTIONDATE, string), '-2147483648') AS c_2, IFNULL(convert(g_0.CREATED, string), '-2147483648') AS c_3 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 ORDER BY c_2, c_3</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 22</value><value>Node Cumulative Next Batch Process Time: 18</value><value>Node Cumulative Process Time: 25</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 1</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 3</value><value>Node Cumulative Process Time: 10</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>CREATED (timestamp)</value><value>RESOLUTIONDATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 8</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 8</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT g_0.CREATED, g_0.RESOLUTIONDATE FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 WHERE g_0.RESOLUTIONDATE IS NULL</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Select Columns"><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField1</value><value>jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue_sub.CREATED AS jiraissue_CREATED</value><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField11</value></property></node></property><property name="Select Columns"><value>CalculatedField1_Sub.CalculatedField1</value><value>CalculatedField1_Sub.jiraissue_RESOLUTIONDATE</value><value>CalculatedField1_Sub.jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField11</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT_DISTINCT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>IFNULL(convert(jiraissue.RESOLUTIONDATE, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(jiraissue.CREATED, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Select Columns"><value>jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue.CREATED AS jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField1 AS CalculatedField1</value></property></node></property><property name="Sort Mode"><value>DUP_REMOVE</value></property></node></property><property name="Row Offset"><value>null</value></property><property name="Row Limit"><value>10</value></property></node>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-3269) Oracle pushdown time formatting incorrect
by Juraj Duráni (JIRA)
[ https://issues.jboss.org/browse/TEIID-3269?page=com.atlassian.jira.plugin... ]
Juraj Duráni reopened TEIID-3269:
---------------------------------
Now the Teiid ignores some options. But not always.
E.g.:
'F MMM-dd-yyyy G' --> ' Jan-01-2000 AD'
'F MMM-dd-yyyy G u' --> '1 Jan-01-2000 AD 6'
'F MMM-dd-yyyy G u ' --> ' Jan-01-2000 AD '
'F MMM-dd-yyyy G u hh-HH' --> ' Jan-01-2000 AD 12-00'
I tried to add all options (according to http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html):
'MMM-dd-yyyy(YYYY)[DDD] G hh(HH):mm:ss.SSS w-W--F-E--u-a--k-K--z-Z-X' --> 'Jan-01-2000(2000)[001] AD 12(00):00:00.000 1-1--1-Sat--6-AM--24-0--CET-+0100-+01'
but with additional space at the end:
'MMM-dd-yyyy(YYYY)[DDD] G hh(HH):mm:ss.SSS w-W--F-E--u-a--k-K--z-Z-X ' --> 'Jan-01-2000()[001] AD 12(00):00:00.000 ----Sat---AM------- '
I tried same queries with MariaDB and did not realize any problem.
> Oracle pushdown time formatting incorrect
> -----------------------------------------
>
> Key: TEIID-3269
> URL: https://issues.jboss.org/browse/TEIID-3269
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.7.1, 8.9.1, 8.10
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.7.1, 8.9.1, 8.10
>
>
> Oracle formatting cannot control the number of digits displayed in the same way as Java formatting, needs to AM instead of PM, and needs better handling for ERA.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-3289) Join subquery containing ifnull in join condition
by Mark Tawk (JIRA)
[ https://issues.jboss.org/browse/TEIID-3289?page=com.atlassian.jira.plugin... ]
Mark Tawk commented on TEIID-3289:
----------------------------------
the workaround by adding {ts '0001-01-01 00:00:00'} in the ifnull solved the problem
Thanks a lot
> Join subquery containing ifnull in join condition
> -------------------------------------------------
>
> Key: TEIID-3289
> URL: https://issues.jboss.org/browse/TEIID-3289
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> I have join subquery linked to main query via 2 fields in the query below.
> To be able to join null values between main query and subquery, I have added ifnull to join fields with a random value.
> If I run my query directly over mysql, the field CalculatedField1 return values whenever the field RESOLUTIONDATE is null in the main query.
> But when executed in Teiid, the query returns always null for the field CalculatedField1.
> When looking at the jdbc queries executed by Teiid, I found that ifnull is used for the main query fields but not for the subquery fields, which may be causing a link problem between main query and subquery.
> I have tried to put the ifnull into the subquery but still getting jdbc subquery fields without the ifnull function.
> SELECT DISTINCT
> jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue.CREATED AS jiraissue_CREATED,
> CalculatedField1_Sub.CalculatedField1 AS CalculatedField1
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue
> LEFT JOIN
> (SELECT DISTINCT
> TIMESTAMPDIFF(
> SQL_TSI_MINUTE,
> jiraissue_sub.CREATED,
> PARSETIMESTAMP('2014-10-03 00:00:00',
> 'yyyy-MM-dd HH:mm:ss'))
> AS CalculatedField1,
> jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue_sub.CREATED AS jiraissue_CREATED
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue_sub
> WHERE ((((jiraissue_sub.RESOLUTIONDATE IS NULL)))))
> CalculatedField1_Sub
> ON (IFNULL(jiraissue.RESOLUTIONDATE, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE,
> -2147483648))
> AND (IFNULL(jiraissue.CREATED, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_CREATED,
> -2147483648))
> Teiid JDBC queries:
> SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0, g_0.`CREATED` AS c_1, ifnull(date_format(g_0.`RESOLUTIONDATE`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_2, ifnull(date_format(g_0.`CREATED`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_3 FROM `tjiradb`.`jiraissue` AS g_0 ORDER BY c_2, c_3
> SELECT g_0.`CREATED`, g_0.`RESOLUTIONDATE` FROM `tjiradb`.`jiraissue` AS g_0 WHERE g_0.`RESOLUTIONDATE` IS NULL
> Teiid Execution plan
>
> <?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 43</value><value>Node Cumulative Process Time: 49</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Child 0"><node name="SortNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 13</value><value>Node Cumulative Next Batch Process Time: 42</value><value>Node Cumulative Process Time: 48</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="JoinNode"><property name="Output Columns"><value>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 10</value><value>Node Blocks: 6</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.RESOLUTIONDATE AS c_0, g_0.CREATED AS c_1, IFNULL(convert(g_0.RESOLUTIONDATE, string), '-2147483648') AS c_2, IFNULL(convert(g_0.CREATED, string), '-2147483648') AS c_3 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 ORDER BY c_2, c_3</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 22</value><value>Node Cumulative Next Batch Process Time: 18</value><value>Node Cumulative Process Time: 25</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 1</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 3</value><value>Node Cumulative Process Time: 10</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>CREATED (timestamp)</value><value>RESOLUTIONDATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 8</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 8</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT g_0.CREATED, g_0.RESOLUTIONDATE FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 WHERE g_0.RESOLUTIONDATE IS NULL</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Select Columns"><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField1</value><value>jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue_sub.CREATED AS jiraissue_CREATED</value><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField11</value></property></node></property><property name="Select Columns"><value>CalculatedField1_Sub.CalculatedField1</value><value>CalculatedField1_Sub.jiraissue_RESOLUTIONDATE</value><value>CalculatedField1_Sub.jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField11</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT_DISTINCT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>IFNULL(convert(jiraissue.RESOLUTIONDATE, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(jiraissue.CREATED, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Select Columns"><value>jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue.CREATED AS jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField1 AS CalculatedField1</value></property></node></property><property name="Sort Mode"><value>DUP_REMOVE</value></property></node></property><property name="Row Offset"><value>null</value></property><property name="Row Limit"><value>10</value></property></node>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-3290) Auto generated REST WAR truncates the larger payloads
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3290?page=com.atlassian.jira.plugin... ]
Ramesh Reddy updated TEIID-3290:
--------------------------------
Fix Version/s: 8.7.1
8.10
> Auto generated REST WAR truncates the larger payloads
> -----------------------------------------------------
>
> Key: TEIID-3290
> URL: https://issues.jboss.org/browse/TEIID-3290
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.4
> Reporter: Ramesh Reddy
> Assignee: Ramesh Reddy
> Fix For: 8.7.1, 8.10
>
>
> Currently only query parameters are supported as the input parameters to the Teiid procedure in POST based call.
> The POST BODY also needs to be supported to handle larger payloads in this situation as query parameters has size limitations. Currently string based query parameters are supported, but since they passed as is as strings on larger payloads the they will be subject 4000 string length restriction.
> It should be possible to read metadata of the procedure and convert the string objects to respective objects before the query gets executed.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-3289) Join subquery containing ifnull in join condition
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3289?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3289:
---------------------------------------
After looking at this more, it is a similar issue to TEIID-3282. The conversion of a timestamp to string (which is chosen as the common type for timestamp/integer) will slightly differ between Teiid and the pushdown equivalent for mysql (in the pushdown query above as "date_format(g_0.`RESOLUTIONDATE`, '%Y-%m-%d %H:%i:%S.%f')"). The mysql value will have all of the trailing zero fractional seconds:
Teiid: 2012-01-01 00:00:00.0
MySQL: 2012-01-01 00:00:00.000000
We do note in the documentation that there can be variations in string formatting. This makes their use in join predicates problematic when one value is computed at the source and the other is computed in Teiid. A recommended workaround would be to compare the timestamp values directly:
IFNULL(CalculatedField1_Sub.jiraissue_CREATED, {ts '0001-01-01 00:00:00'})
Other than not allowing the pushdown of the timestamp to string conversion I'm not sure there is a good code fix as simply removing trailing zeros is not enough.
> Join subquery containing ifnull in join condition
> -------------------------------------------------
>
> Key: TEIID-3289
> URL: https://issues.jboss.org/browse/TEIID-3289
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Priority: Critical
>
> I have join subquery linked to main query via 2 fields in the query below.
> To be able to join null values between main query and subquery, I have added ifnull to join fields with a random value.
> If I run my query directly over mysql, the field CalculatedField1 return values whenever the field RESOLUTIONDATE is null in the main query.
> But when executed in Teiid, the query returns always null for the field CalculatedField1.
> When looking at the jdbc queries executed by Teiid, I found that ifnull is used for the main query fields but not for the subquery fields, which may be causing a link problem between main query and subquery.
> I have tried to put the ifnull into the subquery but still getting jdbc subquery fields without the ifnull function.
> SELECT DISTINCT
> jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue.CREATED AS jiraissue_CREATED,
> CalculatedField1_Sub.CalculatedField1 AS CalculatedField1
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue
> LEFT JOIN
> (SELECT DISTINCT
> TIMESTAMPDIFF(
> SQL_TSI_MINUTE,
> jiraissue_sub.CREATED,
> PARSETIMESTAMP('2014-10-03 00:00:00',
> 'yyyy-MM-dd HH:mm:ss'))
> AS CalculatedField1,
> jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE,
> jiraissue_sub.CREATED AS jiraissue_CREATED
> FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue jiraissue_sub
> WHERE ((((jiraissue_sub.RESOLUTIONDATE IS NULL)))))
> CalculatedField1_Sub
> ON (IFNULL(jiraissue.RESOLUTIONDATE, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE,
> -2147483648))
> AND (IFNULL(jiraissue.CREATED, -2147483648) =
> IFNULL(CalculatedField1_Sub.jiraissue_CREATED,
> -2147483648))
> Teiid JDBC queries:
> SELECT DISTINCT g_0.`RESOLUTIONDATE` AS c_0, g_0.`CREATED` AS c_1, ifnull(date_format(g_0.`RESOLUTIONDATE`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_2, ifnull(date_format(g_0.`CREATED`, '%Y-%m-%d %H:%i:%S.%f'), '-2147483648') AS c_3 FROM `tjiradb`.`jiraissue` AS g_0 ORDER BY c_2, c_3
> SELECT g_0.`CREATED`, g_0.`RESOLUTIONDATE` FROM `tjiradb`.`jiraissue` AS g_0 WHERE g_0.`RESOLUTIONDATE` IS NULL
> Teiid Execution plan
>
> <?xml version='1.0' encoding='UTF-8'?><node name="LimitNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 7</value><value>Node Cumulative Next Batch Process Time: 43</value><value>Node Cumulative Process Time: 49</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Child 0"><node name="SortNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 10</value><value>Node Next Batch Process Time: 13</value><value>Node Cumulative Next Batch Process Time: 42</value><value>Node Cumulative Process Time: 48</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 3</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="JoinNode"><property name="Output Columns"><value>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>CalculatedField1 (long)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 35</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 5</value><value>Node Blocks: 4</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>RESOLUTIONDATE (timestamp)</value><value>CREATED (timestamp)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 2048</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 0</value><value>Node Next Batch Calls: 10</value><value>Node Blocks: 6</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT DISTINCT g_0.RESOLUTIONDATE AS c_0, g_0.CREATED AS c_1, IFNULL(convert(g_0.RESOLUTIONDATE, string), '-2147483648') AS c_2, IFNULL(convert(g_0.CREATED, string), '-2147483648') AS c_3 FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 ORDER BY c_2, c_3</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Child 1"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value><value>expr (string)</value><value>expr (string)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 22</value><value>Node Cumulative Next Batch Process Time: 18</value><value>Node Cumulative Process Time: 25</value><value>Node Next Batch Calls: 4</value><value>Node Blocks: 1</value></property><property name="Child 0"><node name="ProjectNode"><property name="Output Columns"><value>CalculatedField1 (long)</value><value>jiraissue_RESOLUTIONDATE (timestamp)</value><value>jiraissue_CREATED (timestamp)</value><value>CalculatedField11 (long)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 9</value><value>Node Cumulative Next Batch Process Time: 3</value><value>Node Cumulative Process Time: 10</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Child 0"><node name="AccessNode"><property name="Output Columns"><value>CREATED (timestamp)</value><value>RESOLUTIONDATE (timestamp)</value></property><property name="Statistics"><value>Node Output Rows: 1320</value><value>Node Next Batch Process Time: 8</value><value>Node Cumulative Next Batch Process Time: 1</value><value>Node Cumulative Process Time: 8</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: -1.0</value></property><property name="Query"><value>SELECT g_0.CREATED, g_0.RESOLUTIONDATE FROM tjiradb_D1_VDB_DSModel.tjiradb.jiraissue AS g_0 WHERE g_0.RESOLUTIONDATE IS NULL</value></property><property name="Model Name"><value>tjiradb_D1_VDB_DSModel</value></property></node></property><property name="Select Columns"><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField1</value><value>jiraissue_sub.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue_sub.CREATED AS jiraissue_CREATED</value><value>TIMESTAMPDIFF(SQL_TSI_MINUTE, jiraissue_sub.CREATED, {ts'2014-10-03 00:00:00.0'}) AS CalculatedField11</value></property></node></property><property name="Select Columns"><value>CalculatedField1_Sub.CalculatedField1</value><value>CalculatedField1_Sub.jiraissue_RESOLUTIONDATE</value><value>CalculatedField1_Sub.jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField11</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Join Strategy"><value>MERGE JOIN (ALREADY_SORTED/SORT_DISTINCT)</value></property><property name="Join Type"><value>LEFT OUTER JOIN</value></property><property name="Join Criteria"><value>IFNULL(convert(jiraissue.RESOLUTIONDATE, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_RESOLUTIONDATE, string), '-2147483648')</value><value>IFNULL(convert(jiraissue.CREATED, string), '-2147483648')=IFNULL(convert(CalculatedField1_Sub.jiraissue_CREATED, string), '-2147483648')</value></property></node></property><property name="Select Columns"><value>jiraissue.RESOLUTIONDATE AS jiraissue_RESOLUTIONDATE</value><value>jiraissue.CREATED AS jiraissue_CREATED</value><value>CalculatedField1_Sub.CalculatedField1 AS CalculatedField1</value></property></node></property><property name="Sort Mode"><value>DUP_REMOVE</value></property></node></property><property name="Row Offset"><value>null</value></property><property name="Row Limit"><value>10</value></property></node>
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months