Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-3015

retrieve a value from numeric type get no result if using xx='value'

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.0
    • Fix Version/s: 2.3
    • Component/s: sql-general
    • Labels:
      None

      Description

      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))

       

       

        Attachments

          Activity

            People

            • Assignee:
              ovis_poly liu ming
              Reporter:
              Yuan_Liu Yuan Liu
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: