Author: shawkins Date: 2010-02-17 21:45:34 -0500 (Wed, 17 Feb 2010) New Revision: 1839 Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LocateFunctionModifier.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/LocateFunctionModifier.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/postgresql/TestPostgreSQLTranslator.java Log: TEIID-988 correcting the locate position in the three argument form. Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LocateFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LocateFunctionModifier.java 2010-02-17 22:28:31 UTC (rev 1838) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/postgresql/LocateFunctionModifier.java 2010-02-18 02:45:34 UTC (rev 1839) @@ -29,6 +29,7 @@ import org.teiid.connector.language.IExpression; import org.teiid.connector.language.IFunction; import org.teiid.connector.language.ILanguageFactory; +import org.teiid.connector.language.ILiteral; public class LocateFunctionModifier extends org.teiid.connector.jdbc.translator.LocateFunctionModifier { @@ -44,8 +45,29 @@ parts.add("position("); //$NON-NLS-1$ parts.add(params.get(0)); parts.add(" in "); //$NON-NLS-1$ + boolean useSubStr = false; if (params.size() == 3) { + useSubStr = true; + if (params.get(2) instanceof ILiteral && ((ILiteral)params.get(2)).getValue() instanceof Integer) { + Integer value = (Integer)((ILiteral)params.get(2)).getValue(); + if (value > 1) { + ((ILiteral)params.get(2)).setValue(value - 1); + } else { + useSubStr = false; + } + } + } + if (useSubStr) { + parts.add(0, "("); //$NON-NLS-1$ parts.add(this.getLanguageFactory().createFunction("substr", params.subList(1, 3), TypeFacility.RUNTIME_TYPES.STRING)); //$NON-NLS-1$ + parts.add(")"); //$NON-NLS-1$ + parts.add(" + "); //$NON-NLS-1$ + if (params.get(2) instanceof ILiteral && ((ILiteral)params.get(2)).getValue() instanceof Integer) { + parts.add(params.get(2)); + } else { + parts.add(params.get(2)); + parts.add(" - 1"); //$NON-NLS-1$ + } } else { parts.add(params.get(1)); } Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/LocateFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/LocateFunctionModifier.java 2010-02-17 22:28:31 UTC (rev 1838) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/LocateFunctionModifier.java 2010-02-18 02:45:34 UTC (rev 1839) @@ -160,7 +160,7 @@ private IExpression ensurePositiveStartIndex(IExpression startIndex) { if (startIndex instanceof ILiteral) { ILiteral literal = (ILiteral)startIndex; - if (literal.getValue() != null && ((Integer)literal.getValue() < 1)) { + if (literal.getValue() instanceof Integer && ((Integer)literal.getValue() < 1)) { literal.setValue(1); } } else { Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/postgresql/TestPostgreSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/postgresql/TestPostgreSQLTranslator.java 2010-02-17 22:28:31 UTC (rev 1838) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/postgresql/TestPostgreSQLTranslator.java 2010-02-18 02:45:34 UTC (rev 1839) @@ -412,7 +412,7 @@ */ @Test public void testLocate() throws Exception { String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ - String output = "SELECT position(cast(SmallA.IntNum AS varchar(4000)) in substr('chimp', 1)) FROM SmallA"; //$NON-NLS-1$ + String output = "SELECT position(cast(SmallA.IntNum AS varchar(4000)) in 'chimp') FROM SmallA"; //$NON-NLS-1$ TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, @@ -446,7 +446,7 @@ */ @Test public void testLocate3() throws Exception { String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26"; //$NON-NLS-1$ - String output = "SELECT position(cast(SmallA.IntNum AS varchar(4000)) in substr('234567890', 1)) FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$ + String output = "SELECT position(cast(SmallA.IntNum AS varchar(4000)) in '234567890') FROM SmallA WHERE SmallA.IntKey = 26"; //$NON-NLS-1$ TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, @@ -480,7 +480,7 @@ */ @Test public void testLocate5() throws Exception { String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA"; //$NON-NLS-1$ - String output = "SELECT position(SmallA.StringNum in substr('chimp', 1)) FROM SmallA"; //$NON-NLS-1$ + String output = "SELECT position(SmallA.StringNum in 'chimp') FROM SmallA"; //$NON-NLS-1$ TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, @@ -497,7 +497,7 @@ */ @Test public void testLocate6() throws Exception { String input = "SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA"; //$NON-NLS-1$ - String output = "SELECT position(SmallA.StringNum in substr('chimp', CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END)) FROM SmallA"; //$NON-NLS-1$ + String output = "SELECT (position(SmallA.StringNum in substr('chimp', CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END)) + CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END - 1) FROM SmallA"; //$NON-NLS-1$ TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, @@ -514,7 +514,7 @@ */ @Test public void testLocate7() throws Exception { String input = "SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA"; //$NON-NLS-1$ - String output = "SELECT position(SmallA.StringNum in substr('chimp', CASE WHEN (position(SmallA.StringNum in 'chimp') + 1) < 1 THEN 1 ELSE (position(SmallA.StringNum in 'chimp') + 1) END)) FROM SmallA"; //$NON-NLS-1$ + String output = "SELECT (position(SmallA.StringNum in substr('chimp', CASE WHEN (position(SmallA.StringNum in 'chimp') + 1) < 1 THEN 1 ELSE (position(SmallA.StringNum in 'chimp') + 1) END)) + CASE WHEN (position(SmallA.StringNum in 'chimp') + 1) < 1 THEN 1 ELSE (position(SmallA.StringNum in 'chimp') + 1) END - 1) FROM SmallA"; //$NON-NLS-1$ TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output,