[teiid-commits] teiid SVN: r3011 - in trunk: build/kits/jboss-container and 17 other directories.

Author: shawkins Date: 2011-03-20 09:39:50 -0400 (Sun, 20 Mar 2011) New Revision: 3011 Added: trunk/common-core/src/main/java/org/teiid/core/types/TeiidBigDecimal.java Modified: trunk/api/src/main/java/org/teiid/translator/TypeFacility.java trunk/build/kits/jboss-container/teiid-releasenotes.html trunk/common-core/src/main/java/org/teiid/core/types/DataTypeManager.java trunk/common-core/src/main/java/org/teiid/core/types/basic/BigDecimalToBigIntegerTransform.java trunk/common-core/src/main/java/org/teiid/core/types/basic/BigIntegerToBigDecimalTransform.java trunk/common-core/src/main/java/org/teiid/core/types/basic/FloatingNumberToBigDecimalTransform.java trunk/common-core/src/main/java/org/teiid/core/types/basic/StringToBigDecimalTransform.java trunk/documentation/admin-guide/src/main/docbook/en-US/content/appendix-c.xml trunk/documentation/reference/src/main/docbook/en-US/content/federated_planning.xml trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml trunk/engine/src/main/java/org/teiid/query/optimizer/relational/PartitionAnalyzer.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java trunk/engine/src/main/java/org/teiid/query/parser/SQLParserUtil.java trunk/engine/src/main/java/org/teiid/query/processor/relational/DependentValueSource.java trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java trunk/engine/src/main/java/org/teiid/query/sql/lang/ExistsCriteria.java trunk/engine/src/main/java/org/teiid/query/sql/lang/SetCriteria.java trunk/engine/src/main/java/org/teiid/query/sql/lang/SubquerySetCriteria.java trunk/engine/src/main/java/org/teiid/query/sql/visitor/EvaluatableVisitor.java trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java trunk/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj trunk/engine/src/test/java/org/teiid/query/function/TestFunction.java trunk/engine/src/test/java/org/teiid/query/function/TestFunctionLibrary.java trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java trunk/engine/src/test/java/org/teiid/query/parser/TestOptionsAndHints.java trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java Log: TEIID-1497 adding the no_unnest hint and a system property to control the default subquery unnesting. also adding a better runtime type for bigdecimal. Modified: trunk/api/src/main/java/org/teiid/translator/TypeFacility.java =================================================================== --- trunk/api/src/main/java/org/teiid/translator/TypeFacility.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/api/src/main/java/org/teiid/translator/TypeFacility.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -53,7 +53,7 @@ public static final Class<BigInteger> BIG_INTEGER = DataTypeManager.DefaultDataClasses.BIG_INTEGER; public static final Class<Float> FLOAT = DataTypeManager.DefaultDataClasses.FLOAT; public static final Class<Double> DOUBLE = DataTypeManager.DefaultDataClasses.DOUBLE; - public static final Class<BigDecimal> BIG_DECIMAL = DataTypeManager.DefaultDataClasses.BIG_DECIMAL; + public static final Class<? extends BigDecimal> BIG_DECIMAL = DataTypeManager.DefaultDataClasses.BIG_DECIMAL; public static final Class<java.sql.Date> DATE = DataTypeManager.DefaultDataClasses.DATE; public static final Class<Time> TIME = DataTypeManager.DefaultDataClasses.TIME; public static final Class<Timestamp> TIMESTAMP = DataTypeManager.DefaultDataClasses.TIMESTAMP; Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-03-20 13:39:50 UTC (rev 3011) @@ -42,6 +42,7 @@ <LI><B>userRequestSourceConcurrency</B> - was added to control the number of concurrent source queries allowed for each user request. <LI><B>Dependent Join Improvements</B> - dependent join analysis and costing in general was improved to consider dependent joins earlier in planning. <LI><B>Memory Management Improvements</B> - maxReserveBatchColumns and maxProcessingBatchesColumns will be default be determined automatically and will more reliably prevent memory issues. See the admin guide for more. + <LI><B>Subquery optimization control</B> - added the MJ and NO_UNNEST hints and the org.teiid.subqueryUnnestDefault system property to control the optimization of subqueries to traditional joins or to a merge join implemenation of a semijoin or antijoin. </UL> <h2><a name="Compatibility">Compatibility Issues</a></h2> @@ -153,6 +154,7 @@ <h2><a name="Other">Other Issues</a></h2> <ul> + <li>TEIID-1170 - correlated subqueries are not allowed in UPDATEs or DELETEs against internal Teiid tables (internal materialized views or temporary tables). <li>TEIID-1281 - Negative start indexing is not supported by DB2 and Derby databases. Usage of the Teiid SUBSTRING against these sources should not use negative start values. <li>TEIID-1008 - Most versions of Oracle and MySQL do not support deeply nested correlated references. There is currently no workaround for this issue. <li>For compatibility with the 7.0 release if a stored procedure parameter list begins with identifier=, then it will be parsed as a named parameter invocation even if the intent was to use a comparison predicate Modified: trunk/common-core/src/main/java/org/teiid/core/types/DataTypeManager.java =================================================================== --- trunk/common-core/src/main/java/org/teiid/core/types/DataTypeManager.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/common-core/src/main/java/org/teiid/core/types/DataTypeManager.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -173,7 +173,7 @@ public static final Class<BigInteger> BIG_INTEGER = BigInteger.class; public static final Class<Float> FLOAT = Float.class; public static final Class<Double> DOUBLE = Double.class; - public static final Class<BigDecimal> BIG_DECIMAL = BigDecimal.class; + public static final Class<? extends BigDecimal> BIG_DECIMAL = TeiidBigDecimal.class; public static final Class<java.sql.Date> DATE = java.sql.Date.class; public static final Class<Time> TIME = Time.class; public static final Class<Timestamp> TIMESTAMP = Timestamp.class; @@ -201,14 +201,14 @@ } /** Base data type names and classes, Type name --> Type class */ - private static Map<String, Class> dataTypeNames = new LinkedHashMap<String, Class>(128); + private static Map<String, Class<?>> dataTypeNames = new LinkedHashMap<String, Class<?>>(128); /** Base data type names and classes, Type class --> Type name */ - private static Map<Class, String> dataTypeClasses = new LinkedHashMap<Class, String>(128); + private static Map<Class<?>, String> dataTypeClasses = new LinkedHashMap<Class<?>, String>(128); private static Set<String> DATA_TYPE_NAMES; - private static Set<Class> DATA_TYPE_CLASSES = Collections.unmodifiableSet(dataTypeClasses.keySet()); + private static Set<Class<?>> DATA_TYPE_CLASSES = Collections.unmodifiableSet(dataTypeClasses.keySet()); private static Map<Class<?>, SourceTransform> sourceConverters = new HashMap<Class<?>, SourceTransform>(); @@ -251,7 +251,7 @@ return DATA_TYPE_NAMES; } - public static Set<Class> getAllDataTypeClasses() { + public static Set<Class<?>> getAllDataTypeClasses() { return DATA_TYPE_CLASSES; } @@ -736,6 +736,12 @@ return new Timestamp(value.getTime()); } }); + addSourceTransform(BigDecimal.class, new SourceTransform<BigDecimal, TeiidBigDecimal>() { + @Override + public TeiidBigDecimal transform(BigDecimal value) { + return new TeiidBigDecimal(value); + } + }); } public static Object convertToRuntimeType(Object value) { Added: trunk/common-core/src/main/java/org/teiid/core/types/TeiidBigDecimal.java =================================================================== --- trunk/common-core/src/main/java/org/teiid/core/types/TeiidBigDecimal.java (rev 0) +++ trunk/common-core/src/main/java/org/teiid/core/types/TeiidBigDecimal.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -0,0 +1,68 @@ +/* + * JBoss, Home of Professional Open Source. + * See the COPYRIGHT.txt file distributed with this work for information + * regarding copyright ownership. Some portions may be licensed + * to Red Hat, Inc. under one or more contributor license agreements. + * + * 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.core.types; + +import java.math.BigDecimal; +import java.math.BigInteger; + +/** + * Same as a regular BigDecimal, but changes the implementation of hashCode and equals + * so that the same numerical value regardless of scale will be equal + */ +public class TeiidBigDecimal extends BigDecimal { + + private static final long serialVersionUID = -5796515987947436480L; + + public TeiidBigDecimal(BigInteger unscaled, int scale) { + super(unscaled, scale); + } + + public TeiidBigDecimal(BigDecimal bigDecimal) { + this(bigDecimal.unscaledValue(), bigDecimal.scale()); + } + + public TeiidBigDecimal(String val) { + super(val); + } + + @Override + public int hashCode() { + int xsign = this.signum(); + if (xsign == 0) + return 0; + BigDecimal bd = this.stripTrailingZeros(); + return bd.hashCode(); + } + + @Override + public boolean equals(Object x) { + if (x == this) { + return true; + } + if (!(x instanceof BigDecimal)) { + return false; + } + return this.compareTo((BigDecimal)x) == 0; + } + +} Property changes on: trunk/common-core/src/main/java/org/teiid/core/types/TeiidBigDecimal.java ___________________________________________________________________ Added: svn:mime-type + text/plain Modified: trunk/common-core/src/main/java/org/teiid/core/types/basic/BigDecimalToBigIntegerTransform.java =================================================================== --- trunk/common-core/src/main/java/org/teiid/core/types/basic/BigDecimalToBigIntegerTransform.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/common-core/src/main/java/org/teiid/core/types/basic/BigDecimalToBigIntegerTransform.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -23,10 +23,10 @@ package org.teiid.core.types.basic; import java.math.BigDecimal; -import java.math.BigInteger; import org.teiid.core.types.Transform; import org.teiid.core.types.TransformationException; +import org.teiid.core.types.DataTypeManager.DefaultDataClasses; public class BigDecimalToBigIntegerTransform extends Transform { @@ -47,16 +47,16 @@ * Type of the incoming value. * @return Source type */ - public Class getSourceType() { - return BigDecimal.class; + public Class<?> getSourceType() { + return DefaultDataClasses.BIG_DECIMAL; } /** * Type of the outgoing value. * @return Target type */ - public Class getTargetType() { - return BigInteger.class; + public Class<?> getTargetType() { + return DefaultDataClasses.BIG_INTEGER; } public boolean isExplicit() { Modified: trunk/common-core/src/main/java/org/teiid/core/types/basic/BigIntegerToBigDecimalTransform.java =================================================================== --- trunk/common-core/src/main/java/org/teiid/core/types/basic/BigIntegerToBigDecimalTransform.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/common-core/src/main/java/org/teiid/core/types/basic/BigIntegerToBigDecimalTransform.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -22,11 +22,12 @@ package org.teiid.core.types.basic; -import java.math.BigDecimal; import java.math.BigInteger; +import org.teiid.core.types.TeiidBigDecimal; import org.teiid.core.types.Transform; import org.teiid.core.types.TransformationException; +import org.teiid.core.types.DataTypeManager.DefaultDataClasses; public class BigIntegerToBigDecimalTransform extends Transform { @@ -40,23 +41,23 @@ * the transformation fails */ public Object transformDirect(Object value) throws TransformationException { - return new BigDecimal((BigInteger) value); + return new TeiidBigDecimal((BigInteger) value, 0); } /** * Type of the incoming value. * @return Source type */ - public Class getSourceType() { - return BigInteger.class; + public Class<?> getSourceType() { + return DefaultDataClasses.BIG_INTEGER; } /** * Type of the outgoing value. * @return Target type */ - public Class getTargetType() { - return BigDecimal.class; + public Class<?> getTargetType() { + return DefaultDataClasses.BIG_DECIMAL; } } Modified: trunk/common-core/src/main/java/org/teiid/core/types/basic/FloatingNumberToBigDecimalTransform.java =================================================================== --- trunk/common-core/src/main/java/org/teiid/core/types/basic/FloatingNumberToBigDecimalTransform.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/common-core/src/main/java/org/teiid/core/types/basic/FloatingNumberToBigDecimalTransform.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -25,6 +25,7 @@ import java.math.BigDecimal; import org.teiid.core.types.DataTypeManager; +import org.teiid.core.types.TeiidBigDecimal; import org.teiid.core.types.Transform; import org.teiid.core.types.TransformationException; @@ -48,7 +49,7 @@ public Object transformDirect(Object value) throws TransformationException { BigDecimal result = BigDecimal.valueOf(((Number)value).doubleValue()); result = result.setScale(Math.max(result.scale(), (value instanceof Double ? 16 : 8) - result.precision())); - return result; + return new TeiidBigDecimal(result); } /** Modified: trunk/common-core/src/main/java/org/teiid/core/types/basic/StringToBigDecimalTransform.java =================================================================== --- trunk/common-core/src/main/java/org/teiid/core/types/basic/StringToBigDecimalTransform.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/common-core/src/main/java/org/teiid/core/types/basic/StringToBigDecimalTransform.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -22,11 +22,11 @@ package org.teiid.core.types.basic; -import java.math.BigDecimal; - import org.teiid.core.CorePlugin; +import org.teiid.core.types.TeiidBigDecimal; import org.teiid.core.types.Transform; import org.teiid.core.types.TransformationException; +import org.teiid.core.types.DataTypeManager.DefaultDataClasses; public class StringToBigDecimalTransform extends Transform { @@ -41,7 +41,7 @@ */ public Object transformDirect(Object value) throws TransformationException { try { - return new BigDecimal(((String)value).trim()); + return new TeiidBigDecimal(((String)value).trim()); } catch(NumberFormatException e) { throw new TransformationException("ERR.003.029.0014", CorePlugin.Util.getString("ERR.003.029.0014", value)); //$NON-NLS-1$ //$NON-NLS-2$ } @@ -51,16 +51,16 @@ * Type of the incoming value. * @return Source type */ - public Class getSourceType() { - return String.class; + public Class<?> getSourceType() { + return DefaultDataClasses.STRING; } /** * Type of the outgoing value. * @return Target type */ - public Class getTargetType() { - return BigDecimal.class; + public Class<?> getTargetType() { + return DefaultDataClasses.BIG_DECIMAL; } @Override Modified: trunk/documentation/admin-guide/src/main/docbook/en-US/content/appendix-c.xml =================================================================== --- trunk/documentation/admin-guide/src/main/docbook/en-US/content/appendix-c.xml 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/documentation/admin-guide/src/main/docbook/en-US/content/appendix-c.xml 2011-03-20 13:39:50 UTC (rev 3011) @@ -24,5 +24,11 @@ expected by the SQL specification. </para> </listitem> + <listitem id="subquery_unnest"> + <para><emphasis>org.teiid.subqueryUnnestDefault</emphasis> - defaults to false. + Set to true to aggressively unnest subquery IN and EXISTS predicates. If possible the predicate will be unnested to a traditional join and will be eligible for dependent join planning. + If a traditional join is not possible (such as with NOT IN) a merge join version of the semijoin or antijoin will be considered by upon the costing information available. + </para> + </listitem> </itemizedlist> </appendix> \ No newline at end of file Modified: trunk/documentation/reference/src/main/docbook/en-US/content/federated_planning.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/federated_planning.xml 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/documentation/reference/src/main/docbook/en-US/content/federated_planning.xml 2011-03-20 13:39:50 UTC (rev 3011) @@ -262,7 +262,7 @@ </para> </tip> <warning><para> - A simple SELECT COUNT(*) ... against a view where all join tables are marked as optional will not return a meaningful result. + A simple "SELECT COUNT(*) FROM VIEW" against a view where all join tables are marked as optional will not return a meaningful result. </para> </warning> </section> @@ -305,11 +305,60 @@ <listitem> <para>Parallelizing of source access during execution.</para> </listitem> + <listitem> + <para><xref linkend="subquery_optimization"/></para> + </listitem> </itemizedlist> </para> </section> </section> - <section> + <section id="subquery_optimization"> + <title>Subquery optimization</title> + <itemizedlist> + <listitem> + <para>EXISTS subqueries are typically rewrite to "SELECT 1 FROM ..." to prevent unnecessary evaluation of SELECT expressions.</para> + </listitem> + <listitem> + <para>Quantified compare SOME subqueries are always turned into an equivalent IN prediate or comparison against an aggregate value. e.g. col > SOME (select col1 from table) would become col > (select min(col1) from table)</para> + </listitem> + <listitem> + <para>Uncorrelated EXISTs and scalar subquery that are not pushed to the source can be preevaluated prior to source command formation.</para> + </listitem> + <listitem> + <para>Correlated subqueries used in DETELEs or UPDATEs that are not pushed as part of the corresponding DELETE/UPDATE will cause Teiid to perform row-by-row compensating processing. + This will only happen if the affected table has a primary key. If it does not, then an exception will be thrown.</para> + </listitem> + <listitem> + <para>WHERE or HAVING clause IN and EXISTs predicates can take the MJ (merge join) or NO_UNNEST (no unnest) hints appearing just before the subquery. + The MJ hint directs the optimizer to use a traditional, semijoin, or antisemijoin merge join if possible. + The NO_UNNEST hint, which supercedes the MJ hint, will direct the optimizer to leave the subquery in place. + <example> + <title>Merge Join Hint Usage</title> + <programlisting language="SQL">SELECT col1 from tbl where col2 IN /*+ MJ */ (SELECT col1 FROM tbl2)</programlisting> + </example> + <example> + <title>No Unnest Hint Usage</title> + <programlisting language="SQL">SELECT col1 from tbl where col2 IN /*+ NO_UNNEST */ (SELECT col1 FROM tbl2)</programlisting> + </example> + </para> + </listitem> + <listitem> + <para>The system property <link linkend="subquery_unnest">org.teiid.subqueryUnnestDefault</link> controls whether the optimizer will by default unnest subqueries. + The default is false. If true, then most non-negated WHERE or HAVING clause non-negated EXISTS or IN subquery predicates can be converted to a traditional merge join or as antijoin or semijoin variants.</para> + </listitem> + <listitem> + <para>WHERE clause EXISTs and IN predicates that can be rewriten to a traditional join with the semantics of the semi-join can preserved + if the system property <link linkend="subquery_unnest">org.teiid.subqueryUnnestDefault</link> is set to true or the subquery has a MJ hint.</para> + </listitem> + <listitem> + <para>EXISTs and scalar subqueries that are not pushed down, and not converted to merge joins, are implicitly limited to 1 and 2 result rows respectively.</para> + </listitem> + <listitem> + <para>Conversion of subquery predicates to nested loop joins is not yet available.</para> + </listitem> + </itemizedlist> + </section> + <section> <title>Federated Failure Modes</title> <section> <title>Partial Results</title> Modified: trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2011-03-20 13:39:50 UTC (rev 3011) @@ -800,6 +800,7 @@ <listitem><para>FROM <link linkend="texttable">TEXTTABLE...</link></para></listitem> <listitem><para>FROM <link linkend="xmltable">XMLTABLE...</link></para></listitem> <listitem><para>FROM <link linkend="arraytable">ARRAYTABLE...</link></para></listitem> + <listitem><para>FROM <link linkend="from_subquery">(SELECT ...</link></para></listitem> </itemizedlist> <note> <title>DEP Hints</title> @@ -1256,47 +1257,34 @@ </para> </listitem> </itemizedlist> - <itemizedlist> - <para>Supported subquery locations: - </para> - <listitem> - <para><link linkend="from_subquery">Subqueries in the FROM clause</link></para> - </listitem> - <listitem> - <para><link linkend="where_subquery">Subqueries in the WHERE/HAVING Clauses</link></para> - </listitem> - <listitem> - <para>Subqueries may be used in any expression or CASE CRITERIA in the SELECT clasue.</para> - </listitem> - </itemizedlist> <section id="from_subquery"> <title>Inline views</title> - <para> Subqueries in the FROM clause of the outer query (also known as + <para>Subqueries in the FROM clause of the outer query (also known as "inline views") can return any number of rows and columns. This type - of subquery must always be given an alias.</para> + of subquery must always be given an alias. An inline view is nearly identical to a traditional view. See also <xref linkend="with_clause"/>.</para> <example> <title>Example Subquery in FROM Clause (Inline View)</title> - <programlisting>SELECT a FROM (SELECT Y.b, Y.c FROM Y WHERE Y.d = ‘3’) AS X WHERE a = X.c AND b = X.b</programlisting> + <programlisting language="SQL">SELECT a FROM (SELECT Y.b, Y.c FROM Y WHERE Y.d = ‘3’) AS X WHERE a = X.c AND b = X.b</programlisting> </example> </section> - <section id="where_subquery"> - <title>Subqueries in the WHERE and HAVING clauses</title> - <para>Subqueries supported in the criteria of the outer query include subqueries in an IN clause, subqueries using the ANY/SOME or ALL predicate quantifier, and subqueries using the EXISTS predicate. </para> + <section id="other_subqueries"> + <title>Subqueries can appear anywhere where an expression or criteria is expected.</title> + <para>Subqueries are supported in quantified criteria, the EXISTS predicate, the IN predicate, and as <xref linkend="scalar_subqueries"/>.</para> <example> <title>Example Subquery in WHERE Using EXISTS</title> <programlisting>SELECT a FROM X WHERE EXISTS (SELECT 1 FROM Y WHERE c=X.a)</programlisting> </example> - <note><para>EXISTS subqueries should typically follow the convention "SELECT 1 FROM ..." to prevent unnecessary evaluation of select expressions.</para></note> - <para> - The following usages of subqueries must each select only one column, but can return any number of rows. - </para> <example> - <title>Example Subqueries in WHERE Clause</title> - <programlisting>SELECT a FROM X WHERE a IN (SELECT b FROM Y WHERE c=3) -SELECT a FROM X WHERE a &gt;= ANY (SELECT b FROM Y WHERE c=3) + <title>Example Quantified Comparison Subqueries</title> + <programlisting language="SQL">SELECT a FROM X WHERE a &gt;= ANY (SELECT b FROM Y WHERE c=3) SELECT a FROM X WHERE a &lt; SOME (SELECT b FROM Y WHERE c=4) SELECT a FROM X WHERE a = ALL (SELECT b FROM Y WHERE c=2)</programlisting> </example> + <example> + <title>Example IN Subquery</title> + <programlisting language="SQL">SELECT a FROM X WHERE a IN (SELECT b FROM Y WHERE c=3)</programlisting> + </example> + <para>See also <xref linkend="subquery_optimization"/>.</para> </section> </section> </chapter> \ No newline at end of file Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/PartitionAnalyzer.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/PartitionAnalyzer.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/PartitionAnalyzer.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -27,13 +27,13 @@ import java.util.Collection; import java.util.Collections; import java.util.HashMap; +import java.util.HashSet; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.LinkedList; import java.util.List; import java.util.Map; import java.util.Set; -import java.util.TreeSet; import org.teiid.core.types.DataTypeManager; import org.teiid.query.sql.lang.CompareCriteria; @@ -124,9 +124,9 @@ continue; } if (cc.getLeftExpression() instanceof Constant) { - inMap.put(cc.getRightExpression(), new TreeSet<Constant>(Arrays.asList((Constant)cc.getLeftExpression()))); + inMap.put(cc.getRightExpression(), new HashSet<Constant>(Arrays.asList((Constant)cc.getLeftExpression()))); } else if (cc.getRightExpression() instanceof Constant) { - inMap.put(cc.getLeftExpression(), new TreeSet<Constant>(Arrays.asList((Constant)cc.getRightExpression()))); + inMap.put(cc.getLeftExpression(), new HashSet<Constant>(Arrays.asList((Constant)cc.getRightExpression()))); } continue; } @@ -134,7 +134,7 @@ continue; } SetCriteria sc = (SetCriteria)criteria; - TreeSet<Constant> values = new TreeSet<Constant>(); + HashSet<Constant> values = new HashSet<Constant>(); boolean allConstants = true; for (Expression exp : (Collection<Expression>)sc.getValues()) { if (exp instanceof Constant) { Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -86,6 +86,9 @@ public final class RuleMergeCriteria implements OptimizerRule { + public static final String UNNEST_DEFAULT = "org.teiid.subqueryUnnestDefault"; //$NON-NLS-1$ + private final boolean UNNEST = Boolean.getBoolean(UNNEST_DEFAULT); + /** * Used to replace correlated references */ @@ -104,6 +107,11 @@ Reference r = (Reference)element; Expression ex = refs.getMappedExpression(r.getExpression()); if (ex != null) { + if (ex instanceof ElementSymbol) { + ElementSymbol es = (ElementSymbol) ex.clone(); + es.setIsExternalReference(false); + ex = es; + } replacedAny = true; return ex; } @@ -122,6 +130,7 @@ public Criteria additionalCritieria; public Class<?> type; public boolean mergeJoin; + public boolean madeDistinct; } private IDGenerator idGenerator; @@ -348,11 +357,20 @@ if (crit instanceof SubquerySetCriteria) { //convert to the quantified form SubquerySetCriteria ssc = (SubquerySetCriteria)crit; + if (ssc.getSubqueryHint().isNoUnnest()) { + return result; + } result.not ^= ssc.isNegated(); result.type = crit.getClass(); - result.mergeJoin = ssc.isMergeJoin(); + result.mergeJoin = ssc.getSubqueryHint().isMergeJoin(); + if (!UNNEST && !result.mergeJoin) { + return result; + } crit = new SubqueryCompareCriteria(ssc.getExpression(), ssc.getCommand(), SubqueryCompareCriteria.EQ, SubqueryCompareCriteria.SOME); } else if (crit instanceof CompareCriteria) { + if (!UNNEST) { + return result; + } //convert to the quantified form CompareCriteria cc = (CompareCriteria)crit; if (cc.getRightExpression() instanceof ScalarSubquery) { @@ -369,7 +387,7 @@ } if (crit instanceof SubqueryCompareCriteria) { SubqueryCompareCriteria scc = (SubqueryCompareCriteria)crit; - + if (scc.getPredicateQuantifier() != SubqueryCompareCriteria.SOME //TODO: could add an inline view if not a query || !(scc.getCommand() instanceof Query)) { @@ -390,6 +408,9 @@ } if (crit instanceof ExistsCriteria) { ExistsCriteria exists = (ExistsCriteria)crit; + if (exists.getSubqueryHint().isNoUnnest()) { + return result; + } if (!(exists.getCommand() instanceof Query)) { return result; } @@ -397,7 +418,10 @@ result.not = exists.isNegated(); //the correlations can only be in where (if no group by or aggregates) or having result.query = (Query)exists.getCommand(); - result.mergeJoin = exists.isMergeJoin(); + result.mergeJoin = exists.getSubqueryHint().isMergeJoin(); + if (!UNNEST && !result.mergeJoin) { + return result; + } } return result; } @@ -495,6 +519,7 @@ return false; } plannedResult.query.getSelect().setDistinct(true); + plannedResult.madeDistinct = true; } if (addGroupBy) { Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RulePushSelectCriteria.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -33,7 +33,6 @@ import org.teiid.api.exception.query.QueryMetadataException; import org.teiid.api.exception.query.QueryPlannerException; import org.teiid.core.TeiidComponentException; -import org.teiid.core.util.Assertion; import org.teiid.query.QueryPlugin; import org.teiid.query.analysis.AnalysisRecord; import org.teiid.query.metadata.QueryMetadataInterface; @@ -391,8 +390,6 @@ boolean moveNodeAcrossFrame(PlanNode critNode, PlanNode sourceNode, QueryMetadataInterface metadata) throws QueryPlannerException { - Assertion.isNotNull(critNode.getParent()); - // Check that sourceNode has a child to push across if(sourceNode.getChildCount() == 0) { return false; Modified: trunk/engine/src/main/java/org/teiid/query/parser/SQLParserUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/parser/SQLParserUtil.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/java/org/teiid/query/parser/SQLParserUtil.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -38,8 +38,8 @@ import org.teiid.query.sql.lang.Option; import org.teiid.query.sql.lang.QueryCommand; import org.teiid.query.sql.lang.SetQuery; +import org.teiid.query.sql.lang.ExistsCriteria.SubqueryHint; import org.teiid.query.sql.proc.CriteriaSelector; -import org.teiid.query.sql.visitor.SQLStringVisitor; public class SQLParserUtil { @@ -167,14 +167,17 @@ } } - boolean isMergeJoin(Token t) { + SubqueryHint getSubqueryHint(Token t) { + SubqueryHint hint = new SubqueryHint(); String[] parts = getComment(t).split("\\s"); //$NON-NLS-1$ for (int i = 0; i < parts.length; i++) { - if (parts[i].equalsIgnoreCase(SQLStringVisitor.MJ)) { - return true; - } + if (parts[i].equalsIgnoreCase(SubqueryHint.MJ)) { + hint.setMergeJoin(true); + } else if (parts[i].equalsIgnoreCase(SubqueryHint.NOUNNEST)) { + hint.setNoUnnest(true); + } } - return false; + return hint; } String getComment(Token t) { Modified: trunk/engine/src/main/java/org/teiid/query/processor/relational/DependentValueSource.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/relational/DependentValueSource.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/java/org/teiid/query/processor/relational/DependentValueSource.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -26,16 +26,13 @@ import java.util.HashSet; import java.util.Map; import java.util.Set; -import java.util.TreeSet; import org.teiid.common.buffer.IndexedTupleSource; import org.teiid.common.buffer.TupleBuffer; import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; -import org.teiid.core.types.DataTypeManager; import org.teiid.core.util.Assertion; import org.teiid.query.sql.symbol.Expression; -import org.teiid.query.sql.symbol.SingleElementSymbol; import org.teiid.query.sql.util.ValueIterator; import org.teiid.query.sql.util.ValueIteratorSource; @@ -86,11 +83,7 @@ index = buffer.getSchema().indexOf(valueExpression); } Assertion.assertTrue(index != -1); - if (((SingleElementSymbol)buffer.getSchema().get(index)).getType() == DataTypeManager.DefaultDataClasses.BIG_DECIMAL) { - result = new TreeSet<Object>(); - } else { - result = new HashSet<Object>(); - } + result = new HashSet<Object>(); while (its.hasNext()) { Object value = its.nextTuple().get(index); if (value != null) { Modified: trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -40,7 +40,6 @@ import java.util.Map; import java.util.Set; import java.util.StringTokenizer; -import java.util.TreeSet; import org.teiid.api.exception.query.ExpressionEvaluationException; import org.teiid.api.exception.query.FunctionExecutionException; @@ -161,6 +160,7 @@ import org.teiid.query.sql.visitor.EvaluatableVisitor; import org.teiid.query.sql.visitor.ExpressionMappingVisitor; import org.teiid.query.sql.visitor.FunctionCollectorVisitor; +import org.teiid.query.sql.visitor.ValueIteratorProviderCollectorVisitor; import org.teiid.query.sql.visitor.EvaluatableVisitor.EvaluationLevel; import org.teiid.query.util.CommandContext; import org.teiid.query.validator.UpdateValidator.UpdateInfo; @@ -632,7 +632,7 @@ } } - if (from != null) { + if (from != null && !query.getIsXML()) { writeSubqueriesAsJoins(query); } @@ -707,9 +707,10 @@ //check for key preservation HashSet<GroupSymbol> keyPreservingGroups = new HashSet<GroupSymbol>(); ResolverUtil.findKeyPreserved(query, keyPreservingGroups, metadata); - if (!NewCalculateCostUtil.usesKey(plannedResult.leftExpressions, keyPreservingGroups, metadata, true)) { - //if not key perserved then the semi-join will not remain in tact - continue; + if (NewCalculateCostUtil.usesKey(plannedResult.leftExpressions, keyPreservingGroups, metadata, true) && plannedResult.madeDistinct) { + //if key perserved then the semi-join will remain in-tact without make the other query distinct + plannedResult.madeDistinct = false; + plannedResult.query.getSelect().setDistinct(false); } } crits.remove(); @@ -1156,18 +1157,18 @@ if (exists.shouldEvaluate() && processing) { return getCriteria(evaluator.evaluate(exists, null)); } - if (exists.getCommand().getProcessorPlan() == null && exists.getCommand() instanceof Query) { - Query query = (Query)exists.getCommand(); - if ((query.getLimit() == null || query.getOrderBy() == null) && query.getProjectedSymbols().size() > 1) { - query.getSelect().clearSymbols(); - query.getSelect().addSymbol(new ExpressionSymbol("x", new Constant(1))); //$NON-NLS-1$ - } - } rewriteSubqueryContainer((SubqueryContainer)criteria, true); if (!RelationalNodeUtil.shouldExecute(exists.getCommand(), false, true)) { return exists.isNegated()?TRUE_CRITERIA:FALSE_CRITERIA; } if (exists.getCommand().getProcessorPlan() == null) { + if (exists.getCommand() instanceof Query) { + Query query = (Query)exists.getCommand(); + if ((query.getLimit() == null || query.getOrderBy() == null) && query.getSelect().getProjectedSymbols().size() > 1) { + query.getSelect().clearSymbols(); + query.getSelect().addSymbol(new ExpressionSymbol("x", new Constant(1))); //$NON-NLS-1$ + } + } addImplicitLimit(exists, 1); } } else if (criteria instanceof SubquerySetCriteria) { @@ -1338,7 +1339,7 @@ } else { newCrits.remove(crit); CompareCriteria other = (CompareCriteria)crit; - SetCriteria sc = new SetCriteria(cc.getLeftExpression(), new TreeSet<Expression>()); + SetCriteria sc = new SetCriteria(cc.getLeftExpression(), new LinkedHashSet<Expression>()); sc.setAllConstants(true); sc.getValues().add(cc.getRightExpression()); sc.getValues().add(other.getRightExpression()); @@ -2234,7 +2235,7 @@ criteria.setValues(newVals); if (allConstants) { criteria.setAllConstants(true); - criteria.setValues(new TreeSet(newVals)); + criteria.setValues(new LinkedHashSet(newVals)); } if (size == 0) { Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/ExistsCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/lang/ExistsCriteria.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/java/org/teiid/query/sql/lang/ExistsCriteria.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -40,13 +40,57 @@ public class ExistsCriteria extends PredicateCriteria implements SubqueryContainer<QueryCommand>, ContextReference, Negatable { + public static class SubqueryHint { + public static String MJ = "MJ"; //$NON-NLS-1$ + public static String NOUNNEST = "NO_UNNEST"; //$NON-NLS-1$ + + private boolean mergeJoin; + private boolean noUnnest; + + public void setMergeJoin(boolean semiJoin) { + this.mergeJoin = semiJoin; + } + + public boolean isMergeJoin() { + return mergeJoin; + } + + public void setNoUnnest(boolean noUnnest) { + this.noUnnest = noUnnest; + } + + public boolean isNoUnnest() { + return noUnnest; + } + + @Override + public boolean equals(Object obj) { + if (this == obj) { + return true; + } + if (!(obj instanceof SubqueryHint)) { + return false; + } + SubqueryHint other = (SubqueryHint) obj; + return mergeJoin == other.mergeJoin && noUnnest == other.noUnnest; + } + + public SubqueryHint clone() { + SubqueryHint clone = new SubqueryHint(); + clone.mergeJoin = this.mergeJoin; + clone.noUnnest = this.noUnnest; + return clone; + } + + } + private static AtomicInteger ID = new AtomicInteger(); private QueryCommand command; private String id = "$ec/id" + ID.getAndIncrement(); //$NON-NLS-1$ private boolean shouldEvaluate; - private boolean mergeJoin; private boolean negated; + private SubqueryHint subqueryHint = new SubqueryHint(); /** * Default constructor @@ -55,14 +99,6 @@ super(); } - public void setMergeJoin(boolean semiJoin) { - this.mergeJoin = semiJoin; - } - - public boolean isMergeJoin() { - return mergeJoin; - } - public ExistsCriteria(QueryCommand subqueryCommand){ this.command = subqueryCommand; } @@ -127,9 +163,17 @@ return EquivalenceUtil.areEqual(getCommand(), other.getCommand()) && this.negated == other.negated && - this.mergeJoin == other.mergeJoin; + this.subqueryHint.equals(other.subqueryHint); } - + + public SubqueryHint getSubqueryHint() { + return subqueryHint; + } + + public void setSubqueryHint(SubqueryHint subqueryHint) { + this.subqueryHint = subqueryHint; + } + /** * Deep copy of object. The values Iterator of this object * will not be cloned - it will be null in the new object @@ -139,7 +183,7 @@ */ public Object clone() { ExistsCriteria ec = new ExistsCriteria((QueryCommand) this.command.clone()); - ec.setMergeJoin(this.mergeJoin); + ec.subqueryHint = this.subqueryHint.clone(); ec.setNegated(this.negated); return ec; } Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/SetCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/lang/SetCriteria.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/java/org/teiid/query/sql/lang/SetCriteria.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -24,7 +24,7 @@ import java.util.ArrayList; import java.util.Collection; -import java.util.TreeSet; +import java.util.LinkedHashSet; import org.teiid.core.util.EquivalenceUtil; import org.teiid.core.util.HashCodeUtil; @@ -152,7 +152,7 @@ Collection copyValues = null; if (isAllConstants()) { - copyValues = new TreeSet(values); + copyValues = new LinkedHashSet(values); } else { copyValues = LanguageObject.Util.deepClone(new ArrayList(values), Expression.class); } Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/SubquerySetCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/lang/SubquerySetCriteria.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/java/org/teiid/query/sql/lang/SubquerySetCriteria.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -27,6 +27,7 @@ import org.teiid.core.util.EquivalenceUtil; import org.teiid.core.util.HashCodeUtil; import org.teiid.query.sql.LanguageVisitor; +import org.teiid.query.sql.lang.ExistsCriteria.SubqueryHint; import org.teiid.query.sql.symbol.ContextReference; import org.teiid.query.sql.symbol.Expression; @@ -41,8 +42,8 @@ private static AtomicInteger ID = new AtomicInteger(); private QueryCommand command; + private SubqueryHint subqueryHint = new SubqueryHint(); private String id = "$ssc/id" + ID.getAndIncrement(); //$NON-NLS-1$ - private boolean mergeJoin; /** * Constructor for SubquerySetCriteria. @@ -56,12 +57,12 @@ setCommand(subCommand); } - public boolean isMergeJoin() { - return mergeJoin; + public SubqueryHint getSubqueryHint() { + return subqueryHint; } - public void setMergeJoin(boolean semiJoin) { - this.mergeJoin = semiJoin; + public void setSubqueryHint(SubqueryHint subqueryHint) { + this.subqueryHint = subqueryHint; } @Override @@ -127,7 +128,7 @@ return this.isNegated() == sc.isNegated() && EquivalenceUtil.areEqual(getExpression(), sc.getExpression()) && EquivalenceUtil.areEqual(getCommand(), sc.getCommand()) && - this.mergeJoin == sc.mergeJoin; + this.subqueryHint.equals(sc.getSubqueryHint()); } /** @@ -149,7 +150,7 @@ SubquerySetCriteria criteriaCopy = new SubquerySetCriteria(copy, copyCommand); criteriaCopy.setNegated(isNegated()); - criteriaCopy.mergeJoin = this.mergeJoin; + criteriaCopy.subqueryHint = this.subqueryHint.clone(); return criteriaCopy; } Modified: trunk/engine/src/main/java/org/teiid/query/sql/visitor/EvaluatableVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/visitor/EvaluatableVisitor.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/java/org/teiid/query/sql/visitor/EvaluatableVisitor.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -27,8 +27,8 @@ import java.util.TreeSet; +import org.teiid.metadata.FunctionMethod.Determinism; import org.teiid.metadata.FunctionMethod.PushDown; -import org.teiid.metadata.FunctionMethod.Determinism; import org.teiid.query.function.FunctionLibrary; import org.teiid.query.metadata.TempMetadataID; import org.teiid.query.sql.LanguageObject; Modified: trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -84,6 +84,7 @@ import org.teiid.query.sql.lang.Update; import org.teiid.query.sql.lang.WithQueryCommand; import org.teiid.query.sql.lang.XMLTable; +import org.teiid.query.sql.lang.ExistsCriteria.SubqueryHint; import org.teiid.query.sql.lang.TableFunctionReference.ProjectedColumn; import org.teiid.query.sql.lang.TextTable.TextColumn; import org.teiid.query.sql.lang.XMLTable.XMLColumn; @@ -144,10 +145,8 @@ private static final String BEGIN_HINT = "/*+"; //$NON-NLS-1$ private static final String END_HINT = "*/"; //$NON-NLS-1$ private static final char ID_ESCAPE_CHAR = '\"'; - public static String MJ = "MJ"; //$NON-NLS-1$ + protected StringBuilder parts = new StringBuilder(); - protected StringBuilder parts = new StringBuilder(); - /** * Helper to quickly get the parser string for an object using the visitor. * @@ -1067,14 +1066,7 @@ append(SPACE); } append(IN); - if (obj.isMergeJoin()) { - append(SPACE); - append(BEGIN_HINT); - append(SPACE); - append(MJ); - append(SPACE); - append(END_HINT); - } + addSubqueryHint(obj.getSubqueryHint()); append(" ("); //$NON-NLS-1$ visitNode(obj.getCommand()); append(")"); //$NON-NLS-1$ @@ -1560,17 +1552,28 @@ append(SPACE); } append(EXISTS); - if (obj.isMergeJoin()) { + addSubqueryHint(obj.getSubqueryHint()); + append(" ("); //$NON-NLS-1$ + visitNode(obj.getCommand()); + append(")"); //$NON-NLS-1$ + } + + public void addSubqueryHint(SubqueryHint hint) { + if (hint.isNoUnnest()) { + append(SPACE); + append(BEGIN_HINT); append(SPACE); + append(SubqueryHint.NOUNNEST); + append(SPACE); + append(END_HINT); + } else if (hint.isMergeJoin()) { + append(SPACE); append(BEGIN_HINT); append(SPACE); - append(MJ); + append(SubqueryHint.MJ); append(SPACE); append(END_HINT); } - append(" ("); //$NON-NLS-1$ - visitNode(obj.getCommand()); - append(")"); //$NON-NLS-1$ } public void visit( SubqueryCompareCriteria obj ) { Modified: trunk/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/java/org/teiid/query/tempdata/IndexInfo.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -23,11 +23,11 @@ package org.teiid.query.tempdata; import java.util.ArrayList; +import java.util.Collection; import java.util.Iterator; import java.util.LinkedList; import java.util.List; import java.util.ListIterator; -import java.util.TreeSet; import org.teiid.common.buffer.TupleBrowser; import org.teiid.common.buffer.TupleSource; @@ -151,7 +151,7 @@ if (!setCriteria.getExpression().equals(keyColumn) || !setCriteria.isAllConstants()) { continue; } - TreeSet<Constant> values = (TreeSet<Constant>) setCriteria.getValues(); + Collection<Constant> values = (Collection<Constant>) setCriteria.getValues(); this.addSet(i, values); } } @@ -197,7 +197,7 @@ } } - void addSet(int i, TreeSet<Constant> values) { + void addSet(int i, Collection<Constant> values) { if (!valueSet.isEmpty()) { return; } Modified: trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj =================================================================== --- trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2011-03-20 13:39:50 UTC (rev 3011) @@ -2707,7 +2707,7 @@ ) <RPAREN> { - return new Object[] {subquery, isMergeJoin(lparen)}; + return new Object[] {subquery, getSubqueryHint(lparen)}; } } @@ -2877,7 +2877,7 @@ { if (command != null) { SubquerySetCriteria ssc = new SubquerySetCriteria(expression, (QueryCommand)command[0]); - ssc.setMergeJoin((Boolean)command[1]); + ssc.setSubqueryHint((ExistsCriteria.SubqueryHint)command[1]); criteria = ssc; } else { criteria = new SetCriteria(expression, setList); @@ -2902,7 +2902,7 @@ { ExistsCriteria existsCrit = new ExistsCriteria((QueryCommand)subquery[0]); - existsCrit.setMergeJoin((Boolean)subquery[1]); + existsCrit.setSubqueryHint((ExistsCriteria.SubqueryHint)subquery[1]); return existsCrit; } } Modified: trunk/engine/src/test/java/org/teiid/query/function/TestFunction.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/function/TestFunction.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/test/java/org/teiid/query/function/TestFunction.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -34,8 +34,8 @@ import org.junit.Test; import org.teiid.api.exception.query.FunctionExecutionException; +import org.teiid.core.types.TeiidBigDecimal; import org.teiid.language.SQLConstants.NonReserved; -import org.teiid.query.function.FunctionMethods; import org.teiid.query.sql.symbol.Constant; import org.teiid.query.unittest.TimestampUtil; import org.teiid.query.util.CommandContext; @@ -471,7 +471,7 @@ } @Test public void testConvertBigDecimalBigInteger() throws Exception { - helpConvert(new BigDecimal("1.0"), "biginteger", new BigInteger("1")); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + helpConvert(new TeiidBigDecimal("1.0"), "biginteger", new BigInteger("1")); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ } @Test public void testConvertDoubleLong() throws Exception { Modified: trunk/engine/src/test/java/org/teiid/query/function/TestFunctionLibrary.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/function/TestFunctionLibrary.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/test/java/org/teiid/query/function/TestFunctionLibrary.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -68,7 +68,7 @@ private static final Class<String> T_STRING = DataTypeManager.DefaultDataClasses.STRING; private static final Class<Integer> T_INTEGER = DataTypeManager.DefaultDataClasses.INTEGER; private static final Class<BigInteger> T_BIG_INTEGER = DataTypeManager.DefaultDataClasses.BIG_INTEGER; - private static final Class<BigDecimal> T_BIG_DECIMAL = DataTypeManager.DefaultDataClasses.BIG_DECIMAL; + private static final Class<? extends BigDecimal> T_BIG_DECIMAL = DataTypeManager.DefaultDataClasses.BIG_DECIMAL; private static final Class<Long> T_LONG = DataTypeManager.DefaultDataClasses.LONG; private static final Class<Float> T_FLOAT = DataTypeManager.DefaultDataClasses.FLOAT; private static final Class<Double> T_DOUBLE = DataTypeManager.DefaultDataClasses.DOUBLE; Modified: trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/test/java/org/teiid/query/optimizer/TestSubqueryPushdown.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -24,7 +24,7 @@ import static org.teiid.query.optimizer.TestOptimizer.*; -import org.junit.Ignore; +import org.junit.After; import org.junit.Test; import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; @@ -35,6 +35,7 @@ import org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder; import org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder; import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability; +import org.teiid.query.optimizer.relational.rules.RuleMergeCriteria; import org.teiid.query.processor.ProcessorPlan; import org.teiid.query.rewriter.TestQueryRewriter; import org.teiid.query.unittest.FakeMetadataFacade; @@ -395,18 +396,18 @@ ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select max(e1) FROM pm1.g2)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ null, capFinder, - new String[] { "SELECT e1 AS c_0 FROM pm1.g1 ORDER BY c_0" }, SHOULD_SUCCEED); //$NON-NLS-1$ + new String[] { "SELECT e1 FROM pm1.g1" }, SHOULD_SUCCEED); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { 1, // Access 0, // DependentAccess - 0, // DependentSelect + 1, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy - 1, // MergeJoinStrategy + 0, // MergeJoinStrategy 0, // Null - 1, // PlanExecution + 0, // PlanExecution 1, // Project 0, // Select 0, // Sort @@ -779,11 +780,11 @@ } @Test public void testSubqueryRewriteToJoin1() throws Exception { - TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e1 in (select pm1.g1.e1 as x FROM pm1.g1)", "SELECT e1 FROM pm3.g1, (SELECT pm1.g1.e1 AS x FROM pm1.g1) AS X__1 WHERE pm3.g1.e1 = X__1.x", FakeMetadataFactory.example4()); + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e1 in /*+ mj */ (select pm1.g1.e1 as x FROM pm1.g1)", "SELECT e1 FROM pm3.g1, (SELECT pm1.g1.e1 AS x FROM pm1.g1) AS X__1 WHERE pm3.g1.e1 = X__1.x", FakeMetadataFactory.example4()); } @Test public void testSubqueryRewriteToJoin2() throws Exception { - TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e1 in (select pm1.g1.e1 || 1 FROM pm1.g1)", "SELECT e1 FROM pm3.g1, (SELECT DISTINCT concat(pm1.g1.e1, '1') AS EXPR FROM pm1.g1) AS X__1 WHERE pm3.g1.e1 = X__1.EXPR", FakeMetadataFactory.example4()); + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e1 in /*+ mj */ (select pm1.g1.e1 || 1 FROM pm1.g1)", "SELECT e1 FROM pm3.g1, (SELECT concat(pm1.g1.e1, '1') AS EXPR FROM pm1.g1) AS X__1 WHERE pm3.g1.e1 = X__1.EXPR", FakeMetadataFactory.example4()); } /** @@ -794,10 +795,16 @@ } @Test public void testSubqueryRewriteToJoinWithOtherCriteria() throws Exception { - TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e1 in (select pm1.g1.e1 FROM pm1.g1 where e2 < pm3.g1.e2)", "SELECT e1 FROM pm3.g1, (SELECT pm1.g1.e1, e2 FROM pm1.g1) AS X__1 WHERE (X__1.e2 < pm3.g1.e2) AND (pm3.g1.e1 = X__1.e1)", FakeMetadataFactory.example4()); + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e1 in /*+ mj */ (select pm1.g1.e1 FROM pm1.g1 where e2 < pm3.g1.e2)", "SELECT e1 FROM pm3.g1, (SELECT pm1.g1.e1, e2 FROM pm1.g1) AS X__1 WHERE (X__1.e2 < pm3.g1.e2) AND (pm3.g1.e1 = X__1.e1)", FakeMetadataFactory.example4()); } + + @Test public void testDontRewriteToJoinWithOtherCriteria() throws Exception { + System.setProperty(RuleMergeCriteria.UNNEST_DEFAULT, Boolean.TRUE.toString()); + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e1 in /*+ NO_UNNEST */ (select pm1.g1.e1 FROM pm1.g1 where e2 < pm3.g1.e2)", "SELECT e1 FROM pm3.g1 WHERE pm3.g1.e1 IN /*+ NO_UNNEST */ (SELECT pm1.g1.e1 FROM pm1.g1 WHERE e2 < pm3.g1.e2)", FakeMetadataFactory.example4()); + } @Test public void testSubqueryRewriteToJoinWithAggregate() throws Exception { + System.setProperty(RuleMergeCriteria.UNNEST_DEFAULT, Boolean.TRUE.toString()); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 < (select max(e2) FROM pm1.g1 where pm3.g1.e1 = e1)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS MAX, e1 FROM pm1.g1 GROUP BY e1) AS X__1 WHERE (pm3.g1.e2 < X__1.MAX) AND (pm3.g1.e1 = X__1.e1)", FakeMetadataFactory.example4()); } @@ -810,6 +817,7 @@ } @Test public void testSubqueryRewriteToJoinWithAggregate2() throws Exception { + System.setProperty(RuleMergeCriteria.UNNEST_DEFAULT, Boolean.TRUE.toString()); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 < (select max(e2) FROM pm1.g1 WHERE pm3.g1.e1 = e1 HAVING min(e3) < pm3.g1.e3)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS MAX, e1, MIN(e3) AS MIN FROM pm1.g1 GROUP BY e1) AS X__1 WHERE (X__1.MIN < pm3.g1.e3) AND (pm3.g1.e2 < X__1.MAX) AND (pm3.g1.e1 = X__1.e1)", FakeMetadataFactory.example4()); } @@ -830,14 +838,15 @@ } @Test public void testSubqueryExpressionJoin() throws Exception { + System.setProperty(RuleMergeCriteria.UNNEST_DEFAULT, Boolean.TRUE.toString()); TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 < (Select max(e2) from pm2.g2 where e1 = pm3.g1.e1 having convert(min(e2), string) > pm3.g1.e1)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS MAX, e1, MIN(e2) AS MIN FROM pm2.g2 GROUP BY e1) AS X__1 WHERE (convert(X__1.MIN, string) > pm3.g1.e1) AND (pm3.g1.e2 < X__1.MAX) AND (pm3.g1.e1 = X__1.e1)", FakeMetadataFactory.example4()); } /** - * Must be handled as a semi-join + * Must be handled as a semi-join, rather than a regular join */ @Test public void testSemiJoin() { - ProcessorPlan plan = helpPlan("Select e1 from pm2.g2 where e2 in (select count(e2) FROM pm1.g2 group by e1 having e1 < pm2.g2.e3)", FakeMetadataFactory.example4(), //$NON-NLS-1$ + ProcessorPlan plan = helpPlan("Select e1 from pm2.g2 where e2 in /*+ mj */ (select count(e2) FROM pm1.g2 group by e1 having e1 < pm2.g2.e3)", FakeMetadataFactory.example4(), //$NON-NLS-1$ new String[] { "SELECT g_0.e2 AS c_0, g_0.e3 AS c_1, g_0.e1 AS c_2 FROM pm2.g2 AS g_0 ORDER BY c_0" }); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { 1, // Access @@ -854,14 +863,15 @@ 0, // Select 0, // Sort 0 // UnionAll - }); + }); + checkJoinCounts(plan, 1, 0); } /** - * This will not plan as a semi-join since the cost seems too high + * This will not plan as a anti semi-join since the cost seems too high */ - @Test public void testNonSemiJoinExistsCosting() { - ProcessorPlan plan = helpPlan("Select e1 from pm1.g2 as o where exists (select 1 from pm3.g1 where e1 = o.e1 having o.e2 = count(e2))", FakeMetadataFactory.example4(), //$NON-NLS-1$ + @Test public void testNoAntiSemiJoinExistsCosting() { + ProcessorPlan plan = helpPlan("Select e1 from pm1.g2 as o where not exists (select 1 from pm3.g1 where e1 = o.e1 having o.e2 = count(e2))", FakeMetadataFactory.example4(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1, g_0.e2 FROM pm1.g2 AS g_0" }); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { 1, // Access @@ -885,7 +895,7 @@ * Same as above, but the source is much larger, so a semi-join is favorable */ @Test public void testSemiJoinExistsCosting() { - ProcessorPlan plan = helpPlan("Select e1 from pm2.g2 as o where exists (select 1 from pm3.g1 where e1 = o.e1 having o.e2 = count(e2))", FakeMetadataFactory.example4(), //$NON-NLS-1$ + ProcessorPlan plan = helpPlan("Select e1 from pm2.g2 as o where not exists (select 1 from pm3.g1 where e1 = o.e1 having o.e2 = count(e2))", FakeMetadataFactory.example4(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm2.g2 AS g_0 ORDER BY c_0, c_1" }); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { 1, // Access @@ -903,7 +913,7 @@ 0, // Sort 0 // UnionAll }); - checkJoinCounts(plan, 1, 0); + checkJoinCounts(plan, 0, 1); } @Test public void testAntiSemiJoinExistsHint() { @@ -928,8 +938,12 @@ checkJoinCounts(plan, 0, 1); } - @Test public void testSemiJoinInHint() { - ProcessorPlan plan = helpPlan("Select e1 from pm1.g2 as o where e2 IN /*+ MJ */ (select count(e2) from pm3.g1 where e1 = o.e1)", FakeMetadataFactory.example4(), //$NON-NLS-1$ + @After public void tearDown() { + System.setProperty(RuleMergeCriteria.UNNEST_DEFAULT, Boolean.FALSE.toString()); + } + + @Test public void testAntiSemiJoinInHint() { + ProcessorPlan plan = helpPlan("Select e1 from pm1.g2 as o where e2 NOT IN /*+ MJ */ (select count(e2) from pm3.g1 where e1 = o.e1)", FakeMetadataFactory.example4(), //$NON-NLS-1$ new String[] { "SELECT g_0.e2 AS c_0, g_0.e1 AS c_1 FROM pm1.g2 AS g_0 ORDER BY c_1, c_0" }); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { 1, // Access @@ -947,7 +961,7 @@ 0, // Sort 0 // UnionAll }); - checkJoinCounts(plan, 1, 0); + checkJoinCounts(plan, 0, 1); } void checkJoinCounts(ProcessorPlan plan, int semi, int antiSemi) { @@ -987,7 +1001,7 @@ } @Test public void testInvalidGeneratedSemijoinQuery1() throws Exception { - TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 = (Select max(e2) from pm2.g2 where e1 = pm3.g1.e1)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS MAX, e1 FROM pm2.g2 GROUP BY e1) AS X__1 WHERE (pm3.g1.e1 = X__1.e1) AND (pm3.g1.e2 = X__1.MAX)", FakeMetadataFactory.example4()); + TestQueryRewriter.helpTestRewriteCommand("Select e1 from pm3.g1 where pm3.g1.e2 IN /*+ mj */ (Select max(e2) from pm2.g2 where e1 = pm3.g1.e1)", "SELECT e1 FROM pm3.g1, (SELECT MAX(e2) AS MAX, e1 FROM pm2.g2 GROUP BY e1) AS X__1 WHERE (pm3.g1.e1 = X__1.e1) AND (pm3.g1.e2 = X__1.MAX)", FakeMetadataFactory.example4()); } @Test public void testCompareSubquery2() throws Exception { @@ -996,19 +1010,18 @@ checkNodeTypes(plan, FULL_PUSHDOWN); } - @Ignore @Test public void testUncorrelatedSet() { - ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select e1 FROM pm2.g1)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ - new String[] { "SELECT e1, pm1.g1.e2 FROM pm1.g1" }); //$NON-NLS-1$ + ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in /*+ mj */ (select e1 FROM pm2.g1)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$ + new String[] { "SELECT DISTINCT g_0.e1 FROM pm2.g1 AS g_0", "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0" }); //$NON-NLS-1$ checkNodeTypes(plan, new int[] { - 1, // Access + 2, // Access 0, // DependentAccess - 1, // DependentSelect + 0, // DependentSelect 0, // DependentProject 0, // DupRemove 0, // Grouping 0, // NestedLoopJoinStrategy - 0, // MergeJoinStrategy + 1, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project @@ -1016,6 +1029,7 @@ 0, // Sort 0 // UnionAll }); + checkJoinCounts(plan, 0, 0); } } Modified: trunk/engine/src/test/java/org/teiid/query/parser/TestOptionsAndHints.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/parser/TestOptionsAndHints.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/test/java/org/teiid/query/parser/TestOptionsAndHints.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -1144,13 +1144,13 @@ TestParser.helpTest(sql, "/*+ cache */ ? = EXEC proc()", sp); //$NON-NLS-1$ } - @Test public void testSemiJoinHint() { + @Test public void testMergeJoinHint() { String sql = "SELECT e1 FROM m.g2 WHERE EXISTS /*+ MJ */ (SELECT e1 FROM m.g1)"; //$NON-NLS-1$ Query q = TestParser.exampleExists(true); TestParser.helpTest(sql, "SELECT e1 FROM m.g2 WHERE EXISTS /*+ MJ */ (SELECT e1 FROM m.g1)", q); //$NON-NLS-1$ } - @Test public void testSemiJoinHint1() { + @Test public void testMergeJoinHint1() { String sql = "SELECT a FROM db.g WHERE b IN /*+ MJ */ (SELECT a FROM db.g WHERE a2 = 5)"; //$NON-NLS-1$ Query q = TestParser.exampleIn(true); TestParser.helpTest(sql, "SELECT a FROM db.g WHERE b IN /*+ MJ */ (SELECT a FROM db.g WHERE a2 = 5)", q); //$NON-NLS-1$ Modified: trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -4980,7 +4980,7 @@ query.setFrom(from); query.setCriteria(criteria); SubquerySetCriteria subCrit = new SubquerySetCriteria(expr, query); - subCrit.setMergeJoin(semiJoin); + subCrit.getSubqueryHint().setMergeJoin(semiJoin); Query outer = new Query(); outer.setSelect(select); outer.setFrom(from); @@ -5735,7 +5735,7 @@ From f2 = new From(); f2.addGroup(new GroupSymbol("m.g2")); //$NON-NLS-1$ ExistsCriteria existsCrit = new ExistsCriteria(q1); - existsCrit.setMergeJoin(semiJoin); + existsCrit.getSubqueryHint().setMergeJoin(semiJoin); Query q2 = new Query(); q2.setSelect(s2); q2.setFrom(f2); Modified: trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2011-03-19 14:32:44 UTC (rev 3010) +++ trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2011-03-20 13:39:50 UTC (rev 3011) @@ -1061,7 +1061,7 @@ String rewritProc = "CREATE PROCEDURE\n"; //$NON-NLS-1$ rewritProc = rewritProc + "BEGIN\n"; //$NON-NLS-1$ rewritProc = rewritProc + "DECLARE String var1;\n"; //$NON-NLS-1$ - rewritProc = rewritProc + "IF(var1 IN ('x', 'y'))\n"; //$NON-NLS-1$ + rewritProc = rewritProc + "IF(var1 IN ('y', 'x'))\n"; //$NON-NLS-1$ rewritProc = rewritProc + "BEGIN\n"; //$NON-NLS-1$ rewritProc = rewritProc + "SELECT pm1.g1.e2, null, FALSE, TRUE FROM pm1.g1;\n"; //$NON-NLS-1$ rewritProc = rewritProc + "END\n"; //$NON-NLS-1$ @@ -1250,7 +1250,7 @@ String rewritProc = "CREATE PROCEDURE\n"; //$NON-NLS-1$ rewritProc = rewritProc + "BEGIN\n"; //$NON-NLS-1$ rewritProc = rewritProc + "DECLARE integer var1;\n"; //$NON-NLS-1$ - rewritProc = rewritProc + "UPDATE pm1.g1 SET e1 = 'x' WHERE CONCAT(e1, 'm') IN ('1', '2');\n"; //$NON-NLS-1$ + rewritProc = rewritProc + "UPDATE pm1.g1 SET e1 = 'x' WHERE CONCAT(e1, 'm') IN ('2', '1');\n"; //$NON-NLS-1$ rewritProc = rewritProc + "END"; //$NON-NLS-1$ String procReturned = this.getRewritenProcedure(procedure, userQuery,