You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Wes Peng <we...@freenetMail.de> on 2022/04/05 05:09:52 UTC
question about ANY data type
what does ANY data type mean?
apache drill (dfs.pyh)> desc foodmart;
+-----------------+-----------+-------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+-----------------+-----------+-------------+
| employee_id | ANY | YES |
| full_name | ANY | YES |
| position_title | ANY | YES |
| birth_date | ANY | YES |
| salary | ANY | YES |
| education_level | ANY | YES |
+-----------------+-----------+-------------+
6 rows selected (0.534 seconds)
I saw this type can be used in aggregate functions. doesn't it need a
cast() translation?
apache drill (dfs.pyh)> select avg(salary) as avgsal from foodmart;
+--------------------+
| avgsal |
+--------------------+
| 4019.6017316017314 |
+--------------------+
1 row selected (0.452 seconds)
Thanks
Re: question about ANY data type
Posted by James Turton <dz...@apache.org>.
There are implicit type casting rules that allow Drill to cast
automatically between certain types. It is not special in this respect,
you will find you can write the same sort of WHERE clause in many SQL
engines.
On 2022/04/05 09:26, Wes Peng wrote:
> thanks James.
> For the queries below, I got the birth_date as varchar type, but why
> this column can be compared as date type?
>
>
> apache drill (dfs.pyh)> select typeOf(birth_date) from foodmart limit 1 ;
> +---------+
> | EXPR$0 |
> +---------+
> | VARCHAR |
> +---------+
> 1 row selected (0.234 seconds)
>
> apache drill (dfs.pyh)> select * from foodmart where birth_date >
> '1970-01-01';
> +-------------+--------------------+-------------------------+------------+---------+---------------------+
>
> | employee_id | full_name | position_title |
> birth_date | salary | education_level |
> +-------------+--------------------+-------------------------+------------+---------+---------------------+
>
> | 9 | Brenda Blumberg | Store Manager |
> 1979-06-23 | 17000.0 | Graduate Degree |
> | 12 | Jewel Creek | Store Manager |
> 1971-10-18 | 8500.0 | Graduate Degree |
> | 13 | Peggy Medina | Store Manager |
> 1975-10-12 | 15000.0 | Bachelors Degree |
>
>
> Regards
>
>
>
> On 2022/4/5 2:37 下午, James Turton wrote:
>> select typeOf(employee_id) from foodmart limit 1
Re: question about ANY data type
Posted by Wes Peng <we...@freenetMail.de>.
thanks James.
For the queries below, I got the birth_date as varchar type, but why
this column can be compared as date type?
apache drill (dfs.pyh)> select typeOf(birth_date) from foodmart limit 1 ;
+---------+
| EXPR$0 |
+---------+
| VARCHAR |
+---------+
1 row selected (0.234 seconds)
apache drill (dfs.pyh)> select * from foodmart where birth_date >
'1970-01-01';
+-------------+--------------------+-------------------------+------------+---------+---------------------+
| employee_id | full_name | position_title |
birth_date | salary | education_level |
+-------------+--------------------+-------------------------+------------+---------+---------------------+
| 9 | Brenda Blumberg | Store Manager |
1979-06-23 | 17000.0 | Graduate Degree |
| 12 | Jewel Creek | Store Manager |
1971-10-18 | 8500.0 | Graduate Degree |
| 13 | Peggy Medina | Store Manager |
1975-10-12 | 15000.0 | Bachelors Degree |
Regards
On 2022/4/5 2:37 下午, James Turton wrote:
> select typeOf(employee_id) from foodmart limit 1
Re: question about ANY data type
Posted by James Turton <dz...@apache.org>.
I think it's a Calcite data type that is used as a placeholder by Drill
in late binding scenarios, i.e. when Drill doesn't yet know the actual
type. If you run
select typeOf(employee_id) from foodmart limit 1
you should see the resolved data type. This data type is the one that
will be matched against the AVG function.
On 2022/04/05 07:09, Wes Peng wrote:
> what does ANY data type mean?
>
> apache drill (dfs.pyh)> desc foodmart;
> +-----------------+-----------+-------------+
> | COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
> +-----------------+-----------+-------------+
> | employee_id | ANY | YES |
> | full_name | ANY | YES |
> | position_title | ANY | YES |
> | birth_date | ANY | YES |
> | salary | ANY | YES |
> | education_level | ANY | YES |
> +-----------------+-----------+-------------+
> 6 rows selected (0.534 seconds)
>
>
> I saw this type can be used in aggregate functions. doesn't it need a
> cast() translation?
>
> apache drill (dfs.pyh)> select avg(salary) as avgsal from foodmart;
> +--------------------+
> | avgsal |
> +--------------------+
> | 4019.6017316017314 |
> +--------------------+
> 1 row selected (0.452 seconds)
>
>
> Thanks