[
https://issues.apache.org/jira/browse/DRILL-6362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17005138#comment-17005138
]
Paul Rogers edited comment on DRILL-6362 at 12/30/19 6:22 AM:
--------------------------------------------------------------
It is likely that this function was meant to mimic the
[{{typeof()}}|https://www.w3resource.com/sqlite/core-functions-typeof.php]
function of SqlLite, which also returns "NULL" if the actual value is NULL.
Snowflake has the concept of a "Variant" (like Drill's Union type). In this
case
[{{typeof()}}|https://docs.snowflake.net/manuals/sql-reference/functions/typeof.html]
returns the type of the value. The documentation shows an example for a null
value for which {{typeof()}} to returns "NULL".
Given this, the Drill function should probably return the value type for a
UNION type. At present, {{typeof()}} will return "UNION", which is not
consistent with the Snowflake variant pattern.
Postres has the
[{{pg_typeof()}}|https://www.postgresql.org/docs/9.3/functions-info.html]
function, which is a bit convoluted, but the examples shows that it effectively
returns the type name.
Given all this, the proposal is to modify {{typeof()}} as follows:
* For a {{UNION}} type, return the actual type of the specific column value.
* For a {{UNION}} type (only), return "NULL" if the UNION itself is NULL. (Such
a column really does have no type.)
* For all other types, return the {{MinorType}} name.
To be clear, the two changes are:
* Modify handling of {{UNION}} columns.
* Modify handling of columns with values set to {{NULL}}.
These changes seem valid because:
* They make the Drill function closer to operation of other SQL engines.
* Other than for debugging, the most likely use of {{typeof()}} is to work with
UNIONS, a task for which the function currently fails.
was (Author: paul.rogers):
Closing this because we did create the new functions and we we've elected to
leave this function alone for now.
> typeof() lies about types
> -------------------------
>
> Key: DRILL-6362
> URL: https://issues.apache.org/jira/browse/DRILL-6362
> Project: Apache Drill
> Issue Type: Improvement
> Affects Versions: 1.13.0
> Reporter: Paul Rogers
> Assignee: Paul Rogers
> Priority: Major
>
> Drill provides a {{typeof()}} function that returns the type of a column.
> But, it seems to make up types. Consider the following input file:
> {noformat}
> {a: true}
> {a: false}
> {a: null}
> {noformat}
> Consider the following two queries:
> {noformat}
> SELECT a FROM `json/boolean.json`;
> +--------+
> | a |
> +--------+
> | true |
> | false |
> | null |
> +--------+
> > SELECT typeof(a) FROM `json/boolean.json`;
> +---------+
> | EXPR$0 |
> +---------+
> | BIT |
> | BIT |
> | NULL |
> +---------+
> {noformat}
> Notice that the values are reported as BIT. But, I believe the actual type is
> UInt1 (the bit vector is, I believe, deprecated.) Then, the function reports
> NULL instead of the actual type for the null value.
> Since Drill has an {{isnull()}} function, there is no reason for {{typeof()}}
> to muddle the type.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
