[ https://issues.apache.org/jira/browse/CALCITE-5685?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17720284#comment-17720284 ]
ZheHu commented on CALCITE-5685: -------------------------------- Here is a tricky point I've encountered. When parsing *SIGNED* and {*}UNSIGNED{*}, according to [mysql-doc|https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast], the former can be treated as {*}BigInt{*}, however, *UNSIGNED* values from 0 to "2^64-1", which we may not match a corresponding RelDataType(need to be validated in CAST). During test, I've also find two confusing issues: # The following SQL in *function.iq* returns 2 instead of 2.00 {code:java} select cast('2' as decimal(10, 2)) as alia; {code} # Testing two SQL(following SQL1 and SQL2) in JdbcTest, both the results are beyond expectations {code:java} SQL1: select cast('2.1' as decimal(10, 2)) as "u"; {code} I get: *u=2.1*, which should be *2.10*. {code:java} SQL2: final String s = "select cast(\"unit_sales\" as decimal(10, 2)) as \"u\"\n" + "from \"sales_fact_1997\" as \"sales_fact_1997\"\n"; CalciteAssert.that() .with(CalciteAssert.Config.FOODMART_CLONE) .query(s) .returnsUnordered("1.00"); {code} The result has 4 scale, like '1.0000'. > Support MySQL CONVERT function that works on data types > ------------------------------------------------------- > > Key: CALCITE-5685 > URL: https://issues.apache.org/jira/browse/CALCITE-5685 > Project: Calcite > Issue Type: Improvement > Components: core > Affects Versions: 1.34.0 > Reporter: ZheHu > Assignee: ZheHu > Priority: Minor > > CONVERT function in MySQL has two usage: > # convert(s USING transcodingName): as described in > [CALCITE-5664|https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-5664] > # convert(value, type): equivalent to CAST function that converts value to > specific type. > Here are some examples: > * convert(150, CHAR) > * convert(now(), DATE) > * convert('9.5', DECIMAL(10, 2)) > * convert(15, SIGNED) > * convert(-2, UNSIGNED) > Noted: for CONVERT or CAST function in MySQL, they only support converting to > some specific data > types(binary、char、date、time、datetime、decimal、signed、unsigned. Moreover, the > last two aren't JDBC sql Types). > -- This message was sent by Atlassian Jira (v8.20.10#820010)