What’s New in Oracle Database 18c

Back in February 2018, Oracle released version 18c of their database for users of the Oracle Cloud and Engineered Systems. It took another five months⁠—until July—before a download became available for on-site installation. After three more months⁠—in October—the free Express Edition (XE) was also upgraded to 18c. I guess it’s safe to say that Oracle Database 18c is finally released. Now it’s time for me to have a look at it from an SQL standards perspective.

Please keep in mind that Oracle Database 18c is just a “minor” release⁠⁠—even though the previous version was 12.2. Why is this? Oracle just decided to use the last two digits of the release year for the version number from now on.

Contents:

1. JSON
2. Polymorphic Table Functions (PTF)
3. Group By () on Empty Input
4. Listagg without Within Group (Order by ...)
5. Beyond Standard SQL
6. Coming Next on Modern-SQL.com

JSON

If you are not familiar with JSON support in standard SQL, read about it in my article entitled “What’s New in SQL:2016”.

JSON Path

The SQL standard uses a query language called SQL/JSON Path to refer to elements of a JSON document⁠—similar to XPath for XML or CSS selectors for HTML.

This language has two modes: lax, which is the default, and strict. Prior to release 18c, the keyword strict was accepted in JSON path expressions, but it was ignored. In 18c it causes an error.

A similar issue affects the so-called item methods—i.e. methods that act on JSON items. Most of them did not work correctly in earlier releases—they have basically just returned the operand (the JSON item). In 18c, some of these functions cause an error: keyvalue(), double(), ceiling(), floor() and abs(). Most of them are still covered in the documentation—seems like they replaced one bug by another one.

Two other item methods started working in release 18c⁠—at least in part:

size()

Returns the length for arrays or 1 for other JSON types.

However, size() is very hard to use in lax mode because lax mode unwraps arrays implicitly in some cases. Further, there seems to be a bug in release 18c when using size() in JSON path filters—at least I could not get it to work as expected.0

type()

Returns a string with the name of the JSON data type(s): null, number, string, boolean, date, time[stamp] [with|without] timezone, array, object.

Note that the Oracle Database has some proprietary item methods too. Refer to the documentation for the full list.

Json_array[agg] Defaults to Absent on Null

The SQL standard defines the default null handling of the json_array and json_arrayagg functions as absent on null.1 This is in contrast to json_object and json_objectagg, which default to null on null.2 Previous Oracle Database releases incorrectly defaulted to null on null for the array functions too. Release 18c has changed that and uses the default settings defined in the standard. You can still get the old behavior by specifying null on null explicitly.

Beyond Standard SQL/JSON

Most JSON functions that were newly introduced with release 18c are proprietary. Please refer to Tim Hall’s article for further details.

There is one new function that might be worth adding to the standard:3 json_equal. It is a semantic JSON comparison function that ignores formatting and insignificant object member order.

Polymorphic Table Functions (PTF)

I’ve explained polymorphic table functions (PTFs) in my article about new features in SQL:2016 as follows:

SQL table functions—standardized with SQL:2003—are functions that return tables. Table functions have to specify the names and types of the columns they return—the so-called row-type—at the time of creation:

CREATE FUNCTION <name> (<parameters>)
       RETURNS TABLE (c1 NUMERIC(10,2), c2 TIMESTAMP)
   ...

This function returns a table with two columns: c1 and c2 with the respective types.

Once declared, table functions can be used in the from and join clauses similarly to regular tables.4

Prominent examples of table functions are:

• Oracle’s dbms_xplan.display_cursor (and relatives)

• PostgreSQL’s generate_series

SQL:2016 introduces polymorphic table functions (PTF) that don’t need to specify the result type upfront. Instead, they can provide a describe component procedure that determines the return type at run time. Neither the author of the PTF nor the user of the PTF need to declare the returned columns in advance.

Oracle 18c introduces polymorphic table functions but doesn’t use the standard syntax.5 I haven’t checked in great detail, but at first glance it looks like the concepts and functionality of Oracle PTFs are roughly the same as standard PTFs—it’s just the syntax that differs.

Please refer to Tim Hall’s post about Oracle PTFs for working examples. One interesting use case there is to pass through all but a few column from a table—think of select * except (column). A similar example in standard syntax is in clause 12.1 of ISOs technical report “Polymorphic table functions in SQL”.

1. Proprietary syntax
2. Functions returning setof record can return dynamic tables

Group By () on Empty Input

My standard conformance tests have found a change that I haven’t seen mentioned anywhere. The explicit global grouping—i.e. group by ()—gives the correct result (one row) for an empty input in release 18c.

Let me explain this step-by-step.

You have probably used a query like this before:

SELECT COUNT(*)
  FROM tbl

It’s a very simple query that returns the number of rows in a table.

But here is a question: If the table is empty, why does the query still return a row? To put it differently, how can a query on an empty table ever return a row?

Go ahead and give it a try. Count the number of rows on an empty table and you will get one row saying that there are no (zero) rows in this table.

The reason for this result is the way the SQL standard defines the global grouping—i.e. a grouping without grouping key. For that, all rows are put into one common group. Later in the processing, this group is transformed into a single row like with any group by clause. This happens irrespective of how many rows the group contains. Even if the group is empty, it is still transformed into one row. This is why you can count the number of rows in an empty table and still get one row as a result.

This global grouping can be triggered in two different ways:: implicitly or explicitly. The implicit global grouping happens when a grouping is required—because aggregate functions or the having clause is used—but there is no explicit group by clause. The query above relies on that implicit global grouping.

The explicit syntax to do a global grouping is to use empty parenthesis in the group by clause:6

SELECT COUNT(*)
  FROM tbl
 GROUP BY ()

This was introduced with SQL:1999 when grouping sets were introduced. Today, both of the above queries are equivalent in standard SQL.7 However, if you run the second query against an empty table on an Oracle Database up to 12c, you’ll notice that you don’t get any result.

SQL> SELECT COUNT(*)
2  FROM tbl
3 GROUP BY ();

no rows selected

SQL>

That is exactly what has changed in release 18c.

The practical relevance of this conformance improvement is rather limited. Nevertheless it shows that Oracle is willing to move toward the standard even if other products expose the same non-conforming behavior (Microsoft SQL Server). It’s not a giant leap, but it’s a nice step forward after doing it “wrong” for 17 years.8 That raises the hope that they’ll fix their “unique” null-is-an-empty-string approach eventually.

1. Does not return one row on empty input

Listagg without Within Group (Order by ...)

As you have seen in the previous topic, I find these small things that might neither be mentioned in the release notes nor covered in the current documentation, to be the most interesting ones. They sometimes reveal work in progress or motivations that are not being openly communicated.

One of these changes was done to the listagg function. Oracle Database 18c accepts listagg without the within group clause, even though it is mandatory in standard SQL, was mandatory in 12.2, and is still mandatory according to the syntax diagrams in the 18c documentation.

A quick look into the support matrix for listagg reveals that, out of the seven tested databases, only one other database supports listagg—and that one doesn’t require the within group clause. I guess this change was done to improve compatibility with that database.

Beyond Standard SQL

Of course there were many other enhancement introduced with Oracle Database 18c. Please refer to “Oracle Database Release 18c New Features” for the official list. I would like to mention two of them briefly.

Property Graph Query Language (PGQL)

This is similar to the graph querying capabilities introduced to SQL Server 2017 although the syntax is different. The core idea is to introduce a Cypher-like language to query graphs that are stored in SQL tables. You can find out more about it in the Property Graph Query Language (PGQL) documentation.

Approximate Top-N queries

Approximate functions were introduced to Oracle Database 12c—e.g. approx_count_distinct. Release 18c adds function to implement approximate top-n queries—i.e. queries that return only the first N rows of a result. This functionality is supported by the new function approx_rank. Read more about it in the documentation.

Coming Next on Modern-SQL.com

The next articles about SQLite 3.25 (window functions!) and PostgreSQL 11 (more window functions!) are already in the making. Follow modern-sql.com via Twitter, e-mail or RSS to stay updated.