[teiid-issues] [JBoss JIRA] (TEIID-5571) If set null as argument to replace function, the column of all records becomes null

[Steven Hawkins (Jira)]

    [ https://issues.jboss.org/browse/TEIID-5571?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13675015#comment-13675015 ] 

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 at 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 at 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 at 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 at 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 at 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}



--
This message was sent by Atlassian Jira
(v7.12.1#712002)