[
https://issues.apache.org/jira/browse/TRAFODION-3015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16473429#comment-16473429
]
ASF GitHub Bot commented on TRAFODION-3015:
-------------------------------------------
GitHub user traflm opened a pull request:
https://github.com/apache/trafodion/pull/1564
[TRAFODION-3015] retrieve a value from numeric type get no result
When the column is defined as NUMERIC(19,0), underlying data type will use
FLOAT to save the number.
So an implicit cast will generate wrong result when user input is string
literal.
For example:
create table t3015 (a1 NUMERIC(19,0) );
insert into t3015 values(27380468);
select * from t3015 where a1='27380468';
will have wrong output.
The fix is to report error, if the precision of NUMERIC is greater than 18,
and try to cast STRING into it.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/traflm/trafodion TRAFODION-3015-1
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1564.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1564
----
commit 3e8703cbb433deebf616156617db95874aaafcdd
Author: Liu Ming <ovis_poly@...>
Date: 2018-05-13T10:20:14Z
[TRAFODION-3015] retrieve a value from numeric type get no result
----
> 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)
