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