teiid SVN: r3166 - in branches/7.4.x/connectors/translator-jdbc/src: main/java/org/teiid/translator/jdbc/intersyscache and 2 other directories.
by teiid-commits@lists.jboss.org
Author: rareddy
Date: 2011-05-12 10:19:43 -0400 (Thu, 12 May 2011)
New Revision: 3166
Modified:
branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ingres/IngresExecutionFactory.java
branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/intersyscache/InterSystemsCacheExecutionFactory.java
branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java
branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java
Log:
TEIID-1495: few more changes to pushdown correct queries to Teradata. Also fixed other translators with errors in defining them to support all pushdown functions.
Modified: branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ingres/IngresExecutionFactory.java
===================================================================
--- branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ingres/IngresExecutionFactory.java 2011-05-12 03:37:24 UTC (rev 3165)
+++ branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ingres/IngresExecutionFactory.java 2011-05-12 14:19:43 UTC (rev 3166)
@@ -67,13 +67,7 @@
convert.addTypeMapping("clob", FunctionModifier.CLOB); //$NON-NLS-1$
convert.addNumericBooleanConversions();
registerFunctionModifier(SourceSystemFunctions.CONVERT, convert);
- }
-
- @Override
- public List<String> getSupportedFunctions() {
- List<String> supportedFunctions = new ArrayList<String>();
- supportedFunctions.addAll(super.getSupportedFunctions());
-
+
registerFunctionModifier(SourceSystemFunctions.BITAND, new AliasModifier("bit_and")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.BITNOT, new AliasModifier("bit_not")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.BITOR, new AliasModifier("bit_or")); //$NON-NLS-1$
@@ -84,16 +78,29 @@
registerFunctionModifier(SourceSystemFunctions.RAND, new AliasModifier("random")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.UCASE, new AliasModifier("uppercase")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new AliasModifier("day")); //$NON-NLS-1$
- registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory()));
-
+ registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory()));
+ }
+
+ @Override
+ public List<String> getSupportedFunctions() {
+ List<String> supportedFunctions = new ArrayList<String>();
+ supportedFunctions.addAll(super.getSupportedFunctions());
+
supportedFunctions.add(SourceSystemFunctions.ABS);
supportedFunctions.add(SourceSystemFunctions.ATAN);
+ supportedFunctions.add(SourceSystemFunctions.BITAND);
+ supportedFunctions.add(SourceSystemFunctions.BITNOT);
+ supportedFunctions.add(SourceSystemFunctions.BITOR);
+ supportedFunctions.add(SourceSystemFunctions.BITXOR);
supportedFunctions.add(SourceSystemFunctions.CONCAT);
supportedFunctions.add(SourceSystemFunctions.COS);
supportedFunctions.add(SourceSystemFunctions.CONVERT);
+ supportedFunctions.add(SourceSystemFunctions.CURTIME);
+ supportedFunctions.add(SourceSystemFunctions.CURDATE);
supportedFunctions.add(SourceSystemFunctions.DAYOFMONTH);
supportedFunctions.add(SourceSystemFunctions.EXP);
supportedFunctions.add(SourceSystemFunctions.HOUR);
+ supportedFunctions.add(SourceSystemFunctions.LCASE);
supportedFunctions.add(SourceSystemFunctions.LEFT);
supportedFunctions.add(SourceSystemFunctions.LPAD);
supportedFunctions.add(SourceSystemFunctions.LOCATE);
@@ -102,6 +109,7 @@
supportedFunctions.add(SourceSystemFunctions.MINUTE);
supportedFunctions.add(SourceSystemFunctions.MONTH);
supportedFunctions.add(SourceSystemFunctions.POWER);
+ supportedFunctions.add(SourceSystemFunctions.RAND);
supportedFunctions.add(SourceSystemFunctions.RIGHT);
supportedFunctions.add(SourceSystemFunctions.RPAD);
supportedFunctions.add(SourceSystemFunctions.SECOND);
@@ -109,6 +117,7 @@
supportedFunctions.add(SourceSystemFunctions.SQRT);
supportedFunctions.add(SourceSystemFunctions.SUBSTRING);
supportedFunctions.add(SourceSystemFunctions.YEAR);
+ supportedFunctions.add(SourceSystemFunctions.UCASE);
return supportedFunctions;
}
Modified: branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/intersyscache/InterSystemsCacheExecutionFactory.java
===================================================================
--- branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/intersyscache/InterSystemsCacheExecutionFactory.java 2011-05-12 03:37:24 UTC (rev 3165)
+++ branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/intersyscache/InterSystemsCacheExecutionFactory.java 2011-05-12 14:19:43 UTC (rev 3166)
@@ -104,7 +104,7 @@
@Override
public List<?> translate(Function function) {
if (function.getType() == TypeFacility.RUNTIME_TYPES.INTEGER || function.getType() == TypeFacility.RUNTIME_TYPES.LONG) {
- Function result = convert.createConvertFunction(getLanguageFactory(), function, TypeFacility.getDataTypeName(function.getType()));
+ Function result = ConvertModifier.createConvertFunction(getLanguageFactory(), function, TypeFacility.getDataTypeName(function.getType()));
function.setType(TypeFacility.RUNTIME_TYPES.BIG_DECIMAL);
return Arrays.asList(result);
}
@@ -119,19 +119,44 @@
supportedFunctions.addAll(super.getSupportedFunctions());
supportedFunctions.add(SourceSystemFunctions.ABS);
+ supportedFunctions.add(SourceSystemFunctions.ACOS);
+ supportedFunctions.add(SourceSystemFunctions.ASIN);
+ supportedFunctions.add(SourceSystemFunctions.ATAN);
supportedFunctions.add(SourceSystemFunctions.ASCII);
supportedFunctions.add(SourceSystemFunctions.CEILING);
supportedFunctions.add(SourceSystemFunctions.CHAR);
supportedFunctions.add(SourceSystemFunctions.COALESCE);
+ supportedFunctions.add(SourceSystemFunctions.CONCAT);
supportedFunctions.add(SourceSystemFunctions.CONVERT);
+ supportedFunctions.add(SourceSystemFunctions.COS);
+ supportedFunctions.add(SourceSystemFunctions.COT);
+ supportedFunctions.add(SourceSystemFunctions.CURDATE);
+ supportedFunctions.add(SourceSystemFunctions.CURTIME);
+ supportedFunctions.add(SourceSystemFunctions.DAYNAME);
+ supportedFunctions.add(SourceSystemFunctions.DAYOFMONTH);
+ supportedFunctions.add(SourceSystemFunctions.DAYOFWEEK);
+ supportedFunctions.add(SourceSystemFunctions.DAYOFYEAR);
+ supportedFunctions.add(SourceSystemFunctions.EXP);
supportedFunctions.add(SourceSystemFunctions.FLOOR);
+ supportedFunctions.add(SourceSystemFunctions.HOUR);
supportedFunctions.add(SourceSystemFunctions.IFNULL);
supportedFunctions.add(SourceSystemFunctions.LCASE);
supportedFunctions.add(SourceSystemFunctions.LENGTH);
supportedFunctions.add(SourceSystemFunctions.LPAD);
supportedFunctions.add(SourceSystemFunctions.LTRIM);
+ supportedFunctions.add(SourceSystemFunctions.LOG);
+ supportedFunctions.add(SourceSystemFunctions.LOG10);
+ supportedFunctions.add(SourceSystemFunctions.LEFT);
+ supportedFunctions.add(SourceSystemFunctions.MINUTE);
+ supportedFunctions.add(SourceSystemFunctions.MONTH);
+ supportedFunctions.add(SourceSystemFunctions.MONTHNAME);
+ supportedFunctions.add(SourceSystemFunctions.MOD);
supportedFunctions.add(SourceSystemFunctions.NULLIF);
+ supportedFunctions.add(SourceSystemFunctions.NOW);
+ supportedFunctions.add(SourceSystemFunctions.PI);
supportedFunctions.add(SourceSystemFunctions.POWER);
+ supportedFunctions.add(SourceSystemFunctions.QUARTER);
+ supportedFunctions.add(SourceSystemFunctions.RIGHT);
supportedFunctions.add(SourceSystemFunctions.REPEAT);
supportedFunctions.add(SourceSystemFunctions.REPLACE);
supportedFunctions.add(SourceSystemFunctions.ROUND);
@@ -139,8 +164,16 @@
supportedFunctions.add(SourceSystemFunctions.RTRIM);
supportedFunctions.add(SourceSystemFunctions.SIGN);
supportedFunctions.add(SourceSystemFunctions.SUBSTRING);
+ supportedFunctions.add(SourceSystemFunctions.SIN);
+ supportedFunctions.add(SourceSystemFunctions.SECOND);
+ supportedFunctions.add(SourceSystemFunctions.SQRT);
+ supportedFunctions.add(SourceSystemFunctions.TAN);
+ supportedFunctions.add(SourceSystemFunctions.TIMESTAMPADD);
+ supportedFunctions.add(SourceSystemFunctions.TIMESTAMPDIFF);
+ supportedFunctions.add(SourceSystemFunctions.TRUNCATE);
supportedFunctions.add(SourceSystemFunctions.UCASE);
supportedFunctions.add(SourceSystemFunctions.XMLCONCAT);
+ supportedFunctions.add(SourceSystemFunctions.WEEK);
return supportedFunctions;
}
Modified: branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java
===================================================================
--- branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java 2011-05-12 03:37:24 UTC (rev 3165)
+++ branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java 2011-05-12 14:19:43 UTC (rev 3166)
@@ -30,8 +30,10 @@
import java.util.List;
import org.teiid.core.types.DataTypeManager;
+import org.teiid.language.ColumnReference;
import org.teiid.language.Expression;
import org.teiid.language.Function;
+import org.teiid.language.LanguageFactory;
import org.teiid.language.Literal;
import org.teiid.metadata.FunctionMethod;
import org.teiid.metadata.FunctionParameter;
@@ -44,7 +46,6 @@
import org.teiid.translator.jdbc.FunctionModifier;
import org.teiid.translator.jdbc.JDBCExecutionFactory;
import org.teiid.translator.jdbc.SQLConversionVisitor;
-import org.teiid.translator.jdbc.oracle.LeftOrRightFunctionModifier;
@@ -80,8 +81,10 @@
convert.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.TIME, new TimeModifier("TIME")); //$NON-NLS-1$
convert.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new TimeModifier("DATE")); //$NON-NLS-1$
convert.addConvert(FunctionModifier.TIME, FunctionModifier.TIMESTAMP, new TimeModifier("TIMESTAMP")); //$NON-NLS-1$
- convert.addConvert(FunctionModifier.DATE, FunctionModifier.TIMESTAMP, new TimeModifier("TIMESTAMP")); //$NON-NLS-1$
-
+ convert.addConvert(FunctionModifier.DATE, FunctionModifier.TIMESTAMP, new TimeModifier("TIMESTAMP")); //$NON-NLS-1$
+ convert.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.STRING, new TimeModifier("varchar(100)")); //$NON-NLS-1$
+ convert.addConvert(FunctionModifier.TIME, FunctionModifier.STRING, new TimeModifier("varchar(100)")); //$NON-NLS-1$
+ convert.addConvert(FunctionModifier.DATE, FunctionModifier.STRING, new TimeModifier("varchar(100)")); //$NON-NLS-1$
convert.addTypeMapping("varchar(4000)", FunctionModifier.STRING); //$NON-NLS-1$
convert.addNumericBooleanConversions();
@@ -90,27 +93,20 @@
registerFunctionModifier(SourceSystemFunctions.RAND, new AliasModifier("random")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.LOG, new AliasModifier("LN")); //$NON-NLS-1$
- registerFunctionModifier(SourceSystemFunctions.LCASE, new AliasModifier("LOWER")); //$NON-NLS-1$
- registerFunctionModifier(SourceSystemFunctions.UCASE, new AliasModifier("UPPER")); //$NON-NLS-1$
+ registerFunctionModifier(SourceSystemFunctions.LCASE, new UpperOrLowerModifier("LOWER", this.convert)); //$NON-NLS-1$
+ registerFunctionModifier(SourceSystemFunctions.UCASE, new UpperOrLowerModifier("UPPER", this.convert)); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.LENGTH, new AliasModifier("CHARACTER_LENGTH")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.CURDATE, new AliasModifier("CURRENT_DATE")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.CURTIME, new AliasModifier("CURRENT_TIME")); //$NON-NLS-1$
- registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new AliasModifier("substr"));//$NON-NLS-1$
-
registerFunctionModifier(SourceSystemFunctions.YEAR, new ExtractModifier("YEAR")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.MONTH, new ExtractModifier("MONTH")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new ExtractModifier("DAY")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.HOUR, new ExtractModifier("HOUR")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.MINUTE, new ExtractModifier("MINUTE")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.SECOND, new ExtractModifier("SECOND")); //$NON-NLS-1$
- registerFunctionModifier(SourceSystemFunctions.LOCATE, new FunctionModifier() {
- @Override
- public List<?> translate(Function function) {
- return Arrays.asList("position(",function.getParameters().get(0)," in ",function.getParameters().get(1) ,")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
- }
- });
- registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory()));
- registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory()));
+ registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateModifier(this.convert));
+ registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory(), this.convert));
+ registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory(), this.convert));
registerFunctionModifier(SourceSystemFunctions.COT, new FunctionModifier() {
@Override
public List<?> translate(Function function) {
@@ -121,13 +117,13 @@
registerFunctionModifier(SourceSystemFunctions.LTRIM, new FunctionModifier() {
@Override
public List<?> translate(Function function) {
- return Arrays.asList("TRIM(LEADING FROM ", function.getParameters().get(0), ")"); //$NON-NLS-1$ //$NON-NLS-2$
+ return Arrays.asList("TRIM(LEADING FROM ", expressionToString(function.getParameters().get(0), convert), ")"); //$NON-NLS-1$ //$NON-NLS-2$
}
});
registerFunctionModifier(SourceSystemFunctions.RTRIM, new FunctionModifier() {
@Override
public List<?> translate(Function function) {
- return Arrays.asList("TRIM(TRAILING FROM ", function.getParameters().get(0), ")"); //$NON-NLS-1$ //$NON-NLS-2$
+ return Arrays.asList("TRIM(TRAILING FROM ", expressionToString(function.getParameters().get(0), convert), ")"); //$NON-NLS-1$ //$NON-NLS-2$
}
});
registerFunctionModifier(SourceSystemFunctions.MOD, new FunctionModifier() {
@@ -167,6 +163,7 @@
supportedFunctions.add(SourceSystemFunctions.LOCATE);
supportedFunctions.add(SourceSystemFunctions.LOG);
supportedFunctions.add(SourceSystemFunctions.LCASE);
+ supportedFunctions.add("lower");//$NON-NLS-1$
supportedFunctions.add(SourceSystemFunctions.LTRIM);
supportedFunctions.add(SourceSystemFunctions.LENGTH);
supportedFunctions.add(SourceSystemFunctions.MINUTE);
@@ -343,7 +340,55 @@
return false;
}
- public static class ExtractModifier extends FunctionModifier {
+ public static class LocateModifier extends FunctionModifier {
+ ConvertModifier convertModifier;
+
+ public LocateModifier(ConvertModifier convertModifier) {
+ this.convertModifier = convertModifier;
+ }
+
+ @Override
+ public List<?> translate(Function function) {
+ Expression expr1 = function.getParameters().get(0);
+ Expression expr2 = function.getParameters().get(1);
+ ArrayList target = new ArrayList();
+ target.add("position("); //$NON-NLS-1$
+ target.addAll(expressionToString(expr1, this.convertModifier));
+ target.add( " in "); //$NON-NLS-1$
+ target.addAll(expressionToString(expr2, this.convertModifier));
+ target.add(")"); //$NON-NLS-1$
+ return target;
+ }
+ }
+
+ private static List<?> expressionToString(Expression expr, ConvertModifier modifier) {
+ Class tgtType = expr.getType();
+ if (tgtType.equals(String.class) && (expr instanceof Literal)) {
+ return Arrays.asList(expr);
+ }
+ else if (tgtType.equals(String.class) && (expr instanceof Function)) {
+
+ Function func = (Function)expr;
+ if (func.getParameters().get(0) instanceof ColumnReference) {
+ ColumnReference ref = (ColumnReference)func.getParameters().get(0);
+ if(Number.class.isAssignableFrom(ref.getType())) {
+ ArrayList target = new ArrayList();
+ target.add("cast("); //$NON-NLS-1$
+ target.add(func.getParameters().get(0));
+ target.add(" AS varchar(100))"); //$NON-NLS-1$
+ }
+ else {
+ return modifier.translate(func);
+ }
+ }
+ else {
+ return modifier.translate(func);
+ }
+ }
+ return Arrays.asList("cast(" , expr, " AS varchar(100))"); //$NON-NLS-1$ //$NON-NLS-2$
+ }
+
+ public static class ExtractModifier extends FunctionModifier {
private String type;
public ExtractModifier(String type) {
this.type = type;
@@ -369,7 +414,61 @@
}
@Override
public List<?> translate(Function function) {
- return Arrays.asList("cast("+function.getParameters().get(0), " AS "+this.target+")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
+ return Arrays.asList("cast(", function.getParameters().get(0), " AS "+this.target+")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
}
+
+ public static class UpperOrLowerModifier extends FunctionModifier {
+ String funcName;
+ ConvertModifier convertModifier;
+ public UpperOrLowerModifier(String name, ConvertModifier converModifier) {
+ this.funcName = name;
+ this.convertModifier = converModifier;
+ }
+ @Override
+ public List<?> translate(Function function) {
+ Expression expr = function.getParameters().get(0);
+ ArrayList target = new ArrayList();
+ target.add(this.funcName);
+ target.add("("); //$NON-NLS-1$
+ target.addAll(expressionToString(expr, this.convertModifier));
+ target.add(")"); //$NON-NLS-1$
+ return target;
+ }
+ }
+
+ public static class LeftOrRightFunctionModifier extends FunctionModifier {
+ private LanguageFactory langFactory;
+ ConvertModifier convertModifier;
+
+ public LeftOrRightFunctionModifier(LanguageFactory langFactory, ConvertModifier converModifier) {
+ this.langFactory = langFactory;
+ this.convertModifier = converModifier;
+ }
+
+ @Override
+ public List<?> translate(Function function) {
+ List<Expression> args = function.getParameters();
+ ArrayList target = new ArrayList();
+ if (function.getName().equalsIgnoreCase("left")) { //$NON-NLS-1$
+ //substr(string, 1, length)
+ target.add("substr("); //$NON-NLS-1$
+ target.addAll(expressionToString(args.get(0), this.convertModifier));
+ target.add(langFactory.createLiteral(Integer.valueOf(1), TypeFacility.RUNTIME_TYPES.INTEGER));
+ target.add(args.get(1));
+ target.add(")"); //$NON-NLS-1$
+ } else if (function.getName().equalsIgnoreCase("right")) { //$NON-NLS-1$
+ //substr(case_size, character_length(case_size) -4)
+ target.add("substr("); //$NON-NLS-1$
+ target.addAll(expressionToString(args.get(0), this.convertModifier));
+
+ target.add(",(character_length("); //$NON-NLS-1$
+ target.addAll(expressionToString(args.get(0), this.convertModifier));
+ target.add(")-"); //$NON-NLS-1$
+ target.add(args.get(1));
+ target.add("))"); //$NON-NLS-1$
+ }
+ return target;
+ }
+ }
}
Modified: branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java
===================================================================
--- branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java 2011-05-12 03:37:24 UTC (rev 3165)
+++ branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java 2011-05-12 14:19:43 UTC (rev 3166)
@@ -76,9 +76,28 @@
}
@Test public void testTimestampToTime() throws Exception {
- helpTest(LANG_FACTORY.createLiteral(new Timestamp(1304604994220L), Timestamp.class), "time", "cast({ts '2011-05-05 09:16:34.22'} AS TIME)");
+ helpTest(LANG_FACTORY.createLiteral(new Timestamp(1304604994220L), Timestamp.class), "time", "cast(cast('2011-05-05 09:16:34.22' AS TIMESTAMP(6)) AS TIME)");
}
+ @Test public void testIntegerToString() throws Exception {
+ String input = "SELECT lcase(bigdecimalvalue) FROM BQT1.SMALLA";
+ String output = "SELECT LOWER(cast(SmallA.BigDecimalValue AS varchar(100))) FROM SmallA";
+ TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
+ }
+
+ @Test public void testDateToString() throws Exception {
+ String input = "SELECT intkey, UPPER(timevalue) AS UPPER FROM BQT1.SmallA ORDER BY intkey";
+ String output = "SELECT SmallA.IntKey, UPPER(cast(SmallA.TimeValue AS varchar(100))) AS UPPER FROM SmallA ORDER BY SmallA.IntKey";
+ TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
+ }
+
+ @Test public void testLocate() throws Exception {
+ String input = "SELECT INTKEY, BIGDECIMALVALUE FROM BQT1.SmallA WHERE LOCATE('-', BIGDECIMALVALUE) = 1 ORDER BY intkey";
+ String output = "SELECT SmallA.IntKey, SmallA.BigDecimalValue FROM SmallA WHERE position('-' in cast(SmallA.BigDecimalValue AS varchar(100))) = 1 ORDER BY SmallA.IntKey";
+ TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR);
+ }
+
+
@Test public void testByteToString() throws Exception {
helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "string", "1");
}
@@ -158,7 +177,7 @@
@Test public void testRightFunction() throws Exception {
String input = "SELECT INTKEY, FLOATNUM FROM BQT1.SmallA WHERE right(FLOATNUM, 2) <> 0 ORDER BY INTKEY";
- String out = "SELECT SmallA.IntKey, SmallA.FloatNum FROM SmallA WHERE SUBSTR(SmallA.FloatNum, (-1 * 2)) <> '0' ORDER BY SmallA.IntKey";
+ String out = "SELECT SmallA.IntKey, SmallA.FloatNum FROM SmallA WHERE substr(cast(SmallA.FloatNum AS varchar(100)),(character_length(cast(SmallA.FloatNum AS varchar(100)))-2)) <> '0' ORDER BY SmallA.IntKey";
TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, null, input, out, TRANSLATOR);
}
}