You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Mehant Baid (JIRA)" <ji...@apache.org> on 2014/06/03 20:02:03 UTC
[jira] [Updated] (DRILL-515) Result from int divided by in drill
not consistent with other databases.
[ https://issues.apache.org/jira/browse/DRILL-515?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mehant Baid updated DRILL-515:
------------------------------
Component/s: Functions - Drill
> 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
>
> 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.2#6252)