Known Issues for MongoDB Connector for BI
On this page
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)