[teiid-issues] [JBoss JIRA] (TEIID-5296) With MongoDB, timestamp operations throw exceptions when called on null or missing values

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-5296:
---------------------------------------

Another commit was added to maintained branches (10.3, 10.2.2, 10.1.4), so this is only partially resolved for 10.2.1, 10.1.3, and 10.0.5

> With MongoDB, timestamp operations throw exceptions when called on null or missing values
> -----------------------------------------------------------------------------------------
>
>                 Key: TEIID-5296
>                 URL: https://issues.jboss.org/browse/TEIID-5296
>             Project: Teiid
>          Issue Type: Bug
>          Components: Misc. Connectors
>    Affects Versions: 8.12.11.6_4
>            Reporter: Jan Martiska
>            Assignee: Steven Hawkins
>             Fix For: 10.3, 10.1.3, 10.0.5, 10.2.1
>
>
> When a query contains timestamp operations like {{DAYOFMONTH}}, {{DAYOFWEEK}},.. and these are executed when some cells contain null (or missing field), MongoDB throws an exception. This exception is not handled by Teiid in any way and will fail the whole VDB query:
> {noformat}
> Remote com.mongodb.CommandFailureException: 
> { "serverUsed" : "localhost:27017" , 
> "ok" : 0.0 , "errmsg" : "can't convert from BSON type null to Date" , 
> "code" : 16006 , "
> codeName" : "Location16006"}
> {noformat}
> Perhaps Teiid could work around this somehow so that the VDB query will not fail and affected cells will contain null in the result?
> For example, this Mongo aggregation pipeline which extracts hours from timestamps:
> {noformat}
> db.collection.aggregate([
>   { 
>     $project : {
>       hour: {$hour: "$DATEVALUE"}
>     }
>   }
> ])
> {noformat}
> could be transformed to this:
> {noformat}
> db.collection.aggregate([
>   { 
>     $project : {
>       hour: { $cond: 
>         [ { $or: [ 
>                    { $eq: [ "$DATEVALUE", null ] }, 
>                    { $eq: [ { $type: "$DATEVALUE" }, "missing" ] }                                       
>                  ]
>           },
>           null, 
>           { $hour: "$DATEVALUE" } 
>         ] 
>       }
>     }
>   }
> ])
> {noformat}
> after this transformation, the {{hour}} field will be null as expected for documents where {{DATEVALUE}} is null or missing completely



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)