Author: shawkins Date: 2012-09-14 13:19:05 -0400 (Fri, 14 Sep 2012) New Revision: 4447 Modified: trunk/api/src/main/java/org/teiid/translator/SourceSystemFunctions.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/TestSQLConversionVisitor.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/db2/TestDB2SqlTranslator.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/derby/TestDerbySQLTranslator.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/ConnectorWorkItem.java trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java trunk/engine/src/main/java/org/teiid/query/function/FunctionMethods.java trunk/engine/src/main/java/org/teiid/query/function/source/SystemSource.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java trunk/engine/src/test/java/org/teiid/cdk/CommandBuilder.java trunk/engine/src/test/java/org/teiid/dqp/internal/datamgr/TestFunctionImpl.java trunk/engine/src/test/java/org/teiid/query/processor/TestFunctionPushdown.java trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java Log: TEIID-2188 adding support for CONCAT2 pushdown Modified: trunk/api/src/main/java/org/teiid/translator/SourceSystemFunctions.java =================================================================== --- trunk/api/src/main/java/org/teiid/translator/SourceSystemFunctions.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/api/src/main/java/org/teiid/translator/SourceSystemFunctions.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -44,6 +44,7 @@ public static final String ASCII = "ascii"; //$NON-NLS-1$ public static final String CHAR = "char"; //$NON-NLS-1$ public static final String CONCAT = "concat"; //$NON-NLS-1$ + public static final String CONCAT2 = "concat2"; //$NON-NLS-1$ //public static final String DIFFERENCE = "difference"; public static final String INITCAP = "initcap"; //$NON-NLS-1$ public static final String INSERT = "insert"; //$NON-NLS-1$ Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -126,6 +126,7 @@ registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory())); registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory())); registerFunctionModifier(SourceSystemFunctions.CONCAT, new ConcatFunctionModifier(getLanguageFactory())); + registerFunctionModifier(SourceSystemFunctions.CONCAT2, new AliasModifier("||")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.COT, new FunctionModifier() { @Override public List<?> translate(Function function) { @@ -589,6 +590,7 @@ supportedFunctions.add("CHAR"); //$NON-NLS-1$ supportedFunctions.add("CHR"); //$NON-NLS-1$ supportedFunctions.add("CONCAT"); //$NON-NLS-1$ + supportedFunctions.add(SourceSystemFunctions.CONCAT2); supportedFunctions.add("||"); //$NON-NLS-1$ supportedFunctions.add("INITCAP"); //$NON-NLS-1$ supportedFunctions.add("LCASE"); //$NON-NLS-1$ Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/TestSQLConversionVisitor.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/TestSQLConversionVisitor.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/TestSQLConversionVisitor.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -441,5 +441,12 @@ input, output, TRANSLATOR); } + + @Test public void testConcat2() { + helpTestVisitor(getTestVDB(), + "select part_name from parts where concat2(part_name, 'x') = concat2(part_weight, part_id)", //$NON-NLS-1$ + "SELECT PARTS.PART_NAME FROM PARTS WHERE concat(ifnull(PARTS.PART_NAME, ''), 'x') = CASE WHEN PARTS.PART_WEIGHT IS NULL AND PARTS.PART_ID IS NULL THEN NULL ELSE concat(ifnull(PARTS.PART_WEIGHT, ''), ifnull(PARTS.PART_ID, '')) END", //$NON-NLS-1$ + true); + } } Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/db2/TestDB2SqlTranslator.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/db2/TestDB2SqlTranslator.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/db2/TestDB2SqlTranslator.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -98,7 +98,7 @@ @Test public void testConcat2() throws Exception { String input = "select concat2(stringnum, stringnum) from BQT1.Smalla"; //$NON-NLS-1$ - String output = "SELECT CASE WHEN SmallA.StringNum IS NULL THEN NULL ELSE concat(coalesce(SmallA.StringNum, ''), coalesce(SmallA.StringNum, '')) END FROM SmallA"; //$NON-NLS-1$ + String output = "SELECT CASE WHEN SmallA.StringNum IS NULL AND SmallA.StringNum IS NULL THEN NULL ELSE concat(coalesce(SmallA.StringNum, ''), coalesce(SmallA.StringNum, '')) END FROM SmallA"; //$NON-NLS-1$ helpTestVisitor(FakeTranslationFactory.getInstance().getBQTTranslationUtility(), input, Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/derby/TestDerbySQLTranslator.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/derby/TestDerbySQLTranslator.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/derby/TestDerbySQLTranslator.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -67,7 +67,7 @@ @Test public void testConcat2() throws Exception { String input = "select concat2(stringnum, stringnum) from BQT1.Smalla"; //$NON-NLS-1$ - String output = "SELECT CASE WHEN SmallA.StringNum IS NULL THEN NULL ELSE {fn concat(coalesce(SmallA.StringNum, ''), coalesce(SmallA.StringNum, ''))} END FROM SmallA"; //$NON-NLS-1$ + String output = "SELECT CASE WHEN SmallA.StringNum IS NULL AND SmallA.StringNum IS NULL THEN NULL ELSE {fn concat(coalesce(SmallA.StringNum, ''), coalesce(SmallA.StringNum, ''))} END FROM SmallA"; //$NON-NLS-1$ TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); } Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -380,18 +380,22 @@ @Test public void testConcat2_useLiteral() throws Exception { String input = "select concat2(stringnum,'_xx') from bqt1.Smalla"; //$NON-NLS-1$ - String output = "SELECT concat(nvl(SmallA.StringNum, ''), '_xx') FROM SmallA"; //$NON-NLS-1$ - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, UDF, - input, output, - TRANSLATOR); + String output = "SELECT (g_0.StringNum || '_xx') FROM SmallA g_0"; //$NON-NLS-1$ + + CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached()); + commandBuilder.getLanguageBridgeFactory().setSupportsConcat2(true); + Command obj = commandBuilder.getCommand(input, true, true); + TranslationHelper.helpTestVisitor(output, TRANSLATOR, obj); } @Test public void testConcat2() throws Exception { String input = "select concat2(stringnum, stringkey) from bqt1.Smalla"; //$NON-NLS-1$ - String output = "SELECT CASE WHEN SmallA.StringNum IS NULL AND SmallA.StringKey IS NULL THEN NULL ELSE concat(nvl(SmallA.StringNum, ''), nvl(SmallA.StringKey, '')) END FROM SmallA"; //$NON-NLS-1$ - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, UDF, - input, output, - TRANSLATOR); + String output = "SELECT (g_0.StringNum || g_0.StringKey) FROM SmallA g_0"; //$NON-NLS-1$ + + CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached()); + commandBuilder.getLanguageBridgeFactory().setSupportsConcat2(true); + Command obj = commandBuilder.getCommand(input, true, true); + TranslationHelper.helpTestVisitor(output, TRANSLATOR, obj); } /** Modified: trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/ConnectorWorkItem.java =================================================================== --- trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/ConnectorWorkItem.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/ConnectorWorkItem.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -30,6 +30,7 @@ import org.teiid.adminapi.impl.VDBMetaData; import org.teiid.client.ResizingArrayList; +import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; import org.teiid.core.util.Assertion; import org.teiid.dqp.internal.process.RequestWorkItem; @@ -48,15 +49,7 @@ import org.teiid.query.sql.lang.QueryCommand; import org.teiid.query.sql.lang.StoredProcedure; import org.teiid.resource.spi.WrappedConnection; -import org.teiid.translator.CacheDirective; -import org.teiid.translator.DataNotAvailableException; -import org.teiid.translator.Execution; -import org.teiid.translator.ExecutionFactory; -import org.teiid.translator.ProcedureExecution; -import org.teiid.translator.ResultSetExecution; -import org.teiid.translator.ReusableExecution; -import org.teiid.translator.TranslatorException; -import org.teiid.translator.UpdateExecution; +import org.teiid.translator.*; public class ConnectorWorkItem implements ConnectorWork { @@ -85,7 +78,7 @@ private DataNotAvailableException dnae; - ConnectorWorkItem(AtomicRequestMessage message, ConnectorManager manager) { + ConnectorWorkItem(AtomicRequestMessage message, ConnectorManager manager) throws TeiidComponentException { this.id = message.getAtomicRequestID(); this.requestMsg = message; this.manager = manager; @@ -108,6 +101,7 @@ this.securityContext.setTransactional(requestMsg.isTransactional()); LanguageBridgeFactory factory = new LanguageBridgeFactory(this.queryMetadata); factory.setConvertIn(!this.connector.supportsInCriteria()); + factory.setSupportsConcat2(manager.getCapabilities().supportsFunction(SourceSystemFunctions.CONCAT2)); translatedCommand = factory.translate(message.getCommand()); } Modified: trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java =================================================================== --- trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -46,6 +46,7 @@ import org.teiid.metadata.ProcedureParameter; import org.teiid.metadata.FunctionMethod.PushDown; import org.teiid.query.QueryPlugin; +import org.teiid.query.function.FunctionDescriptor; import org.teiid.query.metadata.QueryMetadataInterface; import org.teiid.query.sql.lang.*; import org.teiid.query.sql.lang.Command; @@ -63,6 +64,7 @@ import org.teiid.query.sql.symbol.Function; import org.teiid.query.sql.symbol.ScalarSubquery; import org.teiid.query.sql.symbol.WindowFunction; +import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.TranslatorException; @@ -135,6 +137,7 @@ private List<List<?>> allValues = new LinkedList<List<?>>(); private Map<String, List<? extends List<?>>> dependentSets; private boolean convertIn; + private boolean supportsConcat2; public LanguageBridgeFactory(QueryMetadataInterface metadata) { if (metadata != null) { @@ -149,6 +152,10 @@ public void setConvertIn(boolean convertIn) { this.convertIn = convertIn; } + + public void setSupportsConcat2(boolean supportsConcat2) { + this.supportsConcat2 = supportsConcat2; + } public org.teiid.language.Command translate(Command command) { try { @@ -619,7 +626,7 @@ return result; } - org.teiid.language.Function translate(Function function) { + org.teiid.language.Expression translate(Function function) { Expression [] args = function.getArgs(); List<org.teiid.language.Expression> params = new ArrayList<org.teiid.language.Expression>(args.length); for (int i = 0; i < args.length; i++) { @@ -628,6 +635,43 @@ String name = function.getName(); if (function.getFunctionDescriptor() != null) { name = function.getFunctionDescriptor().getName(); + if (!supportsConcat2 && function.getFunctionDescriptor().getMethod().getParent() == null && name.equalsIgnoreCase(SourceSystemFunctions.CONCAT2)) { + Expression[] newArgs = new Expression[args.length]; + + boolean useCase = true; + for(int i=0; i<args.length; i++) { + if (args[i] instanceof Constant) { + newArgs[i] = args[i]; + useCase = false; + } else { + Function f = new Function(SourceSystemFunctions.IFNULL, new Expression[] {args[i], new Constant("")}); //$NON-NLS-1$ + newArgs[i] = f; + f.setType(args[i].getType()); + FunctionDescriptor descriptor = + metadataFactory.getMetadata().getFunctionLibrary().findFunction(SourceSystemFunctions.IFNULL, new Class[] { args[i].getType(), DataTypeManager.DefaultDataClasses.STRING }); + f.setFunctionDescriptor(descriptor); + } + } + + Function concat = new Function(SourceSystemFunctions.CONCAT, newArgs); + concat.setType(DataTypeManager.DefaultDataClasses.STRING); + + if (!useCase) { + return translate(concat); + } + + FunctionDescriptor descriptor = + metadataFactory.getMetadata().getFunctionLibrary().findFunction(SourceSystemFunctions.CONCAT, new Class[] { DataTypeManager.DefaultDataClasses.STRING, DataTypeManager.DefaultDataClasses.STRING }); + concat.setFunctionDescriptor(descriptor); + + List<CompoundCriteria> when = Arrays.asList(new CompoundCriteria(CompoundCriteria.AND, new IsNullCriteria(args[0]), new IsNullCriteria(args[1]))); + Constant nullConstant = new Constant(null, DataTypeManager.DefaultDataClasses.STRING); + List<Constant> then = Arrays.asList(nullConstant); + SearchedCaseExpression caseExpr = new SearchedCaseExpression(when, then); + caseExpr.setElseExpression(concat); + caseExpr.setType(DataTypeManager.DefaultDataClasses.STRING); + return translate(caseExpr); + } //check for translator pushdown functions, and use the name in source if possible if (function.getFunctionDescriptor().getPushdown() == PushDown.MUST_PUSHDOWN && function.getFunctionDescriptor().getSchema().equalsIgnoreCase(CoreConstants.SYSTEM_MODEL) Modified: trunk/engine/src/main/java/org/teiid/query/function/FunctionMethods.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/function/FunctionMethods.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/engine/src/main/java/org/teiid/query/function/FunctionMethods.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -620,14 +620,27 @@ // ================== Function = length ===================== public static Object length(String str) { - return new Integer(str.length()); + return str.length(); } // ================== Function = concat ===================== - public static Object concat(String str1, String str2) { + public static String concat(String str1, String str2) { return str1 + str2; } + + public static String concat2(String str1, String str2) { + if (str1 == null) { + if (str2 == null) { + return null; + } + return str2; + } + if (str2 == null) { + return str1; + } + return str1 + str2; + } // ================== Function = substring ===================== Modified: trunk/engine/src/main/java/org/teiid/query/function/source/SystemSource.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/function/source/SystemSource.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/engine/src/main/java/org/teiid/query/function/source/SystemSource.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -540,11 +540,12 @@ new FunctionParameter("string2", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.Concatop_arg2")) }, //$NON-NLS-1$ //$NON-NLS-2$ new FunctionParameter("result", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.Concatop_result_desc")) ) ); //$NON-NLS-1$ //$NON-NLS-2$ - FunctionMethod concat2 = new FunctionMethod("concat2", QueryPlugin.Util.getString("SystemSource.Concat_desc"), STRING, PushDown.SYNTHETIC, null, null, //$NON-NLS-1$ //$NON-NLS-2$ - Arrays.asList( - new FunctionParameter("string1", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.Concat_arg1")), //$NON-NLS-1$ //$NON-NLS-2$ - new FunctionParameter("string2", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.Concat_arg2")) ), //$NON-NLS-1$ //$NON-NLS-2$ - new FunctionParameter("result", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.Concat_result_desc")), false, Determinism.DETERMINISTIC ); //$NON-NLS-1$ //$NON-NLS-2$ + FunctionMethod concat2 = new FunctionMethod(SourceSystemFunctions.CONCAT2, QueryPlugin.Util.getString("SystemSource.Concat_desc"), STRING, FUNCTION_CLASS, "concat2", //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter[] { + new FunctionParameter("string1", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.Concat_arg1")), //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("string2", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.Concat_arg2")) }, //$NON-NLS-1$ //$NON-NLS-2$ + new FunctionParameter("result", DataTypeManager.DefaultDataTypes.STRING, QueryPlugin.Util.getString("SystemSource.Concat_result_desc")) ); //$NON-NLS-1$ //$NON-NLS-2$ + concat2.setNullOnNull(false); functions.add(concat2); } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/CapabilitiesUtil.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -48,6 +48,7 @@ import org.teiid.query.sql.symbol.Function; import org.teiid.query.sql.symbol.AggregateSymbol.Type; import org.teiid.query.sql.visitor.ElementCollectorVisitor; +import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.ExecutionFactory.NullOrder; import org.teiid.translator.ExecutionFactory.SupportedJoinCriteria; @@ -231,10 +232,16 @@ //technically the other functions are scoped to SYS or their function model, but that's //not formally part of their metadata yet Schema schema = function.getFunctionDescriptor().getMethod().getParent(); + String fullName = function.getFunctionDescriptor().getMethod().getFullName(); if (schema == null || !schema.isPhysical()) { // Find capabilities - if (!caps.supportsFunction(function.getFunctionDescriptor().getMethod().getFullName())) { - return false; + + if (!caps.supportsFunction(fullName)) { + //special handling for delayed rewrite of concat2 + return (schema == null && SourceSystemFunctions.CONCAT2.equalsIgnoreCase(fullName) + && caps.supportsFunction(SourceSystemFunctions.CONCAT) + && caps.supportsFunction(SourceSystemFunctions.IFNULL) + && caps.supportsCapability(Capability.QUERY_SEARCHED_CASE)); } if (FunctionLibrary.isConvert(function)) { Class<?> fromType = function.getArg(0).getType(); @@ -245,7 +252,7 @@ return caps.supportsConvert(DataTypeManager.getTypeCode(fromType), DataTypeManager.getTypeCode(targetType)); } } else if (!isSameConnector(modelID, schema, metadata, capFinder)) { - return caps.supportsFunction(function.getFunctionDescriptor().getMethod().getFullName()); + return caps.supportsFunction(fullName); } return true; Modified: trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/engine/src/main/java/org/teiid/query/rewriter/QueryRewriter.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -2097,6 +2097,7 @@ } } + boolean omitNull = false; Integer code = FUNCTION_MAP.get(functionName); if (code != null) { switch (code) { @@ -2145,33 +2146,9 @@ } break; } - case 4: { //rewrite concat2 - CONCAT2(a, b) ==> CASE WHEN (a is NULL AND b is NULL) THEN NULL ELSE CONCAT( NVL(a, ''), NVL(b, '') ) - Expression[] args = function.getArgs(); - Function[] newArgs = new Function[args.length]; - - for(int i=0; i<args.length; i++) { - newArgs[i] = new Function(SourceSystemFunctions.IFNULL, new Expression[] {args[i], new Constant("")}); //$NON-NLS-1$ - newArgs[i].setType(args[i].getType()); - Assertion.assertTrue(args[i].getType() == DataTypeManager.DefaultDataClasses.STRING); - FunctionDescriptor descriptor = - funcLibrary.findFunction(SourceSystemFunctions.IFNULL, new Class[] { args[i].getType(), DataTypeManager.DefaultDataClasses.STRING }); - newArgs[i].setFunctionDescriptor(descriptor); - } - - Function concat = new Function(SourceSystemFunctions.CONCAT, newArgs); - concat.setType(DataTypeManager.DefaultDataClasses.STRING); - FunctionDescriptor descriptor = - funcLibrary.findFunction(SourceSystemFunctions.CONCAT, new Class[] { DataTypeManager.DefaultDataClasses.STRING, DataTypeManager.DefaultDataClasses.STRING }); - concat.setFunctionDescriptor(descriptor); - - List when = Arrays.asList(new Criteria[] {new CompoundCriteria(CompoundCriteria.AND, new IsNullCriteria(args[0]), new IsNullCriteria(args[1]))}); - Constant nullConstant = new Constant(null, DataTypeManager.DefaultDataClasses.STRING); - List then = Arrays.asList(new Expression[] {nullConstant}); - SearchedCaseExpression caseExpr = new SearchedCaseExpression(when, then); - caseExpr.setElseExpression(concat); - caseExpr.setType(DataTypeManager.DefaultDataClasses.STRING); - return rewriteExpressionDirect(caseExpr); - } + case 4: + omitNull = true; + break; case 5: { if (function.getType() != DataTypeManager.DefaultDataClasses.TIMESTAMP) { FunctionDescriptor descriptor = @@ -2229,14 +2206,32 @@ Expression[] args = function.getArgs(); Expression[] newArgs = new Expression[args.length]; - + // Rewrite args + int j = 0; for(int i=0; i<args.length; i++) { - newArgs[i] = rewriteExpressionDirect(args[i]); - if (isNull(newArgs[i]) && !function.getFunctionDescriptor().isNullDependent()) { - return new Constant(null, function.getType()); + Expression ex = rewriteExpressionDirect(args[i]); + if (isNull(ex)) { + if (!function.getFunctionDescriptor().isNullDependent()) { + return new Constant(null, function.getType()); + } + if (omitNull) { + continue; + } } + newArgs[j++] = ex; } + if (omitNull) { + if (j==0) { + return new Constant(null, function.getType()); + } + if (j==1) { + return newArgs[0]; + } + if (j!=args.length) { + newArgs = Arrays.copyOf(newArgs, j); + } + } function.setArgs(newArgs); if( FunctionLibrary.isConvert(function)) { Modified: trunk/engine/src/test/java/org/teiid/cdk/CommandBuilder.java =================================================================== --- trunk/engine/src/test/java/org/teiid/cdk/CommandBuilder.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/engine/src/test/java/org/teiid/cdk/CommandBuilder.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -52,14 +52,20 @@ } private QueryMetadataInterface metadata; + private LanguageBridgeFactory languageBridgeFactory; /** * @param metadata The metadata describing the datasource which the query is for. */ public CommandBuilder(QueryMetadataInterface metadata) { this.metadata = metadata; + this.languageBridgeFactory = new LanguageBridgeFactory(metadata); } + public LanguageBridgeFactory getLanguageBridgeFactory() { + return languageBridgeFactory; + } + public org.teiid.language.Command getCommand(String queryString) { return getCommand(queryString, false, false); } @@ -75,7 +81,7 @@ command = (Command)command.clone(); command.acceptVisitor(new AliasGenerator(supportsGroupAlias)); } - return new LanguageBridgeFactory(metadata).translate(command); + return languageBridgeFactory.translate(command); } catch (TeiidException e) { throw new TeiidRuntimeException(e); } Modified: trunk/engine/src/test/java/org/teiid/dqp/internal/datamgr/TestFunctionImpl.java =================================================================== --- trunk/engine/src/test/java/org/teiid/dqp/internal/datamgr/TestFunctionImpl.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/engine/src/test/java/org/teiid/dqp/internal/datamgr/TestFunctionImpl.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -50,7 +50,7 @@ } public static Function example(String name) throws Exception { - return TstLanguageBridgeFactory.factory.translate(helpExample(name)); + return (Function) TstLanguageBridgeFactory.factory.translate(helpExample(name)); } public void testGetName() throws Exception { Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestFunctionPushdown.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestFunctionPushdown.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestFunctionPushdown.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -37,8 +37,10 @@ import org.teiid.query.optimizer.TestOptimizer.ComparisonMode; import org.teiid.query.optimizer.capabilities.BasicSourceCapabilities; import org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder; +import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability; import org.teiid.query.parser.TestDDLParser; import org.teiid.query.unittest.RealMetadataFactory; +import org.teiid.translator.SourceSystemFunctions; @SuppressWarnings({"nls", "unchecked"}) public class TestFunctionPushdown { @@ -141,6 +143,34 @@ new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ } + @Test public void testConcat2() throws Exception { + QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); + + FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); + BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); + caps.setFunctionSupport(SourceSystemFunctions.CONCAT2, true); + capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ + + String sql = "select concat2(x.e1, x.e1) from pm1.g1 as x"; //$NON-NLS-1$ + + helpPlan(sql, metadata, null, capFinder, + new String[] {"SELECT concat2(g_0.e1, g_0.e1) FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + + //cannot pushdown + caps.setFunctionSupport(SourceSystemFunctions.CONCAT2, false); + + helpPlan(sql, metadata, null, capFinder, + new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + + caps.setFunctionSupport(SourceSystemFunctions.CONCAT, true); + caps.setFunctionSupport(SourceSystemFunctions.IFNULL, true); + caps.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, true); + + //will get replaced in the LanguageBridgeFactory + helpPlan(sql, metadata, null, capFinder, + new String[] {"SELECT concat2(g_0.e1, g_0.e1) FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ + } + public static String sourceFunc(String msg) { return msg; } Modified: trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2012-09-14 16:04:37 UTC (rev 4446) +++ trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2012-09-14 17:19:05 UTC (rev 4447) @@ -1336,15 +1336,11 @@ } @Test public void testRewriteConcat2_2() throws Exception { - helpTestRewriteCriteria("concat2(pm1.g1.e1, null) = 'xyz'", "CASE WHEN pm1.g1.e1 IS NULL THEN null ELSE concat(ifnull(pm1.g1.e1, ''), '') END = 'xyz'", true); //$NON-NLS-1$ //$NON-NLS-2$ + helpTestRewriteCriteria("concat2(pm1.g1.e1, null) = 'xyz'", "pm1.g1.e1 = 'xyz'", true); //$NON-NLS-1$ //$NON-NLS-2$ } - @Test public void testRewriteConcat2_3() throws Exception { - helpTestRewriteCriteria("concat2(pm1.g1.e1, convert(pm1.g1.e2, string)) = 'xyz'", "CASE WHEN (pm1.g1.e1 IS NULL) AND (convert(pm1.g1.e2, string) IS NULL) THEN null ELSE concat(ifnull(pm1.g1.e1, ''), ifnull(convert(pm1.g1.e2, string), '')) END = 'xyz'", true); //$NON-NLS-1$ //$NON-NLS-2$ - } - @Test public void testRewriteConcat2_4() throws Exception { - helpTestRewriteCriteria("concat2('a', pm1.g1.e1) = 'xyz'", "concat('a', ifnull(pm1.g1.e1, '')) = 'xyz'"); //$NON-NLS-1$ //$NON-NLS-2$ + helpTestRewriteCriteria("concat2('a', pm1.g1.e1) = 'xyz'", "concat2('a', pm1.g1.e1) = 'xyz'"); //$NON-NLS-1$ //$NON-NLS-2$ } @Test public void testRewriteFromUnixTime() throws Exception {