Author: shawkins Date: 2012-06-05 19:07:38 -0400 (Tue, 05 Jun 2012) New Revision: 4168 Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java Log: TEIID-2064 ensuring IN expansion only happens for non-literal values Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java 2012-06-05 15:19:22 UTC (rev 4167) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataSQLConversionVisitor.java 2012-06-05 23:07:38 UTC (rev 4168) @@ -21,6 +21,7 @@ */ package org.teiid.translator.jdbc.teradata; +import java.util.ArrayList; import java.util.List; import org.teiid.language.AndOr; @@ -29,7 +30,9 @@ import org.teiid.language.Expression; import org.teiid.language.In; import org.teiid.language.LanguageFactory; +import org.teiid.language.Literal; import org.teiid.language.AndOr.Operator; +import org.teiid.language.SQLConstants.Tokens; import org.teiid.translator.jdbc.SQLConversionVisitor; public class TeradataSQLConversionVisitor extends SQLConversionVisitor { @@ -42,27 +45,39 @@ public void visit(In obj) { List<Expression> exprs = obj.getRightExpressions(); - Class expectedType = obj.getLeftExpression().getType(); - boolean decompose = false; for (Expression expr:exprs) { - if (!(expr.getType().equals(expectedType)) || (!(expr.getType().isAssignableFrom(Number.class)) && !expr.getType().isAssignableFrom(String.class))) { + if (!(expr instanceof Literal)) { decompose = true; + break; } } if (decompose) { + List<Expression> literals = new ArrayList<Expression>(); Comparison.Operator opCode = obj.isNegated()?Comparison.Operator.NE:Comparison.Operator.EQ; if (exprs.size() > 1) { - Condition left = LanguageFactory.INSTANCE.createCompareCriteria(opCode, obj.getLeftExpression(), exprs.get(0)); - for (int i = 1; i < exprs.size(); i++) { - AndOr replace = LanguageFactory.INSTANCE.createAndOr(obj.isNegated()?Operator.AND:Operator.OR, left, LanguageFactory.INSTANCE.createCompareCriteria(opCode, obj.getLeftExpression(), exprs.get(i))); - left = replace; - } + Condition left = null; + for (Expression expr : obj.getRightExpressions()) { + if (expr instanceof Literal) { + literals.add(expr); + } else { + if (left == null) { + left = LanguageFactory.INSTANCE.createCompareCriteria(opCode, obj.getLeftExpression(), expr); + } else { + left = LanguageFactory.INSTANCE.createAndOr(obj.isNegated()?Operator.AND:Operator.OR, left, LanguageFactory.INSTANCE.createCompareCriteria(opCode, obj.getLeftExpression(), expr)); + } + } + } + if (!literals.isEmpty()) { + left = LanguageFactory.INSTANCE.createAndOr(obj.isNegated()?Operator.AND:Operator.OR, left, new In(obj.getLeftExpression(), literals, obj.isNegated())); + } + buffer.append(Tokens.LPAREN); super.visit((AndOr)left); + buffer.append(Tokens.RPAREN); } else { - super.visit(LanguageFactory.INSTANCE.createCompareCriteria(opCode, obj.getLeftExpression(), exprs.get(0))); + super.visit(LanguageFactory.INSTANCE.createCompareCriteria(opCode, obj.getLeftExpression(), exprs.get(0))); } } else { Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java 2012-06-05 15:19:22 UTC (rev 4167) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java 2012-06-05 23:07:38 UTC (rev 4168) @@ -23,11 +23,8 @@ import static org.junit.Assert.*; -import java.sql.Date; import java.sql.Timestamp; -import java.util.ArrayList; import java.util.Arrays; -import java.util.List; import org.junit.BeforeClass; import org.junit.Test; @@ -35,7 +32,6 @@ import org.teiid.language.Command; import org.teiid.language.Expression; import org.teiid.language.Function; -import org.teiid.language.In; import org.teiid.language.LanguageFactory; import org.teiid.query.unittest.TimestampUtil; import org.teiid.translator.TranslatorException; @@ -155,59 +151,6 @@ helpTest(LANG_FACTORY.createLiteral("1.0", String.class), "double", "cast('1.0' AS double precision)"); } - @Test public void testInDecompose() throws Exception { - Expression left = LANG_FACTORY.createLiteral("1", String.class); - List<Expression> right = new ArrayList<Expression>(); - right.add(LANG_FACTORY.createLiteral("2", String.class)); - right.add(LANG_FACTORY.createLiteral("3", String.class)); - - In expr = LANG_FACTORY.createIn(left,right, false); - - assertEquals("'1' IN ('2', '3')", helpGetString(expr)); - } - - @Test public void testSingleInDecompose() throws Exception { - Expression left = LANG_FACTORY.createLiteral("1", String.class); - List<Expression> right = new ArrayList<Expression>(); - right.add(LANG_FACTORY.createLiteral("2", String.class)); - - In expr = LANG_FACTORY.createIn(left,right, false); - - assertEquals("'1' IN ('2')", helpGetString(expr)); - } - - @Test public void testInDecomposeNonLiterals() throws Exception { - Expression left = LANG_FACTORY.createLiteral("1", String.class); - List<Expression> right = new ArrayList<Expression>(); - right.add(LANG_FACTORY.createFunction("func", new Expression[] {}, Date.class)); - right.add(LANG_FACTORY.createLiteral("3", String.class)); - - In expr = LANG_FACTORY.createIn(left,right, false); - - assertEquals("'1' = func() OR '1' = '3'", helpGetString(expr)); - } - - @Test public void testNegatedInDecomposeNonLiterals() throws Exception { - Expression left = LANG_FACTORY.createLiteral("1", String.class); - List<Expression> right = new ArrayList<Expression>(); - right.add(LANG_FACTORY.createFunction("func", new Expression[] {}, Date.class)); - right.add(LANG_FACTORY.createLiteral("3", String.class)); - - In expr = LANG_FACTORY.createIn(left,right, true); - - assertEquals("'1' <> func() AND '1' <> '3'", helpGetString(expr)); - } - - @Test public void testsingleInDecomposeNonLiterals() throws Exception { - Expression left = LANG_FACTORY.createLiteral("1", String.class); - List<Expression> right = new ArrayList<Expression>(); - right.add(LANG_FACTORY.createFunction("func", new Expression[] {}, Date.class)); - - In expr = LANG_FACTORY.createIn(left,right, false); - - assertEquals("'1' = func()", helpGetString(expr)); - } - @Test public void testNullComapreNull() throws Exception { String input = "SELECT INTKEY, STRINGKEY, DOUBLENUM FROM bqt1.smalla WHERE NULL <> NULL"; String out = "SELECT SmallA.IntKey, SmallA.StringKey, SmallA.DoubleNum FROM SmallA WHERE 1 = 0"; @@ -231,4 +174,11 @@ String out = "SELECT SmallA.IntKey, SmallA.StringKey, SmallA.ShortValue FROM SmallA WHERE position('0' in SmallA.StringKey) = 2 OR position('2' in substr(cast(SmallA.ShortValue AS varchar(4000)),4)) = 6 ORDER BY 1"; TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, null, input, out, TRANSLATOR); } + + @Test public void testInValues() throws Exception { + String input = "SELECT INTKEY FROM BQT1.SmallA WHERE STRINGKEY IN (INTKEY, 'a', 'b') AND STRINGKEY NOT IN (SHORTVALUE, 'c') AND INTKEY IN (1, 2) ORDER BY intkey"; + String out = "SELECT SmallA.IntKey FROM SmallA WHERE (SmallA.StringKey = cast(SmallA.IntKey AS varchar(4000)) OR SmallA.StringKey IN ('a', 'b')) AND (SmallA.StringKey <> cast(SmallA.ShortValue AS varchar(4000)) AND SmallA.StringKey NOT IN ('c')) AND SmallA.IntKey IN (1, 2) ORDER BY 1"; + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, null, input, out, TRANSLATOR); + } + }