[teiid-commits] teiid SVN: r3710 - in trunk: common-core/src/main/java/org/teiid/core/types and 19 other directories.

Author: shawkins Date: 2011-11-30 17:17:23 -0500 (Wed, 30 Nov 2011) New Revision: 3710 Modified: 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/FloatingNumberToBigIntegerTransform.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java trunk/documentation/admin-guide/src/main/docbook/en-US/content/appendix-c.xml trunk/engine/src/main/java/org/teiid/query/function/FunctionLibrary.java trunk/engine/src/main/java/org/teiid/query/function/FunctionMethods.java trunk/engine/src/main/java/org/teiid/query/function/aggregate/Avg.java trunk/engine/src/main/java/org/teiid/query/function/aggregate/Sum.java trunk/engine/src/main/java/org/teiid/query/parser/SQLParserUtil.java trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverUtil.java trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java trunk/engine/src/main/java/org/teiid/query/sql/symbol/AggregateSymbol.java trunk/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj trunk/engine/src/main/resources/org/teiid/query/i18n.properties trunk/engine/src/test/java/org/teiid/query/function/TestFunctionLibrary.java trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java trunk/engine/src/test/java/org/teiid/query/processor/relational/TestGroupingNode.java trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java Log: TEIID-1600 bringing decimal parsing and exact numeric AVG in line with the spec Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-11-30 22:17:23 UTC (rev 3710) @@ -35,10 +35,16 @@ <li>Support for using the FROM clause post item hints MAKEDEP/MAKENOTDEP has been deprecated. Use the pre item comment hint syntax instead, e.g. /*+ MAKEDEP */ tbl </ul> -<h4>from 7.6</h4> +<h4>from 7.x</h4> <ul> <li>TRANSLATE/HAS CRITERIA has been removed. INSTEAD OF trigger actions should be used instead. ROWS_UPDATED, INPUTS, and INPUT are no longer procedure reserved words. - <li>7.6 or earlier VDBs with table cardinalities set the old unknown value 0 will now report that value as -1 via the metadata API or SYS.TABLES. + <li>7.x or earlier VDBs with table cardinalities set the old unknown value 0 will now report that value as -1 via the metadata API or SYS.TABLES. + <li>Exact fixed point literals, e.g. 1.0, are now parsed as decimal/BigDecimal values as per the ANSI specification, rather than as double values. + Also the AVG aggregate function will return an exact numeric (BigDecimal) value for integral types rather than returning a double value. + Views that were projecting doubles from exact numeric literals or using AVG will need to be updated. There is also a new system property org.teiid.parseDecimalAsDouble to + enable the pre-8.0 parsing of exact fixed point literals as doubles. The BigDecimal form of the AVG function now uses the same precision and scale logic as the division system function, rather than using a + fixed scale of 9. + <li>BigDecimal division that returns a quotient that is equal to zero will have a scale of zero as well. Prior releases would typically return a zero value with a scale of 16. </ul> <h4>from 7.5</h4> @@ -91,27 +97,6 @@ <ul> <li>The term data policy was replaced with data role. The AdminAPI methods for adding role mappings have changed from addRoleToDataPolicy and removeRoleFromDataPolicy to addDataRoleMapping and removeDataRoleMapping respectively. </ul> -<h4>from 6.2</h4> -<ul> - <li>The connector API has changed substantially. Custom connectors need to be retargeted to the new Translator API. See <a href="https://jira.jboss.org/browse/TEIID-1003">TEIID-1003&l... and the <a href="teiid-docs/teiid_developers_guide.pdf">Developer's Guide</a> for more information. - <li>Connector bindings are no longer valid. Use the Designer Tooling or see the AdminShell download for a migration utility. - <li>Calling out to web services and xml processing has been dramatically simplified and improved, but is a breaking change from prior releases. - XML/Relational processing has been replaced by XMLTABLE, retrieving XML from file has been replaced by the File Translator/Resource Adapter, and - web service calls have been replaced by the WS Translator/Resource Adapter. See <a href="https://jira.jboss.org/browse/TEIID-1118">TEIID-1118&l...;, <a href="https://jira.jboss.org/browse/TEIID-1114">TEIID-1114&l...;, and the <a href="teiid-docs/teiid_reference.pdf">Reference</a> guide for more information. - <li>The direct usage of XQuery has been replaced with the XMLQuery function. - <li>The text connector file processing has been replaced with the TEXTTABLE function. See <a href="https://jira.jboss.org/browse/TEIID-1102">TEIID-1102&l... and the <a href="teiid-docs/teiid_reference.pdf">Reference</a> guide for more information - <li>Double quoted values are now parsed as identifiers by default. See <a href="https://jira.jboss.org/browse/TEIID-145">TEIID-145<... and the <a href="teiid-docs/teiid_reference.pdf">Reference</a> guide for more information. - <li>The system information schema has changed from System to SYS. The tables and table structures have also changed. See <a href="https://jira.jboss.org/browse/TEIID-871">TEIID-871<... and the <a href="teiid-docs/teiid_reference.pdf">Reference</a> guide for more information. - <li>Client control over query plans has changed. OPTION PLANONLY, DEBUG, and SHOWPLAN are no longer allowed. This behavior should not be controlled by the SET statement, SET SHOWPLAN (ON|OFF|DEBUG) and SET NOEXEC (ON|OFF). Usage of the client PlanNode class will also need to be updated. - <li>Teiid specific JDBC client interfaces and methods have changed. In general these custom features have mostly been moved to other more standard locations or been removed. See <a href="https://jira.jboss.org/browse/TEIID-1020">TEIID-1020&l... and the <a href="teiid-docs/teiid_client_developers_guide.pdf">Client Developers</a> guide for more information. - <li>Teiid's reserved words have changed. Many of the Teiid specific reserved words have become non-reserved words. All of SQL Foundation and SQL/MED 2003, and SQL/XML from SQL 2006 reserved words have been added as reserved words. It is generally required to double quote all reserved words. It is advisable to double quote all non-reserved words as well. The use of the INPUT qualifier for procedure inputs has been deprecated in favor of INPUTS. The use of the USING qualifier for dynamic SQL inputs has been deprecated in favor of DVARS. - <li>The AdminApi has been redesigned for use with the new Translator/JCA split and for deployment in JBoss AS. - <li>The AdminShell is based on Groovy. Most BeanShell syntax is also valid in Groovy, but there are extensive changes in the AdminAPI that may necessitate rewriting scripts. - <li>OPTION NO CACHE &lt;table list&gt; will now only by-pass cache for the given tables at their immediate view layer. Previously all caches would have transitively been skipped. See <a href="https://jira.jboss.org/browse/TEIID-900">TEIID-900<... for more information. - <li>Nan and +-Infinity are no longer allowed as numerical results by default. This may be changed via the system property org.teiid.allowNanInfinity. - <li>The scale of BigDecimal values is now adjusted to allow for more intuitive results. Conversion from floating numeric types will preserve the approximate scale and division results will allow for additional scale. See <a href="https://jira.jboss.org/browse/TEIID-159">TEIID-159<... for more information. - <li>The 7.0 client is not compatible with 6.x or vice versa. It is expected that 7.0 clients will be compatible with 7.x releases for standard JDBC operations. -</ul> <h2><a name="Configuration">Configuration Issues</a></h2> @@ -169,13 +154,6 @@ <li>Code table relate configuration properties have been removed. Code tables are now implemented as materialized views. </ul> -<h4>from 6.2</h4> -<ul> - <li>A JBoss AS instance is now required. Embedded mode is also currently not supported.</li> - <li>Engine configuration is now handled through the admin console or deploy/teiid/teiid-jboss-beans.xml - <li>Logging is now done through the container's log4j instance. Any Teiid specific logging changes, see the conf/jboss-teiid-log4j.xml, need to be incorporated into the master jboss-log4j.xml. -</ul> - <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). @@ -190,12 +168,6 @@ <ul> <li>Fixed xsd type handling for SQL/XML and XML document models. xsd:date, xsd:dateTime, and xsd:time types will now all be displayed using the GMT timezone (Z). SQL types, such as timestamp, used for an XMLTABLE column will now expect their values to be in the form of the corresponding xsd type. </ul> -<h4>From 6.2</h4> -<ul> - <li>Start time is now significantly longer due to the container deployment. However many operations can be performed on configruation files that will trigger a redeployment without the need for a restart. - <li>Inversion of parse, format, and convert system functions is not used if it can be narrowing. In situations where a predicate has the form: parseTime(column, 'format') = {t 'time value'} may lead to reduced performance, since - the parseTime function cannot be pushed down. This should be addressed in future releases. -</ul> <h2><a name="LibraryUpdates">Thirdparty Library Updates</a></h2> @@ -217,13 +189,6 @@ <li>Netty was upgraded to 3.2.0 <li>JDOM was removed. </ul> -<h4>From 6.2</h4> -<ul> - <li>Netty was upgraded to 3.1.5 - <li>Saxon was upgraded to 9.1.0.8 - <li>A 5.1 container's version of Arjuna is newer than what was previously included in Teiid 6.x. - <li>Various other dependencies have been removed as they are no longer needed or now provided by the container. -</ul> <h2><a name="Details">Detailed Release Notes</a></h2> Detailed Release Notes - Teiid - Version ${project.version} 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-11-30 19:08:23 UTC (rev 3709) +++ trunk/common-core/src/main/java/org/teiid/core/types/DataTypeManager.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -41,22 +41,7 @@ import java.util.Set; import org.teiid.core.CorePlugin; -import org.teiid.core.types.basic.AnyToObjectTransform; -import org.teiid.core.types.basic.AnyToStringTransform; -import org.teiid.core.types.basic.BooleanToNumberTransform; -import org.teiid.core.types.basic.FixedNumberToBigDecimalTransform; -import org.teiid.core.types.basic.FixedNumberToBigIntegerTransform; -import org.teiid.core.types.basic.FloatingNumberToBigDecimalTransform; -import org.teiid.core.types.basic.FloatingNumberToBigIntegerTransform; -import org.teiid.core.types.basic.NullToAnyTransform; -import org.teiid.core.types.basic.NumberToBooleanTransform; -import org.teiid.core.types.basic.NumberToByteTransform; -import org.teiid.core.types.basic.NumberToDoubleTransform; -import org.teiid.core.types.basic.NumberToFloatTransform; -import org.teiid.core.types.basic.NumberToIntegerTransform; -import org.teiid.core.types.basic.NumberToLongTransform; -import org.teiid.core.types.basic.NumberToShortTransform; -import org.teiid.core.types.basic.ObjectToAnyTransform; +import org.teiid.core.types.basic.*; import org.teiid.core.util.ArgCheck; import org.teiid.core.util.HashCodeUtil; import org.teiid.core.util.PropertiesUtils; @@ -336,7 +321,7 @@ * @param dataType * New data type defined by Class */ - static void addDataType(String typeName, Class dataType) { + static void addDataType(String typeName, Class<?> dataType) { dataTypeNames.put(typeName, dataType); dataTypeClasses.put(dataType, typeName); } @@ -361,13 +346,13 @@ * Data type name * @return Data type class */ - public static Class getDataTypeClass(String name) { + public static Class<?> getDataTypeClass(String name) { if (name == null) { return DefaultDataClasses.NULL; } // Hope this is the correct case (as it will be if using the constants - Class dataTypeClass = dataTypeNames.get(name); + Class<?> dataTypeClass = dataTypeNames.get(name); // If that fails, do a lower case to make sure we match if (dataTypeClass == null) { @@ -380,7 +365,7 @@ return dataTypeClass; } - public static String getDataTypeName(Class typeClass) { + public static String getDataTypeName(Class<?> typeClass) { if (typeClass == null) { return DefaultDataTypes.NULL; } @@ -424,7 +409,7 @@ * Outgoing value type * @return A transform if one exists, null otherwise */ - public static Transform getTransform(Class sourceType, Class targetType) { + public static Transform getTransform(Class<?> sourceType, Class<?> targetType) { if (sourceType == null || targetType == null) { throw new IllegalArgumentException(CorePlugin.Util.getString( "ERR.003.029.0002", sourceType, targetType)); //$NON-NLS-1$ @@ -464,14 +449,8 @@ * Outgoing value type * @return True if a transform exists */ - public static boolean isTransformable(Class sourceType, Class targetType) { - if (sourceType == null || targetType == null) { - throw new IllegalArgumentException(CorePlugin.Util.getString( - "ERR.003.029.0002", sourceType, targetType)); //$NON-NLS-1$ - } - return (getTransformFromMaps(DataTypeManager - .getDataTypeName(sourceType), DataTypeManager - .getDataTypeName(targetType)) != null); + public static boolean isTransformable(Class<?> sourceType, Class<?> targetType) { + return getTransform(sourceType, targetType) != null; } /** Modified: trunk/common-core/src/main/java/org/teiid/core/types/basic/FloatingNumberToBigIntegerTransform.java =================================================================== --- trunk/common-core/src/main/java/org/teiid/core/types/basic/FloatingNumberToBigIntegerTransform.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/common-core/src/main/java/org/teiid/core/types/basic/FloatingNumberToBigIntegerTransform.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -53,7 +53,7 @@ * Type of the incoming value. * @return Source type */ - public Class getSourceType() { + public Class<?> getSourceType() { return sourceType; } @@ -61,7 +61,7 @@ * Type of the outgoing value. * @return Target type */ - public Class getTargetType() { + public Class<?> getTargetType() { return DataTypeManager.DefaultDataClasses.BIG_INTEGER; } Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -21,37 +21,27 @@ */ package org.teiid.translator.jdbc.netezza; -import junit.framework.TestCase; +import static org.junit.Assert.*; +import org.junit.BeforeClass; import org.junit.Test; import org.mockito.Mockito; import org.teiid.cdk.unittest.FakeTranslationFactory; -import org.teiid.dqp.internal.datamgr.FakeExecutionContextImpl; import org.teiid.language.Command; import org.teiid.translator.ExecutionContext; import org.teiid.translator.TranslatorException; import org.teiid.translator.jdbc.TranslatedCommand; -import org.teiid.translator.jdbc.TranslationHelper; -public class TestNetezzaTranslatorCapabilities extends TestCase { +@SuppressWarnings("nls") +public class TestNetezzaTranslatorCapabilities { private static NetezzaExecutionFactory TRANSLATOR; - private static ExecutionContext EMPTY_CONTEXT = new FakeExecutionContextImpl(); - static { - try { - TRANSLATOR = new NetezzaExecutionFactory(); - TRANSLATOR.start(); - } catch(TranslatorException e) { - e.printStackTrace(); - } + @BeforeClass public static void oneTimeSetup() throws TranslatorException { + TRANSLATOR = new NetezzaExecutionFactory(); + TRANSLATOR.start(); } - - private String getTestBQTVDB() { - return TranslationHelper.BQT_VDB; - } - public void helpTestVisitor(String input, String expectedOutput) throws TranslatorException { // Convert from sql to objects Command obj = FakeTranslationFactory.getInstance().getBQTTranslationUtility().parseCommand(input); @@ -198,7 +188,7 @@ @Test public void testScalarSubQuery() throws Exception { String input = "select intkey, intnum from bqt1.smalla where intnum < (0.01 * (select sum(intnum) from bqt1.smalla ))"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < (0.010000000000000 * (SELECT SUM(SmallA.IntNum) FROM SmallA))"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < (0.01 * (SELECT SUM(SmallA.IntNum) FROM SmallA))"; helpTestVisitor( input, Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -227,7 +227,7 @@ @Test public void testNonIntMod() throws Exception { String input = "select mod(intkey/1.5, 3) from bqt1.smalla"; //$NON-NLS-1$ - String output = "SELECT ((cast(SmallA.IntKey AS double precision) / 1.5) - (sign((cast(SmallA.IntKey AS double precision) / 1.5)) * floor(abs(((cast(SmallA.IntKey AS double precision) / 1.5) / 3.0))) * abs(3.0))) FROM SmallA"; //$NON-NLS-1$ + String output = "SELECT ((cast(SmallA.IntKey AS numeric(38, 19)) / 1.5) - (sign((cast(SmallA.IntKey AS numeric(38, 19)) / 1.5)) * floor(abs(((cast(SmallA.IntKey AS numeric(38, 19)) / 1.5) / 3))) * abs(3))) FROM SmallA"; //$NON-NLS-1$ helpTestVisitor(getBQTVDB(), input, 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-11-30 19:08:23 UTC (rev 3709) +++ trunk/documentation/admin-guide/src/main/docbook/en-US/content/appendix-c.xml 2011-11-30 22:17:23 UTC (rev 3710) @@ -40,5 +40,10 @@ Set to true to use ISO 8601 rules for week calculations regardless of the locale. When true the dayOfWeek function will begin with 1 for MONDAY rather than SUNDAY, and the week function will require that week 1 of a year contains the year's first Thursday. </para> </listitem> + <listitem> + <para><emphasis>org.teiid.parseDecimalAsDouble</emphasis> - defaults to false. + Set to true to parse exact fixed point literals, e.g. 1.0, as double values rather than as decimal/BigDecimal values in the same way as releases earlier than 8.0. + </para> + </listitem> </itemizedlist> </appendix> \ No newline at end of file Modified: trunk/engine/src/main/java/org/teiid/query/function/FunctionLibrary.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/function/FunctionLibrary.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/main/java/org/teiid/query/function/FunctionLibrary.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -32,8 +32,11 @@ import org.teiid.api.exception.query.InvalidFunctionException; import org.teiid.api.exception.query.QueryResolverException; import org.teiid.core.types.DataTypeManager; +import org.teiid.core.types.Transform; import org.teiid.metadata.FunctionMethod; import org.teiid.metadata.FunctionParameter; +import org.teiid.query.resolver.util.ResolverUtil; +import org.teiid.query.sql.symbol.Constant; import org.teiid.query.sql.symbol.Expression; import org.teiid.query.sql.symbol.Function; @@ -193,22 +196,21 @@ * executing the function. * @param name Name of function * @param returnType + * @param args * @param types Existing types passed to the function * @return Null if no conversion could be found, otherwise an array of conversions * to apply to each argument. The list should match 1-to-1 with the parameters. * Parameters that do not need a conversion are null; parameters that do are - * FunctionDescriptors. + * FunctionDescriptors. + * @throws InvalidFunctionException * @throws QueryResolverException */ - public FunctionDescriptor[] determineNecessaryConversions(String name, Class<?> returnType, Class<?>[] types, boolean hasUnknownType) { + public FunctionDescriptor[] determineNecessaryConversions(String name, Class<?> returnType, Expression[] args, Class<?>[] types, boolean hasUnknownType) throws InvalidFunctionException { // Check for no args - no conversion necessary if(types.length == 0) { - return new FunctionDescriptor[0]; + return null; } - // Construct results array - FunctionDescriptor[] results = null; - //First find existing functions with same name and same number of parameters final Collection<FunctionMethod> functionMethods = new LinkedList<FunctionMethod>(); functionMethods.addAll( this.systemFunctions.findFunctionMethods(name, types.length) ); @@ -223,12 +225,12 @@ //Current best score (lower score is best. Higher score results in more implicit conversions int bestScore = Integer.MAX_VALUE; boolean ambiguous = false; + FunctionMethod result = null; for (FunctionMethod nextMethod : functionMethods) { - int currentScore = 0; + int currentScore = 0; final List<FunctionParameter> methodTypes = nextMethod.getInputParameters(); //Holder for current signature with converts where required - FunctionDescriptor[] currentSignature = new FunctionDescriptor[types.length]; //Iterate over the parameters adding conversions where required or failing when //no implicit conversion is possible @@ -240,17 +242,21 @@ Class<?> sourceType = types[i]; if (sourceType == null) { - FunctionDescriptor fd = findTypedConversionFunction(DataTypeManager.DefaultDataClasses.NULL, targetType); - currentSignature[i] = fd; currentScore++; continue; } try { - FunctionDescriptor fd = getConvertFunctionDescriptor(sourceType, targetType); - if (fd != null) { - currentScore++; - currentSignature[i] = fd; + Transform t = getConvertFunctionDescriptor(sourceType, targetType); + if (t != null) { + if (t.isExplicit()) { + if (!(args[i] instanceof Constant) || ResolverUtil.convertConstant(DataTypeManager.getDataTypeName(sourceType), tmpTypeName, (Constant)args[i]) == null) { + break; + } + currentScore++; + } else { + currentScore++; + } } } catch (InvalidFunctionException e) { break; @@ -265,13 +271,18 @@ if (hasUnknownType) { if (returnType != null) { try { - FunctionDescriptor fd = getConvertFunctionDescriptor(DataTypeManager.getDataTypeClass(nextMethod.getOutputParameter().getType()), returnType); - if (fd != null) { - currentScore++; + Transform t = getConvertFunctionDescriptor(DataTypeManager.getDataTypeClass(nextMethod.getOutputParameter().getType()), returnType); + if (t != null) { + if (t.isExplicit()) { + //there still may be a common type, but use any other valid conversion over this one + currentScore += types.length + 1; + } else { + currentScore++; + } } } catch (InvalidFunctionException e) { //there still may be a common type, but use any other valid conversion over this one - currentScore += (types.length + 1); + currentScore += (types.length * types.length); } } ambiguous = currentScore == bestScore; @@ -279,40 +290,52 @@ if (currentScore < bestScore) { - if (currentScore == 0) { + if (currentScore == 0) { //this must be an exact match - return currentSignature; + return null; } bestScore = currentScore; - results = currentSignature; + result = nextMethod; } } - if (ambiguous) { - return null; + if (ambiguous || result == null) { + throw new InvalidFunctionException(); } - return results; + return getConverts(result, types); } - private FunctionDescriptor getConvertFunctionDescriptor(Class<?> sourceType, Class<?> targetType) throws InvalidFunctionException { - final String sourceTypeName = DataTypeManager.getDataTypeName(sourceType); - final String targetTypeName = DataTypeManager.getDataTypeName(targetType); + private FunctionDescriptor[] getConverts(FunctionMethod method, Class<?>[] types) { + final List<FunctionParameter> methodTypes = method.getInputParameters(); + FunctionDescriptor[] result = new FunctionDescriptor[types.length]; + for(int i = 0; i < types.length; i++) { + //treat all varags as the same type + final String tmpTypeName = methodTypes.get(Math.min(i, methodTypes.size() - 1)).getType(); + Class<?> targetType = DataTypeManager.getDataTypeClass(tmpTypeName); + + Class<?> sourceType = types[i]; + if (sourceType == null) { + result[i] = findTypedConversionFunction(DataTypeManager.DefaultDataClasses.NULL, targetType); + } else if (sourceType != targetType){ + result[i] = findTypedConversionFunction(sourceType, targetType); + } + } + return result; + } + + private Transform getConvertFunctionDescriptor(Class<?> sourceType, Class<?> targetType) throws InvalidFunctionException { //If exact match no conversion necessary - if(sourceTypeName.equals(targetTypeName)) { + if(sourceType.equals(targetType)) { return null; } + Transform result = DataTypeManager.getTransform(sourceType, targetType); //Else see if an implicit conversion is possible. - if(!DataTypeManager.isImplicitConversion(sourceTypeName, targetTypeName)){ + if(result == null){ throw new InvalidFunctionException(); } - //Else no conversion is available and the current method is not a valid match - final FunctionDescriptor fd = findTypedConversionFunction(sourceType, targetType); - if(fd == null) { - throw new InvalidFunctionException(); - } - return fd; + return result; } /** Modified: trunk/engine/src/main/java/org/teiid/query/function/FunctionMethods.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/function/FunctionMethods.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/main/java/org/teiid/query/function/FunctionMethods.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -166,9 +166,15 @@ return x.divide(y); } + private static final BigDecimal ZERO = new BigDecimal(0); + public static Object divide(BigDecimal x, BigDecimal y) { BigDecimal bd = x.divide(y, Math.max(16, x.scale() + y.precision() + 1), RoundingMode.HALF_UP).stripTrailingZeros(); - return bd.setScale(Math.max(x.scale(), bd.scale())); + bd = bd.setScale(Math.max(x.scale(), bd.scale())); + if (bd.compareTo(ZERO) == 0) { + return ZERO; + } + return bd; } // ================== Function = abs ===================== Modified: trunk/engine/src/main/java/org/teiid/query/function/aggregate/Avg.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/function/aggregate/Avg.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/main/java/org/teiid/query/function/aggregate/Avg.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -30,6 +30,7 @@ import org.teiid.core.TeiidComponentException; import org.teiid.core.types.DataTypeManager; import org.teiid.query.QueryPlugin; +import org.teiid.query.function.FunctionMethods; /** @@ -40,8 +41,6 @@ */ public class Avg extends Sum { - private static final int AVG_SCALE = 9; - private int count = 0; /** @@ -87,9 +86,9 @@ case BIG_DECIMAL: try { - return ((BigDecimal)sum).divide(new BigDecimal(count), AVG_SCALE, BigDecimal.ROUND_HALF_UP); + return FunctionMethods.divide((BigDecimal)sum, new BigDecimal(count)); } catch(ArithmeticException e) { - throw new FunctionExecutionException(e, "ERR.015.001.0048", QueryPlugin.Util.getString("ERR.015.001.0048", sum, new Integer(count))); //$NON-NLS-1$ //$NON-NLS-2$ + throw new FunctionExecutionException(e, "ERR.015.001.0048", QueryPlugin.Util.getString("ERR.015.001.0048", sum, count)); //$NON-NLS-1$ //$NON-NLS-2$ } default: throw new AssertionError("unknown accumulator type"); //$NON-NLS-1$ Modified: trunk/engine/src/main/java/org/teiid/query/function/aggregate/Sum.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/function/aggregate/Sum.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/main/java/org/teiid/query/function/aggregate/Sum.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -47,7 +47,10 @@ protected int accumulatorType = LONG; - private Object sum = null; + private long sumLong; + private double sumDouble; + private BigDecimal sumBigDecimal; + private boolean isNull = true; /** * Allows subclasses to determine type of accumulator for the SUM. @@ -69,16 +72,16 @@ this.accumulatorType = DOUBLE; - } else if(dataType.equals(DataTypeManager.DefaultDataClasses.BIG_INTEGER)) { - - this.accumulatorType = BIG_INTEGER; } else { this.accumulatorType = BIG_DECIMAL; } } public void reset() { - sum = null; + sumLong = 0; + sumDouble = 0; + sumBigDecimal = null; + isNull = true; } /** @@ -87,39 +90,27 @@ public void addInputDirect(Object input, List<?> tuple) throws FunctionExecutionException, ExpressionEvaluationException, TeiidComponentException { - if (this.sum == null) { - switch (this.accumulatorType) { - case LONG: - this.sum = new Long(0); - break; - case DOUBLE: - this.sum = new Double(0); - break; - case BIG_INTEGER: - this.sum = new BigInteger(String.valueOf(0)); - break; - case BIG_DECIMAL: - this.sum = new BigDecimal(0); - break; - } - } - + isNull = false; + switch(this.accumulatorType) { case LONG: - this.sum = new Long(((Long)this.sum).longValue() + ((Number)input).longValue()); + this.sumLong += ((Number)input).longValue(); break; case DOUBLE: - this.sum = new Double(((Double)this.sum).doubleValue() + ((Number)input).doubleValue()); + this.sumDouble += ((Number)input).doubleValue(); break; case BIG_INTEGER: - this.sum = ((BigInteger)this.sum).add( (BigInteger) input ); - break; case BIG_DECIMAL: + if (sumBigDecimal == null) { + sumBigDecimal = BigDecimal.valueOf(0); + } if (input instanceof BigInteger) { BigInteger bigIntegerInput = (BigInteger) input; - this.sum = ((BigDecimal)this.sum).add( new BigDecimal(bigIntegerInput) ); + this.sumBigDecimal = this.sumBigDecimal.add( new BigDecimal(bigIntegerInput) ); + } else if (input instanceof BigDecimal){ + this.sumBigDecimal = this.sumBigDecimal.add( (BigDecimal) input ); } else { - this.sum = ((BigDecimal)this.sum).add( (BigDecimal) input ); + this.sumBigDecimal = this.sumBigDecimal.add( new BigDecimal(((Number)input).longValue())); } break; } @@ -131,6 +122,18 @@ public Object getResult() throws FunctionExecutionException, ExpressionEvaluationException, TeiidComponentException { - return sum; + if (isNull){ + return null; + } + + switch(this.accumulatorType) { + case LONG: + return this.sumLong; + case DOUBLE: + return this.sumDouble; + case BIG_INTEGER: + return this.sumBigDecimal.toBigInteger(); + } + return this.sumBigDecimal; } } Modified: trunk/engine/src/main/java/org/teiid/query/parser/SQLParserUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/parser/SQLParserUtil.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/main/java/org/teiid/query/parser/SQLParserUtil.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -30,6 +30,7 @@ import java.util.regex.Pattern; import org.teiid.core.util.Assertion; +import org.teiid.core.util.PropertiesUtils; import org.teiid.core.util.StringUtil; import org.teiid.language.SQLConstants.Reserved; import org.teiid.query.QueryPlugin; @@ -49,6 +50,8 @@ public class SQLParserUtil { + public static final boolean PARSE_DECIMAL_AS_DOUBLE = PropertiesUtils.getBooleanProperty(System.getProperties(), "org.teiid.parseDecimalAsDouble", false); //$NON-NLS-1$ + String matchesAny(String arg, String ... expected) { for (String string : expected) { if (string.equalsIgnoreCase(arg)) { Modified: trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverUtil.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverUtil.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -22,19 +22,7 @@ package org.teiid.query.resolver.util; -import java.util.ArrayList; -import java.util.Arrays; -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.LinkedHashSet; -import java.util.LinkedList; -import java.util.List; -import java.util.Map; -import java.util.Set; +import java.util.*; import org.teiid.api.exception.query.QueryMetadataException; import org.teiid.api.exception.query.QueryResolverException; @@ -57,33 +45,8 @@ import org.teiid.query.optimizer.relational.rules.RuleChooseJoinStrategy; import org.teiid.query.optimizer.relational.rules.RuleRaiseAccess; import org.teiid.query.sql.LanguageObject; -import org.teiid.query.sql.lang.Command; -import org.teiid.query.sql.lang.CompareCriteria; -import org.teiid.query.sql.lang.Criteria; -import org.teiid.query.sql.lang.FromClause; -import org.teiid.query.sql.lang.JoinPredicate; -import org.teiid.query.sql.lang.JoinType; -import org.teiid.query.sql.lang.Limit; -import org.teiid.query.sql.lang.OrderBy; -import org.teiid.query.sql.lang.Query; -import org.teiid.query.sql.lang.QueryCommand; -import org.teiid.query.sql.lang.SetQuery; -import org.teiid.query.sql.lang.SubqueryContainer; -import org.teiid.query.sql.lang.UnaryFromClause; -import org.teiid.query.sql.symbol.AbstractCaseExpression; -import org.teiid.query.sql.symbol.AggregateSymbol; -import org.teiid.query.sql.symbol.AliasSymbol; -import org.teiid.query.sql.symbol.Constant; -import org.teiid.query.sql.symbol.DerivedColumn; -import org.teiid.query.sql.symbol.ElementSymbol; -import org.teiid.query.sql.symbol.Expression; -import org.teiid.query.sql.symbol.ExpressionSymbol; -import org.teiid.query.sql.symbol.Function; -import org.teiid.query.sql.symbol.GroupSymbol; -import org.teiid.query.sql.symbol.Reference; -import org.teiid.query.sql.symbol.ScalarSubquery; -import org.teiid.query.sql.symbol.SelectSymbol; -import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.lang.*; +import org.teiid.query.sql.symbol.*; import org.teiid.query.sql.util.SymbolMap; import org.teiid.query.sql.visitor.ElementCollectorVisitor; @@ -483,7 +446,7 @@ * @since 4.3 */ private static Constant getProperlyTypedConstant(Object defaultValue, - Class parameterType) + Class<?> parameterType) throws QueryResolverException{ try { Object newValue = DataTypeManager.transformValue(defaultValue, parameterType); Modified: trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -31,6 +31,7 @@ import java.util.List; import java.util.Map; +import org.teiid.api.exception.query.InvalidFunctionException; import org.teiid.api.exception.query.QueryMetadataException; import org.teiid.api.exception.query.QueryResolverException; import org.teiid.api.exception.query.UnresolvedSymbolDescription; @@ -449,7 +450,7 @@ // Look up types for all args boolean hasArgWithoutType = false; Expression[] args = function.getArgs(); - Class[] types = new Class[args.length]; + Class<?>[] types = new Class[args.length]; for(int i=0; i<args.length; i++) { types[i] = args[i].getType(); if(types[i] == null) { @@ -527,30 +528,35 @@ private FunctionDescriptor findWithImplicitConversions(FunctionLibrary library, Function function, Expression[] args, Class<?>[] types, boolean hasArgWithoutType) throws QueryResolverException, TeiidComponentException { // Try to find implicit conversion path to still perform this function - FunctionDescriptor[] conversions = library.determineNecessaryConversions(function.getName(), function.getType(), types, hasArgWithoutType); + FunctionDescriptor[] conversions; + try { + conversions = library.determineNecessaryConversions(function.getName(), function.getType(), args, types, hasArgWithoutType); + } catch (InvalidFunctionException e) { + return null; + } + Class<?>[] newSignature = types; - if(conversions == null) { - return null; + if(conversions != null) { + newSignature = new Class[conversions.length]; + // Insert new conversion functions as necessary, while building new signature + for(int i=0; i<conversions.length; i++) { + + Class<?> newType = types[i]; + + if(conversions[i] != null) { + newType = conversions[i].getReturnType(); + + setDesiredType(args[i], newType, function); + + //only currently typed expressions need conversions + if (types[i] != null && newType != DataTypeManager.DefaultDataClasses.OBJECT) { + function.insertConversion(i, conversions[i]); + } + } + + newSignature[i] = newType; + } } - // Insert new conversion functions as necessary, while building new signature - Class<?>[] newSignature = new Class[conversions.length]; - for(int i=0; i<conversions.length; i++) { - - Class<?> newType = types[i]; - - if(conversions[i] != null) { - newType = conversions[i].getReturnType(); - - setDesiredType(args[i], newType, function); - - //only currently typed expressions need conversions - if (types[i] != null && newType != DataTypeManager.DefaultDataClasses.OBJECT) { - function.insertConversion(i, conversions[i]); - } - } - - newSignature[i] = newType; - } // Now resolve using the new signature to get the function's descriptor return library.findFunction(function.getName(), newSignature); Modified: trunk/engine/src/main/java/org/teiid/query/sql/symbol/AggregateSymbol.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/symbol/AggregateSymbol.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/main/java/org/teiid/query/sql/symbol/AggregateSymbol.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -90,10 +90,10 @@ SUM_TYPES.put(DataTypeManager.DefaultDataClasses.BIG_DECIMAL, DataTypeManager.DefaultDataClasses.BIG_DECIMAL); AVG_TYPES = new HashMap<Class<?>, Class<?>>(); - AVG_TYPES.put(DataTypeManager.DefaultDataClasses.BYTE, DataTypeManager.DefaultDataClasses.DOUBLE); - AVG_TYPES.put(DataTypeManager.DefaultDataClasses.SHORT, DataTypeManager.DefaultDataClasses.DOUBLE); - AVG_TYPES.put(DataTypeManager.DefaultDataClasses.INTEGER, DataTypeManager.DefaultDataClasses.DOUBLE); - AVG_TYPES.put(DataTypeManager.DefaultDataClasses.LONG, DataTypeManager.DefaultDataClasses.DOUBLE); + AVG_TYPES.put(DataTypeManager.DefaultDataClasses.BYTE, DataTypeManager.DefaultDataClasses.BIG_DECIMAL); + AVG_TYPES.put(DataTypeManager.DefaultDataClasses.SHORT, DataTypeManager.DefaultDataClasses.BIG_DECIMAL); + AVG_TYPES.put(DataTypeManager.DefaultDataClasses.INTEGER, DataTypeManager.DefaultDataClasses.BIG_DECIMAL); + AVG_TYPES.put(DataTypeManager.DefaultDataClasses.LONG, DataTypeManager.DefaultDataClasses.BIG_DECIMAL); AVG_TYPES.put(DataTypeManager.DefaultDataClasses.BIG_INTEGER, DataTypeManager.DefaultDataClasses.BIG_DECIMAL); AVG_TYPES.put(DataTypeManager.DefaultDataClasses.FLOAT, DataTypeManager.DefaultDataClasses.DOUBLE); AVG_TYPES.put(DataTypeManager.DefaultDataClasses.DOUBLE, DataTypeManager.DefaultDataClasses.DOUBLE); Modified: trunk/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -66,20 +66,7 @@ import org.teiid.query.processor.ProcessorDataManager; import org.teiid.query.processor.QueryProcessor; import org.teiid.query.resolver.util.ResolverUtil; -import org.teiid.query.sql.lang.CacheHint; -import org.teiid.query.sql.lang.Command; -import org.teiid.query.sql.lang.CompareCriteria; -import org.teiid.query.sql.lang.Create; -import org.teiid.query.sql.lang.Criteria; -import org.teiid.query.sql.lang.Delete; -import org.teiid.query.sql.lang.Drop; -import org.teiid.query.sql.lang.Insert; -import org.teiid.query.sql.lang.Option; -import org.teiid.query.sql.lang.ProcedureContainer; -import org.teiid.query.sql.lang.Query; -import org.teiid.query.sql.lang.SPParameter; -import org.teiid.query.sql.lang.StoredProcedure; -import org.teiid.query.sql.lang.Update; +import org.teiid.query.sql.lang.*; import org.teiid.query.sql.navigator.PostOrderNavigator; import org.teiid.query.sql.symbol.Constant; import org.teiid.query.sql.symbol.ElementSymbol; @@ -314,9 +301,12 @@ } Constant key = (Constant)proc.getParameter(2).getExpression(); LogManager.logInfo(LogConstants.CTX_MATVIEWS, QueryPlugin.Util.getString("TempTableDataManager.row_refresh", matViewName, key)); //$NON-NLS-1$ + Object id = ids.iterator().next(); + String targetTypeName = metadata.getElementType(id); + Object value = DataTypeManager.transformValue(key.getValue(), DataTypeManager.getDataTypeClass(targetTypeName)); String queryString = Reserved.SELECT + " * " + Reserved.FROM + ' ' + matViewName + ' ' + Reserved.WHERE + ' ' + //$NON-NLS-1$ - metadata.getFullName(ids.iterator().next()) + " = ?" + ' ' + Reserved.OPTION + ' ' + Reserved.NOCACHE; //$NON-NLS-1$ - QueryProcessor qp = context.getQueryProcessorFactory().createQueryProcessor(queryString, matViewName.toUpperCase(), context, key.getValue()); + metadata.getFullName(id) + " = ?" + ' ' + Reserved.OPTION + ' ' + Reserved.NOCACHE; //$NON-NLS-1$ + QueryProcessor qp = context.getQueryProcessorFactory().createQueryProcessor(queryString, matViewName.toUpperCase(), context, value); qp.setNonBlocking(true); qp.getContext().setDataObjects(null); TupleSource ts = new BatchCollector.BatchProducerTupleSource(qp); @@ -324,7 +314,7 @@ boolean delete = false; if (tuple == null) { delete = true; - tuple = Arrays.asList(key.getValue()); + tuple = Arrays.asList(value); } else { tuple = new ArrayList<Object>(tuple); //ensure the list is serializable } Modified: trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj =================================================================== --- trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/main/javacc/org/teiid/query/parser/SQLParser.jj 2011-11-30 22:17:23 UTC (rev 3710) @@ -392,8 +392,9 @@ | < BOOLEANTYPE: "{" "b" > | < POS_REF: ["$"] (<DIGIT>)+ > | < INTEGERVAL: (<MINUS>)?(<DIGIT>)+ > -| < FLOATVAL: (<MINUS>)? (<DIGIT>)* <PERIOD> (<DIGIT>)+ - ( ["e", "E"] (["+","-"])? (<DIGIT>)+ )? > +| < DECIMALVAL: (<MINUS>)? (<DIGIT>)* <PERIOD> (<DIGIT>)+ > +| < FLOATVAL: (<MINUS>)? <DIGIT> <PERIOD> (<DIGIT>)+ + ["e", "E"] (["+","-"])? (<DIGIT>)+ > | < STRINGVAL: (("N"|"E")? "'" ( ("''") | ~["'"] )* "'") > | < #LETTER: (["a"-"z","A"-"Z"] | ["\u0153"-"\ufffd"]) > | < #DIGIT: ["0"-"9"] > @@ -4001,7 +4002,18 @@ throw new ParseException(QueryPlugin.Util.getString("SQLParser.Float_parse", params)); //$NON-NLS-1$ } } | - + t=<DECIMALVAL> { + try { + if (PARSE_DECIMAL_AS_DOUBLE) { + constant = new Constant(Double.valueOf(t.image), DataTypeManager.DefaultDataClasses.DOUBLE); + } else { + constant = new Constant(new java.math.BigDecimal(t.image), DataTypeManager.DefaultDataClasses.BIG_DECIMAL); + } + } catch(NumberFormatException e) { + Object[] params = new Object[] { t.image }; + throw new ParseException(QueryPlugin.Util.getString("SQLParser.decimal_parse", params)); //$NON-NLS-1$ + } + } | t=<FALSE> { constant = new Constant(Boolean.FALSE, DataTypeManager.DefaultDataClasses.BOOLEAN); } | t=<TRUE> { constant = new Constant(Boolean.TRUE, DataTypeManager.DefaultDataClasses.BOOLEAN); } | t=<UNKNOWN> { constant = new Constant(null, DataTypeManager.DefaultDataClasses.BOOLEAN); } | Modified: trunk/engine/src/main/resources/org/teiid/query/i18n.properties =================================================================== --- trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2011-11-30 22:17:23 UTC (rev 3710) @@ -263,6 +263,7 @@ SQLParser.Invalid_func=Invalid function name: [{0}] SQLParser.Integer_parse=Unable to parse integer literal: {0} SQLParser.Float_parse=Unable to parse floating point literal: {0} +SQLParser.decimal_parse=Unable to parse decimal literal: {0} SQLParser.Invalid_id=Invalid metadata ID format: [{0}] SQLParser.Invalid_alias=Invalid alias format: [{0}] SQLParser.Invalid_short_name=Invalid simple identifier format: [{0}] Modified: trunk/engine/src/test/java/org/teiid/query/function/TestFunctionLibrary.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/function/TestFunctionLibrary.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/test/java/org/teiid/query/function/TestFunctionLibrary.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -43,6 +43,7 @@ import org.junit.Before; import org.junit.Test; import org.teiid.api.exception.query.FunctionExecutionException; +import org.teiid.api.exception.query.InvalidFunctionException; import org.teiid.common.buffer.BufferManagerFactory; import org.teiid.core.types.BlobType; import org.teiid.core.types.ClobType; @@ -55,6 +56,7 @@ import org.teiid.core.util.TimestampWithTimezone; import org.teiid.language.SQLConstants.NonReserved; import org.teiid.metadata.FunctionMethod.PushDown; +import org.teiid.query.sql.symbol.Expression; import org.teiid.query.unittest.RealMetadataFactory; import org.teiid.query.unittest.TimestampUtil; import org.teiid.query.util.CommandContext; @@ -145,7 +147,15 @@ private void helpFindConversions(String fname, Class<?>[] types, FunctionDescriptor[] expected) { - FunctionDescriptor[] actual = library.determineNecessaryConversions(fname, null, types, false); + FunctionDescriptor[] actual; + try { + actual = library.determineNecessaryConversions(fname, null, new Expression[types.length], types, false); + if (actual == null) { + actual = new FunctionDescriptor[types.length]; + } + } catch (InvalidFunctionException e) { + actual = null; + } if(expected == null) { if(actual != null) { Modified: trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/test/java/org/teiid/query/parser/TestParser.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -5242,5 +5242,10 @@ String actualString = actualCommand.toString(); assertEquals("SELECT substring(RTRIM(MED.BATDAT), 4, 4) FROM FCC.MEDMAS AS MED", actualString); } + + @Test public void testExactFixedPoint() throws QueryParserException { + Query actualCommand = (Query)QueryParser.getQueryParser().parseCommand("SELECT 1.1", new ParseInfo()); + assertEquals(DataTypeManager.DefaultDataClasses.BIG_DECIMAL, ((Expression)actualCommand.getSelect().getSymbol(0)).getType()); + } } Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -664,7 +664,7 @@ // Create expected results List[] expected = new List[] { - Arrays.asList(new Object[] { new Integer(0), new Integer(3), new Long(7), new Double(1.1666666666666667), new Long(6), new Double(1.5) }), + Arrays.asList(new Object[] { new Integer(0), new Integer(3), new Long(7), new BigDecimal("1.1666666666666667"), new Long(6), new BigDecimal(1.5) }), }; // Construct data manager with data @@ -4180,7 +4180,7 @@ // Create expected results List[] expected = new List[] { - Arrays.asList(new Object[] { new Integer(1), Boolean.TRUE, new Double(2.0), TimestampUtil.createDate(103, 10, 4) }) + Arrays.asList(new Object[] { new Integer(1), Boolean.TRUE, new BigDecimal("2.0"), TimestampUtil.createDate(103, 10, 4) }) }; // Construct data manager with data @@ -4772,7 +4772,7 @@ // Create expected results List[] expected = new List[] { - Arrays.asList(new Object[] { new Double(1.5), new Double(1.5), new Double(1.5), new Double(1.5), new Double(1.5), new Double(1.5), new Double(1.5), new BigDecimal("1.500000000"), new BigDecimal("1.500000000") }), //$NON-NLS-1$//$NON-NLS-2$ + Arrays.asList(new Object[] { new BigDecimal(1.5), new BigDecimal(1.5), new Double(1.5), new BigDecimal(1.5), new Double(1.5), new BigDecimal(1.5), new BigDecimal(1.5), new BigDecimal(1.5), new BigDecimal(1.5) }), //$NON-NLS-1$//$NON-NLS-2$ }; // Construct data manager with data Modified: trunk/engine/src/test/java/org/teiid/query/processor/relational/TestGroupingNode.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/relational/TestGroupingNode.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/test/java/org/teiid/query/processor/relational/TestGroupingNode.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -54,7 +54,7 @@ import org.teiid.query.unittest.RealMetadataFactory; import org.teiid.query.util.CommandContext; -@SuppressWarnings("unchecked") +@SuppressWarnings({"unchecked", "nls"}) public class TestGroupingNode { public static FakeTupleSource createTupleSource1() { @@ -158,14 +158,14 @@ CommandContext context = new CommandContext("pid", "test", null, null, 1); //$NON-NLS-1$ //$NON-NLS-2$ List[] expected = new List[] { - Arrays.asList(new Object[] { null, new Integer(2), new Integer(1), new Integer(1), new Long(3), new Long(3), new Double(3.0), new Double(3.0), new Integer(3), new Integer(3), new Integer(3), new Integer(3) }), - Arrays.asList(new Object[] { new Integer(0), new Integer(1), new Integer(1), new Integer(1), new Long(4), new Long(4), new Double(4.0), new Double(4.0), new Integer(4), new Integer(4), new Integer(4), new Integer(4) }), - Arrays.asList(new Object[] { new Integer(1), new Integer(1), new Integer(1), new Integer(1), new Long(2), new Long(2), new Double(2.0), new Double(2.0), new Integer(2), new Integer(2), new Integer(2), new Integer(2) }), - Arrays.asList(new Object[] { new Integer(2), new Integer(4), new Integer(4), new Integer(2), new Long(5), new Long(3), new Double(1.25), new Double(1.5), new Integer(1), new Integer(1), new Integer(2), new Integer(2) }), - Arrays.asList(new Object[] { new Integer(3), new Integer(1), new Integer(1), new Integer(1), new Long(0), new Long(0), new Double(0.0), new Double(0.0), new Integer(0), new Integer(0), new Integer(0), new Integer(0) }), - Arrays.asList(new Object[] { new Integer(4), new Integer(3), new Integer(2), new Integer(2), new Long(5), new Long(5), new Double(2.5), new Double(2.5), new Integer(2), new Integer(2), new Integer(3), new Integer(3) }), - Arrays.asList(new Object[] { new Integer(5), new Integer(1), new Integer(1), new Integer(1), new Long(3), new Long(3), new Double(3.0), new Double(3.0), new Integer(3), new Integer(3), new Integer(3), new Integer(3) }), - Arrays.asList(new Object[] { new Integer(6), new Integer(2), new Integer(2), new Integer(2), new Long(7), new Long(7), new Double(3.5), new Double(3.5), new Integer(3), new Integer(3), new Integer(4), new Integer(4) }) + Arrays.asList(new Object[] { null, new Integer(2), new Integer(1), new Integer(1), new Long(3), new Long(3), new BigDecimal(3.0), new BigDecimal(3.0), new Integer(3), new Integer(3), new Integer(3), new Integer(3) }), + Arrays.asList(new Object[] { new Integer(0), new Integer(1), new Integer(1), new Integer(1), new Long(4), new Long(4), new BigDecimal(4.0), new BigDecimal(4.0), new Integer(4), new Integer(4), new Integer(4), new Integer(4) }), + Arrays.asList(new Object[] { new Integer(1), new Integer(1), new Integer(1), new Integer(1), new Long(2), new Long(2), new BigDecimal(2.0), new BigDecimal(2.0), new Integer(2), new Integer(2), new Integer(2), new Integer(2) }), + Arrays.asList(new Object[] { new Integer(2), new Integer(4), new Integer(4), new Integer(2), new Long(5), new Long(3), new BigDecimal(1.25), new BigDecimal(1.5), new Integer(1), new Integer(1), new Integer(2), new Integer(2) }), + Arrays.asList(new Object[] { new Integer(3), new Integer(1), new Integer(1), new Integer(1), new Long(0), new Long(0), new BigDecimal(0.0), new BigDecimal(0.0), new Integer(0), new Integer(0), new Integer(0), new Integer(0) }), + Arrays.asList(new Object[] { new Integer(4), new Integer(3), new Integer(2), new Integer(2), new Long(5), new Long(5), new BigDecimal(2.5), new BigDecimal(2.5), new Integer(2), new Integer(2), new Integer(3), new Integer(3) }), + Arrays.asList(new Object[] { new Integer(5), new Integer(1), new Integer(1), new Integer(1), new Long(3), new Long(3), new BigDecimal(3.0), new BigDecimal(3.0), new Integer(3), new Integer(3), new Integer(3), new Integer(3) }), + Arrays.asList(new Object[] { new Integer(6), new Integer(2), new Integer(2), new Integer(2), new Long(7), new Long(7), new BigDecimal(3.5), new BigDecimal(3.5), new Integer(3), new Integer(3), new Integer(4), new Integer(4) }) }; helpProcess(mgr, node, context, expected, null); @@ -243,7 +243,7 @@ }; List[] expected = new List[] { - Arrays.asList(new Object[] { new BigDecimal("10.0"), new BigDecimal("2.000000000") }) //$NON-NLS-1$ //$NON-NLS-2$ + Arrays.asList(new Object[] { new BigDecimal("10.0"), new BigDecimal("2.0") }) //$NON-NLS-1$ //$NON-NLS-2$ }; List symbols = new ArrayList(); @@ -283,8 +283,8 @@ }; List[] expected = new List[] { - Arrays.asList(new Object[] { new Integer(1), new BigDecimal("1.0"), new BigDecimal("0.500000000") }), //$NON-NLS-1$ //$NON-NLS-2$ - Arrays.asList(new Object[] { new Integer(2), new BigDecimal("9.0"), new BigDecimal("3.000000000") }) //$NON-NLS-1$ //$NON-NLS-2$ + Arrays.asList(new Object[] { new Integer(1), new BigDecimal("1.0"), new BigDecimal(.5) }), //$NON-NLS-1$ + Arrays.asList(new Object[] { new Integer(2), new BigDecimal("9.0"), new BigDecimal("3.0") }) //$NON-NLS-1$ }; List symbols = new ArrayList(); Modified: trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/test/java/org/teiid/query/resolver/TestResolver.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -798,8 +798,8 @@ helpResolveException("SELECT abc(e1) FROM pm1.g1", "Error Code:ERR.015.008.0039 Message:The function 'abc(e1)' is an unknown form. Check that the function name and number of arguments is correct."); //$NON-NLS-1$ //$NON-NLS-2$ } - @Test public void testConversionNotPossible() { - helpResolveException("SELECT dayofmonth('2002-01-01') FROM pm1.g1", "Error Code:ERR.015.008.0040 Message:The function 'dayofmonth('2002-01-01')' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit type conversions."); //$NON-NLS-1$ //$NON-NLS-2$ + @Test public void testConversionPossible() { + helpResolve("SELECT dayofmonth('2002-01-01') FROM pm1.g1"); //$NON-NLS-1$ //$NON-NLS-2$ } @Test public void testResolveParameters() throws Exception { @@ -1213,34 +1213,7 @@ * projected symbol of the subquery. */ @Test public void testSubQueryINClauseNoConversionFails(){ - //select e1 from pm1.g1 where e1 in (select e2 from pm4.g1) - - //sub command - Select innerSelect = new Select(); - ElementSymbol e2inner = new ElementSymbol("e2"); //$NON-NLS-1$ - innerSelect.addSymbol(e2inner); - From innerFrom = new From(); - GroupSymbol pm4g1 = new GroupSymbol("pm4.g1"); //$NON-NLS-1$ - innerFrom.addGroup(pm4g1); - Query innerQuery = new Query(); - innerQuery.setSelect(innerSelect); - innerQuery.setFrom(innerFrom); - - //outer command - Select outerSelect = new Select(); - ElementSymbol e1 = new ElementSymbol("e1"); //$NON-NLS-1$ - outerSelect.addSymbol(e1); - From outerFrom = new From(); - GroupSymbol pm1g1 = new GroupSymbol("pm1.g1"); //$NON-NLS-1$ - outerFrom.addGroup(pm1g1); - SubquerySetCriteria crit = new SubquerySetCriteria(e1, innerQuery); - Query outerQuery = new Query(); - outerQuery.setSelect(outerSelect); - outerQuery.setFrom(outerFrom); - outerQuery.setCriteria(crit); - - //test - this.helpResolveFails(outerQuery); + helpResolveException("select e1 from pm1.g1 where e1 in (select e2 from pm4.g1)"); } @Test public void testSubQueryINClauseTooManyColumns(){ @@ -2246,7 +2219,7 @@ String sql = "SELECT CASE WHEN x > 0 THEN 1.0 ELSE 2.0 END FROM (SELECT e2 AS x FROM pm1.g1) AS g"; //$NON-NLS-1$ Command c = helpResolve(sql); assertEquals(sql, c.toString()); - verifyProjectedTypes(c, new Class[] { Double.class }); + verifyProjectedTypes(c, new Class[] { BigDecimal.class }); } @@ -2434,10 +2407,10 @@ helpResolveException(sql); } - @Test public void testPowerWithLong_Fails() throws Exception { + @Test public void testPowerWithLong() throws Exception { String sql = "SELECT power(10, 999999999999)"; //$NON-NLS-1$ - helpResolveException(sql); + helpResolve(sql); } @Test public void testUpdateError() { Modified: trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -1108,7 +1108,7 @@ } @Test public void testRewiteCompoundCriteria() { - helpTestRewriteCriteria("(pm1.g1.e1 = 1 and pm1.g1.e2 = 2) and (pm1.g1.e3 = 1 and pm1.g1.e4 = 2)", "(pm1.g1.e1 = '1') AND (pm1.g1.e2 = 2) AND (pm1.g1.e3 = TRUE) AND (pm1.g1.e4 = 2.0)"); //$NON-NLS-1$ //$NON-NLS-2$ + helpTestRewriteCriteria("(pm1.g1.e1 = 1 and pm1.g1.e2 = 2) and (pm1.g1.e3 = 1 and pm1.g1.e4 = 2.0e0)", "(pm1.g1.e1 = '1') AND (pm1.g1.e2 = 2) AND (pm1.g1.e3 = TRUE) AND (pm1.g1.e4 = 2.0e0)"); //$NON-NLS-1$ //$NON-NLS-2$ } @Test public void testRewriteWhile() throws Exception { Modified: trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java =================================================================== --- trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java 2011-11-30 19:08:23 UTC (rev 3709) +++ trunk/test-integration/common/src/test/java/org/teiid/dqp/internal/process/TestTPCR.java 2011-11-30 22:17:23 UTC (rev 3710) @@ -58,8 +58,8 @@ HardcodedDataManager dataMgr = new HardcodedDataManager(); - List[] expected = - new List[] { Arrays.asList(new Object[] { new Double(2456423.0), new BigDecimal("406181.0111"), TimestampUtil.createDate(95, 2, 5), new Double(0.0) }), //$NON-NLS-1$ + List<?>[] expected = + new List<?>[] { Arrays.asList(new Object[] { new Double(2456423.0), new BigDecimal("406181.0111"), TimestampUtil.createDate(95, 2, 5), new Double(0.0) }), //$NON-NLS-1$ Arrays.asList(new Object[] { new Double(3459808.0), new BigDecimal("405838.6989"), TimestampUtil.createDate(95, 2, 4), new Double(0.0) }), //$NON-NLS-1$ Arrays.asList(new Object[] { new Double(492164.0), new BigDecimal("390324.0610"), TimestampUtil.createDate(95, 1, 19), new Double(0.0) }) }; //$NON-NLS-1$ @@ -83,8 +83,8 @@ finder.addCapabilities("TPCR_Ora", CapabilitiesConverter.convertCapabilities(new OracleExecutionFactory())); //$NON-NLS-1$ HardcodedDataManager dataMgr = new HardcodedDataManager(); - List[] expected = - new List[] { Arrays.asList(new Object[] { new Integer(5) } ) }; + List<?>[] expected = + new List<?>[] { Arrays.asList(new Object[] { new Integer(5) } ) }; dataMgr.addData("SELECT COUNT(*) FROM TPCR_Ora.CUSTOMER AS g_0 LEFT OUTER JOIN TPCR_Ora.ORDERS AS g_1 ON g_0.C_CUSTKEY = g_1.O_CUSTKEY WHERE (g_1.O_ORDERKEY IS NULL) OR ((g_1.O_ORDERDATE < {ts'1992-01-02 00:00:00.0'}) AND (g_0.C_ACCTBAL > 0))", //$NON-NLS-1$ expected); @@ -117,21 +117,21 @@ HardcodedDataManager dataMgr = new HardcodedDataManager(); - List[] oracleExpected = - new List[] { Arrays.asList(new Object[] { new Long(5), "Bill", "101 Fake St.", "392839283", "21.12" } ), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ + List<?>[] oracleExpected = + new List<?>[] { Arrays.asList(new Object[] { new Long(5), "Bill", "101 Fake St.", "392839283", "21.12" } ), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ Arrays.asList(new Object[] { new Long(6), "Stu", "102 Fake St.", "385729385", "51.50" } )}; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ dataMgr.addData("SELECT g_0.C_CUSTKEY AS c_0, g_0.C_NAME AS c_1, g_0.C_ADDRESS AS c_2, g_0.C_PHONE AS c_3, g_0.C_ACCTBAL AS c_4 FROM TPCR_Ora.CUSTOMER AS g_0 WHERE g_0.C_ACCTBAL > 50 ORDER BY c_0 NULLS FIRST", //$NON-NLS-1$ oracleExpected); - List[] sqlServerExpected = - new List[] { Arrays.asList(new Object[] { new Integer(5), new Integer(12), new Long(5) } ), + List<?>[] sqlServerExpected = + new List<?>[] { Arrays.asList(new Object[] { new Integer(5), new Integer(12), new Long(5) } ), Arrays.asList(new Object[] { new Integer(5), new Integer(13), new Long(5) } )}; dataMgr.addData("SELECT g_0.O_CUSTKEY AS c_0, g_0.O_ORDERKEY AS c_1, g_0.O_CUSTKEY AS c_2 FROM TPCR_SQLS.ORDERS AS g_0 WHERE (g_0.O_ORDERDATE < {ts'1992-01-02 00:00:00.0'}) AND (g_0.O_CUSTKEY IN (5, 6)) ORDER BY c_2", //$NON-NLS-1$ sqlServerExpected); - List[] expected = - new List[] { Arrays.asList(new Object[] { new Long(5), "Bill", "101 Fake St.", "392839283", "21.12", new Integer(12) } ), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ + List<?>[] expected = + new List<?>[] { Arrays.asList(new Object[] { new Long(5), "Bill", "101 Fake St.", "392839283", "21.12", new Integer(12) } ), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ Arrays.asList(new Object[] { new Long(5), "Bill", "101 Fake St.", "392839283", "21.12", new Integer(13) } ), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ Arrays.asList(new Object[] { new Long(6), "Stu", "102 Fake St.", "385729385", "51.50", null } )}; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ @@ -157,21 +157,21 @@ HardcodedDataManager dataMgr = new HardcodedDataManager(); - List[] oracleExpected = - new List[] { Arrays.asList(new Object[] { new Long(5), "Bill", "101 Fake St.", "392839283", "51.12" } ), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ + List<?>[] oracleExpected = + new List<?>[] { Arrays.asList(new Object[] { new Long(5), "Bill", "101 Fake St.", "392839283", "51.12" } ), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ Arrays.asList(new Object[] { new Long(6), "Stu", "102 Fake St.", "385729385", "51.50" } )}; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ dataMgr.addData("SELECT g_0.C_CUSTKEY AS c_0, g_0.C_NAME AS c_1, g_0.C_ADDRESS AS c_2, g_0.C_PHONE AS c_3, g_0.C_ACCTBAL AS c_4 FROM TPCR_Ora.CUSTOMER AS g_0 WHERE g_0.C_ACCTBAL > 50 ORDER BY c_0 NULLS FIRST", //$NON-NLS-1$ oracleExpected); - List[] sqlServerExpected = - new List[] { Arrays.asList(new Object[] { new Integer(5), new Integer(12), new Long(5) } ), + List<?>[] sqlServerExpected = + new List<?>[] { Arrays.asList(new Object[] { new Integer(5), new Integer(12), new Long(5) } ), Arrays.asList(new Object[] { new Integer(5), new Integer(13), new Long(5) } )}; dataMgr.addData("SELECT g_0.O_CUSTKEY AS c_0, g_0.O_ORDERKEY AS c_1, g_0.O_CUSTKEY AS c_2 FROM TPCR_SQLS.ORDERS AS g_0 WHERE (g_0.O_ORDERDATE < {ts'1992-01-02 00:00:00.0'}) AND (g_0.O_CUSTKEY IN (5, 6)) ORDER BY c_2", //$NON-NLS-1$ sqlServerExpected); - List[] expected = - new List[] { Arrays.asList(new Object[] { new Long(5), "Bill", "101 Fake St.", "392839283", "51.12", new Integer(12) } ), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ + List<?>[] expected = + new List<?>[] { Arrays.asList(new Object[] { new Long(5), "Bill", "101 Fake St.", "392839283", "51.12", new Integer(12) } ), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ Arrays.asList(new Object[] { new Long(5), "Bill", "101 Fake St.", "392839283", "51.12", new Integer(13) } ), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ Arrays.asList(new Object[] { new Long(6), "Stu", "102 Fake St.", "385729385", "51.50", null } )}; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ @@ -191,7 +191,7 @@ ProcessorPlan plan = TestOptimizer.helpPlan("SELECT custsale.cntrycode, COUNT(*) AS numcust, SUM(c_acctbal) AS totacctbal FROM (SELECT left(C_PHONE, 2) AS cntrycode, CUSTOMER.C_ACCTBAL FROM CUSTOMER WHERE (left(C_PHONE, 2) IN ('13','31','23','29','30','18','17')) AND (CUSTOMER.C_ACCTBAL > (SELECT AVG(CUSTOMER.C_ACCTBAL) FROM CUSTOMER WHERE (CUSTOMER.C_ACCTBAL > 0.0) AND (left(C_PHONE, 2) IN ('13','31','23','29','30','18','17')))) AND (NOT (EXISTS (SELECT * FROM ORDERS WHERE O_CUSTKEY = C_CUSTKEY)))) AS custsale GROUP BY custsale.cntrycode ORDER BY custsale.cntrycode", //$NON-NLS-1$ METADATA, null, finder, - new String[] {"SELECT left(g_0.C_PHONE, 2) AS c_0, COUNT(*) AS c_1, SUM(g_0.C_ACCTBAL) AS c_2 FROM TPCR_Oracle_9i.CUSTOMER AS g_0 WHERE (left(g_0.C_PHONE, 2) IN ('13', '31', '23', '29', '30', '18', '17')) AND (g_0.C_ACCTBAL > (SELECT AVG(g_1.C_ACCTBAL) FROM TPCR_Oracle_9i.CUSTOMER AS g_1 WHERE (g_1.C_ACCTBAL > 0E-15) AND (left(g_1.C_PHONE, 2) IN ('13', '31', '23', '29', '30', '18', '17')))) AND (NOT EXISTS (SELECT 1 FROM TPCR_Oracle_9i.ORDERS AS g_2 WHERE g_2.O_CUSTKEY = g_0.C_CUSTKEY)) GROUP BY left(g_0.C_PHONE, 2) ORDER BY c_0 NULLS FIRST"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + new String[] {"SELECT left(g_0.C_PHONE, 2) AS c_0, COUNT(*) AS c_1, SUM(g_0.C_ACCTBAL) AS c_2 FROM TPCR_Oracle_9i.CUSTOMER AS g_0 WHERE (left(g_0.C_PHONE, 2) IN ('13', '31', '23', '29', '30', '18', '17')) AND (g_0.C_ACCTBAL > (SELECT AVG(g_1.C_ACCTBAL) FROM TPCR_Oracle_9i.CUSTOMER AS g_1 WHERE (g_1.C_ACCTBAL > 0.0) AND (left(g_1.C_PHONE, 2) IN ('13', '31', '23', '29', '30', '18', '17')))) AND (NOT EXISTS (SELECT 1 FROM TPCR_Oracle_9i.ORDERS AS g_2 WHERE g_2.O_CUSTKEY = g_0.C_CUSTKEY)) GROUP BY left(g_0.C_PHONE, 2) ORDER BY c_0 NULLS FIRST"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }