Release Notes for MongoDB Connector for BI

Bug Fixes

• Fixes a bug where aggregate functions returned NULL in dual queries with the DUAL keyword omitted.

• Fixes a bug where some invalid DUAL queries did not return an error.

• Fixes a bug that prevented pushdown of some joins to derived tables selecting from DUAL.

• Fixes some correctness bugs in the in-memory evaluation for mysql-mode conversions.

• Fixes a bug where some trig functions (ascii, acos, and asin) could return incorrect results due to missing type conversions.

• Fixes a bug where some subquery comparisons could return incorrect results due to missing type conversions.

• Fixes a bug where we lost precision while converting a Uint64 to a Decimal.

• Fixes a correctness bug with the INSERT scalar function's pushdown translation.

• Fixes a bug where unstable sorting caused non-deterministic renaming of colliding collection/field names during schema mapping.

• Fixes a bug where some valid subquery comparisons returned errors.

• Fixes a bug where some valid subquery and tuple comparisons returned errors.

• Fixes a bug where queries using xor returned errors on MongoDB 3.2 because the generated pipelines required features added in MongoDB 3.4.

• Fixes a bug that caused kerberos authentication to fail with MongoDB 4.0.

Date and Time Changes

This release resolves a number of inconsistencies in the BI Connector's date and timestamp semantics. Resolving these inconsistencies resulted in some changes to user-facing behavior:

• Removes inconsistent support for partial dates.

  • MySQL can represent partial dates that are missing month or day components. It is impossible to represent these incomplete dates with MongoDB dates. Attempting to correctly manipulate and render incomplete dates was causing a number of bugs and inconsistencies with complete dates.

  • Starting with version 2.10, the BI Connector does not support operations on or expressions that return incomplete dates. Going forward, we cannot provide guarantees about the behavior of operations that attempt to construct incomplete dates.

• Updates the DATE(a) and TIMESTAMP(a) scalar functions to be equivalent to CAST(a AS DATE), and CAST(a AS DATETIME), respectively.

  • The behavior of the DATE and TIMESTAMP scalar functions will depend on the value of the type_conversion_mode variable. This will change the default behavior for those scalar functions.

  • Queries affected by this change can be rewritten to use a more explicit conversion function (such as str_to_date) that does not depend on type_conversion_mode. If an affected query cannot be rewritten, a user may set type_conversion_mode variable to mysql, which instructs the BI Connector to perform all type conversions with MySQL semantics instead of MongoDB semantics.

    Important

    This is discouraged unless absolutely necessary, as it significantly curtails the ability to push down type conversions across the board.

  • For more information on type conversions and the type_conversion_mode system variable, see Type Conversion Modes.

Features and Performance Improvements

• Includes a sample mongosqld configuration file in the release archive.

• Allows setting commonly used system variables in the configuration file.

• Optimizes conversions of years to days.

• Optimizes join performance.

• General performance improvements.

Bug Fixes

• Improves the BI Connector's resiliency to queries against fields that may contain unsupported BSON types.

Features and Performance Improvements

• Improvements to the BI Connector’s type system and the way it handles polymorphic data.

• Users can alter the type of a given column.

• Users can access “dirty” datasets without query errors.

• Support for pushdown of the GROUP_CONCAT aggregation function.

• Non-admin users can set global variables.

Bug Fixes

• Improvements in parsing strToDateTime to float.

• Fixes to how the BI Connector honors readPreference specifications.

Features and Performance Improvements

• Collapse associative functions

• Support the optimize_view_sampling global variable

• Support the polymorphic_type_conversion_mode session/global variable

• Improve filter optimization for MongoDB versions 3.6 and later

• Expose type_conversion_mode in configuration parameters

• Cache catalog columns for quicker lookup

Bug Fixes

• Fix incorrect semantics for null safe equality operator ( <=> )

• The || operator returns incorrect type (and value) when operating on two strings

• Add DRDL type validation

• Support CAST to unsigned

• Fix WHERE clause removed in certain cross-join queries

• Fix some incorrect left self-join results when dynamic schemas are used

• Support turning off view optimization with $match

• MongoDB BI Connector ODBC Driver on macOS should install in versioned directories

• Fix MongoDB BI Connector ODBC Driver installer issue on macOS that caused unicode driver to be reported as ANSI

Released version 1.2.0 of the MongoDB BI Connector ODBC Driver.

EXPLAIN Support

Users can use EXPLAIN to return details of aggregation pipeline used to generate query results.

Common Table Expression Support

The BI Connector now supports queries that include Common Table Expressions.

New SSPI Support

Windows Clients may now use kerberos to authenticate against the BI Connector.

NULL Valued Types Are Now Cast to Varchar During Sampling

This resolves the issue where queries would fail due to NULL values being ignored during sampling. Now, NULL values will be cast as SQL type VARCHAR.

SIN, COS, TAN Are Now Pushed Down

BI Connector will now push down SIN, COS, and TAN SQL functions. This will improve performance for queries using these functions.

Aggregate Expressions

BI Connector 2.4 improves support for aggregate expressions including math, date, and string functions.

Improved Support for Subqueries and Non-equijoins

With version 2.4, mongosqld has improved support for subqueries and non-equijoins.

Improved Schema Management

In BI Connector 2.3, mongosqld includes the schema mapping functionality previously found in mongodrdl.

Log Rotation

With version 2.3, you can rotate the mongosqld logs. To rotate the logs, issue a command or restart mongosqld.

Authentication Plugins

MongoDB Connector for BI Version 2.0 introduced passthrough authentication, in which clients authenticate as a user defined in MongoDB, without the need to create a separate user within the connector itself.

MongoDB Connector for BI Version 2.2 further increases ease of use with a set of authentication plugins which can authenticate users through the connector without the need for SSL certificates. These plugins perform user authentication via the SASL framework and support the SCRAM-SHA-1 and PLAIN authentication mechanisms. We currently provide authentication plugins in the following languages:

• Java - for use with MySQL’s Connector/J driver

• C - for use with the MySQL ODBC driver and the MySQL Shell

Improved Server-Side Execution

The MongoDB Connector for BI delivers significant performance and scalability improvements by moving as much execution as possible to the underlying MongoDB instance. Pushing execution down to the server reduces network latency and reduces bandwidth consumption.

This approach also reduces memory and CPU consumption in the MongoDB Connector for BI itself.

Pass-through Authentication

The user authentication process is now passed through the MongoDB Connector for BI to the underlying MongoDB instance. Users now authenticate as a user already created within MongoDB.

Simplified Architecture

The MongoDB Connector for BI package is now trimmed down from four separate components to two:

mongodrdl

This tool connects to a MongoDB instance and generates a Document-Relational Definition Language (DRDL) file, which maps a given collection shape to relational schema.

mongosqld

Once installed and run as a daemon, mongosqld responds to SQL queries, requests for DB information, and schema from the requesting BI tool via the MySQL wire protocol.

mongosqld requires at least one DRDL file to start. You will need to have defined your schema with mongodrdl before starting mongosqld.

SQL Compatibility

MongoDB Connector for BI Version 2.x is compatible with SQL-99 SELECT statements, and uses the MySQL wire protocol.

MongoDB Connector for BI 2.0.2

Released Jan 5, 2017

• Hides .pem key file passwords in logs.

• mongodrdl ignores binary fields when generating DRDL files.

• Supports queries that SELECT variables prefixed with "mongodb".

• Fixes incorrect result set from a CROSS JOIN.

• Supports leading "." characters on table names.

MongoDB Connector for BI 2.0.1

Released Dec 13, 2016

• Fixes precedence issue with the NOT operator.

• Fixes error looking up privilege information in databases with capital letters.

• Supports LIKE pattern matching in SHOW COLUMNS queries.

• Improves formatting of aggregation pipeline in logs.

• Fixes substring function when the combined length of the starting index and substring exceeds the length of the string.