[
https://issues.apache.org/jira/browse/TRAFODION-3015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16473760#comment-16473760
]
Anoop Sharma commented on TRAFODION-3015:
-----------------------------------------
When a NUMERIC datatype precision is > 18, it is NOT converted to float.
It is internally handled as a software exact numeric type called BIGNUM which
maintains user specified precision and scale.
Conversion to/from bignum from/to string as well as comparisons work.
So maybe there is something more than the simple testcase attached to this
jira.The Jira only shows the testcase and not the actual output of that run.
The run shown below on latest traf returns correct results on numeric(19,0) and
conversions to/from string.
Apache Trafodion Conversational Interface 2.3.0
Copyright (c) 2015-2017 Apache Software Foundation
>>create table t3015 (a1 NUMERIC(19,0) );
--- SQL operation complete.
>>invoke t3015;
-- Definition of Trafodion table TRAFODION.SCH.T3015
-- Definition current Mon May 14 04:14:17 2018
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
NOT SERIALIZED
, A1 NUMERIC(19, 0) DEFAULT NULL NOT SERIALIZED
)
--- SQL operation complete.
>> insert into t3015 values(27380468);
--- 1 row(s) inserted.
>>select * from t3015 where a1='27380468';
A1
--------------------
27380467
--- 1 row(s) selected.
>>select cast(a1 as char(20)) from t3015;
(EXPR)
--------------------
27380468
--- 1 row(s) selected.
>>SELECT cast('27380468' as numeric(19,0)) from t3015;
(EXPR)
--------------------
27380468
--- 1 row(s) selected.
>>
> retrieve a value from numeric type get no result if using xx='value'
> ---------------------------------------------------------------------
>
> Key: TRAFODION-3015
> URL: https://issues.apache.org/jira/browse/TRAFODION-3015
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-general
> Affects Versions: 2.2.0
> Reporter: Yuan Liu
> Assignee: liu ming
> Priority: Major
> Fix For: any
>
>
> select * from c_xkzsmgp_esg_5 where ticket_id = 27380468; returns one row.
> select * from c_xkzsmgp_esg_5 where ticket_id = '27380468'; returns no row.
>
> Table DDL:
> CREATE TABLE TRAFODION.SEABASE.C_XKZSMGP_ESG_5
> (
> TICKET_ID NUMERIC(19, 0) NO DEFAULT NOT NULL NOT
> DROPPABLE NOT SERIALIZED
> , CC VARCHAR(50 BYTES) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , FCSJ TIMESTAMP(6) DEFAULT NULL NOT SERIALIZED
> , SEAT_NUM VARCHAR(10 BYTES) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , DDZ VARCHAR(50 BYTES) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , USERNAME VARCHAR(50 BYTES) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , IDNUM VARCHAR(50 BYTES) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , SPY VARCHAR(50 BYTES) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , WD VARCHAR(50 BYTES) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , SJLYSD VARCHAR(12 BYTES) CHARACTER SET UTF8
> COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
> , JLZLBS CHAR(1 CHAR) CHARACTER SET UTF8 COLLATE
> DEFAULT DEFAULT _ISO88591'0' NOT NULL NOT DROPPABLE NOT SERIALIZED
> , JLZXBS CHAR(1 CHAR) CHARACTER SET UTF8 COLLATE
> DEFAULT DEFAULT _ISO88591'0' NOT SERIALIZED
> , JLZXSJ TIMESTAMP(6) DEFAULT NULL NOT SERIALIZED
> , JLRKSJ TIMESTAMP(6) DEFAULT CURRENT NOT NULL NOT
> DROPPABLE NOT SERIALIZED
> , JLGXSJ TIMESTAMP(6) DEFAULT CURRENT NOT NULL NOT
> DROPPABLE NOT SERIALIZED
> , SFZJC NUMERIC(2, 0) DEFAULT NULL NOT SERIALIZED
> , PRIMARY KEY (TICKET_ID ASC)
> )
> SALT USING 5 PARTITIONS
> ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_RSRVD_3' STORED DESC
> HBASE_OPTIONS
> (
> DATA_BLOCK_ENCODING = 'FAST_DIFF',
> COMPRESSION = 'SNAPPY',
> MEMSTORE_FLUSH_SIZE = '1073741824'
> )
> ;
>
> query plans of two sqls are different:
> select * from c_xkzsmgp_esg_5 where ticket_id = '27380468';
> begin_key .............. (_SALT_ = (HashDistPartHash((
> 2.73804679999999968E+007 narrow
> \:_sys_ignored_CC_convErrorFlag)) Hash2Distrib
> (cast(5) narrow
> \:_sys_ignored_CC_convErrorFlag)))
> , (TICKET_ID = 2.73804679999999968E+007)
> end_key ................ (_SALT_ = (HashDistPartHash((
> 2.73804679999999968E+007 narrow
> \:_sys_ignored_CC_convErrorFlag)) Hash2Distrib (5
> narrow \:_sys_ignored_CC_convErrorFlag))),
> (TICKET_ID = 2.73804679999999968E+007)
>
>
> select * from c_xkzsmgp_esg_5 where ticket_id = 27380468;
> begin_key .............. (_SALT_ = (HashDistPartHash(cast(%(27380468)))
> Hash2Distrib (cast(5) narrow
> \:_sys_ignored_CC_convErrorFlag))),
> (TICKET_ID = %(27380468))
> end_key ................ (_SALT_ = (HashDistPartHash((%(27380468) narrow
> \:_sys_ignored_CC_convErrorFlag)) Hash2Distrib (5
> narrow \:_sys_ignored_CC_convErrorFlag))),
> (TICKET_ID = %(27380468))
>
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
