[
https://issues.apache.org/jira/browse/CALCITE-6937?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17941009#comment-17941009
]
Julian Hyde commented on CALCITE-6937:
--------------------------------------
This case contains so many misleading statements (you are mistaken about Oracle
and Snowflake above) that I think you should close it and open a new one. I
agree with your goal - to generate the right SQL to send to Hive and Phoenix -
but we cannot undermine our terminology.
Here are the facts. Standard SQL decreed that REAL was 32 bit floating point,
and created FLOAT for floating point with user-defined precision. The
Java-based databases in the Hadoop ecosystem screwed up and defined FLOAT as 32
bit, just like Java. (They similarly screwed up the TIMESTAMP type.)
We screwed up slightly. We made FLOAT fixed at 64 bits, when it should be
variable. But let’s not try to fix that here.
if you want to be neutral when writing the new case you can qualify every type
every time you mention it, “Calcites REAL type (32 bit floating point)”, “Hives
FLOAT type (32 bit floating point)”. Or you can use unqualified type names to
mean Calcites type system.
> Hive/Phoenix can not support REAL type
> --------------------------------------
>
> Key: CALCITE-6937
> URL: https://issues.apache.org/jira/browse/CALCITE-6937
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.39.0
> Reporter: Yu Xu
> Assignee: Yu Xu
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.40.0
>
>
> REAL type should equivalent to FLOAT type in Calcite（can refer to
> [https://calcite.apache.org/docs/reference.html#scalar-types]），but some
> systems not support REAL type, such as Hive//Phoenix, so in Dialect covnert
> from REAL to REAL is not consistent with these data system.
>
> *floating-point data type only support FLOAT and DOUBLE in follow systems:*
>
> Phoenix: [https://phoenix.apache.org/language/datatypes.html]
>
> Hive: [https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types]
>
> *in Hive test which is not supported:*
> hive> select cast(1.2 as REAL);
> NoViableAltException(26@[])
> at
> org.apache.hadoop.hive.ql.parse.HiveParser.primitiveType(HiveParser.java:39374)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.castExpression(HiveParser_IdentifiersParser.java:5434)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.atomExpression(HiveParser_IdentifiersParser.java:6716)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceFieldExpression(HiveParser_IdentifiersParser.java:6879)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceUnaryPrefixExpression(HiveParser_IdentifiersParser.java:7264)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceUnarySuffixExpression(HiveParser_IdentifiersParser.java:7324)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceBitwiseXorExpression(HiveParser_IdentifiersParser.java:7508)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceStarExpression(HiveParser_IdentifiersParser.java:7668)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedencePlusExpression(HiveParser_IdentifiersParser.java:7828)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceAmpersandExpression(HiveParser_IdentifiersParser.java:7988)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceBitwiseOrExpression(HiveParser_IdentifiersParser.java:8147)
> at
> org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.precedenceEqualExpression(HiveParser_IdentifiersParser.java:8677)
> FAILED: ParseException line 1:19 cannot recognize input near 'REAL' ')'
> '<EOF>' in primitive type specification
>
> *in Phoenix also not supported:*
> phoenix> select cast(1.2 as REAL);
> Error: ERROR 201 (22000): Illegal data. Unsupported sql type: REAL
> (state=22000,code=201)
> java.sql.SQLException: ERROR 201 (22000): Illegal data. Unsupported sql type:
> REAL
>
> *but in Spark it is work well(convert to float type):*
> spark-sql> select cast(1.1 as real);
> 25/04/02 22:36:56 WARN TaskSetManager: Finished tasks (1/1) in stage 2.0
> OK
> CAST(1.1 AS FLOAT)
> 1.1
> Time taken: 2.113 seconds, Fetched 1 row(s)
>
> *so in Hive/Phoenix system need convert from real to float type.*
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
