/*
* JBoss, Home of Professional Open Source.
* Copyright (C) 2009 Red Hat, Inc.
* Licensed to Red Hat, Inc. under one or more contributor
* license agreements. See the copyright.txt file in the
* distribution for a full listing of individual contributors.
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
* 02110-1301 USA.
*/
package org.teiid.query.optimizer;
import java.util.List;
import junit.framework.TestCase;
import org.teiid.api.exception.query.QueryParserException;
import org.teiid.api.exception.query.QueryResolverException;
import org.teiid.api.exception.query.QueryValidatorException;
import org.teiid.core.TeiidComponentException;
import org.teiid.query.optimizer.TestOptimizer.ComparisonMode;
import org.teiid.query.processor.ProcessorPlan;
import org.teiid.query.processor.relational.RelationalPlan;
import org.teiid.query.sql.symbol.Constant;
import org.teiid.query.sql.symbol.Expression;
import org.teiid.query.sql.symbol.ExpressionSymbol;
import org.teiid.query.sql.util.SymbolMap;
/**
* <p><code>TestCase</code> to cover planning and optimization of JOINs which
* use a scalar function as a symbol or as part of the JOIN criteria.</p>
*
* <p>All tests should verify and validate that the scalar function is being
* pushed/merged with the correct layer of the plan. For example, if a
* non-deterministic function is being merged with a parent node the resulting
* query may alter the final result set. Most specifically, if the function is
* executed too late during the processing of a command, the results may be
* different than if it were executed earlier during processing.</p>
* @since 6.0
*/
public class TestJoinWithFunction extends TestCase {
/**
* <p>Test the use of a non-deterministic function on a user command that
* performs a JOIN of two sources.</p>
*
* <p>The function should be executed on the result returned from the JOIN and
* is expected to be executed for each row of the final result set.</p>
* @throws TeiidComponentException
* @throws QueryValidatorException
* @throws QueryResolverException
* @throws QueryParserException
*/
public void testNonDeterministicPostJoin() throws Exception {
// source query for one side of a JOIN
String leftQuery = "SELECT pm1.g1.e1 as ID, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 " //$NON-NLS-1$
+ "FROM pm1.g1"; //$NON-NLS-1$
// source query for other side of a JOIN
String rightQuery = "SELECT pm2.g2.e1 as ID, pm2.g2.e2, pm2.g2.e3, pm2.g2.e4 " //$NON-NLS-1$
+ "FROM pm2.g2"; //$NON-NLS-1$
// User Command
/*
* Return everything from the JOIN. RandomTop is the use of RAND() on
* the user command and should result in unique random numbers for each
* row in the JOINed output.
*/
String sql = "SELECT l.ID, l.e2, l.e3, l.e4, r.ID, r.e2, r.e3, r.e4, RAND() AS RandomTop " + //$NON-NLS-1$
"FROM (" + leftQuery + ") AS l, " + //$NON-NLS-1$ //$NON-NLS-2$
"(" + rightQuery + ") AS r " + //$NON-NLS-1$ //$NON-NLS-2$
"WHERE l.ID = r.ID"; //$NON-NLS-1$
// The user command should result in two atomic commands
String[] expected = new String[] {
"SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm1.g1 AS g_0 ORDER BY c_0", //$NON-NLS-1$
"SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm2.g2 AS g_0 ORDER BY c_0", //$NON-NLS-1$
};
ProcessorPlan plan = TestOptimizer.helpPlan(sql, TestOptimizer.example1(), expected,
ComparisonMode.EXACT_COMMAND_STRING);
TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
1, // MergeJoinStrategy
0, // Null
0, // PlanExecution
1, // Project
0, // Select
0, // Sort
0 // UnionAll
});
}
/**
* <p>Test the use of a non-deterministic function on the source command of a JOIN
* defined by a user command which performs a JOIN of two sources.</p>
*
* <p>The function should be executed on the result that will be used for one side
* of the JOIN. The function should only be executed for each row of the the result
* set returned from the left-side of the JOIN which should result in the same return
* value for multiple rows of the final result set after the JOIN is completed. For
* example, if the left-side query is expected to return one row and the right-side
* query will return three rows which match the JOIN criteria for the one row on the
* left-side then the expected result should be that the function be executed once to
* represent the one row from the left-side and the function's return value will be
* repeated for each of the three post-JOINed results.</p>
* @throws TeiidComponentException
* @throws QueryValidatorException
* @throws QueryResolverException
* @throws QueryParserException
*/
public void testNonDeterministicPreJoin() throws Exception {
// source query for one side of a JOIN
String leftQuery = "SELECT pm1.g1.e1 as ID, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4, RAND() AS RandomLeft " //$NON-NLS-1$
+ "FROM pm1.g1"; //$NON-NLS-1$
// source query for other side of a JOIN
String rightQuery = "SELECT pm1.g2.e1 as ID, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 " //$NON-NLS-1$
+ "FROM pm1.g2"; //$NON-NLS-1$
// User Command
/*
* Return everything from the JOIN. TopRandom is the use of RAND() on
* the user command while RandomLeft is the use of RAND() within a
* source node.
*/
String sql = "SELECT l.ID, l.e2, l.e3, l.e4, r.ID, r.e2, r.e3, r.e4, l.RandomLeft " + //$NON-NLS-1$
"FROM (" + leftQuery + ") AS l, " + //$NON-NLS-1$ //$NON-NLS-2$
"(" + rightQuery + ") AS r " + //$NON-NLS-1$ //$NON-NLS-2$
"WHERE l.ID = r.ID"; //$NON-NLS-1$
// The user command should result in two atomic commands
String[] expected = new String[] {
"SELECT g_0.e1, g_0.e2, g_0.e3, g_0.e4 FROM pm1.g1 AS g_0", //$NON-NLS-1$
"SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm1.g2 AS g_0 ORDER BY c_0", //$NON-NLS-1$
};
// create a plan and assert our atomic queries
ProcessorPlan plan = TestOptimizer.helpPlan(sql, TestOptimizer.example1(), expected,
ComparisonMode.EXACT_COMMAND_STRING);
TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
1, // MergeJoinStrategy
0, // Null
0, // PlanExecution
2, // Project
0, // Select
0, // Sort
0 // UnionAll
});
}
/**
* Note that we detect the lower rand is not used
*/
public void testNonDeterministicPreJoin1() throws Exception {
// source query for one side of a JOIN
String leftQuery = "SELECT pm1.g1.e1 as ID, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4, RAND() AS RandomLeft " //$NON-NLS-1$
+ "FROM pm1.g1"; //$NON-NLS-1$
// source query for other side of a JOIN
String rightQuery = "SELECT pm1.g2.e1 as ID, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 " //$NON-NLS-1$
+ "FROM pm1.g2"; //$NON-NLS-1$
// User Command
/*
* Return everything from the JOIN. TopRandom is the use of RAND() on
* the user command while RandomLeft is the use of RAND() within a
* source node.
*/
String sql = "SELECT l.ID, l.e2, l.e3, l.e4, r.ID, r.e2, r.e3, r.e4 " + //$NON-NLS-1$
"FROM (" + leftQuery + ") AS l, " + //$NON-NLS-1$ //$NON-NLS-2$
"(" + rightQuery + ") AS r " + //$NON-NLS-1$ //$NON-NLS-2$
"WHERE l.ID = r.ID"; //$NON-NLS-1$
// The user command should result in two atomic commands
String[] expected = new String[] {
"SELECT g_0.e1, g_0.e2, g_0.e3, g_0.e4, g_1.e1, g_1.e2, g_1.e3, g_1.e4 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE g_0.e1 = g_1.e1", //$NON-NLS-1$
};
// create a plan and assert our atomic queries
ProcessorPlan plan = TestOptimizer.helpPlan(sql, TestOptimizer.example1(), expected,
ComparisonMode.EXACT_COMMAND_STRING);
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
/**
* <p>Test the use of a non-deterministic function on the sub-command of a user
* command and the user command itself, which performs a JOIN of two sources.</p>
*
* <p>This test combines the PostJoin and PreJoin test cases.</p>
* @throws TeiidComponentException
* @throws QueryValidatorException
* @throws QueryResolverException
* @throws QueryParserException
* @see #testNonDeterministicPostJoin
* @see #testNonDeterministicPreJoin
*/
public void testNonDeterministicPrePostJoin() throws Exception {
// source query for one side of a JOIN
String leftQuery = "SELECT pm1.g1.e1 as ID, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4, RAND() AS RandomLeft " //$NON-NLS-1$
+ "FROM pm1.g1"; //$NON-NLS-1$
// source query for other side of a JOIN
String rightQuery = "SELECT pm2.g2.e1 as ID, pm2.g2.e2, pm2.g2.e3, pm2.g2.e4 " //$NON-NLS-1$
+ "FROM pm2.g2"; //$NON-NLS-1$
// User Command
/*
* Return everything from the JOIN. TopRandom is the use of RAND() on
* the user command while RandomLeft is the use of RAND() within a
* source node.
*/
String sql = "SELECT l.ID, l.e2, l.e3, l.e4, r.ID, r.e2, r.e3, r.e4, l.RandomLeft, RAND() AS RandomTop " + //$NON-NLS-1$
"FROM (" + leftQuery + ") AS l, " + //$NON-NLS-1$ //$NON-NLS-2$
"(" + rightQuery + ") AS r " + //$NON-NLS-1$ //$NON-NLS-2$
"WHERE l.ID = r.ID"; //$NON-NLS-1$
// The user command should result in two atomic commands
String[] expected = new String[] {
"SELECT g_0.e1, g_0.e2, g_0.e3, g_0.e4 FROM pm1.g1 AS g_0", //$NON-NLS-1$
"SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm2.g2 AS g_0 ORDER BY c_0", //$NON-NLS-1$
};
// create a plan and assert our atomic queries
ProcessorPlan plan = TestOptimizer.helpPlan(sql, TestOptimizer.example1(), expected,
ComparisonMode.EXACT_COMMAND_STRING);
TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
1, // MergeJoinStrategy
0, // Null
0, // PlanExecution
2, // Project
0, // Select
0, // Sort
0 // UnionAll
});
}
/**
* <p>Test the use of a deterministic function on the user command which
* performs a JOIN of two sources.</p>
*
* <p>The function should be executed prior to the JOIN being executed and should
* result in the projected symbol becoming a constant.</p>
* @throws TeiidComponentException
* @throws QueryValidatorException
* @throws QueryResolverException
* @throws QueryParserException
*/
public void testDeterministicPostJoin() throws Exception {
// source query for one side of a JOIN
String leftQuery = "SELECT pm1.g1.e1 as ID, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 " //$NON-NLS-1$
+ "FROM pm1.g1"; //$NON-NLS-1$
// source query for other side of a JOIN
String rightQuery = "SELECT pm2.g2.e1 as ID, pm2.g2.e2, pm2.g2.e3, pm2.g2.e4 " //$NON-NLS-1$
+ "FROM pm2.g2"; //$NON-NLS-1$
// User Command
/*
* Return everything from the JOIN. SqrtTop is the use of SQRT(100) on
* the user command and should result in 10 for each row in the JOINed
* output.
*/
String sql = "SELECT l.ID, l.e2, l.e3, l.e4, r.ID, r.e2, r.e3, r.e4, SQRT(100) AS SqrtTop " + //$NON-NLS-1$
"FROM (" + leftQuery + ") AS l, " + //$NON-NLS-1$ //$NON-NLS-2$
"(" + rightQuery + ") AS r " + //$NON-NLS-1$ //$NON-NLS-2$
"WHERE l.ID = r.ID"; //$NON-NLS-1$
// The user command should result in two atomic commands
String[] expected = new String[] {
"SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm1.g1 AS g_0 ORDER BY c_0", //$NON-NLS-1$
"SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm2.g2 AS g_0 ORDER BY c_0", //$NON-NLS-1$
};
// create a plan and assert our atomic queries
ProcessorPlan plan = TestOptimizer.helpPlan(sql, TestOptimizer.example1(), expected,
ComparisonMode.EXACT_COMMAND_STRING);
TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
1, // MergeJoinStrategy
0, // Null
0, // PlanExecution
1, // Project
0, // Select
0, // Sort
0 // UnionAll
});
/*
* Retrieve root nodes elements to assert that a constant has
* has replaced the SQRT() function.
*/
List<?> elem = ((RelationalPlan) plan).getRootNode().getElements();
Constant expectedConst = new Constant(new Double(10.0));
assertEquals("Did not get expected constant value for SqrtTop in root node of plan: ", //$NON-NLS-1$
expectedConst,
SymbolMap.getExpression((Expression)elem.get(8)) // should be a AliasSymbol containing an expression
);
}
/**
* <p>The function should be executed prior to the JOIN being executed and should
* result in the projected symbol becoming a constant.</p>
* <p>Test the use of a deterministic function on the source command of a JOIN
* defined by a user command which performs a JOIN of two sources.</p>
*
* <p>The function should be executed prior to the commands from either side of the
* JOIN being executed and merged into user command prior to the JOIN actually being
* executed.</p>
* @throws TeiidComponentException
* @throws QueryValidatorException
* @throws QueryResolverException
* @throws QueryParserException
*/
public void testDeterministicPreJoin() throws Exception {
// source query for one side of a JOIN
String leftQuery = "SELECT pm1.g1.e1 as ID, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4, SQRT(100) AS SqrtLeft " //$NON-NLS-1$
+ "FROM pm1.g1"; //$NON-NLS-1$
// source query for other side of a JOIN
String rightQuery = "SELECT pm2.g2.e1 as ID, pm2.g2.e2, pm2.g2.e3, pm2.g2.e4 " //$NON-NLS-1$
+ "FROM pm2.g2"; //$NON-NLS-1$
// User Command
/*
* Return everything from the JOIN. SqrtLeft is the use of SQRT()
* within a source node.
*/
String sql = "SELECT l.ID, l.e2, l.e3, l.e4, r.ID, r.e2, r.e3, r.e4, l.SqrtLeft " + //$NON-NLS-1$
"FROM (" + leftQuery + ") AS l, " + //$NON-NLS-1$ //$NON-NLS-2$
"(" + rightQuery + ") AS r " + //$NON-NLS-1$ //$NON-NLS-2$
"WHERE l.ID = r.ID"; //$NON-NLS-1$
// The user command should result in two atomic commands
String[] expected = new String[] {
"SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm1.g1 AS g_0 ORDER BY c_0", //$NON-NLS-1$
"SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm2.g2 AS g_0 ORDER BY c_0", //$NON-NLS-1$
};
// create a plan and assert our atomic queries
ProcessorPlan plan = TestOptimizer.helpPlan(sql, TestOptimizer.example1(), expected,
ComparisonMode.EXACT_COMMAND_STRING);
TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
1, // MergeJoinStrategy
0, // Null
0, // PlanExecution
1, // Project
0, // Select
0, // Sort
0 // UnionAll
});
/*
* Retrieve root nodes elements to assert that a constant has
* has replaced the SQRT() function.
*/
List<?> elem = ((RelationalPlan) plan).getRootNode().getElements();
Constant expectedConst = new Constant(new Double(10.0));
assertEquals("Did not get expected constant value for SqrtLeft in root node of plan: ", //$NON-NLS-1$
expectedConst,
((ExpressionSymbol)elem.get(8)).getExpression() // should be a AliasSymbol containing an expression
);
}
/**
* <p>Test the use of a deterministic function on the sub-command of a user
* command and the user command itself, which performs a JOIN of two sources.</p>
*
* <p>This test combines the PostJoin and PreJoin test cases.</p>
* @throws TeiidComponentException
* @throws QueryValidatorException
* @throws QueryResolverException
* @throws QueryParserException
* @see #testDeterministicPostJoin
* @see #testDeterministicPreJoin
*/
public void testDeterministicPrePostJoin() throws Exception {
// sub-query for one side of a JOIN
String leftQuery = "SELECT pm1.g1.e1 as ID, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4, SQRT(100) AS SqrtLeft " //$NON-NLS-1$
+ "FROM pm1.g1"; //$NON-NLS-1$
// sub-query for other side of a JOIN
String rightQuery = "SELECT pm2.g2.e1 as ID, pm2.g2.e2, pm2.g2.e3, pm2.g2.e4 " //$NON-NLS-1$
+ "FROM pm2.g2"; //$NON-NLS-1$
// User Command
/*
* Return everything from the JOIN. SqrtTop is the use of SQRT(100) on
* the user command while SqrtLeft is the use of SQRT() within a
* source node.
*/
String sql = "SELECT l.ID, l.e2, l.e3, l.e4, r.ID, r.e2, r.e3, r.e4, l.SqrtLeft, SQRT(100) AS SqrtTop " + //$NON-NLS-1$
"FROM (" + leftQuery + ") AS l, " + //$NON-NLS-1$ //$NON-NLS-2$
"(" + rightQuery + ") AS r " + //$NON-NLS-1$ //$NON-NLS-2$
"WHERE l.ID = r.ID"; //$NON-NLS-1$
// The user command should result in two atomic commands
String[] expected = new String[] {
"SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm1.g1 AS g_0 ORDER BY c_0", //$NON-NLS-1$
"SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm2.g2 AS g_0 ORDER BY c_0", //$NON-NLS-1$
};
// create a plan and assert our atomic queries
ProcessorPlan plan = TestOptimizer.helpPlan(sql, TestOptimizer.example1(), expected,
ComparisonMode.EXACT_COMMAND_STRING);
TestOptimizer.checkNodeTypes(plan, new int[] { 2, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
1, // MergeJoinStrategy
0, // Null
0, // PlanExecution
1, // Project
0, // Select
0, // Sort
0 // UnionAll
});
/*
* Retrieve root nodes elements to assert that a constant has
* replaced the SQRT() function for both SqrtTop and SqrtLeft.
*/
List<?> elem = ((RelationalPlan) plan).getRootNode().getElements();
Constant expectedConst = new Constant(new Double(10.0));
assertEquals("Did not get expected constant value for SqrtLeft in root node of plan: ", //$NON-NLS-1$
expectedConst,
SymbolMap.getExpression((Expression)elem.get(8)) // should be a AliasSymbol containing an expression
);
assertEquals("Did not get expected constant value for SqrtTop in root node of plan: ", //$NON-NLS-1$
expectedConst,
SymbolMap.getExpression((Expression)elem.get(9)) // should be a AliasSymbol containing an expression
);
}
}