Changes in 20.2.16

Topics

New Features

Modified the Hive/Big Data SQL integration mechanism to support querying table fields of the data types FieldDef.Type.JSON, FieldDef.Type.NUMBER, and FieldDef.Type.TIMESTAMP. Along with this feature, new example code is also provided to help create tables with fields of these new types.
[#25802]
Support Time to Live (TTL) in the Streams API and in Multi-Region Tables. The Rows supplied by the Streams API to subscribers will include the TTL expiration time for the operation from source store. Note that the expiration time is computed from the original TTL at source store where and when the operation is made, instead of the time when Streams API receives the operation. Therefore, the operation that Streams API returns may have already expired if its expiration time has passed. For Multi-Region tables, the TTL expiration time of each row is replicated to other regions, and thus a row in Multi-Region table in any region will expire at the expiration time replicated from the region where and when operation is made.

We strongly recommended upgrading Multi-Region agents and stores in all regions to this release before specifying any rows with non-zero TTL values for Multi-Region tables. Expiration times will be lost when rows are replicated to regions running older software versions, meaning that the copied rows will not expire.
[#28165]
Make XRegion Service agent parameter configurable in JSON config file. This release adds following configurable parameters with their default values in the JSON config file:
```
{
  "checkpointIntvSecs" : 300,
  "checkpointIntvOps" : 1048576
}
```
[#28152]
Implemented generic ORDER BY and GROUP BY.

In previous releases, ORDER BY and GROUP BY were possible only if there was an index that sorted the rows by the ORDER BY/GROUP BY expressions. Furthermore, it was not possible for queries to include both ORDER BY and GROUP BY. These restrictions are lifted in this release. For example, the following query returns the monthly sales for year 2020, ordered by the sales amount.
```
select f.xact.month, sum(f.xact.amount) as sales
    from Foo f
    where f.xact.year = 2020
    group by f.xact.month
    order by sum(f.xact.amount);
```
Notice that generic ORDER BY and GROUP BY may consume a lot of driver memory, because of the need to materialize the full query result in driver memory. In contrast, index-based ORDER BY/GROUP BY exploit the row sorting provided by the index to avoid the materialization and caching of any intermediate results. As a result, it is recommended that users create appropriate indexes for use in ORDER BY/GROUP BY queries. For example, if there is an index on both xact.year and xact.month, the above query will use that index and the GROUP BY will be an index-based one. If no such composite index exists, but instead there are two separate indexes on xact.year and xact.month respectively, the above query will use the index on xact.year and the GROUP BY will be a generic one. This is because, in general, indexes that "cover" query predicates are preferred over indexes that "cover" ORDER BY/GROUP BY expressions.

For generic ORDER BY/GROUP BY, applications can specify (via a query-level execution option) how much memory such operations are allowed to consume at the driver, and a query will raise an exception if the maximum allowed threshold is exceeded.
[#28202]
Implemented SELECT DISTINCT.

The DISTINCT keyword is now supported in the SELECT clause. If present, duplicate results will be removed from query result set. As with generic ORDER BY and GROUP BY, SELECT DISTINCT needs to cache the full result set in driver memory. The same execution option controls how much memory such operations are allowed to consume at the driver.
[#28202]
Introduced a new Admin CLI command show mrtable-agent-statistics to show the latest statistics for Multi-Region table agents.
```
show mrtable-agent-statistics [-agent <agentID>]
[-table <tableName>] [-merge-agents]
```
With no argument, the command shows combined statistics over all tables for each agent. The -agent flag limits the statistics shown to the agent with the specified agent ID. The -table flag limits the statistics shown to the Multi-Region table with the specified name. The -merge-agents flag combines statistics over all agents.
[#28155]
Key distribution statistics enabled by default.

The collection of key distribution statistics is now enabled by default. You can control whether statistics are collected by setting the rnStatisticsEnabled parameter.
[KVSTORE-155]
Key distribution statistics include storage size information by default.

The collection of key distribution statistics now, by default, collects information about the storage size of the associated table entries. This information can be used to find the storage size for a table in specific partitions and across the store as a whole.

The new statsIncludeStorageSize parameter controls whether the collection of key distribution statistics includes information about entry storage sizes.
[KVSTORE-375]
Updated the topology change-repfactor Admin CLI command to support reducing the replication factor for online secondary zones. You can now use this command to reduce the replication factor for a secondary zone that you want to keep, or in preparation for removing the zone. To remove a secondary zone, use topology change-repfactor to change the replication factor of the zone to zero, and then use the plan remove-sn and plan remove-zone commands to remove the storage nodes and complete the removal of the zone. Note that you can only reduce the replication factor of a zone whose storage nodes and replication nodes are currently online.
[KVSTORE-185]
In the regex_like function, the source param is now always implicitly cast to a string.
[KVSTORE-342]

Bug and Performance Fixes

Fixed a bug which could cause OutOfMemoryErrors in service processes when there are many new client connections established during a short time. The problem is more likely for secure stores. It also appears more often on SNs, since they usually have smaller heap sizes. A heap dump from the process with such problem will show a large number of SSLDataChannel and ConnectTimeoutTask.
[KVSTORE-288]
Fixed a bug that the XRegion Service agent may be blocked when a multi-region table is not found at remote regions. If any Multi-Region table is missing, the table polling thread will be created if not exist, and periodically poll the remote regions for missing tables till the table is found, and the thread will start initialization and include it in the running stream.
[#28218] [#28194]
Fixed incomplete table initialization bug.

When the Multi-Region agent restarts from a crash, there was a problem that incomplete table initialization might not resume correctly, resulting in table corruption in some cases. The fix ensures that when the Multi-Region agent restarts, the agent will reinitialize all tables whose initialization was not complete before the crash.
[KVSTORE-321]
Fixed bug in min/max aggregate functions.

In previous releases, these functions were non-deterministic if their input were values of different types. For example, if you had min(t.a.b) and t.a.b was a number in some rows and a string in others, the result would be either the min number or the min string, depending on which row was encountered first. For ORDER BY, we already defined an ordering among otherwise non-comparable values. This ordering is now used for min/max as well, thus making their results deterministic.
[#28202]
Fixed a bug that can cause a query that has both index-based group-by and joins to miss one result per partition/shard.
[KVSTORE-276]
When running Big Data SQL queries on tables mapped to different tables in the Oracle NoSQL store, the split information for each table is now cleared in the storage handler before executing the query. Before this fix, errors could occur in the Oracle DB when split information from a previous query on a different table is inconsistent with the split information for the current table.
[KVSTORE-372]
Update security DDL commands to work with bootstrap admin without deploying any storage nodes. Before this fix, security DDL commands, such as CREATE USER, GRANT and etc, only can be executed after a storage node is deployed. You can now use security DDL command to create the initial admin user instead of deprecated plan create-user right after configure the store name.
[KVSTORE-382]
Fixed a bug that could cause queries on Multi-Region tables to not work when the region id was greater than 119.
[KVSTORE-418]
Fixed a problem which could cause a Replication Node to create a large number of threads while shutting down after encountering a fatal error. In some cases, the large number of threads created could cause other processes on the same host to fail because the maximum processes limit was reached.
[KVSTORE-405]
Fixed bugs in show tables command on Admin CLI and sql CLI.

If global namespace of CLI is set to sysdefault, show tables is expected to return all tables in both default and non-default namespaces. If the global namespace of CLI is set to a non-default namespace, show tables is expected to return those tables in the specified namespace.

Before the fix, show tables on admin CLI returns no table if global namespace is sysdefault. On sql CLI, the global namespace was ignored, show tables always returns all tables.
[#28195]
Fixed a bug that show snapshots return empty when kvstore uses customized admin storage directory.
[KVSTORE-326]
Fixed a bug that show indexes on admin CLI does not return the index in the case where child table has index but there is no index on its parent table.
[KVSTORE-394]
Fixed a bug so RNs are not restarted if a mutable JE parameter is changed.
[KVSTORE-1]
Fixed a bug that the put methods in TableAPI threw a FaultException when the table has an identity column that ran out of values. The exception could cause unexpected retries and the request time out. Now an IllegalArgumentException will be thrown when an identity column reaches the limit.
[KVSTORE-261]
Fixed a bug that the TableAPI could throw an IllegalArgumentException when the Multi-Region table service agent merges remote rows for key-only tables. The exception would eventually cause the agent terminate the stream and log the error.
[KVSTORE-253]
Fixed a bug where modifying a multi-region table using an SQL UPDATE statement could cause a Replication Node to crash when the Multi-Region agent processed table data from a remote store. The exception that caused the crash was IllegalArgumentException: This is not a record of multiregion tables.
[KVSTORE-463]