]
Steven Hawkins commented on TEIID-5571:
---------------------------------------
See also the related upstream issue that handles removing unsupported characters.
If set null as argument to replace function, the column of all
records becomes null
-----------------------------------------------------------------------------------
Key: TEIID-5571
URL:
https://issues.jboss.org/browse/TEIID-5571
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 8.12.16.6_4
Environment: jdv6.4.5
Reporter: Hiroki Daicho
Assignee: Steven Hawkins
Priority: Major
Attachments: Client.java, logs.zip, oraclevdb-vdb.xml
When argument of replace function set to null, all records of the column are set null
value.
- Query
select num, replace(name, 'a', null) from TEST01
Results
1: 0, null
2: 1, null
3: 2, null
4: 3, null
Results (Expected)
1: 0, null
2: 1, b
3: 2, c
4: 3, d
{code}
14:03:17,019 INFO [org.teiid.COMMAND_LOG] (New I/O worker #2) MH/yC1t35aMa START USER
COMMAND: startTime=2018-12-13
14:03:17.019 requestID=MH/yC1t35aMa.0 txID=null sessionID=MH/yC1t35aMa applicationName=JDBC principal=teiidUser@teiid-security vdbName=oraclevdb vdbVersion=1 sql=select
num, replace(name, 'a', null) from TEST01
14:03:17,309 DEBUG [org.teiid.COMMAND_LOG] (Worker1_QueryProcessorQueue1) MH/yC1t35aMa
START DATA SRC COMMAND: startTime=2018-12-13
14:03:17.309 requestID=MH/yC1t35aMa.0 sourceCommandID=0 executionID=0 txID=null modelName=oracle translatorName=oracle sessionID=MH/yC1t35aMa principal=teiidUser@teiid-security sql=SELECT
g_0.NUM FROM oracle.TEST01 AS g_0
14:03:17,314 DEBUG [org.teiid.COMMAND_LOG] (Worker1_QueryProcessorQueue1) MH/yC1t35aMa
SOURCE SRC COMMAND: endTime=2018-12-13
14:03:17.314 requestID=MH/yC1t35aMa.0 sourceCommandID=0 executionID=0 txID=null modelName=oracle translatorName=oracle sessionID=MH/yC1t35aMa principal=teiidUser@teiid-security sourceCommand=[SELECT
g_0."NUM" FROM "TESTDB"."TEST01" g_0]
14:03:17,440 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue2) MH/yC1t35aMa
END SRC COMMAND: endTime=2018-12-13
14:03:17.44 requestID=MH/yC1t35aMa.0 sourceCommandID=0 executionID=0 txID=null modelName=oracle translatorName=oracle sessionID=MH/yC1t35aMa principal=teiidUser@teiid-security finalRowCount=4 cpuTime(ns)=121768769
14:03:17,481 INFO [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue3) MH/yC1t35aMa
END USER COMMAND: endTime=2018-12-13
14:03:17.481 requestID=MH/yC1t35aMa.0 txID=null sessionID=MH/yC1t35aMa principal=teiidUser@teiid-security vdbName=oraclevdb vdbVersion=1 finalRowCount=4
{code}
This query is also same result.
select num, replace(name, null, 'a') from TEST01
When the replace function is in view model as follows, the result is same as the above.
{code}
<model name="oracle_view" type="VIRTUAL">
<property name="imports" value="oracle"/>
<metadata type="DDL"><![CDATA[
CREATE VIEW V_TEST01 (
NUM bigdecimal(38) NOT NULL OPTIONS(NAMEINSOURCE '"NUM"', NATIVE_TYPE
'NUMBER', UPDATABLE 'FALSE', FIXED_LENGTH 'TRUE'),
NAME string(20) OPTIONS(NAMEINSOURCE '"NAME"', NATIVE_TYPE
'VARCHAR2', UPDATABLE 'FALSE'),
CONSTRAINT PK_NUM PRIMARY KEY(NUM) OPTIONS(NAMEINSOURCE '"PK_NUM"')
) OPTIONS(NAMEINSOURCE '"TESTDB"."TEST01"')
AS
SELECT
t.NUM, replace(t.NAME, 'a', null) AS name
FROM
oracle.TEST01 AS t;
]]></metadata>
</model>
{code}