Known Issues for MongoDB Connector for BI

On this page

SQL Compatibility Issues

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)

← Release Notes for MongoDB Connector for BI