[ https://issues.apache.org/jira/browse/CALCITE-3864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17066323#comment-17066323 ]
Feng Zhu commented on CALCITE-3864: ----------------------------------- When we converting a function _*f1*_ to another one *_f2_*, there is a common sense that _*f2*_ is more expressive or powerful than *_f1_*. For example, we can use *_case-when_* to replace _*IF*_ function. This PR tries to use the binary "_*||*_" operator to replace *_concat_* function in validation phase. However, *concat* function is more general than "*||*". I have some concerns for this approach. (1) It may produce complicated nested expressions, bringing burden or even stackoverflow error. E.g., {code:java} concat('a0', 'a1', ..., 'a9999') -> (...(('a0'||'a1') || 'a2')...||'a9999') {code} (2) Nested "||" expression generates complex SQL in unparse phase. [~winipanda], can we make some efforts to implement the CONCAT_FUNCTION in the runtime, and *reuse* the implementation for "*||*"? Since "*||*" is just a special case for CONCAT_FUNCTION. > Add Implementation for SqlLibraryOperators.CONCAT_FUNCTION in SqlFunctions > and correct the return type inference of SqlLibraryOperators.CONCAT_FUNCTION > ------------------------------------------------------------------------------------------------------------------------------------------------------- > > Key: CALCITE-3864 > URL: https://issues.apache.org/jira/browse/CALCITE-3864 > Project: Calcite > Issue Type: Bug > Reporter: TANG Wen-hui > Assignee: TANG Wen-hui > Priority: Major > Labels: pull-request-available > Time Spent: 3h > Remaining Estimate: 0h > > Add the Implementation for SqlLibraryOperators.CONCAT_FUNCTION in > SqlFunctions rather than register it as a kind of || operator in > StandardConvertletTable, because it would be better for Jdbc adaptor to > generate CONCAT for mysql rather than || operator, since CONCAT in mysql is > not totally same as || operator. > According to my test, as for PG ,the result of "concat('a', 'b', 'c')" is > equal to " 'a' || 'b' || 'c' " , which is 'abc', as for Mysql, the result of > "concat('a', 'b', 'c')" is 'abc', but the result of " 'a'||'b'|| 'c' " is 0, > as for oracle, concat only have two argument and the the result of > 'a'||'b'||'c' is 'abc'. > And the return type inference of SqlLibraryOperators.CONCAT_FUNCTION will > cause AssertionError when the precsion of its operands are not specified. > {code:java} > concat(cast('a' as varchar), cast('b' as varchar),cast('c' as varchar)){code} > {code:java} > at > org.apache.calcite.sql.type.SqlTypeFactoryImpl.createSqlType(SqlTypeFactoryImpl.java:64)at > > org.apache.calcite.sql.type.SqlTypeFactoryImpl.createSqlType(SqlTypeFactoryImpl.java:64) > at > org.apache.calcite.sql.fun.SqlLibraryOperators.lambda$static$1(SqlLibraryOperators.java:291) > at > org.apache.calcite.sql.type.SqlTypeTransformCascade.inferReturnType(SqlTypeTransformCascade.java:54) > at org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:486) > at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:453) > at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:321) at > org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:218) at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5858) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5845) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1800) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1785) > at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:260) at > org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:423) at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:5552) > at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:116) at > org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:259) at > org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:423) at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:5552) > at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:116) at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1059) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:766) > at > org.apache.calcite.sql.test.AbstractSqlTester.parseAndValidate(AbstractSqlTester.java:175) > at > org.apache.calcite.sql.test.AbstractSqlTester.getResultType(AbstractSqlTester.java:163) > at > org.apache.calcite.sql.test.AbstractSqlTester.getColumnType(AbstractSqlTester.java:155) > at > org.apache.calcite.sql.test.AbstractSqlTester.check(AbstractSqlTester.java:477) > at > org.apache.calcite.sql.test.SqlOperatorBaseTest$TesterImpl.check(SqlOperatorBaseTest.java:9489) > at > org.apache.calcite.sql.test.AbstractSqlTester.check(AbstractSqlTester.java:462) > at > org.apache.calcite.sql.test.AbstractSqlTester.checkString(AbstractSqlTester.java:447) > at > org.apache.calcite.sql.test.SqlOperatorBaseTest.testConcatOperator(SqlOperatorBaseTest.java:2184){code} -- This message was sent by Atlassian Jira (v8.3.4#803005)