Changes in 20.1.15

Topics

New Features

Added IN operator in SQL for Oracle NoSQL. The IN operator is essentially a compact alternative to a number of OR-ed equality conditions. For example, the query
```
SELECT * FROM Foo WHERE a IN (1, 5, 4)

is equivalent to

SELECT * FROM Foo WHERE a = 1 OR a = 5 OR a = 4
```
and the query,
```
SELECT * FROM Foo
    WHERE (a, b) IN ((1, "a"), (5, "g"), (4, "t"))

is equivalent to

SELECT * FROM Foo
    WHERE (a = 1 AND b = "a") OR (a = 5 AND b = "g") 
    OR (a = 4 AND b = "t")
```
However, in addition to being more compact, queries using IN operators will be executed more efficiently if appropriate indexes exist. For example, if table Foo has an index on columns a and b, then both of the above IN queries will use that index to find the qualifying rows, whereas the equivalent OR queries will be executed via full table scans.

The above IN queries follow the standard SQL syntax. An alternative syntax has also been implemented, that allows a relative large number of search keys to be provided via a single bind variable. For example, if the $keys variable is bound to the array [ 1, "a", 5, "g", 4, "t"], then the following query is equivalent to the second IN query above.
```
DECLARE $keys array(json);
    SELECT *
    FROM Foo
    WHERE (a, b) IN $keys[]
```
[#27900]
Support for "untyped" json indexes.

So far, indexing a field inside json documents required the specification of a concrete json atomic type for the field (one of integer, long, double, number, string, or boolean). If, for some document, the index field did not conform to its declared type, index creation or the insertion of that document in a table would fail. In this release, anyAtomic is added as a valid type for indexed json fields. A field indexed with the anyAtomic type can be of any valid json atomic type and the index will store all the heterogeneous values of that field.
[#27989]
Query execution plans are now being displayed in json format.
[#26016]
Added the new rnRHAsyncMaxConcurrentRequests parameter, which specifies the maximum number of concurrent asynchronous requests that a Rep Node can handle before it should do throttling. This parameter replaces the rnRHAsyncExecQueueSize parameter, which is now ignored, since the queue size is now computed from the difference between the rnRHAsyncMaxConcurrentRequests and rnRHAsyncExecMaxThreads parameters.
[#27823]
The SQL command "ALTER TABLE" now supports adding or removing regions from existing multi-region tables.
[#28027]
The new SQL command "SET LOCAL REGION" can be used to specify the name of the local region for use with multi-region tables.
[#28013]
When creating a multi-region table or adding new regions to an existing multi-region table, the associated tables in remote regions no longer have to be empty.
[#28120]
Multi-Region tables are now only supported in the Enterprise Edition.
[#28144]

Bug and Performance Fixes

When upgrading from a version prior to 18.3, the store must be fully upgraded before an elasticity plan that includes migrating partitions can be run. If the store is not fully upgraded to 18.3 or above, a partition migration may fail, causing the elasticity plan to exit. The plan can be re-run once the store is fully upgraded. This new behavior avoids a deadlock risk between the deploy topology plan and an internal Admin plan.
[#28036]
Added the snapshotName to the JSON output of the snapshot command result.
[#27700]
Fixed a problem where the incorrect math context might be used in queries that use sum() and seq_sum() functions on the Number data type.
[#28040]
Fixed a bug affecting on-prem, proxy-based, join queries. In this mode, some join queries would wrongly raise exception saying that a single results consumes more bytes than the max allowed.
[#28103]
Fixed a problem that could occur when using the HTTP proxy and drivers on queries with a LIMIT clause. It was possible that some results within the limit would be omitted, but some outside of the limit included.
[#28034]
Fixed problems when the Java client is used to access multiples stores which have the same name. Previously, for secure stores, client access to the store could fail with a certificate signature validation error. In addition, socket open and read timeout values specified for a particular store were not applied correctly. Both problems are now fixed.
[#27952]
TableAPI.getTable() is now more reliable when one or more nodes is unavailable.
[#28150]
Made scalability improvements to reduce the overhead of using large numbers of tables and indexes.
[#27204]