[teiid-issues] [JBoss JIRA] (TEIID-3598) MongoDB string functions - different handling of NULL values

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-3598:
---------------------------------------

It would require using a case or similar expression.  They seem to have a $cond that may work for that.

> MongoDB string functions - different handling of NULL values
> ------------------------------------------------------------
>
>                 Key: TEIID-3598
>                 URL: https://issues.jboss.org/browse/TEIID-3598
>             Project: Teiid
>          Issue Type: Bug
>          Components: Misc. Connectors
>    Affects Versions: 8.7.1.6_2
>            Reporter: Jan Stastny
>            Assignee: Ramesh Reddy
>
> Teiid doesn't handle NULL values right in given functions when working with mongodb.
> * SUBSTRING(<column resolving to NULL>,2)
> ** query:
> {code:sql}
> SELECT INTKEY, STRINGNUM, SUBSTRING(STRINGNUM, 2) FROM BQT1.SmallA ORDER BY INTKEY
> {code}
> ** source query:
> {code:plain}
> {"$project": {{ "c_0" : "$INTKEY" , "c_1" : "$STRINGNUM" , "c_2" : { "$substr" : [ "$STRINGNUM" , { "$subtract" : [ 2 , 1]} , 4000]}}}}
> {code}
> ** problem:
> For row with NULL value in given column returns sth like: 
> {code:plain}{ "_id" : ObjectId("534bf17516997a2a41000002"), "c_0" : 1, "c_1" : null, "c_2" : "" }
> {code}which results in "" as result for SUBSTRING(NULL, 2)  which should return NULL instead
> * UCASE, LCASE (UPPER, LOWER)
> ** query:
> {code:sql}
> SELECT intkey, stringnum, LOWER(stringnum) AS LOWER FROM BQT1.SmallA ORDER BY intkey
> {code}
> ** source query:
> {code:plain}
> {"$project": {{ "c_0" : "$INTKEY" , "c_1" : "$STRINGNUM" , "c_2" : { "$toLower" : [ "$STRINGNUM"]}}}}
> {code}
> ** problem:
> For row with NULL value in given column returns sth like:
> {code:plain}
> { "_id" : ObjectId("534bf17516997a2a41000002"), "c_0" : 1, "c_1" : null, "c_2" : "" }
> {code} which results in "" as result for LCASE(NULL)  which should return NULL instead



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)