You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Reuben Kuhnert (JIRA)" <ji...@apache.org> on 2016/06/13 17:21:21 UTC
[jira] [Commented] (HIVE-13946) Decimal value need to be
single-quoted when selecting where clause with that decimal value in order
to get row
[ https://issues.apache.org/jira/browse/HIVE-13946?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15327783#comment-15327783 ]
Reuben Kuhnert commented on HIVE-13946:
---------------------------------------
I'm getting different results, am I doing something wrong?
{code}
0: jdbc:hive2://localhost:10000> show tables;
show tables;
No rows selected (2.659 seconds)
+-----------+--+
| tab_name |
+-----------+--+
+-----------+--+
0: jdbc:hive2://localhost:10000> create table test (dc decimal(38,18));
18));
0: jdbc:hive2://localhost:10000> No rows affected (1.367 seconds)
insert into table test values (4327269606205.029297);
27269606205.029297);
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. tez, spark) or using Hive 1.X releases.
No rows affected (20.19 seconds)
0: jdbc:hive2://localhost:10000>
0: jdbc:hive2://localhost:10000> select * from test;
select * from test;
1 row selected (0.564 seconds)
+-----------------------------------+--+
| test.dc |
+-----------------------------------+--+
| 4327269606205.029297000000000000 |
+-----------------------------------+--+
0: jdbc:hive2://localhost:10000> select * from test where dc = 4327269606205.029297
7269606205.029297
. . . . . . . . . . . . . . . .> ;
;
1 row selected (6.726 seconds)
+-----------------------------------+--+
| test.dc |
+-----------------------------------+--+
| 4327269606205.029300000000000000 |
+-----------------------------------+--+
0: jdbc:hive2://localhost:10000> explain select * from test where dc = 4327269606205.029297
dc = 4327269606205.029297
. . . . . . . . . . . . . . . .> ;
;
+-----------------------------------------------------------------------------------------------+--+
| Explain |
+-----------------------------------------------------------------------------------------------+--+
| STAGE DEPENDENCIES: |
| Stage-0 is a root stage |
| |
| STAGE PLANS: |
| Stage: Stage-0 |
| Fetch Operator |
| limit: -1 |
| Processor Tree: |
| TableScan |
| alias: test |
| Statistics: Num rows: 1 Data size: 32 Basic stats: COMPLETE Column stats: NONE |
| Filter Operator |
| predicate: (UDFToDouble(dc) = 4.3272696062050293E12) (type: boolean) |
| Statistics: Num rows: 1 Data size: 32 Basic stats: COMPLETE Column stats: NONE |
| Select Operator |
| expressions: 4327269606205.0293 (type: decimal(38,18)) |
| outputColumnNames: _col0 |
| Statistics: Num rows: 1 Data size: 32 Basic stats: COMPLETE Column stats: NONE |
| ListSink |
| |
+-----------------------------------------------------------------------------------------------+--+
{code}
> Decimal value need to be single-quoted when selecting where clause with that decimal value in order to get row
> --------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-13946
> URL: https://issues.apache.org/jira/browse/HIVE-13946
> Project: Hive
> Issue Type: Bug
> Affects Versions: 1.2.1
> Reporter: Takahiko Saito
> Fix For: 1.2.1
>
>
> Create a table withe a column of decimal type(38,18) and insert '4327269606205.029297'. Then select with that value does not return anything.
> {noformat}
> 0: jdbc:hive2://ts-0531-1.openstacklocal:2181> drop table if exists test;
> No rows affected (0.175 seconds)
> 0: jdbc:hive2://ts-0531-1.openstacklocal:2181>
> 0: jdbc:hive2://ts-0531-1.openstacklocal:2181> create table test (dc decimal(38,18));
> No rows affected (0.098 seconds)
> 0: jdbc:hive2://ts-0531-1.openstacklocal:2181>
> 0: jdbc:hive2://ts-0531-1.openstacklocal:2181> insert into table test values (4327269606205.029297);
> INFO : Session is already open
> INFO : Dag name: insert into table tes...327269606205.029297)(Stage-1)
> INFO : Tez session was closed. Reopening...
> INFO : Session re-established.
> INFO :
> INFO : Status: Running (Executing on YARN cluster with App id application_1464727816747_0762)
> INFO : Map 1: -/-
> INFO : Map 1: 0/1
> INFO : Map 1: 0(+1)/1
> INFO : Map 1: 1/1
> INFO : Loading data to table default.test from hdfs://ts-0531-5.openstacklocal:8020/apps/hive/warehouse/test/.hive-staging_hive_2016-06-04_00-03-54_302_7708281807413586675-940/-ext-10000
> INFO : Table default.test stats: [numFiles=1, numRows=1, totalSize=21, rawDataSize=20]
> No rows affected (13.821 seconds)
> 0: jdbc:hive2://ts-0531-1.openstacklocal:2181>
> 0: jdbc:hive2://ts-0531-1.openstacklocal:2181> select * from test;
> +-----------------------+--+
> | test.dc |
> +-----------------------+--+
> | 4327269606205.029297 |
> +-----------------------+--+
> 1 row selected (0.078 seconds)
> 0: jdbc:hive2://ts-0531-1.openstacklocal:2181> select * from test where dc = 4327269606205.029297;
> +----------+--+
> | test.dc |
> +----------+--+
> +----------+--+
> No rows selected (0.224 seconds)
> {noformat}
> If you single quote that decimal value, a row is returned.
> {noformat}
> 0: jdbc:hive2://ts-0531-1.openstacklocal:2181> select * from test where dc = '4327269606205.029297';
> +-----------------------+--+
> | test.dc |
> +-----------------------+--+
> | 4327269606205.029297 |
> +-----------------------+--+
> 1 row selected (0.085 seconds)
> {noformat}
> explain shows:
> {noformat}
> 0: jdbc:hive2://ts-0531-1.openstacklocal:2181> explain select * from test where dc = 4327269606205.029297;
> +----------------------------------------------------------------------+--+
> | Explain |
> +----------------------------------------------------------------------+--+
> | STAGE DEPENDENCIES: |
> | Stage-0 is a root stage |
> | |
> | STAGE PLANS: |
> | Stage: Stage-0 |
> | Fetch Operator |
> | limit: -1 |
> | Processor Tree: |
> | TableScan |
> | alias: test |
> | filterExpr: (dc = 4.3272696062050293E12) (type: boolean) |
> | Filter Operator |
> | predicate: (dc = 4.3272696062050293E12) (type: boolean) |
> | Select Operator |
> | expressions: dc (type: decimal(38,18)) |
> | outputColumnNames: _col0 |
> | ListSink |
> | |
> +----------------------------------------------------------------------+--+
> 18 rows selected (0.512 seconds)
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)