[
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.)
The above might not actually be a bug, however if someone has changed the type
of missing columns from the old {{INT}} to a newer (untyped) {{NULL}}.
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.
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.)
> 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.)
> The above might not actually be a bug, however if someone has changed the
> type of missing columns from the old {{INT}} to a newer (untyped) {{NULL}}.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
