You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "TANG Wen-hui (Jira)" <ji...@apache.org> on 2020/03/23 14:24:00 UTC
[jira] [Comment Edited] (CALCITE-3864) Add Implementation for
SqlLibraryOperators.CONCAT_FUNCTION in SqlFunctions and correct the return
type inference of SqlLibraryOperators.CONCAT_FUNCTION
[ https://issues.apache.org/jira/browse/CALCITE-3864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17064743#comment-17064743 ]
TANG Wen-hui edited comment on CALCITE-3864 at 3/23/20, 2:23 PM:
-----------------------------------------------------------------
[~donnyzone] I have updated above. IMO, maybe we can register a convertlet for concat, since it is equevient to || operator. And as for Mysql,[https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html#operator_or]
"If the PIPES_AS_CONCAT SQL mode is enabled, || signifies the SQL-standard string concatenation operator (like CONCAT())."
"The ||, operator is a nonstandard MySQL extension. As of MySQL 8.0.17, this operator is deprecated and support for it will be removed in a future MySQL version. Applications should be adjusted to use the standard SQL OR operator. Exception: Deprecation does not apply if PIPES_AS_CONCAT is enabled because, in that case, || signifies string concatentation."
so we can translate || operator to concat function in mysqldialect , or just let it be || operator.
was (Author: winipanda):
[~donnyzone] I have updated above. IMO, maybe we can make a classification of concat functions for each library, since oracle only have two argument for concat. And register a convertlet for concat, since it is equevient to || operator. And as for Mysql,[https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html#operator_or]
"If the PIPES_AS_CONCAT SQL mode is enabled, || signifies the SQL-standard string concatenation operator (like CONCAT())."
"The ||, operator is a nonstandard MySQL extension. As of MySQL 8.0.17, this operator is deprecated and support for it will be removed in a future MySQL version. Applications should be adjusted to use the standard SQL OR operator. Exception: Deprecation does not apply if PIPES_AS_CONCAT is enabled because, in that case, || signifies string concatentation."
so we can translate || operator to concat function in mysqldialect , or just let it be || operator.
> 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: 10m
> 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)