SonarSQL Release Notes

1.2.4

Setup

  • Do not specify setup or upgrade when running sonarsql-setup. Just run sonarsql-setup and it will do the right thing. To force setup, remove the file /etc/default/sonarsql, and run again.

SQL

  • sonarSqlUser role created by SonarSQL contains the following additional actions to complete find: collStats, dbHash, dbStats, killCursors, listIndexes.
  • Improved support for PowerBI.
  • Allow joins on information_schema tables.

Misc

  • Allow specifying log-file, log-size, and log-rotate in the configuration file.
  • Logs are smaller in size. Use --debug to get detailed debug information.

Bugs

  • Handle new syntax for creating users: SNRSQL-344.
  • Set authentication mechanism to MONGODB-CR before creating users.
  • Allow simple queries to use a collection from a different database: SNRSQL-343

1.2.1 (Sep 13, 2016)

SQL

  • Added support for window functions e.g. AVG(foo) OVER (ORDER BY bar).
  • utf8_general_ci is now listed as the default collation in SHOW COLLATION.
  • Handle : in a simple regular expression, so that %foo:bar% is translated to /foo:bar/i.
  • Add and Use information_schema builtin views and information_schema.routines.
  • Use SonarW builtin views for information_schema.* tables. This allows showing information from different databases, and handling of complex information_schema queries. Many SHOW * commands also use these tables so are handled correctly as well. needed for support of many 3’rd party tools.
  • Add Support for: SHOW STATUS, SHOW KEYS, SHOW PROCEDURE STATUS, SHOW FUNCTION STATUS, SHOW TRIGGERS, SHOW EVENTS, SHOW PLUGINS (returns empty result), SHOW ENGINES (returns empty result), SHOW CREATE TABLE, SHOW TABLE STATUS.
  • Support querying mysql.proc.
  • Project fields selected using SELECT * FROM (SELECT...).
  • Support arbitrary nesting of subqueries.
  • Boolean value from Sonar will be given as a tinyint(1) and value of 1 or 0. to mysql clients.
  • Date interval calculation field type is now TIMESTAMP.
  • Upgraded the parser to support MariaDB 10.2 syntax.

Optimizations

  • When doing unwind, if a match is requested for fields that do not require unwind, match these first.
  • Allow complex expressions in $elemMatch.

Package

  • End user license agreement is installed to /usr/share/doc/sonarsql/eula.txt.
  • Release notes installed in /usr/share/doc/sonarsql/release-notes.rst.
  • Removed dependency on MariaDB packages and repositories.
  • Default uri now includes admin as the default database.

Misc

  • Change to default port SonarSQL listens on is 27133.
  • Performance improvement for metadata handling of complex/many types.
  • Requires sonarw-v1.4.0-3934. Not compatible with sonarw-1.2.

Bugs

  • Use alias in the query with a subquery from: SNRSQL-295.
  • Handle reordering of tables within a join in a subquery: SNRSQL-299.
  • Fix issue in join with NOT LIKE condition: SNRSQL-304.
  • Correct handling of bool in a join comparison: SNRSQL-305.
  • Implement LIKE and NOT LIKE for non-string fields: SNRSQL-306.
  • Unrestricted cross joins: SNRSQL-307.
  • Enable conditions on different DIM tables using the same selector: SNRSQL-319.
  • Fix pipeline involving SQL having : SNRSQL-320.
  • Fix handling of not equal to null : SNRSQL-322.
  • Properly exclude cases when not using $group to handle DISTINCT: SNRSQL-276.
  • Allow duplicate condition functions (e.g. a!=1 AND a!=2): SNRSQL-326.
  • Fix NULL takes over integer data type: SNRSQL-328.
  • Clean up mariadb internal structure after parsing: SNRSQL-331.
  • Invalid translation of AND when same table is aliased twice: SNRSQL-332.
  • Do not allow compressed protocol: SNRSQL-329
  • RPM post install to detect upgrade: SNRSQL-281.
  • Use new $distinct to handle DISTINCT with limits and subqueries: SNRSQL-313.
  • Handle TRUNCATE and ROUND with negative arguments: SNRSQL-253.
  • Raise an error when trying to use a LIMIT in a subquery: SNRSQL-65.
  • Handle COLLATE in select list: SNRSQL-47.
  • Do not cast double as int64_t: SNRSQL-345.
  • Only use bigints if there are bigints present in the data set: SNRSQL-347.
  • Maintain column order: SNRSQL-349.

1.2.0-30 (May 6, 2016)

SQL

  • Detect the condition that a query contains an unrestricted cross join. The query has mentioned a DIM table, but there is no where clause to define how this dimension table relates to other tables.
  • Handling conditions on multiple DIM tables with selectors. The pipeline was poorly formed, not separating out the selectors.

1.2.0 (Apr 30, 2016)

SQL

  • Allow ORDER BY NULL in MySQL. This means no sorting.
  • Generate an error when trying to order by a constant e.g. ORDER BY "foo". This will avoid situations when a field name is quoted using double quotes instead of backticks.

Pipeline

  • Translate COUNT DISTINCT using the new $countDistinct group operator in SonarW.
  • When grouping by a single expression, do not create an _id sub-document in the $group stage, rather use _id: <expr> directly. This is an important optimization for SonarW.

Misc

  • Refuse to run any queries against a SonarW that is too old.
  • Requires v1.1.12-175 now for $countDistinct.

Bugs

  • Imply $and only in top level of a query operator: SNRSQL-275.
  • Handle the case of an alias the same as a field name e.g. select points+1 as points, points ....

1.1.12 (April 10, 2016)

SQL

  • disabled by default GROUP before JOIN. If you require this feature, run SET @groupFirst = 1. The setting will be in effect for the remainder of the session. The default is 0.
  • Support single subquery in FROM clause. Covers SNRSQL-16, SNRSQL-244.
  • Issue explicit error if OR is detected as being used in a join condition: SNRSQL-251.
  • Support expressions in ORDER BY clause: SNRSQL-68.
  • Support INSERT...SELECT syntax with subqueries in FROM clause.
  • SHOW TABLES only shows tables from the metadata: SNRSQL-249.
  • SELECT ... LIMIT 0 returns an empty set (was an OK packet): SNRSQL-247.
  • Support for SHOW TABLES LIKE ...
  • Support for SHOW FULL TABLES [LIKE...].
  • SHOW VARIABLES responds with an empty set if no variables match (was an OK packet).

Pipeline

  • Use simplified expression syntax in $having inside $join and $group stages.
  • Reuse expressions that have already been projected and available.
  • Do not automatically project every $group key with $first. If it’s needed in subsequent stages, _id.<field> will be used.

Bugs

  • Remove $expr from match-like stages after $group: SNRSQL-55.
  • Keep $expr for <field> <op> <field> non-join filters: SNRSQL-246.
  • Ignore NULL in COUNT DISTINCT: SNRSQL-252.
  • Handle . (dot) in field names referring to subdocuments in GROUP: SNRSQL-259.
  • Handle multiple $expr involving IN: SNRSQL-255.
  • Handle subselect filters mixed with join conditions: SNRSQL-269.

1.1.10 (February 27, 2016)

SQL

  • Support BETWEEN as a join condition SNRSQL-216.
  • Support specifying roles when creating users like CREATE USER <user>@<role>
  • Handle unqualified column names, attempting to determine the table to which a column belongs. Covers SNRSQL-192, SNRSQL-217.

Misc

  • Added --verbose option. This will print debug messages to the log.

1.1.7 (January 20, 2016)

SQL

  • Support inequalities and expressions in join conditions.
  • Translate RIGHT(): SNRSQL-117.
  • Support expressions in LIKE and NOT LIKE as well as translating them when they appear in the select list.
  • Added CHARACTER_SET_NAME to INFORMATION_SCHEMA.COLUMNS table: SNRSQL-184.
  • Ignore null types from the metadata if there are other types for the field.
  • Support SHOW WARNINGS: SNRSQL-188.
  • Translate basic join conditions involving cyclic relations between tables
  • Better compatibility with MySQL protocol, in attempt to fix SNRSQL-186.
  • Support filters on outer joins SNRSQL-150.
  • Return error on unsupported prepared statement commands
  • Return proper error when cursor gets killed during a query (e.g. due to maxTimeMS)

Package

  • Adapt startup scripts to Amazon Linux (upstart 0.6.5).

Pipeline

  • Optimization: Use $having inside $group instead of a $match stage following $group to handle HAVING clause.
  • Queries on INFORMATION_SCHEMA will always turn the optimizer off.
  • Use $group to simulate distinct when select list contains only one field, covers SNRSQL-208.

Misc

  • The full version number of both SonarSQL and SonarW will be reported by the MySQL client upon connecting.
  • Remove --debug option for the binary. Always run in console.

Bugs

  • Support COUNT(DISTINCT()) in select list when another field or ref is also in select list: SNRSQL-72
  • Translate ORDER BY aggregate functions when those functions do not appear elsewhere in the statement: SNRSQL-170
  • Handle MIN/MAX for integers: SNRSQL-166
  • Support LIKE in case: SNRSQL-89
  • Support table alias in INFORMATION_SCHEMA queries: SNRSQL-180.
  • Hide the special lmrm_bson_ field from DESCRIBE: SNRSQL-182.
  • Regression of SHOW VARIABLES LIKE: SNRSQL-26.
  • Fix translation of WHERE conditions involving fact and dimension fields.
  • Unsuppress projecting _id in $group: SNRSQL-115.
  • Fixed incorrect optimization for BETWEEN in datetime: SNRSQL-218

1.1.0 (January 7, 2016)

SQL

  • Support UNION

Bugs

  • Fixed access to freed memory in constants: SNRSQL-155
  • INSERT...SELECT properly appends to the out collection, rather than erasing and creating.
  • Fix adding a negative YEAR_MONTH in DATE_ADD, DATE_SUB: SNRSQL-159

1.0.7 (December 22, 2015)

Package

  • New configuration file path: /etc/sonar/sonarsql.conf.
  • Binaries are now installed under /usr and not /.
  • RPM package

Pipeline

  • Do not use { $project: { *:1 } } phase, as it is redundant and hurts performance.

SQL

  • Support basic NOT LIKE: SNRSQL-13.
  • Support SHOW VARIABLE WHERE Variable_name='foo': SNRSQL-141, SNRSQL-54.
  • Support the NULL safe equals operator (<=>): SNRSQL-138.
  • DATABASE(), USER(), CURRENT_USER() and CONNECTION_ID() are now first class expressions, meaning they can be used anywhere in the query: SNRSQL-137, SNRSQL-135.
  • Support CASE...WHEN in a GROUP: SNRSQL-118.
  • Support CASE WHEN <field>... by explicitly testing <field> for being null: SNRSQL-81
  • Support MAX and MIN for strings: SNRSQL-74.
  • Transfer real boolean true/false values to SonraW when used in a query (instead of 1/0): SNRSQL-45.
  • Better support for user and system variables of different types through SELECT @@variable syntax.
  • Support IS TRUE, IS NOT TRUE, IS FALSE, IS NOT FALSE: SNRSQL-27.

Bugs

  • Support expressions within compond-interval EXTRACT().
  • Fix date formatting to only apply at top level date/time cast when in select list: SNRSQL-154.

1.0.6 (December 14, 2015)

Pipeline

  • Allow disabling of the SonarW optimizer by using SET @sonarwOptimizer=0. This will prepend $optimizer:0 to the pipeline and will take effect for the remainder of the session. Use SET @sonarwOptimizer=1 to re-enable the SonarW optimizer.
  • Allow disabling of implicit table reordering in JOIN by specifying SET @explicitTableOrder=1. The settings will take effect for the remainder of the session. The default value is 0.
  • Join v2. This matches changes in sonard build 937 or later. INNER and LEFT joins supported.

Misc

  • Correct SonarSQL and SonarW version will be reported when running SELECT @@version_comment
  • Keep old metadata if cannot get updated metadata from SonarW.

SQL

Bugs

  • Handle COALESCE with a single argument: SNRSQL-143.
  • Correctly handle SELECT * when some fields have nested objects.
  • Fix aggregating on dimension columns: SNRSQL-145.
  • Fix $match in a snowflake multi-left join: SNRSQL-153.

1.0.4 (November 25, 2015)

SQL

  • Support DATE_ADD(), DATE_SUB() for SECONDS, MINUTES, HOURS DAYS, WEEKS, MONTHS, QUARTERS, YEARS, YEARS-MONTHS: part of SNRSQL-106.
  • Support LEFT JOIN.
  • Support grouping on dimension columns.

Misc

  • Accept IPv6 connections as well.
  • Obey multi-thread limitations of mongo-cxx driver: Block SonarW access on the same connection while waiting for metadata to be created.
  • Constant expressions of the form DATE(YYYY-MM-DD) will be converted to dates immediately anywhere (no $toDate in this case): SNRSQL-126.
  • Properly daemonize, and allow upstart correct pid tracking: SNRSQL-121
  • Run a single thread when --debug is used.
  • metadata collection will be regenerated if it is found to be empty.

Bugs

  • Put subselects in proper $match stage: SNRSQL-130.
  • In statements with join, separate dimension columns from fact columns: SNRSQL-9.
  • Optimization: finish basic support of * in statements with joins: SNRSQL-15.
  • Fix pipeline when alias is used on dimension field: SNRSQL-22.

1.0.3-1 (November 10, 2015)

  • Depend on specific libmariadbd-dev and mariadb-server-core-5.5 versions
  • Update dependency on libmongocxx to support $distinctDocs change in SonarW.

1.0.3 (November 4, 2015)

Pipeline

  • Optimization: use $selector in $join for filters on dimension collections
  • Optimization: run filters on fact table before $join stage, covers SNRSQL-38

Package

  • Add a configuration file option --config or -c.
  • Use a configuration file /etc/sonarsql.conf by default instead of command line parameters. The installation scripts have been modified to update this file instead of /etc/default/sonarsql, which is not being used anymore. Command line options can still be given as before and are given priority over the values in the configuration file.

Misc

  • sonarSqlUser role will be created when creating users if it does not exist.
  • metadata refresh (db.runCommand({lmrm__metadata:1})) returns immediately if there is an lmrm__metadata2 collection. If there is no lmrm__metadata2 collection, the system will wait until it is generated. After that, it will be refreshed every 10 minutes per each user connection.
  • Ignore permission errors on /tmp/sonarsql.

Bugs

1.0.0 (October 20, 2015)

SQL

  • Support negatives, as in SELECT -1.

Pipeline

  • Underlying arrays are only unwound if selected specifically. SELECT * will not generate an $unwind, but SELECT a will (given a represents an array). See here for more information.
  • In the case above, the text array will show up in the Extra column on the output of DESCRIBE: SNRSQL-99
  • Use SonarW $distinct support for DISTINCT instead of simulating with GROUP
  • On queries that involve the same field more than once, consolidate the match query just like an $elemMatch would, but do not use $elemMatch itself e.g. WHERE results > 80 AND results<=85 would be translated as {$match: { results: { $gt: 80, $lte:85 }}}. This works if results is a scalar, or an array of scalars. In the latter case, $elemMatch is implied by SonarW.
  • Queries that involve an array of subdocuments but do not unwind the array will use $elemMatch if there is more than one question on the subdocuments.
  • Queries that involve an array of subdocuments and unwind the array will use $elemMatch within the $unwind expression.

Misc

  • Create users with sonarSqlUser role in SonarW. The role must be pre-existing on SonarW.
  • Remove CLIENT_MULTI_STATEMENT capability from the server handshake: SNRSQL-113.
  • Handle COM_SET_OPTION - but if we are asked to turn on multi statements, respond with an error: SNRSQL-113.

Package

  • Show the typed root password during configuration.
  • Now requires libmongocxx>=0.3.4.

Bugs

  • Reset root password every time reconfigure is requested: SNRSQL-111
  • Return distinct records when DISTINCT used with complex expressions in select list: SNRSQL-30

0.2.9 (October 7, 2015)

SQL

  • Support getting the current time and date, local(based on the server’s timezone), or UTC. The following are implemented, and will return a string: CURDATE(), CURRENT_DATE(), CURTIME(), CURRENT_TIME(), CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP(), NOW(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP. Changing of the server timezone requires a restart of sonarsql to take effect.
  • Support EXTRACT with units YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND: SNRSQL-90.
  • Bogus user variables (@foo) return NULL value, and do not crash: SNRSQL-92.
  • Handle correctly select constant (SELECT 1 FROM foo): SNRSQL-93.
  • Support INSTR() and LOCATE(): SNRSQL-91.
  • Support IN in SELECT LIST: SNRSQL-88.
  • Support expressions on left side of IN, covers SNRSQL-78, SNRSQL-107.
  • When using SELECT * with JOIN, all fields from both tables will be projected: SNRSQL-15.
  • Using unknown table/alias will generate an ‘unknown table’ error.
  • Support accepting a string as a parameter in date functions.

Pipeline

  • Use a single $expr within $match: SNRSQL-77
  • Optimize $match for HAVING (as well as WHERE): SNRSQL-83
  • Optimize $match for IS NULL and IS NOT NULL, the same way we do for simple expressions (like = or !=) (see v0.2.6).
  • Only use $literal when trying to project a constant.
  • Translate SELECT * to project *:1 instead of individual field names.
  • Use $literal for information_schema mock tables: SNRD-2260, SNRSQL-100.
  • Use *:1 within join: SNRSQL-101.

Misc

  • Broader permissions on /tmp/sonarsql directory allow occasional use of sql2pipeline and running as users other than sonarsql.
  • Hide URI password in the log file and command line arguments (e.g. ps).

Bugs Fixed

  • Long integers use regression: SNRSQL-102.
  • Qualified * (e.g. table.*) will project all fields from the the named table: SNRSQL-103.

0.2.8 (September 22, 2015)

SQL

  • Support current_user() (for MySQLWorkBench).
  • SonarSQL will bail on parse errors.
  • field names in SELECT/WHERE/GROUP clauses must match by case.
  • Support alias with DISTINCT(column).
  • Support expressions containing aggregate functions in clauses other than GROUP.
  • Support using long integers in SQL expressions.
  • Fix COUNT(x) to work with x being any expression.
  • Support UTC_TIMESTAMP() returning a string.

Pipeline

  • Optimize regular expressions of the form %[a-zA-Z0-9\-_] to just be a substring match /<text>/. Backslash all other characters that may not match themselves (namely .[{}()\*+?|^$).
  • Properly create $group using id null when no explicit GROUP clause is specified, after SNRD-1928 fixed.

Package

  • Added missing boost-regex runtime dependency.

0.2.7 (September 16, 2015)

SQL

  • Support aggregate expressions in HAVING properly, i.e. works with and without aliases
  • Expand column alias support, handles column aliases in SELECT, HAVING, ORDER, GROUP
  • Properly detect result type for IFNULL statements: SNRSQL-4.
  • Support LENGTH() for strings: SNRSQL-71.

Pipeline

  • Optimize $match to appear before $group for all filters that refer only to the fact table and do not refer to aggregate functions
  • Run with --max-time-ms=<value> to set the default value for maxTimeMS. This can be overridden per session using SET @maxTimeMS=<value> within MySQL, and examined using SHOW @maxTimeMS. If you do not supply a value, the system default is 100,000. You can add this parameter to the SONARSQL_OPTS variable in /etc/default/sonarsql if you need. Since incorrect values can break the system, this cannot be configured using dpkg-reconfigure: SNRSQL-69.

Fixes

  • Support HAVING(COUNT(1)>0) : SNRSQL-17.
  • Sort by aggregate function : SNRSQL-25.
  • Do not return empty tables or collections to MySQL client. This resolves a crash when an empty collection exist in SonarW: SNRSQL-67.
  • COUNT(*) now uses 64 bit integers: SNRSQL-66.

0.2.6 (August 26, 2015)

SQL

  • Never drop records, even if all fields turn out to be NULLs: SNRSQL-51.
  • Support IN <subselect>.
  • Arguments for SUBSTR() can now be any expression.
  • Support LEFT().

Pipeline

  • Stop using $expr in $match when can do without (simple expressions).
  • Collapse $and: [ { k1:v1 }, { k2: v2 }, ...] to { k1:v1, k2:v2, ...} when all keys k1, k2, ... are unique.
  • BETWEEN DATE('YYYY-MM_DD') AND DATE('YYYY-MM_DD') now translates to $inBetween.

Authentication

  • CREATE USER now creates a user both in SonarSQL and SonarW, with the same password.
  • SonarSQL verifies the user when she logs in, and also logs her into SonarW on the backend side.
  • To create users you must be logged in as root to SonarSQL.
  • Specify SonarSQL root password hash in the command line using --root-pwhash.
  • Generate SonarSQL root password hash in the command line using --generate-root-pwhash.

Packaging

  • When installing the package, you will be asked for the root password. You can modify this selection later by running dpkg-reconfigure sonarsql.
  • Configure uri and root password upon package install, or by using dpkg-reconfigure.
  • SonarSQL now requires libmongocxx-0.3.2. Find it in the packages/ directory.
  • No longer suggesting to install maxscale after installing the debian package.
  • SonarSQL now requires mariadb-10.0 libraries. Install from mariadb.org (vagrant will install it automatically for new machines. For existing machines, either recreate them, or re-run the provisioning scripts).

Miscellaneous

  • Display version number in log files, and when ran with --version.
  • Rotated log files are now named sonarsql.log.n.

Fixes

  • Better percision for floating points : SNRSQL-50.

0.2.5 (July 31, 2015)

  • Work somewhat asynchronously with SonarW, so that we can handle more jobs than thread (though in this case, performance will start degrading).
  • Removed support for using with MaxScale mongosql filter module.
  • Control SonarW execution limit using set @maxTimeMS=<limit in ms>. View the current settings using select @maxTimeMS. This is per session, and the default is 100,000.
  • SQLAlchemy/JDBC fixes:
    • support SHOW VARIABLES LIKE.
    • support ROLLBACK (but does nothing).
    • support SHOW COLLATION [WHERE...] (but ignores the WHERE part).
  • Debian/Ubuntu package now installs a service, and a configuration file in /etc/default/sonarsq.
  • Debian/Ubuntu package now creates and uses a special user sonarsql.
  • When you run sonarsql it will daemonize by default. Use -X to stay in foreground (for debugging).

0.2.4 (July 21, 2015)

  • Configurable threads using --threads.

0.2.3 (July 21, 2015)

  • JDBC can now connect (the issue was in the authentication packet).
  • Fixed package dependencies.
  • multi-threaded logging.
  • boolean system variables support.