Navigation

Type Conversion Modes

On this page

New in version 2.6.

The MongoDB Connector for BI attempts to convert SQL queries into MongoDB aggregation pipelines. When the BI Connector cannot translate a query into an aggregation pipeline, it executes the query in memory resulting in increased latency. Prior to version 2.6, mongosqld could not translate type conversions to aggregation expressions, which prevented the translation of any query relying on type conversions.

Starting in version 2.6, the mongosql type conversion mode pushes all type conversions down to MongoDB servers running version 4.0 or later for improved expression performance. This mode is enabled by default but you can configure it on a per-session basis by setting the type_conversion_mode session variable to one of the following values:

Mode Description
mongosql Default. Pushes all type conversions down to MongoDB servers running version 4.0 or later. For MongoDB versions prior to 4.0, the BI Connector performs type conversions in memory.
mysql Instructs the BI Connector to match MySQL’s type conversion semantics as closely as possible. If the BI Connector can reproduce MySQL’s type casting behavior in the aggregation language, it will push the expression down to the MongoDB server for execution. Otherwise, mongosqld executes the expression in memory.

The following example sets the type_conversion_mode variable to mysql:

SET SESSION type_conversion_mode = 'mysql';

Important

mongosql is the recommended type casting mode unless you require close replication of MySQL’s behavior. The example above sets the mysql type conversion mode because mongosql is the default and does not need to be explicitly set.

The type conversion mode applies to both:

  • Explicit type conversions. For example:

    CAST("123" AS unsigned)
    
  • Implicit type conversions. For example, "123" + 456

Mode Comparison Table

The following table provides an overview of how MySQL’s type conversion behavior differs from the BI Connector’s default type conversion behavior (mongosql mode):

From Type To Type MySQL Behavior mongosql-mode Behavior
Varchar Numeric Parse the number from a string in a variety of different formats. For more information, see the MySQL Numeric Literals documentation. Parse the number from a string in decimal format with optional leading sign character.
Varchar Timestamp Parse the date from a string in a variety of different formats. For more information, see the MySQL Date and Time Literals. Parse the date from a string in the "%Y-%m-%dT%H:%M:%S.%LZ" format.
Int Timestamp Attemps to parse the date from the varchar representation of the int. For example, the integer 20180809183456 becomes the date 2018-08-09 18:34:56.000000. Treats the input as milliseconds since the unix epoch. For example, 1533839696000.
Timestamp Long Parse the timestamp as a long in the YYYYMMDDHHMMSS format. For example, 20180701123400. Parse the timestamp as a long representing the milliseconds since the unix epoch. For example, 1530448440000.
Timestamp Varchar Parse the timestamp as a varchar in the YYYY-MM-DD HH:MM:SS.MMMMMM format. For example, “2018-07-01 12:34:00.000000”. Parse the timestamp as a varchar in the YYYY-MM-DDTHH:MM:SS:MMMZ format. For example, “2018-07-01T12:34:00.000Z”.

Note

mongosql is the recommended type casting mode unless close replication of MySQL’s behavior is required.