[teiid-issues] [JBoss JIRA] Updated: (TEIID-949) Criteria on constant column with aggregate function doesn't push criteria down correctly
Steven Hawkins (JIRA)
jira-events at lists.jboss.org
Wed Feb 10 13:57:19 EST 2010
[ https://jira.jboss.org/jira/browse/TEIID-949?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steven Hawkins updated TEIID-949:
---------------------------------
Fix Version/s: 7.0
Affects Version/s: 6.0.0
(was: 7.0)
Assignee: Steven Hawkins (was: Larry O'Leary)
> Criteria on constant column with aggregate function doesn't push criteria down correctly
> ----------------------------------------------------------------------------------------
>
> Key: TEIID-949
> URL: https://jira.jboss.org/jira/browse/TEIID-949
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 6.0.0
> Environment: MM5.5.4
> Reporter: Larry O'Leary
> Assignee: Steven Hawkins
> Fix For: 7.0
>
>
> When a column value is a constant and used along with an aggregate, criteria on the constant column is being ignored. Here is an example query:
> SELECT * FROM (
> SELECT b.count, enterprise_id FROM (
> SELECT COUNT(*), 35 AS enterprise_id FROM (
> SELECT "A Name" AS Name, 37 AS enterprise_id
> ) c
> ) b
> ) a WHERE enterprise_id = 1 OPTION DEBUG
> The expected result is an empty result set. Instead, the result set comes back as:
> COUNT, ENTERPRISE_ID
> 0, 35
> The resulting plan looks like:
> OPTIMIZATION COMPLETE:
> PLAN TREE:
> (type=Relational, command=SELECT * FROM (SELECT b."count", enterprise_id FROM (SELECT COUNT(*), 35 AS enterprise_id FROM (SELECT 'A Name' AS Name, 37 AS enterprise_id) AS c) AS b) AS a WHERE enterprise_id = 1 OPTION DEBUG, props={0=PlanHints})
> canonical plan:
> Project(groups=[b], props={90=[b."count", enterprise_id], 30=[b."count", enterprise_id], 114=0.0, 91=[A."COUNT", A.ENTERPRISE_ID]})
> Source(groups=[b], props={90=[b."count", enterprise_id], 64=SELECT COUNT(*), 35 AS enterprise_id FROM (SELECT 'A Name' AS Name, 37 AS enterprise_id) AS c, 114=0.0, 60={b."count"=COUNT(*), b.enterprise_id=35}})
> Project(groups=[], props={90=[b."count", enterprise_id], 30=[COUNT(*), 35 AS enterprise_id], 114=0.0, 60={b."count"=COUNT(*), b.enterprise_id=35}, 91=[COUNT(*), 35 AS enterprise_id]})
> Group(groups=[c], props={90=[COUNT(*)], 114=0.0, 81=[COUNT(*)]})
> Null(groups=[c], props={90=[], 114=0.0})
> PROCESSOR PLAN:
> ProjectNode(1) output=[b."count", enterprise_id] [b."count", enterprise_id]
> ProjectNode(2) output=[b."count", enterprise_id] [COUNT(*), 35 AS enterprise_id]
> GroupingNode(3) output=[COUNT(*)] null
> NullNode(4) output=[]
> If the query is changed as follows, the expected result is received:
> SELECT * FROM (
> SELECT b.count, enterprise_id FROM (
> SELECT COUNT(*), 35 AS enterprise_id FROM (
> SELECT "A Name" AS Name, 37 AS enterprise_id
> ) c GROUP BY enterprise_id
> ) b
> ) a WHERE enterprise_id = 1 OPTION DEBUG
> Which yields a very similar plan:
> PLAN TREE:
> (type=Relational, command=SELECT * FROM (SELECT b."count", enterprise_id FROM (SELECT COUNT(*), 35 AS enterprise_id FROM (SELECT 'A Name' AS Name, 37 AS enterprise_id) AS c GROUP BY enterprise_id) AS b) AS a WHERE enterprise_id = 1 OPTION DEBUG, props={0=PlanHints})
> canonical plan:
> Project(groups=[b], props={90=[b."count", enterprise_id], 30=[b."count", enterprise_id], 114=0.0, 91=[A."COUNT", A.ENTERPRISE_ID]})
> Source(groups=[b], props={90=[b."count", enterprise_id], 64=SELECT COUNT(*), 35 AS enterprise_id FROM (SELECT 'A Name' AS Name, 37 AS enterprise_id) AS c GROUP BY enterprise_id, 114=0.0, 60={b."count"=COUNT(*), b.enterprise_id=35}})
> Project(groups=[], props={90=[b."count", enterprise_id], 30=[COUNT(*), 35 AS enterprise_id], 114=0.0, 60={b."count"=COUNT(*), b.enterprise_id=35}, 91=[COUNT(*), 35 AS enterprise_id]})
> Group(groups=[c], props={90=[COUNT(*)], 114=0.0, 80=[enterprise_id], 81=[COUNT(*)]})
> Null(groups=[c], props={90=[enterprise_id], 114=0.0})
> PROCESSOR PLAN:
> ProjectNode(1) output=[b."count", enterprise_id] [b."count", enterprise_id]
> ProjectNode(2) output=[b."count", enterprise_id] [COUNT(*), 35 AS enterprise_id]
> GroupingNode(3) output=[COUNT(*)] [enterprise_id]
> NullNode(4) output=[enterprise_id]
> Except the NullNode node contains enterprise_id as its target column vs an empty value.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the teiid-issues
mailing list