[ https://issues.apache.org/jira/browse/DRILL-7499?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Paul Rogers updated DRILL-7499: ------------------------------- Description: The {{sqltypeof()}} function was introduced in Drill 1.14 to work around limitations of the original {{typeof()}} function. The function is mentioned in _Learning Apache Drill_, Chapter 8, page 152: {noformat} ELECT sqlTypeOf(columns) AS cols_type, modeOf(columns) AS cols_mode FROM `csv/cust.csv` LIMIT 1; +--------------------+------------+ | cols_type | cols_mode | +--------------------+------------+ | CHARACTER VARYING | ARRAY | +--------------------+------------+ {noformat} When the same query is run against the just-released Drill 1.17, we get the *wrong* results: {noformat} +-----------+-----------+ | cols_type | cols_mode | +-----------+-----------+ | ARRAY | ARRAY | +-----------+-----------+ {noformat} The definition of {{sqlTypeOf()}} is that it should return the type portion of the columns (type, mode) major type. Clearly, it is no longer doing so for arrays. As a result, there is no function to obtain the data type for arrays. The problem also shows up in the query from page 158: {code:sql} SELECT a, b, sqlTypeOf(b) AS b_type, modeof(b) AS b_mode FROM `gen/70kmissing.json` WHERE mod(a, 70000) = 1; {code} Expected (table from the book with Drill 1.14 results): {noformat} +--------+-------+----------+-----------+ | a | b | b_type | b_mode | +--------+-------+----------+-----------+ | 1 | null | INTEGER | NULLABLE | +--------+-------+----------+-----------+ {noformat} Actual Drill 1.17 results: {noformat} +-------+-----------+-------------------+----------+ | a | b | b_type | b_mode | +-------+-----------+-------------------+----------+ | 1 | null | NULL | NULLABLE | +-------+-----------+-------------------+----------+ {noformat} (Second line of table is omitted because something else changed, not relevant to this ticket.) was: The {{sqltypeof()}} function was introduced in Drill 1.14 to work around limitations of the original {{typeof()}} function. The function is mentioned in _Learning Apache Drill_, Chapter 8, page 152: {noformat} ELECT sqlTypeOf(columns) AS cols_type, modeOf(columns) AS cols_mode FROM `csv/cust.csv` LIMIT 1; +--------------------+------------+ | cols_type | cols_mode | +--------------------+------------+ | CHARACTER VARYING | ARRAY | +--------------------+------------+ {noformat} When the same query is run against the just-released Drill 1.17, we get the *wrong* results: {noformat} +-----------+-----------+ | cols_type | cols_mode | +-----------+-----------+ | ARRAY | ARRAY | +-----------+-----------+ {noformat} The definition of {{sqlTypeOf()}} is that it should return the type portion of the columns (type, mode) major type. Clearly, it is no longer doing so for arrays. As a result, there is no function to obtain the data type for arrays. > sqltypeof() function with an array returns "ARRAY", not type > ------------------------------------------------------------ > > Key: DRILL-7499 > URL: https://issues.apache.org/jira/browse/DRILL-7499 > Project: Apache Drill > Issue Type: Improvement > Reporter: Paul Rogers > Priority: Minor > Labels: regresion > > The {{sqltypeof()}} function was introduced in Drill 1.14 to work around > limitations of the original {{typeof()}} function. The function is mentioned > in _Learning Apache Drill_, Chapter 8, page 152: > {noformat} > ELECT sqlTypeOf(columns) AS cols_type, > modeOf(columns) AS cols_mode > FROM `csv/cust.csv` LIMIT 1; > +--------------------+------------+ > | cols_type | cols_mode | > +--------------------+------------+ > | CHARACTER VARYING | ARRAY | > +--------------------+------------+ > {noformat} > When the same query is run against the just-released Drill 1.17, we get the > *wrong* results: > {noformat} > +-----------+-----------+ > | cols_type | cols_mode | > +-----------+-----------+ > | ARRAY | ARRAY | > +-----------+-----------+ > {noformat} > The definition of {{sqlTypeOf()}} is that it should return the type portion > of the columns (type, mode) major type. Clearly, it is no longer doing so for > arrays. As a result, there is no function to obtain the data type for arrays. > The problem also shows up in the query from page 158: > {code:sql} > SELECT a, b, > sqlTypeOf(b) AS b_type, modeof(b) AS b_mode > FROM `gen/70kmissing.json` > WHERE mod(a, 70000) = 1; > {code} > Expected (table from the book with Drill 1.14 results): > {noformat} > +--------+-------+----------+-----------+ > | a | b | b_type | b_mode | > +--------+-------+----------+-----------+ > | 1 | null | INTEGER | NULLABLE | > +--------+-------+----------+-----------+ > {noformat} > Actual Drill 1.17 results: > {noformat} > +-------+-----------+-------------------+----------+ > | a | b | b_type | b_mode | > +-------+-----------+-------------------+----------+ > | 1 | null | NULL | NULLABLE | > +-------+-----------+-------------------+----------+ > {noformat} > (Second line of table is omitted because something else changed, not relevant > to this ticket.) > -- This message was sent by Atlassian Jira (v8.3.4#803005)