Author: shawkins Date: 2010-07-07 21:49:34 -0400 (Wed, 07 Jul 2010) New Revision: 2331 Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/ConcatFunctionModifier.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java Log: TEIID-1147 fix for sybase concat Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/ConcatFunctionModifier.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/ConcatFunctionModifier.java 2010-07-07 17:10:31 UTC (rev 2330) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/ConcatFunctionModifier.java 2010-07-08 01:49:34 UTC (rev 2331) @@ -86,7 +86,7 @@ return Arrays.asList(langFactory.createSearchedCaseExpression(cases, function, TypeFacility.RUNTIME_TYPES.STRING)); } - private boolean isNotNull(Expression expr) { + public static boolean isNotNull(Expression expr) { if (expr instanceof Literal) { Literal literal = (Literal)expr; return literal.getValue() != null; Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java 2010-07-07 17:10:31 UTC (rev 2330) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java 2010-07-08 01:49:34 UTC (rev 2331) @@ -188,5 +188,10 @@ public boolean supportsAggregatesEnhancedNumeric() { return true; } - + + @Override + public boolean nullPlusNonNullIsNull() { + return true; + } + } Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java 2010-07-07 17:10:31 UTC (rev 2330) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java 2010-07-08 01:49:34 UTC (rev 2331) @@ -43,6 +43,7 @@ import org.teiid.translator.jdbc.FunctionModifier; import org.teiid.translator.jdbc.JDBCExecutionFactory; import org.teiid.translator.jdbc.ModFunctionModifier; +import org.teiid.translator.jdbc.oracle.ConcatFunctionModifier; @Translator(name="sybase") @@ -59,8 +60,18 @@ public void start() throws TranslatorException { super.start(); - registerFunctionModifier(SourceSystemFunctions.MOD, new ModFunctionModifier("%", getLanguageFactory())); //$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.CONCAT, new AliasModifier("+")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.MOD, new ModFunctionModifier("%", getLanguageFactory())); //$NON-NLS-1$ + if (nullPlusNonNullIsNull()) { + registerFunctionModifier(SourceSystemFunctions.CONCAT, new AliasModifier("+")); //$NON-NLS-1$ + } else { + registerFunctionModifier(SourceSystemFunctions.CONCAT, new ConcatFunctionModifier(getLanguageFactory()) { + @Override + public List<?> translate(Function function) { + function.setName("+"); //$NON-NLS-1$ + return super.translate(function); + } + }); + } registerFunctionModifier(SourceSystemFunctions.LCASE, new AliasModifier("lower")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("isnull")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.UCASE, new AliasModifier("upper")); //$NON-NLS-1$ @@ -100,8 +111,18 @@ public List<?> translate(Function function) { List<Object> result = new ArrayList<Object>(); result.add("cast("); //$NON-NLS-1$ + boolean needsEnd = false; + if (!nullPlusNonNullIsNull() && !ConcatFunctionModifier.isNotNull(function.getParameters().get(0))) { + result.add("CASE WHEN "); //$NON-NLS-1$ + result.add(function.getParameters().get(0)); + result.add(" IS NOT NULL THEN "); //$NON-NLS-1$ + needsEnd = true; + } result.add("'1970-01-01 ' + "); //$NON-NLS-1$ result.addAll(convertTimeToString(function)); + if (needsEnd) { + result.add(" END"); //$NON-NLS-1$ + } result.add(" AS datetime)"); //$NON-NLS-1$ return result; } @@ -302,4 +323,8 @@ public boolean supportsAggregatesEnhancedNumeric() { return getDatabaseVersion().compareTo(FIFTEEN_0_2) >= 0; } + + public boolean nullPlusNonNullIsNull() { + return false; + } } 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 2010-07-07 17:10:31 UTC (rev 2330) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2010-07-08 01:49:34 UTC (rev 2331) @@ -80,7 +80,7 @@ @Test public void testConcatFunction() { String input = "SELECT concat(part_name, 'b') FROM PARTS"; //$NON-NLS-1$ - String output = "SELECT (PARTS.PART_NAME + 'b') FROM PARTS"; //$NON-NLS-1$ + String output = "SELECT CASE WHEN PARTS.PART_NAME IS NULL THEN NULL ELSE (PARTS.PART_NAME + 'b') END FROM PARTS"; //$NON-NLS-1$ helpTestVisitor(getTestVDB(), input, @@ -146,6 +146,15 @@ output); } + @Test public void testConvertTimestampTime() { + String input = "SELECT convert(TIMESTAMPVALUE, time) FROM BQT1.SMALLA"; //$NON-NLS-1$ + String output = "SELECT cast(CASE WHEN SmallA.TimestampValue IS NOT NULL THEN '1970-01-01 ' + convert(varchar, SmallA.TimestampValue, 8) END AS datetime) FROM SmallA"; //$NON-NLS-1$ + + helpTestVisitor(getBQTVDB(), + input, + output); + } + @Test public void testConvertFunctionChar() { String input = "SELECT convert(PART_NAME, char) FROM PARTS"; //$NON-NLS-1$