[jira] [Created] (DRILL-515) Result from int divided by in drill not consistent with other databases.

["Krystal (JIRA)" <ji...@apache.org>]

Krystal created DRILL-515:
-----------------------------

             Summary: 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
    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)