.. -*- coding: utf-8 -*-

======================
SonarSQL Release Notes
======================

.. contents::
   :depth: 1

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_

.. _SNRSQL-343: https://jsonar.atlassian.net/browse/SNRSQL-343
.. _SNRSQL-344: https://jsonar.atlassian.net/browse/SNRSQL-344

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`_.

.. _SNRSQL-47: https://jsonar.atlassian.net/browse/SNRSQL-47
.. _SNRSQL-65: https://jsonar.atlassian.net/browse/SNRSQL-65
.. _SNRSQL-75: https://jsonar.atlassian.net/browse/SNRSQL-75
.. _SNRSQL-139: https://jsonar.atlassian.net/browse/SNRSQL-139
.. _SNRSQL-178: https://jsonar.atlassian.net/browse/SNRSQL-178
.. _SNRSQL-179: https://jsonar.atlassian.net/browse/SNRSQL-179
.. _SNRSQL-181: https://jsonar.atlassian.net/browse/SNRSQL-181
.. _SNRSQL-184: https://jsonar.atlassian.net/browse/SNRSQL-184
.. _SNRSQL-243: https://jsonar.atlassian.net/browse/SNRSQL-243
.. _SNRSQL-253: https://jsonar.atlassian.net/browse/SNRSQL-253
.. _SNRSQL-267: https://jsonar.atlassian.net/browse/SNRSQL-267
.. _SNRSQL-276: https://jsonar.atlassian.net/browse/SNRSQL-276
.. _SNRSQL-279: https://jsonar.atlassian.net/browse/SNRSQL-279
.. _SNRSQL-281: https://jsonar.atlassian.net/browse/SNRSQL-281
.. _SNRSQL-285: https://jsonar.atlassian.net/browse/SNRSQL-285
.. _SNRSQL-287: https://jsonar.atlassian.net/browse/SNRSQL-287
.. _SNRSQL-288: https://jsonar.atlassian.net/browse/SNRSQL-288
.. _SNRSQL-289: https://jsonar.atlassian.net/browse/SNRSQL-289
.. _SNRSQL-290: https://jsonar.atlassian.net/browse/SNRSQL-290
.. _SNRSQL-291: https://jsonar.atlassian.net/browse/SNRSQL-291
.. _SNRSQL-295: https://jsonar.atlassian.net/browse/SNRSQL-295
.. _SNRSQL-299: https://jsonar.atlassian.net/browse/SNRSQL-299
.. _SNRSQL-300: https://jsonar.atlassian.net/browse/SNRSQL-300
.. _SNRSQL-304: https://jsonar.atlassian.net/browse/SNRSQL-304
.. _SNRSQL-305: https://jsonar.atlassian.net/browse/SNRSQL-305
.. _SNRSQL-306: https://jsonar.atlassian.net/browse/SNRSQL-306
.. _SNRSQL-307: https://jsonar.atlassian.net/browse/SNRSQL-307
.. _SNRSQL-313: https://jsonar.atlassian.net/browse/SNRSQL-313
.. _SNRSQL-319: https://jsonar.atlassian.net/browse/SNRSQL-319
.. _SNRSQL-320: https://jsonar.atlassian.net/browse/SNRSQL-320
.. _SNRSQL-322: https://jsonar.atlassian.net/browse/SNRSQL-322
.. _SNRSQL-326: https://jsonar.atlassian.net/browse/SNRSQL-326
.. _SNRSQL-328: https://jsonar.atlassian.net/browse/SNRSQL-328
.. _SNRSQL-329: https://jsonar.atlassian.net/browse/SNRSQL-329
.. _SNRSQL-331: https://jsonar.atlassian.net/browse/SNRSQL-331
.. _SNRSQL-332: https://jsonar.atlassian.net/browse/SNRSQL-332
.. _SNRSQL-333: https://jsonar.atlassian.net/browse/SNRSQL-333
.. _SNRSQL-345: https://jsonar.atlassian.net/browse/SNRSQL-345
.. _SNRSQL-347: https://jsonar.atlassian.net/browse/SNRSQL-347
.. _SNRSQL-349: https://jsonar.atlassian.net/browse/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 ...``.

.. _SNRSQL-275: https://jsonar.atlassian.net/browse/SNRSQL-275

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`_.

.. _SNRSQL-16: https://jsonar.atlassian.net/browse/SNRSQL-16
.. _SNRSQL-55: https://jsonar.atlassian.net/browse/SNRSQL-55
.. _SNRSQL-68: https://jsonar.atlassian.net/browse/SNRSQL-68
.. _SNRSQL-244: https://jsonar.atlassian.net/browse/SNRSQL-244
.. _SNRSQL-246: https://jsonar.atlassian.net/browse/SNRSQL-246
.. _SNRSQL-247: https://jsonar.atlassian.net/browse/SNRSQL-247
.. _SNRSQL-249: https://jsonar.atlassian.net/browse/SNRSQL-249
.. _SNRSQL-251: https://jsonar.atlassian.net/browse/SNRSQL-251
.. _SNRSQL-252: https://jsonar.atlassian.net/browse/SNRSQL-252
.. _SNRSQL-255: https://jsonar.atlassian.net/browse/SNRSQL-255
.. _SNRSQL-259: https://jsonar.atlassian.net/browse/SNRSQL-259
.. _SNRSQL-269: https://jsonar.atlassian.net/browse/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.

.. _SNRSQL-192: https://jsonar.atlassian.net/browse/SNRSQL-192
.. _SNRSQL-216: https://jsonar.atlassian.net/browse/SNRSQL-216
.. _SNRSQL-217: https://jsonar.atlassian.net/browse/SNRSQL-217

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`_

.. _SNRSQL-26:  https://jsonar.atlassian.net/browse/SNRSQL-26
.. _SNRSQL-72:  https://jsonar.atlassian.net/browse/SNRSQL-72
.. _SNRSQL-89:  https://jsonar.atlassian.net/browse/SNRSQL-89
.. _SNRSQL-115: https://jsonar.atlassian.net/browse/SNRSQL-115
.. _SNRSQL-117: https://jsonar.atlassian.net/browse/SNRSQL-117
.. _SNRSQL-150: https://jsonar.atlassian.net/browse/SNRSQL-150
.. _SNRSQL-166: https://jsonar.atlassian.net/browse/SNRSQL-166
.. _SNRSQL-170: https://jsonar.atlassian.net/browse/SNRSQL-170
.. _SNRSQL-180: https://jsonar.atlassian.net/browse/SNRSQL-180
.. _SNRSQL-182: https://jsonar.atlassian.net/browse/SNRSQL-182
.. _SNRSQL-184: https://jsonar.atlassian.net/browse/SNRSQL-184
.. _SNRSQL-186: https://jsonar.atlassian.net/browse/SNRSQL-186
.. _SNRSQL-188: https://jsonar.atlassian.net/browse/SNRSQL-188
.. _SNRSQL-208: https://jsonar.atlassian.net/browse/SNRSQL-208
.. _SNRSQL-218: https://jsonar.atlassian.net/browse/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`_

.. _SNRSQL-155: https://jsonar.atlassian.net/browse/SNRSQL-155
.. _SNRSQL-159: https://jsonar.atlassian.net/browse/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`_.

.. _SNRSQL-13: https://jsonar.atlassian.net/browse/SNRSQL-13
.. _SNRSQL-27: https://jsonar.atlassian.net/browse/SNRSQL-27
.. _SNRSQL-45: https://jsonar.atlassian.net/browse/SNRSQL-45
.. _SNRSQL-54: https://jsonar.atlassian.net/browse/SNRSQL-54
.. _SNRSQL-74: https://jsonar.atlassian.net/browse/SNRSQL-74
.. _SNRSQL-81: https://jsonar.atlassian.net/browse/SNRSQL-81
.. _SNRSQL-118: https://jsonar.atlassian.net/browse/SNRSQL-118
.. _SNRSQL-135: https://jsonar.atlassian.net/browse/SNRSQL-135
.. _SNRSQL-137: https://jsonar.atlassian.net/browse/SNRSQL-137
.. _SNRSQL-138: https://jsonar.atlassian.net/browse/SNRSQL-138
.. _SNRSQL-141: https://jsonar.atlassian.net/browse/SNRSQL-141
.. _SNRSQL-154: https://jsonar.atlassian.net/browse/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
~~~
* Support ``QUARTER()``: part of `SNRSQL-104`_.

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`_.

.. _SNRSQL-104: https://jsonar.atlassian.net/browse/SNRSQL-104
.. _SNRSQL-145: https://jsonar.atlassian.net/browse/SNRSQL-145
.. _SNRSQL-143: https://jsonar.atlassian.net/browse/SNRSQL-143
.. _SNRSQL-153: https://jsonar.atlassian.net/browse/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`_.

.. _SNRSQL-9: https://jsonar.atlassian.net/browse/SNRSQL-9
.. _SNRSQL-15: https://jsonar.atlassian.net/browse/SNRSQL-15
.. _SNRSQL-22: https://jsonar.atlassian.net/browse/SNRSQL-22
.. _SNRSQL-106: https://jsonar.atlassian.net/browse/SNRSQL-106
.. _SNRSQL-121: https://jsonar.atlassian.net/browse/SNRSQL-121
.. _SNRSQL-126: https://jsonar.atlassian.net/browse/SNRSQL-126
.. _SNRSQL-130: https://jsonar.atlassian.net/browse/SNRSQL-130


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
~~~~
* Ask root password only once: `SNRSQL-119`_.
* Incorrect configuration file permissions: `SNRSQL-120`_.
* Incorrect service status: `SNRSQL-121`_.

.. _SNRSQL-38: https://jsonar.atlassian.net/browse/SNRSQL-38
.. _SNRSQL-119: https://jsonar.atlassian.net/browse/SNRSQL-119
.. _SNRSQL-120: https://jsonar.atlassian.net/browse/SNRSQL-120
.. _SNRSQL-121: https://jsonar.atlassian.net/browse/SNRSQL-121

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
  <docs/mysql.rst#handling-simple-arrays>`__ 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`_

.. _SNRSQL-30: https://jsonar.atlassian.net/browse/SNRSQL-30
.. _SNRSQL-99: https://jsonar.atlassian.net/browse/SNRSQL-99
.. _SNRSQL-111: https://jsonar.atlassian.net/browse/SNRSQL-111
.. _SNRSQL-113: https://jsonar.atlassian.net/browse/SNRSQL-113

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`_.

.. _SNRSQL-15: https://jsonar.atlassian.net/browse/SNRSQL-15
.. _SNRSQL-77: https://jsonar.atlassian.net/browse/SNRSQL-77
.. _SNRSQL-78: https://jsonar.atlassian.net/browse/SNRSQL-78
.. _SNRSQL-83: https://jsonar.atlassian.net/browse/SNRSQL-83
.. _SNRSQL-88: https://jsonar.atlassian.net/browse/SNRSQL-88
.. _SNRSQL-90: https://jsonar.atlassian.net/browse/SNRSQL-90
.. _SNRSQL-91: https://jsonar.atlassian.net/browse/SNRSQL-91
.. _SNRSQL-92: https://jsonar.atlassian.net/browse/SNRSQL-92
.. _SNRSQL-93: https://jsonar.atlassian.net/browse/SNRSQL-93
.. _SNRSQL-100: https://jsonar.atlassian.net/browse/SNRSQL-100
.. _SNRSQL-101: https://jsonar.atlassian.net/browse/SNRSQL-101
.. _SNRSQL-102: https://jsonar.atlassian.net/browse/SNRSQL-102
.. _SNRSQL-103: https://jsonar.atlassian.net/browse/SNRSQL-103
.. _SNRSQL-107: https://jsonar.atlassian.net/browse/SNRSQL-107
.. _SNRD-2260: https://jsonar.atlassian.net/browse/SNRD-2260


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.

.. _SNRD-1928: https://jsonar.atlassian.net/browse/SNRD-1928

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`_.

.. _SNRSQL-4: https://jsonar.atlassian.net/browse/SNRSQL-4
.. _SNRSQL-17: https://jsonar.atlassian.net/browse/SNRSQL-17
.. _SNRSQL-25: https://jsonar.atlassian.net/browse/SNRSQL-25
.. _SNRSQL-66: https://jsonar.atlassian.net/browse/SNRSQL-66
.. _SNRSQL-67: https://jsonar.atlassian.net/browse/SNRSQL-67
.. _SNRSQL-69: https://jsonar.atlassian.net/browse/SNRSQL-69
.. _SNRSQL-71: https://jsonar.atlassian.net/browse/SNRSQL-71

.. _v0.2.6:

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`_.

.. _SNRSQL-50: https://jsonar.atlassian.net/browse/SNRSQL-50
.. _SNRSQL-51: https://jsonar.atlassian.net/browse/SNRSQL-51
.. _mariadb.org: https://downloads.mariadb.org/mariadb/repositories/#mirror=marty-anstey&distro=Ubuntu&distro_release=trusty--ubuntu_trusty&version=10.0

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.