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

[Ramesh Reddy (JIRA)]

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

Ramesh Reddy edited comment on TEIID-3598 at 7/31/15 2:52 PM:
--------------------------------------------------------------

There is no operator to check for $eq with null, what mongo has is $ifnull \{ <expr>, <if-null-expr> \}, so I found I could not design like what you mentioned in second code fragment, so went little round about as in first code fragment. The same issue with substring too. 

I do not think I follow your comment about not distinguishing empty and null sting, empty is the first check, so in the else clause only an NULL string will result in a empty result, so I have check for that.



was (Author: rareddy):
There is no operator to check for $eq with null, what mongo has is $ifnull { <expr>, <if-null-expr> }, so I found I could not design like what you mentioned in second code fragment, so went little round about as in first code fragment. The same issue with substring too. 

I do not think I follow your comment about not distinguishing empty and null sting, empty is the first check, so in the else clause only an NULL string will result in a empty result, so I have check 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
>              Labels: Alpha3
>             Fix For: 8.12
>
>
> 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)