[
https://issues.apache.org/jira/browse/TRAFODION-3015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16481615#comment-16481615
]
ASF GitHub Bot commented on TRAFODION-3015:
-------------------------------------------
GitHub user traflm opened a pull request:
https://github.com/apache/trafodion/pull/1570
[TRAFODION-3015] query no result when comparing string with BIGNUM
When the column is BIGNUM, and the predicate is comparing to a string.
Trafodion cast the string into FLOAT and the convert FLOAT into BIGNUM,
There is an issue when convert FLOAT into BIGNUM that didn't considering
the rounding:
so 2.73804679999999968E+007 will become 27380467 instead of 27380468
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/traflm/trafodion TRAFODION-3015-2
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1570.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 #1570
----
commit fac342d0b06c1e248b6389c070ccb62ce37a60a9
Author: Liu Ming <ovis_poly@...>
Date: 2018-05-19T10:47:14Z
[TRAFODION-3015] compare string to BIGNUM return no result
commit a08afe4940b828309e80f91ee0f2c29aaf08f5c6
Author: Liu Ming <ovis_poly@...>
Date: 2018-05-19T11:56:59Z
add regression test
----
> 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)
