drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Krystal (JIRA)" <j...@apache.org>
Subject [jira] [Closed] (DRILL-515) Result from int divided by in drill not consistent with other databases.
Date Thu, 16 Feb 2017 17:11:41 GMT

     [ https://issues.apache.org/jira/browse/DRILL-515?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Krystal closed DRILL-515.
-------------------------

Close as won't fix.

> Result from int divided by in drill not consistent with other databases. 
> -------------------------------------------------------------------------
>
>                 Key: DRILL-515
>                 URL: https://issues.apache.org/jira/browse/DRILL-515
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Functions - Drill
>    Affects Versions: 1.0.0
>            Reporter: Krystal
>             Fix For: 0.4.0
>
>
> Here is the schema of voter parquet table:
> message pig_schema {
>   optional int32 rownum;
>   optional binary name;
>   optional int32 age;
>   optional binary registration;
>   optional double contributions;
>   optional int32 voterzone;
>   optional binary create_time;
> }
> Row data from voter table:
> 0: jdbc:drill:schema=dfs> select rownum, age from dfs.`voter` where rownum=10;
> +------------+------------+
> |   rownum   |    age     |
> +------------+------------+
> | 10         | 75         |
> +------------+------------+
> Division query from drill:
> 0: jdbc:drill:schema=dfs> select (age/rownum) from dfs.`voter` where rownum=10;
> +------------+
> |   EXPR$0   |
> +------------+
> | 7          |
> +------------+
> If hard code the value, 7.5 is returned:
> 0: jdbc:drill:schema=dfs> select (75/10) from dfs.`voter` where rownum=10;
> +------------+
> |   EXPR$0   |
> +------------+
> | 7.5        |
> +------------+
> Here is the result from postgres:
> postgres=# \d voter
>                   Table "public.voter"
>     Column     |            Type             | Modifiers 
> ---------------+-----------------------------+-----------
>  rownum        | integer                     | 
>  name          | character varying(50)       | 
>  age           | integer                     | 
>  registration  | character varying(20)       | 
>  contributions | numeric(7,2)                | 
>  voterzone     | smallint                    | 
>  create_time   | timestamp without time zone | 
> postgres=# select (age/rownum) from voter where rownum=10;
>  ?column? 
> ----------
>         7
> (1 row)
> postgres=# select (75/10) from voter where rownum=10;
>  ?column? 
> ----------
>         7
> (1 row)
> From Oracle:
> SQL> describe voter;
>  Name					   Null?    Type
>  ----------------------------------------- -------- ----------------------------
>  VOTER_ID					    NUMBER
>  NAME						    VARCHAR2(50)
>  AGE						    NUMBER
>  REGISTRATION					    VARCHAR2(20)
>  CONTRIBUTIONS					    NUMBER(5,2)
>  VOTERZONE					    NUMBER
>  CREATE_TIME					    TIMESTAMP(6)
> Note that oracle does not have an integer data type.
> SQL> select (age/voter_id) from voter where voter_id=10;
> (AGE/VOTER_ID)
> --------------
> 	   7.5
> SQL> select (75/10) as result from voter where voter_id=10;
>     RESULT
> ----------
>        7.5
> From mysql database:
> mysql> describe voter;
> +---------------+-------------+------+-----+---------+-------+
> | Field         | Type        | Null | Key | Default | Extra |
> +---------------+-------------+------+-----+---------+-------+
> | rownum        | int(11)     | YES  |     | NULL    |       |
> | name          | varchar(50) | YES  |     | NULL    |       |
> | age           | tinyint(4)  | YES  |     | NULL    |       |
> | registration  | varchar(15) | YES  |     | NULL    |       |
> | contributions | float       | YES  |     | NULL    |       |
> | voterzone     | smallint(6) | YES  |     | NULL    |       |
> +---------------+-------------+------+-----+---------+-------+
> mysql> select (age/rownum) from voter where rownum=10;
> +--------------+
> | (age/rownum) |
> +--------------+
> |       7.5000 |
> +--------------+
> mysql> select (75/10);
> +---------+
> | (75/10) |
> +---------+
> |  7.5000 |
> +---------+



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

Mime
View raw message