[JBoss JIRA] (TEIID-3289) Join subquery containing ifnull in join condition
by Mark Tawk (JIRA)
Mark Tawk created TEIID-3289:
--------------------------------
Summary: 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-2821) Getting invalid Query against Postgres DB
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2821?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-2821.
-----------------------------------
Fix Version/s: 8.10
Resolution: Done
Updated the resolving logic to allow items from the grouping expressions.
> Getting invalid Query against Postgres DB
> -----------------------------------------
>
> Key: TEIID-2821
> URL: https://issues.jboss.org/browse/TEIID-2821
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 7.7
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Priority: Minor
> Fix For: 8.10
>
>
> Issuing the following query when postgres datasource is the target:
> SELECT SUM(TBALANCE) FROM TELLERS JOIN BRANCHES ON BRANCHES.BID=TELLERS.BID GROUP BY TELLERS.BID ORDER BY TELLERS.BID
> (which works when run directly against postgres)
> And getting the following exception from Teiid:
> Caused by: org.teiid.core.TeiidProcessingException: TEIID30088 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30088 Unrelated order by column TELLERS.BID cannot be used in a SET query, with SELECT DISTINCT, or GROUP BY
> at org.teiid.query.resolver.util.ResolverUtil.resolveOrderBy(ResolverUtil.java:422)
> at org.teiid.query.resolver.command.SimpleQueryResolver.resolveCommand(SimpleQueryResolver.java:94)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:270)
> at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:124)
> at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:260)
> at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:370)
> at org.teiid.dqp.internal.process.Request.processRequest(Request.java:436)
> at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:600)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:309)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:722)
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-3253) Expand allowed order by expressions
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3253?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3253.
-----------------------------------
Fix Version/s: 8.10
Resolution: Done
Updated the resolving logic to allow for values from the grouping columns.
> Expand allowed order by expressions
> -----------------------------------
>
> Key: TEIID-3253
> URL: https://issues.jboss.org/browse/TEIID-3253
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.10
>
>
> with a non-simple query we won't allow derived expressions in the order by such as:
> select max\(x), y from t group by y order by case when y is null then 0 else y end
> We treat this ordering as unrelated - however it is expressed in terms of expressions from the select clause. Mondrian will generate queries of this form, which requires a workaround of modifying the dialect to use ansi null ordering.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-3253) Expand allowed order by expressions
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3253?page=com.atlassian.jira.plugin... ]
Steven Hawkins reassigned TEIID-3253:
-------------------------------------
Assignee: Steven Hawkins
> Expand allowed order by expressions
> -----------------------------------
>
> Key: TEIID-3253
> URL: https://issues.jboss.org/browse/TEIID-3253
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.10
>
>
> with a non-simple query we won't allow derived expressions in the order by such as:
> select max\(x), y from t group by y order by case when y is null then 0 else y end
> We treat this ordering as unrelated - however it is expressed in terms of expressions from the select clause. Mondrian will generate queries of this form, which requires a workaround of modifying the dialect to use ansi null ordering.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months
[JBoss JIRA] (TEIID-3146) Billing mechanism based on the query's complexity
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3146?page=com.atlassian.jira.plugin... ]
Steven Hawkins edited comment on TEIID-3146 at 1/16/15 10:19 AM:
-----------------------------------------------------------------
I'm not sure we can provide a turn-key billing mechanism. It's best to concentrate on the underlying parameters and work from there. We have
TEIID-3147 - for bytes transferred to the Teiid client
The various processing time in the query plan and TEIID-2813 which is applicable to processor threads (although they end up doing some processing work on behalf of other threads in the buffer manager) as well to get the cpu time rather than the wall clock time.
We could also look at adding memory batch statistics to the plan - however that can get messy as typically you'd want to correlate to disk usage which is handled lower down and by other threads etc.
was (Author: shawkins):
I'm not sure we can provide a turn-key billing mechanism. It's best to concentrate on the underlying parameters and work from there. We have
TEIID-3146 - for bytes transferred to the Teiid client
The various processing time in the query plan and TEIID-2813 which is applicable to processor threads (although they end up doing some processing work on behalf of other threads in the buffer manager) as well to get the cpu time rather than the wall clock time.
We could also look at adding memory batch statistics to the plan - however that can get messy as typically you'd want to correlate to disk usage which is handled lower down and by other threads etc.
> Billing mechanism based on the query's complexity
> -------------------------------------------------
>
> Key: TEIID-3146
> URL: https://issues.jboss.org/browse/TEIID-3146
> Project: Teiid
> Issue Type: Feature Request
> Reporter: Joao Viragine
> Assignee: Steven Hawkins
> Priority: Minor
> Fix For: Open To Community
>
>
> It would be nice if we had a billing mechanism based on the query's complexity.
> Teeid exposes information about the queries issued, the amount of records returned, the time elapsed and the query plan.
> It could also expose the amount of data (in bytes) returned by each query.
> We could combine the above data plus the query complexity to create a score for a billing calculation.
--
This message was sent by Atlassian JIRA
(v6.3.11#6341)
9 years, 11 months