Author: jolee
Date: 2013-08-01 09:27:00 -0400 (Thu, 01 Aug 2013)
New Revision: 4585
Modified:
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushLimit.java
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java
branches/7.7.x/engine/src/test/java/org/teiid/query/processor/TestWindowFunctions.java
Log:
TEIID-2501: window functions in view return incorrect results
Modified:
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushLimit.java
===================================================================
---
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushLimit.java 2013-07-31
20:33:58 UTC (rev 4584)
+++
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushLimit.java 2013-08-01
13:27:00 UTC (rev 4585)
@@ -170,7 +170,7 @@
}
case NodeConstants.Types.PROJECT:
{
- return child.getProperty(NodeConstants.Info.INTO_GROUP) == null;
+ return child.getProperty(NodeConstants.Info.INTO_GROUP) == null
&& !child.hasProperty(Info.HAS_WINDOW_FUNCTIONS);
}
case NodeConstants.Types.SOURCE:
{
Modified:
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java
===================================================================
---
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2013-07-31
20:33:58 UTC (rev 4584)
+++
branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2013-08-01
13:27:00 UTC (rev 4585)
@@ -52,9 +52,10 @@
import org.teiid.query.sql.symbol.ElementSymbol;
import org.teiid.query.sql.symbol.Expression;
import org.teiid.query.sql.symbol.GroupSymbol;
+import org.teiid.query.sql.symbol.SingleElementSymbol;
import org.teiid.query.sql.symbol.WindowFunction;
+import org.teiid.query.sql.symbol.WindowSpecification;
import org.teiid.query.sql.util.SymbolMap;
-import org.teiid.query.sql.visitor.AggregateSymbolCollectorVisitor;
import org.teiid.query.sql.visitor.ElementCollectorVisitor;
import org.teiid.query.sql.visitor.ValueIteratorProviderCollectorVisitor;
import org.teiid.query.util.CommandContext;
@@ -590,24 +591,37 @@
if(projectNode.getChildCount() == 0) {
return false;
}
- List<WindowFunction> windowFunctions = null;
- if (projectNode.hasBooleanProperty(Info.HAS_WINDOW_FUNCTIONS)) {
- windowFunctions = new LinkedList<WindowFunction>();
- }
Criteria crit = (Criteria)
critNode.getProperty(NodeConstants.Info.SELECT_CRITERIA);
-
- Boolean conversionResult = checkConversion(symbolMap,
ElementCollectorVisitor.getElements(crit, true), windowFunctions);
+ Collection<ElementSymbol> cols = ElementCollectorVisitor.getElements(crit,
true);
- if (conversionResult == Boolean.FALSE) {
- return false; //not convertable
- }
+ if (projectNode.hasBooleanProperty(Info.HAS_WINDOW_FUNCTIONS)) {
+ //we can push iff the predicate is against partitioning columns in all projected
window functions
+ Set<WindowFunction> windowFunctions =
RuleAssignOutputElements.getWindowFunctions((List<SingleElementSymbol>)
projectNode.getProperty(Info.PROJECT_COLS));
+ for (WindowFunction windowFunction : windowFunctions) {
+ WindowSpecification spec = windowFunction.getWindowSpecification();
+ if (spec.getOrderBy() != null || spec.getPartition() == null) {
+ return false;
+ }
+ for (ElementSymbol col : cols) {
+ if (!spec.getPartition().contains(symbolMap.getMappedExpression(col))) {
+ return false;
+ }
+ }
+ }
+ }
+
+ Boolean conversionResult = checkConversion(symbolMap, cols);
+
+ if (conversionResult == Boolean.FALSE) {
+ return false; //not convertable
+ }
+
+ if (!critNode.getSubqueryContainers().isEmpty()
+ && checkConversion(symbolMap,
critNode.getCorrelatedReferenceElements()) != null) {
+ return false; //not convertable, or has an aggregate for a correlated reference
+ }
- if (!critNode.getSubqueryContainers().isEmpty()
- && checkConversion(symbolMap,
critNode.getCorrelatedReferenceElements(), windowFunctions) != null) {
- return false; //not convertable, or has an aggregate for a correlated reference
- }
-
PlanNode copyNode = copyNode(critNode);
if (conversionResult == Boolean.TRUE) {
@@ -634,7 +648,7 @@
}
private Boolean checkConversion(SymbolMap symbolMap,
- Collection<ElementSymbol> elements, List<WindowFunction> windowFunctions)
{
+ Collection<ElementSymbol> elements) {
Boolean result = null;
for (ElementSymbol element : elements) {
@@ -652,13 +666,6 @@
if (!ElementCollectorVisitor.getAggregates(converted, false).isEmpty()) {
result = Boolean.TRUE;
}
-
- if (windowFunctions != null) {
- AggregateSymbolCollectorVisitor.getAggregates(converted, null, null, null,
windowFunctions, null);
- if (!windowFunctions.isEmpty()) {
- return false;
- }
- }
}
return result;
}
Modified:
branches/7.7.x/engine/src/test/java/org/teiid/query/processor/TestWindowFunctions.java
===================================================================
---
branches/7.7.x/engine/src/test/java/org/teiid/query/processor/TestWindowFunctions.java 2013-07-31
20:33:58 UTC (rev 4584)
+++
branches/7.7.x/engine/src/test/java/org/teiid/query/processor/TestWindowFunctions.java 2013-08-01
13:27:00 UTC (rev 4585)
@@ -158,6 +158,22 @@
helpProcess(plan, dataManager, expected);
}
+ @Test public void testRankingView() throws Exception {
+ String sql = "select * from (select e1, row_number() over (order by e1) as rn,
rank() over (order by e1) as r, dense_rank() over (order by e1 nulls last) as dr from
pm1.g1) as x where e1 = 'a'";
+
+ List<?>[] expected = new List[] {
+ Arrays.asList("a", 2, 2, 1),
+ Arrays.asList("a", 3, 2, 1),
+ Arrays.asList("a", 4, 2, 1),
+ };
+
+ FakeDataManager dataManager = new FakeDataManager();
+ sampleData1(dataManager);
+ ProcessorPlan plan = helpGetPlan(sql, RealMetadataFactory.example1Cached(),
TestOptimizer.getGenericFinder());
+
+ helpProcess(plan, dataManager, expected);
+ }
+
@Test public void testPartitionedMax() throws Exception {
String sql = "select e2, max(e1) over (partition by e2) as y from
pm1.g1";
@@ -303,5 +319,57 @@
helpProcess(plan, dataManager, expected);
}
+ @Test public void testViewCriteria() throws Exception {
+ BasicSourceCapabilities caps = getTypicalCapabilities();
+ caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true);
+ ProcessorPlan plan = TestOptimizer.helpPlan("SELECT * FROM (select e1, e3,
count(distinct e1) over (partition by e3) as r from pm1.g1) as x where x.e1 =
'a'", //$NON-NLS-1$
+ RealMetadataFactory.example1Cached(), null, new
DefaultCapabilitiesFinder(caps),
+ new String[] {
+ "SELECT g_0.e1, g_0.e3 FROM pm1.g1 AS
g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
+ FakeDataManager dataManager = new FakeDataManager();
+ sampleData1(dataManager);
+ List<?>[] expected = new List<?>[] {
+ Arrays.asList("a", Boolean.FALSE, 2),
+ Arrays.asList("a", Boolean.TRUE, 2),
+ Arrays.asList("a", Boolean.FALSE, 2),
+ };
+ helpProcess(plan, dataManager, expected);
+ }
+
+ @Test public void testViewCriteriaPushdown() throws Exception {
+ BasicSourceCapabilities caps = getTypicalCapabilities();
+ caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true);
+ ProcessorPlan plan = TestOptimizer.helpPlan("SELECT * FROM (select e1, e3,
count(distinct e1) over (partition by e3) as r from pm1.g1) as x where x.e3 = false",
//$NON-NLS-1$
+ RealMetadataFactory.example1Cached(), null, new
DefaultCapabilitiesFinder(caps),
+ new String[] {
+ "SELECT g_0.e1, g_0.e3 FROM pm1.g1 AS g_0
WHERE g_0.e3 = FALSE"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
+
+ FakeDataManager dataManager = new FakeDataManager();
+ sampleData1(dataManager);
+ List<?>[] expected = new List<?>[] {
+ Arrays.asList("a", Boolean.FALSE, 2),
+ Arrays.asList(null, Boolean.FALSE, 2),
+ Arrays.asList("b", Boolean.FALSE, 2),
+ Arrays.asList("a", Boolean.FALSE, 2),
+ };
+ helpProcess(plan, dataManager, expected);
+ }
+
+ @Test public void testViewLimit() throws Exception {
+ BasicSourceCapabilities caps = getTypicalCapabilities();
+ caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true);
+ ProcessorPlan plan = TestOptimizer.helpPlan("SELECT * FROM (select e1, e3,
count(distinct e1) over (partition by e3) as r from pm1.g1) as x limit 1",
//$NON-NLS-1$
+ RealMetadataFactory.example1Cached(), null, new
DefaultCapabilitiesFinder(caps),
+ new String[] {
+ "SELECT g_0.e1, g_0.e3 FROM pm1.g1 AS
g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
+
+ FakeDataManager dataManager = new FakeDataManager();
+ sampleData1(dataManager);
+ List<?>[] expected = new List<?>[] {
+ Arrays.asList("a", Boolean.FALSE, 2),
+ };
+ helpProcess(plan, dataManager, expected);
+ }
+
}