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

Tuesday, 24 April 2018

    [
https://issues.jboss.org/browse/TEIID-5296?page=com.atlassian.jira.plugin...
] 

Jan Martiska commented on TEIID-5296:
-------------------------------------

[~shawkins], FYI what you mention in your last comment doesn't seem to work for
MongoDB 3.2 and 3.4 (works with 3.6), for missing values I'm still getting an error
with 3.2 and 3.4:

{noformat}
...
 db.test1.drop() true
...
 db.test1.insertMany([ ... { _id: 1, timestamp:
ISODate("1999-12-31T23:05:11Z")},
... { _id: 2}
... ])
{ "acknowledged" : true, "insertedIds" : [ 1, 2 ] }
...
 db.test1.aggregate([ ...   { 
...     $project : {
...       hour: { $cond : [ { "$eq" : [ "$timestamp" ,  null ]} , 
null  , { "$hour" : [ "$timestamp"]}]}
...     }
...   }
... ])
assert: command failed: {
	"ok" : 0,
	"errmsg" : "can't convert from BSON type missing to Date",
	"code" : 16006,
	"codeName" : "Location16006"
} : aggregate failed
{noformat}

So if we decide to backport this to JDV (which supports 3.2/3.4) at some point, we would
probably need to first substitute missing values with nulls, as I suggested in the first
comment, or find a different solution.

...
 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)

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009