[teiid-issues] [JBoss JIRA] (TEIID-5296) With MongoDB, timestamp operations throw exceptions when called on null or missing values
[
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)