Docs Menu
Docs Home
/
BI Connector
/
Release Notes

Known Issues for MongoDB Connector for BI

On this page

  • SQL Compatibility Issues
  • BI Connector Returns NULL Values Due to Mapping Error

SQL Compatibility Issues

BI Connector Returns Empty Set Where MySQL Returns NULL Values

Some SQL aggregate functions (like SUM, MIN, or COUNT) return NULL if there are no matching values for that aggregate function. This response is effectively an empty row.

Given the exact same SQL query, the BI Connector does not return NULL for any aggregate functions; it returns an empty set instead.

Example

You are analyzing the test performance of students for each of the classes offered in the last year by a university. Using Tableau, you run a SQL query to find statistics for each class, including the highest and lowest test scores as well as the sum and count of all the scores in the class.

SELECT SUM(scores), COUNT(*), COUNT(scores), MAX(scores), MIN(scores)
FROM students_2019
GROUP BY class;

Unfortunately, you accidentally run the query on the data for next year's classes, so there are no test scores for the query to find.

If the student data was stored in MySQL, this query would return a single row with NULL values for fields that are calculated with the SUM, MAX, and MIN functions and 0 for fields that are calculated with the COUNT function.

SELECT SUM(scores), COUNT(*), COUNT(scores), MAX(scores), MIN(scores)
FROM students_2019
GROUP BY class;
+-------------+----------+---------------+-------------+-------------+
| sum(scores) | count(*) | count(scores) | max(scores) | min(scores) |
+-------------+----------+---------------+-------------+-------------+
|   NULL      |        0 |             0 |   NULL      |   NULL      |
+-------------+----------+---------------+-------------+-------------+

If the student data was stored in MongoDB and accessed via the BI Connector, this query would return an empty set.

SELECT SUM(scores), COUNT(*), COUNT(scores), MAX(scores), MIN(scores)
FROM students_2019
GROUP BY class;
Empty set (0.00 sec)

BI Connector Returns NULL Values Due to Mapping Error

A known mapping error can occur that causes BI Connector to return all NULL values. This error occurs when you query a collection that contains an array field that can be NULL, and the same array contains an additional field that can also be NULL. A workaround for this error is to create a view with the $match filter that only returns documents in which the field $type is array with the createView method.

Example

For a collection named nextDeparture with an array field named response.schedule that is sometimes NULL, you can create the following view:

db.createView('nonullsched', '"nextDeparture"',  [{'$match': {'response.schedule': {'$type': 'array'}}}])

Back

System Variables