Hello, IIRC there was a discussion some time ago about this topic, see comments in https://issues.apache.org/jira/browse/CALCITE-4777 and related tickets (all of them still open).
Best, Ruben On Mon, Jan 16, 2023 at 3:46 PM Николай Ижиков <[email protected]> wrote: > Hello. > > Right now, Calcite allows to cast FLOAT, DOUBLE expressions to BOOLEAN. > But, results is always `false`. Cast works as > `java.lang.Boolean.parseBoolean` call and always return false for anything > except «true» string(`Primitive#parse`). > > Simple additional test for `InterpreterTest` shows this: > > ``` > @Test void testDoubleCast() { > sql("SELECT CAST(CAST('0.0' AS DOUBLE) AS > BOOLEAN)").returnsRows("[false]"); > sql("SELECT CAST(CAST('0.1' AS DOUBLE) AS > BOOLEAN)").returnsRows("[false]"); > sql("SELECT CAST(CAST('1.0' AS DOUBLE) AS > BOOLEAN)").returnsRows("[false]"); > sql("SELECT CAST(CAST('42' AS DOUBLE) AS > BOOLEAN)").returnsRows("[false]"); > } > ``` > > Other database, such as Postgres, disallow cast from double(float) to > boolean: > > ``` > psql (14.6 (Homebrew)) > Type "help" for help. > > postgres=# SELECT CAST(CAST('1' AS float) AS BOOLEAN); > ERROR: cannot cast type double precision to boolean > LINE 1: SELECT CAST(CAST('1' AS float) AS BOOLEAN); > ^ > postgres=# > ``` > > Do we really need to support this? > I think Calcite should disallow this kind of conversion for better user > experience. > I can provide PR to implement proposed behavior > > What do you think?
