[
https://issues.apache.org/jira/browse/CALCITE-6700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17902147#comment-17902147
]
Zhengqiang Duan commented on CALCITE-6700:
------------------------------------------
When I was implementing the MySQL BIT_COUNT function, I found that Calcite
would convert DATE, TIME, TIMESTAMP and other time types to integer values
since 1970.
{code:java}
switch (literal.getType().getSqlTypeName()) {
case DECIMAL:
final BigDecimal bd = literal.getValueAs(BigDecimal.class);
if (javaClass == float.class) {
return Expressions.constant(bd, javaClass);
} else if (javaClass == double.class) {
return Expressions.constant(bd, javaClass);
}
assert javaClass == BigDecimal.class;
return Expressions.new_(BigDecimal.class,
Expressions.constant(
requireNonNull(bd,
() -> "value for " + literal).toString()));
case DATE:
case TIME:
case TIME_WITH_LOCAL_TIME_ZONE:
case INTERVAL_YEAR:
case INTERVAL_YEAR_MONTH:
case INTERVAL_MONTH:
value2 = literal.getValueAs(Integer.class);
javaClass = int.class;
break;
case TIMESTAMP:
case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
case INTERVAL_DAY:
case INTERVAL_DAY_HOUR:
case INTERVAL_DAY_MINUTE:
case INTERVAL_DAY_SECOND:
case INTERVAL_HOUR:
case INTERVAL_HOUR_MINUTE:
case INTERVAL_HOUR_SECOND:
case INTERVAL_MINUTE:
case INTERVAL_MINUTE_SECOND:
case INTERVAL_SECOND:
value2 = literal.getValueAs(Long.class);
javaClass = long.class;
break; {code}
However, when the MySQL BIT_COUNT function processes a time type such as
`1996-08-03`, it converts it to a value of `19960803` and then performs a
BIT_COUNT operation. This difference in processing logic will cause
inconsistent BIT_COUNT calculation results between Calcite and MySQL.
{code:java}
-- Calcite BIT_COUNT with DATE type
-- joinedat is DATE '1996-08-03'
select bit_count(joinedat) from emps limit 1
ColumnLabel: EXPR$0, ColumnValue: 10
-- MySQL BIT_COUNT with DATE type
mysql> select bit_count(STR_TO_DATE('1996-08-03', '%Y-%m-%d'));
+--------------------------------------------------+
| bit_count(STR_TO_DATE('1996-08-03', '%Y-%m-%d')) |
+--------------------------------------------------+
| 12 |
+--------------------------------------------------+
1 row in set (0.01 sec) {code}
I have no idea how to handle this situation. If anyone could provide some
guidance I would really appreciate it.
> MySQL BIT_COUNT function should return result when parameter is Boolean,
> String types
> -------------------------------------------------------------------------------------
>
> Key: CALCITE-6700
> URL: https://issues.apache.org/jira/browse/CALCITE-6700
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.38.0
> Reporter: Zhengqiang Duan
> Assignee: Zhengqiang Duan
> Priority: Major
> Labels: pull-request-available
>
> In [CALCITE-3697|https://issues.apache.org/jira/browse/CALCITE-3697], Caclite
> already supports MySQL BIT_COUNT by reusing the standard BITCOUNT function
> logic, but when I tested more MySQL data types, the Calcite validator would
> report errors because the standard BITCOUNT function does not support these
> types.
> For example, when I execute the following query, the Calcite validator throws
> the exception Cannot apply 'BIT_COUNT' to arguments of type
> 'BIT_COUNT(<BOOLEAN>)'. Supported form(s): 'BIT_COUNT(<NUMERIC>)'.
> {code:java}
> SELECT bit_count(123456), bit_count('123456'), bit_count('abcdefg'),
> BIT_COUNT('abcdef1234'), bit_count(''), bit_count(1 + 1), bit_count(true)
> {code}
> Executed via MySQL, they all return correct results.
> {code:java}
> mysql> SELECT bit_count(123456), bit_count('123456'), bit_count('abcdefg'),
> BIT_COUNT('abcdef1234'), bit_count(''), bit_count(1 + 1), bit_count(true) ;
> +-------------------+---------------------+----------------------+-------------------------+---------------+------------------+-----------------+
> | bit_count(123456) | bit_count('123456') | bit_count('abcdefg') |
> BIT_COUNT('abcdef1234') | bit_count('') | bit_count(1 + 1) | bit_count(true) |
> +-------------------+---------------------+----------------------+-------------------------+---------------+------------------+-----------------+
> | 6 | 6 | 0 |
> 0 | 0 | 1 | 1 |
> +-------------------+---------------------+----------------------+-------------------------+---------------+------------------+-----------------+
> 1 row in set, 3 warnings (0.00 sec) {code}
> In addition to the sample SQL above, the MySQL BIT_COUNT function also
> supports time, datetime, timestamp, year and other types.
> So, I think we should add a bitCount4MySQL implementation in SqlFunctions to
> adapt to different data types.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
