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.