Re: [DISCUSS] CAST DOUBLE to BOOLEAN
I agree, we should not support converting (explicitly or implicitly)
any numeric type to BOOLEAN. We made a mistake when we documented the
conversions (the table did not match actual or intended behavior)
I have just logged https://issues.apache.org/jira/browse/CALCITE-5485
to ensure that the documentation, intended behavior, and tests are in
sync. I'd be grateful if someone would fix it.
On Tue, Jan 17, 2023 at 12:36 AM Ruben Q L wrote:
>
> 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 Николай Ижиков 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?
[jira] [Created] (CALCITE-5485) Make type conversion documentation consistent with behavior and tests
Julian Hyde created CALCITE-5485:
Summary: Make type conversion documentation consistent with
behavior and tests
Key: CALCITE-5485
URL: https://issues.apache.org/jira/browse/CALCITE-5485
Project: Calcite
Issue Type: Bug
Reporter: Julian Hyde
Make type conversion documentation consistent with behavior and tests. Use the
following steps:
* Create an internal map that, for each pair of data types, says whether
conversion is implicit, explicit, or disallowed
* Run tests based on that map
* In {{{}DocumentationTest{}}}, generate an HTML table of that mapping, and
check that that table is consistent with the one in the [SQL
reference|https://calcite.apache.org/docs/reference.html#implicit-type-conversion].
I know of two errors in the table:
* The table says that you can explicitly cast from INT to TIMESTAMP (see
CALCITE-5483); this should be disallowed;
* The table says that you can explicitly cast from DECIMAL to BOOLEAN (see
CALCITE-4777); this should be disallowed.
There may be others.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
Help converting a SHA1 hash to Oracle
Hi
I am trying to write some code that will take a MySQL SQL statement that
includes a SHA1 hash statement and have it translate successfully to Oracle and
in doing so add a cast function.
Eg:
select sha1(address) from locations limit 10
Should translate to:
SELECT CAST(STANDARD_HASH(ADDRESS) AS VARCHAR(200)) FROM LOCATIONS FETCH FIRST
10 ROWS ONLY
I have 90% done but am struggling with the last part. I have via creating my
own SqlNodes done the following:
SELECT CAST(STANDARD_HASH("LOCATIONS"."ADDRESS") AS "VARCHAR(200)") "ADDRESS"
FROM “LOCATIONS"
This has been achieved by the following code:
…skipping setup code…
//create Hash function
SqlNode[] HashFunctionOperandList = new SqlNode[1];
HashFunctionOperandList[0] = sqlIdentifier;
//ORACLE_HASH is a UDF defined elsewhere
SqlBasicCall innerFunction = new SqlBasicCall(ORACLE_HASH,
HashFunctionOperandList, new SqlParserPos(0, 0));
//Create the Cast function
SqlNode[] functionOperandList = new SqlNode[2];
functionOperandList[0] = innerFunction;
functionOperandList[1] = new SqlIdentifier("VARCHAR(200)", SqlParserPos.ZERO);
compliantFunction = new SqlBasicCall(new
SqlCastFunction(),functionOperandList, new SqlParserPos(0, 0));
…skipping to output code…
SqlDialect translationDialect;
translationDialect = OracleSqlDialect.DEFAULT;;
String Query = compliantFunction.toSqlString(translationDialect).toString();
My key issue seems to be how I get VARCHAR(200) in my cast function, secondly
everything included the CAST AS has been quoted.
I’ve tried SO for support and according to the Calcite website, It recommended
I try here. So any help would be appreciated?
Thanks
