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)
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.
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.
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.