You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:17:51 UTC

[jira] [Created] (TRAFODION-699) LP Bug: 1382686 - LIKE predicate starting with % not working properly when select from a view

Alice Chen created TRAFODION-699:
------------------------------------

             Summary: LP Bug: 1382686 - LIKE predicate starting with % not working properly  when select from a view
                 Key: TRAFODION-699
                 URL: https://issues.apache.org/jira/browse/TRAFODION-699
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
            Reporter: Chong Hsu
            Assignee: Apache Trafodion
             Fix For: 1.1 (pre-incubation)


When trying a select statement from a view with LIKE predicate starting with %, it does not return expected results.  The same LIKE predicate works ok when select from view's base table.  Please see the steps below to reproduce the case.

CREATE TABLE REORG_CONTROL_INFO
  (
    VERSION                          INT NO DEFAULT NOT NULL
  , CATALOG_NAME                     CHAR(128) CHARACTER SET UCS2  NO DEFAULT NOT NULL
  , SCHEMA_NAME                      CHAR(128) CHARACTER SET UCS2  NO DEFAULT NOT NULL
  , TABLE_NAME                       CHAR(128) CHARACTER SET UCS2  NO DEFAULT NOT NULL
  , TABLE_UID                        LARGEINT NO DEFAULT NOT NULL
  , OBJECT_CLASS                     CHAR(2) CHARACTER SET ISO88591  NO DEFAULT NOT NULL
  , REORGCHK_STATUS                  CHAR(40) CHARACTER SET ISO88591  NO DEFAULT NOT NULL
  , COMPR_TYPE                       INT NO DEFAULT NOT NULL
  , SLACK                            INT NO DEFAULT NOT NULL
  , PARTNS_TOTAL                     INT NO DEFAULT NOT NULL
  , PARTNS_CHECKED                   INT NO DEFAULT NOT NULL
  , PARTNS_TO_REORG                  INT NO DEFAULT NOT NULL
  , PARTNS_IN_ERROR                  INT NO DEFAULT NOT NULL
  , REORGCHK_TIME                    TIMESTAMP(6) NO DEFAULT NOT NULL
  , ELAPSED_TIME                     INTERVAL HOUR(2) TO SECOND(3) NO DEFAULT NOT NULL
  , OTHERINFO                        VARCHAR(10000) CHARACTER SET ISO88591 DEFAULT NULL
  , PRIMARY KEY (TABLE_UID ASC)
  );

CREATE TABLE REORG_CONTROL_PARTN_INFO
  (
    TABLE_UID                        LARGEINT NO DEFAULT NOT NULL
  , PARTN_NUM                        INT NO DEFAULT NOT NULL
  , LOCATION                         CHAR(36) CHARACTER SET ISO88591 NO DEFAULT NOT NULL
  , PARTN_STATUS                     SMALLINT NO DEFAULT NOT NULL
  , RECOMMEND                        SMALLINT NO DEFAULT NOT NULL
  , RECOMMEND_SLACK                  SMALLINT NO DEFAULT NOT NULL
  , NUM_LEVELS                       SMALLINT NO DEFAULT NOT NULL
  , CURRENT_EOF                      LARGEINT NO DEFAULT NOT NULL
  , DATA_CHAINS                      SMALLINT NO DEFAULT NOT NULL
  , DATA_SLACK                       SMALLINT NO DEFAULT NOT NULL
  , COMPR_TYPE                       SMALLINT NO DEFAULT NOT NULL
  , COMPR_RATIO                      NUMERIC(4, 1) NO DEFAULT NOT NULL
  , COMPR_EOF                        LARGEINT NO DEFAULT NOT NULL
  , CMAP_CHAINS                      SMALLINT NO DEFAULT NOT NULL
  , CMAP_SLACK                       SMALLINT NO DEFAULT NOT NULL
  , PRIMARY KEY (TABLE_UID ASC, PARTN_NUM ASC)
  );

CREATE VIEW REORGPARTNINFOVIEW AS
  SELECT TRANSLATE(TRIM(T.CATALOG_NAME) || '.' || TRIM(T.SCHEMA_NAME) || '.'
    || TRIM(T.TABLE_NAME) USING UCS2TOUTF8) AS QUALIFIED_NAME,
    TRANSLATE(TRIM(T.CATALOG_NAME) USING UCS2TOUTF8) AS CATALOG_NAME,
    TRANSLATE(TRIM(T.SCHEMA_NAME) USING UCS2TOUTF8) AS SCHEMA_NAME,
    TRANSLATE(TRIM(T.TABLE_NAME) USING UCS2TOUTF8) AS TABLE_NAME,
    P.PARTN_NUM, P.LOCATION, CASE WHEN P.PARTN_STATUS = 1 THEN
    'REORG_NEEDED    ' WHEN P.PARTN_STATUS = 0 THEN 'REORG_NOT_NEEDED' WHEN
    P.PARTN_STATUS = 2 THEN 'REORG_ERROR     ' END AS PARTN_STATUS, CASE WHEN
    P.RECOMMEND = 0 THEN 'REORG_NOT_RECOMMENDED              ' WHEN
    P.RECOMMEND = 1 THEN 'REORG_RECOMMENDED_COMP_SLACK       ' WHEN
    P.RECOMMEND = 2 THEN 'REORG_RECOMMENDED_COMP_DISORGANIZED' WHEN
    P.RECOMMEND = 3 THEN 'REORG_RECOMMENDED_DISORGANIZED     ' WHEN
    P.RECOMMEND = 4 THEN 'REORG_RECOMMENDED_NOT_SEQUENTIAL   ' WHEN
    P.RECOMMEND = 5 THEN 'REORG_RECOMMENDED_SLACK            ' END AS
    RECOMMEND, P.RECOMMEND_SLACK, P.NUM_LEVELS, P.CURRENT_EOF, P.DATA_CHAINS,
    P.DATA_SLACK, CASE WHEN P.COMPR_TYPE = 0 THEN 'NONE    ' WHEN
    P.COMPR_TYPE = 1 THEN 'SOFTWARE' WHEN P.COMPR_TYPE = 2 THEN 'HARDWARE'
    END AS COMPR_TYPE, P.COMPR_RATIO, P.COMPR_EOF, P.CMAP_CHAINS,
    P.CMAP_SLACK FROM REORG_CONTROL_INFO T,
    REORG_CONTROL_PARTN_INFO P WHERE T.TABLE_UID =
    P.TABLE_UID AND T.OBJECT_CLASS = 'UT' ORDER BY 1, 5 ;

insert into REORG_CONTROL_INFO values (1, 'NEO', 'TEST', 'CUSTOMER', 1234, 'UT', 'ok', 1, 1, 1, 1, 1, 1, TIMESTAMP '2007-12-21 08:15:00.00', INTERVAL '1' DAY, '');

insert into REORG_CONTROL_PARTN_INFO values (1234, 1, 'n001', 1, 1, 1, 1, 1, 1, 1, 1, 222.2, 1, 1, 1);

-- works with 'CUST%'
select * from REORGPARTNINFOVIEW WHERE TABLE_NAME like 'CUST%';

--- 1 row(s) selected.

-- doe not work with '%CUST%'
select * from REORGPARTNINFOVIEW WHERE TABLE_NAME like '%CUST%';

--- 0 row(s) selected.

-- doe not work with '%UST%'
select * from REORGPARTNINFOVIEW WHERE TABLE_NAME like '%UST%';

--- 0 row(s) selected.

-- works with view's base table
select * from REORG_CONTROL_INFO WHERE TABLE_NAME like '%CUST%';

--- 1 row(s) selected.
Assigned to LaunchPad User James Capps



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)