Author: shawkins Date: 2010-05-03 16:24:22 -0400 (Mon, 03 May 2010) New Revision: 2094 Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleCapabilities.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java trunk/engine/src/main/java/com/metamatrix/query/validator/AggregateValidationVisitor.java trunk/engine/src/test/java/com/metamatrix/query/validator/TestValidator.java Log: fixing ctc issues: date handling regress, distinct agg validation, and adding cot oracle pushdown Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleCapabilities.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleCapabilities.java 2010-05-03 19:31:05 UTC (rev 2093) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleCapabilities.java 2010-05-03 20:24:22 UTC (rev 2094) @@ -26,6 +26,7 @@ import java.util.*; +import org.teiid.connector.api.SourceSystemFunctions; import org.teiid.connector.jdbc.JDBCCapabilities; @@ -47,7 +48,8 @@ supportedFunctions.add("ASIN"); //$NON-NLS-1$ supportedFunctions.add("ATAN"); //$NON-NLS-1$ supportedFunctions.add("ATAN2"); //$NON-NLS-1$ - supportedFunctions.add("COS"); //$NON-NLS-1$ + supportedFunctions.add("COS"); //$NON-NLS-1$ + supportedFunctions.add(SourceSystemFunctions.COT); supportedFunctions.add("EXP"); //$NON-NLS-1$ supportedFunctions.add("FLOOR"); //$NON-NLS-1$ supportedFunctions.add("CEILING"); //$NON-NLS-1$ Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2010-05-03 19:31:05 UTC (rev 2093) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2010-05-03 20:24:22 UTC (rev 2094) @@ -39,6 +39,7 @@ import org.teiid.connector.api.ConnectorException; import org.teiid.connector.api.ExecutionContext; import org.teiid.connector.api.SourceSystemFunctions; +import org.teiid.connector.api.TypeFacility; import org.teiid.connector.jdbc.JDBCManagedConnectionFactory; import org.teiid.connector.jdbc.JDBCPlugin; import org.teiid.connector.jdbc.translator.AliasModifier; @@ -50,10 +51,12 @@ import org.teiid.connector.language.ColumnReference; import org.teiid.connector.language.Command; import org.teiid.connector.language.DerivedColumn; +import org.teiid.connector.language.Expression; import org.teiid.connector.language.ExpressionValueSource; import org.teiid.connector.language.Function; import org.teiid.connector.language.Insert; import org.teiid.connector.language.Limit; +import org.teiid.connector.language.Literal; import org.teiid.connector.language.NamedTable; import org.teiid.connector.language.QueryExpression; import org.teiid.connector.language.Select; @@ -103,7 +106,14 @@ registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new AliasModifier("substr"));//$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory())); registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory())); - registerFunctionModifier(SourceSystemFunctions.CONCAT, new ConcatFunctionModifier(getLanguageFactory())); + registerFunctionModifier(SourceSystemFunctions.CONCAT, new ConcatFunctionModifier(getLanguageFactory())); + registerFunctionModifier(SourceSystemFunctions.COT, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + function.setName(SourceSystemFunctions.TAN); + return Arrays.asList(getLanguageFactory().createFunction(SourceSystemFunctions.DIVIDE_OP, new Expression[] {new Literal(1, TypeFacility.RUNTIME_TYPES.INTEGER), function}, TypeFacility.RUNTIME_TYPES.DOUBLE)); + } + }); //spatial functions registerFunctionModifier(RELATE, new OracleSpatialFunctionModifier()); @@ -130,7 +140,22 @@ }); convertModifier.addConvert(FunctionModifier.DATE, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", "YYYY-MM-DD")); //$NON-NLS-1$ //$NON-NLS-2$ convertModifier.addConvert(FunctionModifier.TIME, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", "HH24:MI:SS")); //$NON-NLS-1$ //$NON-NLS-2$ - convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", "YYYY-MM-DD HH24:MI:SS.FF")); //$NON-NLS-1$ //$NON-NLS-2$ + convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.STRING, new FunctionModifier() { + @Override + public List<?> translate(Function function) { + //if column and type is date, just use date format + Expression ex = function.getParameters().get(0); + String format = "YYYY-MM-DD HH24:MI:SS.FF"; //$NON-NLS-1$ + if (ex instanceof ColumnReference) { + if ("date".equals(((ColumnReference)ex).getMetadataObject().getNativeType())) { //$NON-NLS-1$ + format = "YYYY-MM-DD HH24:MI:SS"; //$NON-NLS-1$ + } + } else if (!(ex instanceof Function)) { + ex = ConvertModifier.createConvertFunction(getLanguageFactory(), function.getParameters().get(0), TypeFacility.RUNTIME_NAMES.TIMESTAMP); + } + return Arrays.asList("to_char(", ex, ", '"+format+"')"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + } + }); convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.DATE, new ConvertModifier.FormatModifier("to_date", "YYYY-MM-DD")); //$NON-NLS-1$ //$NON-NLS-2$ convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIME, new ConvertModifier.FormatModifier("to_date", "HH24:MI:SS")); //$NON-NLS-1$ //$NON-NLS-2$ convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIMESTAMP, new ConvertModifier.FormatModifier("to_timestamp", "YYYY-MM-DD HH24:MI:SS.FF")); //$NON-NLS-1$ //$NON-NLS-2$ Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java 2010-05-03 19:31:05 UTC (rev 2093) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java 2010-05-03 20:24:22 UTC (rev 2094) @@ -140,6 +140,17 @@ input, output, TRANSLATOR); } + + /** + * here we use the date form of the conversion + */ + @Test public void testConversion6a() throws Exception { + String input = "SELECT convert(timestampvalue, string) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT to_char(SmallishA.timestampvalue, 'YYYY-MM-DD HH24:MI:SS') FROM SmallishA"; //$NON-NLS-1$ + + helpTestVisitor(getOracleSpecificMetadata(), input, EMPTY_CONTEXT, null, output); + } + @Test public void testConversion8() throws Exception { String input = "SELECT nvl(INTNUM, 'otherString') FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT nvl(to_char(SmallA.IntNum), 'otherString') FROM SmallA"; //$NON-NLS-1$ @@ -705,14 +716,17 @@ String[] elemNames = new String[] { "DoubleNum", //$NON-NLS-1$ "ID", //$NON-NLS-1$ + "timestampvalue", //$NON-NLS-1$ }; String[] elemTypes = new String[] { DataTypeManager.DefaultDataTypes.DOUBLE, DataTypeManager.DefaultDataTypes.INTEGER, + DataTypeManager.DefaultDataTypes.TIMESTAMP, }; List<Column> cols = RealMetadataFactory.createElements(table, elemNames, elemTypes); cols.get(1).setAutoIncremented(true); cols.get(1).setNameInSource("ID:SEQUENCE=MYSEQUENCE.nextVal"); //$NON-NLS-1$ + cols.get(2).setNativeType("date"); //$NON-NLS-1$ RealMetadataFactory.createElements(dual, new String[] {"something"}, new String[] {DataTypeManager.DefaultDataTypes.STRING}); //$NON-NLS-1$ CompositeMetadataStore store = new CompositeMetadataStore(metadataStore); @@ -757,9 +771,15 @@ String input = "(select intkey from bqt1.smalla limit 50, 100) union select intnum from bqt1.smalla order by intkey"; //$NON-NLS-1$ String output = "SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT g_1.IntKey AS c_0 FROM SmallA g_1) VIEW_FOR_LIMIT WHERE ROWNUM <= 150) WHERE ROWNUM_ > 50 UNION SELECT g_0.IntNum AS c_0 FROM SmallA g_0 ORDER BY c_0 NULLS FIRST"; //$NON-NLS-1$ - CommandBuilder commandBuilder = new CommandBuilder(FakeMetadataFactory.exampleBQTCached()); + CommandBuilder commandBuilder = new CommandBuilder(FakeMetadataFactory.exampleBQTCached()); Command obj = commandBuilder.getCommand(input, true, true); this.helpTestVisitor(obj, EMPTY_CONTEXT, null, output); } + + @Test public void testCot() throws Exception { + String sql = "select cot(doublenum) from BQT1.Smalla"; //$NON-NLS-1$ + String expected = "SELECT (1 / tan(SmallA.DoubleNum)) FROM SmallA"; //$NON-NLS-1$ + helpTestVisitor(FakeMetadataFactory.exampleBQTCached(), sql, EMPTY_CONTEXT, null, expected); + } } Modified: trunk/engine/src/main/java/com/metamatrix/query/validator/AggregateValidationVisitor.java =================================================================== --- trunk/engine/src/main/java/com/metamatrix/query/validator/AggregateValidationVisitor.java 2010-05-03 19:31:05 UTC (rev 2093) +++ trunk/engine/src/main/java/com/metamatrix/query/validator/AggregateValidationVisitor.java 2010-05-03 20:24:22 UTC (rev 2094) @@ -75,7 +75,7 @@ if((aggregateFunction.equals(SQLReservedWords.SUM) || aggregateFunction.equals(SQLReservedWords.AVG)) && obj.getType() == null) { handleValidationError(QueryPlugin.Util.getString(ErrorMessageKeys.VALIDATOR_0041, new Object[] {aggregateFunction, obj}), obj); } - if((aggregateFunction.equals(SQLReservedWords.MIN) || aggregateFunction.equals(SQLReservedWords.MAX)) && DataTypeManager.isNonComparable(DataTypeManager.getDataTypeName(aggExp.getType()))) { + if((obj.isDistinct() || aggregateFunction.equals(SQLReservedWords.MIN) || aggregateFunction.equals(SQLReservedWords.MAX)) && DataTypeManager.isNonComparable(DataTypeManager.getDataTypeName(aggExp.getType()))) { handleValidationError(QueryPlugin.Util.getString("AggregateValidationVisitor.non_comparable", new Object[] {aggregateFunction, obj}), obj); //$NON-NLS-1$ } validateBelow = false; Modified: trunk/engine/src/test/java/com/metamatrix/query/validator/TestValidator.java =================================================================== --- trunk/engine/src/test/java/com/metamatrix/query/validator/TestValidator.java 2010-05-03 19:31:05 UTC (rev 2093) +++ trunk/engine/src/test/java/com/metamatrix/query/validator/TestValidator.java 2010-05-03 20:24:22 UTC (rev 2094) @@ -487,6 +487,11 @@ new String[] {"MAX(ObjectValue)"}, FakeMetadataFactory.exampleBQTCached() ); //$NON-NLS-1$ } + @Test public void testInvalidAggregate9() { + helpValidate("SELECT count(distinct ObjectValue) FROM BQT1.SmallA GROUP BY StringKey", //$NON-NLS-1$ + new String[] {"COUNT(DISTINCT ObjectValue)"}, FakeMetadataFactory.exampleBQTCached() ); //$NON-NLS-1$ + } + @Test public void testInvalidAggregateIssue190644() { helpValidate("SELECT e3 + 1 from pm1.g1 GROUP BY e2 + 1 HAVING e2 + 1 = 5", new String[] {"e3"}, FakeMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ }