[
https://issues.apache.org/jira/browse/CALCITE-4777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17416376#comment-17416376
]
Julian Hyde edited comment on CALCITE-4777 at 9/17/21, 1:16 AM:
----------------------------------------------------------------
The documentation that says you can cast DECIMAL to BOOLEAN was added in
CALCITE-3311. But that was not, and is not, Calcite's behavior.
I would support adding a conformance property for more lenient casting. My
intuition is that "CAST(n AS BOOLEAN)" should rewrite to "n <> 0", and "CAST(b
AS aNumericType)" should rewrite to "case when b then 1 else 0 end". But I
don't know what other DBs do. Log a new JIRA case for that. In the default
conformance, we should retain the current behavior, which is to disallow
explicit cast from DECIMAL to BOOLEAN.
To fix the current case:
* The validator should a nice exception if the user tries to cast DECIMAL to
BOOLEAN (and a few other cases of numeric to and from boolean);
* Add a test case to {{SqlOperatorBaseTest.testCastToBoolean}};
* Fix the documentation in reference.md.
was (Author: julianhyde):
The documentation that says you can cast DECIMAL to BOOLEAN was added in
CALCITE-3311. But that was not, and is not, Calcite's behavior.
I would support adding a conformance property for more lenient casting. But in
the default conformance, we should retain the current behavior, which is to
disallow explicit cast from DECIMAL to BOOLEAN. And the documentation should be
fixed.
> Casting from DECIMAL to BOOLEAN throws an exception
> ---------------------------------------------------
>
> Key: CALCITE-4777
> URL: https://issues.apache.org/jira/browse/CALCITE-4777
> Project: Calcite
> Issue Type: Bug
> Reporter: xuyangzhong
> Priority: Major
> Attachments: calcite.png
>
>
> My sql is the following:
> {code:java}
> // code placeholder
> select * from test where cast (0.10915913549909961 as boolean){code}
>
> I want to simplify the cast. An exception is thrown:
>
> {code:java}
> // code placeholder
> Exception in thread "main" java.lang.RuntimeException: while resolving method
> 'booleanValue' in class class java.math.BigDecimal at
> org.apache.calcite.linq4j.tree.Expressions.call(Expressions.java:424) at
> org.apache.calcite.linq4j.tree.Expressions.call(Expressions.java:435) at
> org.apache.calcite.linq4j.tree.Expressions.unbox(Expressions.java:1453) at
> org.apache.calcite.adapter.enumerable.EnumUtils.convert(EnumUtils.java:398)
> at
> org.apache.calcite.adapter.enumerable.EnumUtils.convert(EnumUtils.java:326)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCast(RexToLixTranslator.java:543)
> at
> org.apache.calcite.adapter.enumerable.RexImpTable$CastImplementor.implementSafe(RexImpTable.java:2450)
> at
> org.apache.calcite.adapter.enumerable.RexImpTable$AbstractRexCallImplementor.genValueStatement(RexImpTable.java:2894)
> at
> org.apache.calcite.adapter.enumerable.RexImpTable$AbstractRexCallImplementor.implement(RexImpTable.java:2859)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1089)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:90)
> at org.apache.calcite.rex.RexCall.accept(RexCall.java:174) at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:975)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitLocalRef(RexToLixTranslator.java:90)
> at org.apache.calcite.rex.RexLocalRef.accept(RexLocalRef.java:75) at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:237)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:231)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateList(RexToLixTranslator.java:823)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateProjects(RexToLixTranslator.java:198)
> at org.apache.calcite.rex.RexExecutorImpl.compile(RexExecutorImpl.java:90)
> at org.apache.calcite.rex.RexExecutorImpl.compile(RexExecutorImpl.java:66) at
> org.apache.calcite.rex.RexExecutorImpl.reduce(RexExecutorImpl.java:128) at
> org.apache.calcite.rex.RexSimplify.simplifyCast(RexSimplify.java:2101) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:326) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:287) at
> org.apache.flink.table.examples.java.tests.CalciteTest.main(CalciteTest.java:47)
> Caused by: java.lang.NoSuchMethodException:
> java.math.BigDecimal.booleanValue() at
> java.lang.Class.getMethod(Class.java:1786) at
> org.apache.calcite.linq4j.tree.Expressions.call(Expressions.java:421) ... 25
> more
> {code}
> In order to avoid that I used the wrong rule or it caused by my bad coding, i
> write the test case following:
>
> {code:java}
> // code placeholder
> JavaTypeFactory typeFactory = new
> JavaTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
> RexBuilder rexBuilder = new RexBuilder(typeFactory);
> final RexSimplify simplify = new RexSimplify(rexBuilder,
> RelOptPredicateList.EMPTY, RexUtil.EXECUTOR);
> RelDataType type = new BasicSqlType(typeFactory.getTypeSystem(),
> SqlTypeName.BOOLEAN);
> RelDataType bb = new
> BasicSqlType(typeFactory.getTypeSystem(),SqlTypeName.DECIMAL,18,17);
> SqlOperator op = new SqlCastFunction();
> RexLiteral lt =
> rexBuilder.makeExactLiteral(BigDecimal.valueOf(0.10915913549909961),bb);
> List<RexNode> list = new ArrayList<>();
> list.add(lt);
> RexNode rexNode = rexBuilder.makeCall(type,op,list);
> simplify.simplifyUnknownAs(rexNode, RexUnknownAs.UNKNOWN);
> {code}
> and it throws the same exception.
>
> Actually, the cast simplify operation will enter the function _translateCast_
> in _RexToLixTranslator_.It misses the "case BOOLEAN" and uses the convert in
> EnumUtils. However, because the Decimal's Primitive is null and fromNumber is
> true, the Expression's function named "call" calls the "booleanValue"
> function in "java.math.BigDecimal", which does not actually exist. So the
> exception is thrown.
> I find in SqFunctions, we have a function "toBoolean(Number number)" (which
> seems never to be used?). This function may very fit dealing with this
> question, right?
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
