Author: shawkins Date: 2010-10-13 12:42:50 -0400 (Wed, 13 Oct 2010) New Revision: 2646 Modified: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/access/AccessExecutionFactory.java branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseConvertModifier.java branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java branches/7.1.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml Log: TEIID-1303 fix for sybase timestamp to string and full outer join handling. also updated literal handling to work with JTDS Modified: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java 2010-10-13 15:46:43 UTC (rev 2645) +++ branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java 2010-10-13 16:42:50 UTC (rev 2646) @@ -603,7 +603,7 @@ */ public String translateLiteralTime(Time timeValue) { if (!hasTimeType()) { - return "{ts '1970-01-01 " + formatDateValue(timeValue) + "'}"; //$NON-NLS-1$ //$NON-NLS-2$ + return translateLiteralTimestamp(new Timestamp(timeValue.getTime())); } return "{t '" + formatDateValue(timeValue) + "'}"; //$NON-NLS-1$ //$NON-NLS-2$ } Modified: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/access/AccessExecutionFactory.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/access/AccessExecutionFactory.java 2010-10-13 15:46:43 UTC (rev 2645) +++ branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/access/AccessExecutionFactory.java 2010-10-13 16:42:50 UTC (rev 2646) @@ -38,6 +38,7 @@ public AccessExecutionFactory() { setSupportsOrderBy(false); + setDatabaseVersion("2003"); //$NON-NLS-1$ } @Override Modified: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java 2010-10-13 15:46:43 UTC (rev 2645) +++ branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java 2010-10-13 16:42:50 UTC (rev 2646) @@ -24,6 +24,7 @@ */ package org.teiid.translator.jdbc.sqlserver; +import java.sql.Time; import java.util.ArrayList; import java.util.Arrays; import java.util.List; @@ -204,5 +205,9 @@ public boolean booleanNullable() { return true; } - + + public boolean hasTimeType() { + return getDatabaseVersion().compareTo(V_2005) >= 0; + } + } Modified: branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java 2010-10-13 15:46:43 UTC (rev 2645) +++ branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java 2010-10-13 16:42:50 UTC (rev 2646) @@ -25,11 +25,14 @@ package org.teiid.translator.jdbc.sybase; import java.sql.CallableStatement; +import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.Timestamp; import java.util.ArrayList; import java.util.Arrays; +import java.util.LinkedList; import java.util.List; import org.teiid.language.Command; @@ -37,10 +40,10 @@ import org.teiid.language.Limit; import org.teiid.language.OrderBy; import org.teiid.language.SetQuery; +import org.teiid.translator.ExecutionContext; +import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; import org.teiid.translator.TranslatorException; -import org.teiid.translator.ExecutionContext; -import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.TypeFacility; import org.teiid.translator.jdbc.AliasModifier; import org.teiid.translator.jdbc.ConvertModifier; @@ -60,6 +63,7 @@ public SybaseExecutionFactory() { setDatabaseVersion(TWELVE_5); + setSupportsFullOuterJoins(false); } public void start() throws TranslatorException { @@ -173,13 +177,13 @@ return Arrays.asList("stuff(stuff(convert(varchar, ", function.getParameters().get(0), ", 102), 5, 1, '-'), 8, 1, '-')"); //$NON-NLS-1$ //$NON-NLS-2$ } - /** - * Written to only support version 15 - * @param function - * @return - */ + //TODO: this looses the milliseconds protected List<?> convertTimestampToString(Function function) { - return Arrays.asList("stuff(convert(varchar, ", function.getParameters().get(0), ", 123), 11, 1, ' ')"); //$NON-NLS-1$ //$NON-NLS-2$ + LinkedList<Object> result = new LinkedList<Object>(); + result.addAll(convertDateToString(function)); + result.add(' '); + result.addAll(convertTimeToString(function)); + return result; } @Override @@ -365,4 +369,15 @@ } super.bindValue(stmt, param, paramType, i); } + + @Override + public String translateLiteralTimestamp(Timestamp timestampValue) { + return "CAST('" + formatDateValue(timestampValue) +"' AS DATETIME)"; //$NON-NLS-1$ //$NON-NLS-2$ + } + + @Override + public String translateLiteralDate(Date dateValue) { + return "CAST('" + formatDateValue(dateValue) +"' AS DATE)"; //$NON-NLS-1$ //$NON-NLS-2$ + } + } Modified: branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2010-10-13 15:46:43 UTC (rev 2645) +++ branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2010-10-13 16:42:50 UTC (rev 2646) @@ -559,7 +559,7 @@ helpTestVisitor(getTestVDB(), "select {t '13:59:59'} FROM parts", //$NON-NLS-1$ null, - "SELECT {ts '1970-01-01 13:59:59'} FROM PARTS"); //$NON-NLS-1$ + "SELECT to_date('1970-01-01 13:59:59', 'YYYY-MM-DD HH24:MI:SS') FROM PARTS"); //$NON-NLS-1$ } @Test public void testTimestampLiteral() throws Exception { Modified: branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseConvertModifier.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseConvertModifier.java 2010-10-13 15:46:43 UTC (rev 2645) +++ branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseConvertModifier.java 2010-10-13 16:42:50 UTC (rev 2646) @@ -105,7 +105,7 @@ LANG_FACTORY.createLiteral("date", String.class)}, //$NON-NLS-1$ java.sql.Date.class); - helpGetString1(func, "cast(stuff(stuff(convert(varchar, {ts '1989-03-03 07:08:12.0'}, 102), 5, 1, '-'), 8, 1, '-') AS datetime)"); //$NON-NLS-1$ + helpGetString1(func, "cast(stuff(stuff(convert(varchar, CAST('1989-03-03 07:08:12.0' AS DATETIME), 102), 5, 1, '-'), 8, 1, '-') AS datetime)"); //$NON-NLS-1$ } /********************END of cast(date AS INPUT) ******************/ @@ -129,7 +129,7 @@ LANG_FACTORY.createLiteral("time", String.class)}, //$NON-NLS-1$ java.sql.Time.class); - helpGetString1(func, "cast('1970-01-01 ' + convert(varchar, {ts '1989-03-03 07:08:12.0'}, 8) AS datetime)"); //$NON-NLS-1$ + helpGetString1(func, "cast('1970-01-01 ' + convert(varchar, CAST('1989-03-03 07:08:12.0' AS DATETIME), 8) AS datetime)"); //$NON-NLS-1$ } /********************END of cast(time AS INPUT) ******************/ @@ -152,7 +152,7 @@ LANG_FACTORY.createLiteral("timestamp", String.class)}, //$NON-NLS-1$ java.sql.Timestamp.class); - helpGetString1(func, "{ts '1970-01-01 12:02:03'}"); //$NON-NLS-1$ + helpGetString1(func, "CAST('1970-01-01 12:02:03.0' AS DATETIME)"); //$NON-NLS-1$ } @Test public void testDateToTimestamp() throws Exception { @@ -162,7 +162,7 @@ LANG_FACTORY.createLiteral("timestamp", String.class)}, //$NON-NLS-1$ java.sql.Timestamp.class); - helpGetString1(func, "{d '1989-03-03'}"); //$NON-NLS-1$ + helpGetString1(func, "CAST('1989-03-03' AS DATE)"); //$NON-NLS-1$ } /********************END of cast(timestamp AS INPUT) ******************/ @@ -185,7 +185,7 @@ LANG_FACTORY.createLiteral("string", String.class)}, //$NON-NLS-1$ String.class); - helpGetString1(func, "stuff(convert(varchar, {ts '2003-11-01 12:05:02.0'}, 123), 11, 1, ' ')"); //$NON-NLS-1$ + helpGetString1(func, "stuff(stuff(convert(varchar, CAST('2003-11-01 12:05:02.0' AS DATETIME), 102), 5, 1, '-'), 8, 1, '-') convert(varchar, CAST('2003-11-01 12:05:02.0' AS DATETIME), 8)"); //$NON-NLS-1$ } @Test public void testDateToString() throws Exception { @@ -196,7 +196,7 @@ LANG_FACTORY.createLiteral("string", String.class)}, //$NON-NLS-1$ String.class); - helpGetString1(func, "stuff(stuff(convert(varchar, {d '2003-11-01'}, 102), 5, 1, '-'), 8, 1, '-')"); //$NON-NLS-1$ + helpGetString1(func, "stuff(stuff(convert(varchar, CAST('2003-11-01' AS DATE), 102), 5, 1, '-'), 8, 1, '-')"); //$NON-NLS-1$ } @Test public void testTimeToString() throws Exception { @@ -207,7 +207,7 @@ LANG_FACTORY.createLiteral("string", String.class)}, //$NON-NLS-1$ String.class); - helpGetString1(func, "convert(varchar, {ts '1970-01-01 03:10:01'}, 8)"); //$NON-NLS-1$ + helpGetString1(func, "convert(varchar, CAST('1970-01-01 03:10:01.0' AS DATETIME), 8)"); //$NON-NLS-1$ } @Test public void testBigDecimalToString() throws Exception { Modified: branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java =================================================================== --- branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2010-10-13 15:46:43 UTC (rev 2645) +++ branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2010-10-13 16:42:50 UTC (rev 2646) @@ -189,21 +189,21 @@ public void testDateLiteral() { helpTestVisitor(getTestVDB(), "select {d '2002-12-31'} FROM parts", //$NON-NLS-1$ - "SELECT {d '2002-12-31'} FROM PARTS"); //$NON-NLS-1$ + "SELECT CAST('2002-12-31' AS DATE) FROM PARTS"); //$NON-NLS-1$ } @Test public void testTimeLiteral() { helpTestVisitor(getTestVDB(), "select {t '13:59:59'} FROM parts", //$NON-NLS-1$ - "SELECT {ts '1970-01-01 13:59:59'} FROM PARTS"); //$NON-NLS-1$ + "SELECT CAST('1970-01-01 13:59:59.0' AS DATETIME) FROM PARTS"); //$NON-NLS-1$ } @Test public void testTimestampLiteral() { helpTestVisitor(getTestVDB(), "select {ts '2002-12-31 13:59:59'} FROM parts", //$NON-NLS-1$ - "SELECT {ts '2002-12-31 13:59:59.0'} FROM PARTS"); //$NON-NLS-1$ + "SELECT CAST('2002-12-31 13:59:59.0' AS DATETIME) FROM PARTS"); //$NON-NLS-1$ } @Test Modified: branches/7.1.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml =================================================================== --- branches/7.1.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-10-13 15:46:43 UTC (rev 2645) +++ branches/7.1.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-10-13 16:42:50 UTC (rev 2646) @@ -1206,8 +1206,9 @@ <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> <example> <title>Example Subquery in WHERE Using EXISTS</title> - <programlisting>SELECT a FROM X WHERE EXISTS (SELECT b, c FROM Y WHERE c=3)</programlisting> + <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>