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)