[ https://issues.apache.org/jira/browse/TRAFODION-3015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16469071#comment-16469071 ]
liu ming commented on TRAFODION-3015: ------------------------------------- The best way is to report an ERROR if the implicit conversion is against a numeric data with more than 18 precision, since Trafodion has to convert it into float which lost the accuracy and introduce wrong semantics. > 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)