[jira] [Comment Edited] (CALCITE-4777) Casting from DECIMAL to BOOLEAN throws an exception

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ 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)