lateral join – decorrelation gone wrong

A colleaque made me aware of the following misbehaviour of Oracles optimizer. Shoutout to Christine S. who discovered that problem.

demo

create table a 
as select 1 x from dual;

create table b 
as select 1 x, 1 y from dual union all 
   select 1 x ,2 y from dual;

select a.x, c.y
from  a,
     lateral (select b.x, max (b.y) y from b where b.x = a.x) c
where a.x = c.x;

result

         X          Y
---------- ----------
         1          2

At first glance this looks like what we intended to see. However a closer inspection of the code reveals that select b.x, max (b.y) y from b is not valid syntax. We do get an error message, when running it directly.

ORA-00937: not a single-group group function

We also get the same error message when we use the select with the lateral keyword to drive a materialized view.

create materialized view mv
as
select a.x, c.y
from a,
     lateral (select b.x, max (b.y) y from b where b.x = a.x) c
where a.x = c.x;
ERROR at line 5:
ORA-00937: not a single-group group function

The ORA-937 error message is correct and should be displayed.

That we get a result in the first case, is an error!

reason

Oracle transforms the lateral join into a decorrelated lateral join. During this transformation the syntax error disappears. We see this when checking the outline section of the execution plan (see below). This transformation is different when used in a materialized view.

select *
from dbms_xplan.display_cursor(sql_id=>'2c1nchvqtjt6s', format=>'+outline');
Plan hash value: 3244120894

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |       |       |     7 (100)|          |
|*  1 |  HASH JOIN           |                 |     1 |    29 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | A               |     1 |     3 |     3   (0)| 00:00:01 |
|   3 |   VIEW               | VW_DCL_A18161FF |     1 |    26 |     4  (25)| 00:00:01 |
|   4 |    HASH GROUP BY     |                 |     1 |     6 |     4  (25)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| B               |     2 |    12 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$B1519A3D")
      DECORRELATE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$13D91C5D")
      OUTER_JOIN_TO_INNER(@"SEL$71877063" "C"@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$71877063")
      DECORRELATE(@"SEL$B1519A3D")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$B1519A3D")
      DECORRELATE(@"SEL$2")
      FULL(@"SEL$13D91C5D" "A"@"SEL$1")
      NO_ACCESS(@"SEL$13D91C5D" "C"@"SEL$1")
      LEADING(@"SEL$13D91C5D" "A"@"SEL$1" "C"@"SEL$1")
      USE_HASH(@"SEL$13D91C5D" "C"@"SEL$1")
      FULL(@"SEL$B1519A3D" "B"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$B1519A3D")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."X"="C"."X")

The name of the view VW_DCL_xxx indicates that it was used for a decorrelated lateral operation. For a normal lateral operation the name of the view woud be VW_LAT_xxx.

opinion

I’m a big fan of lateral joins. For more insights what a lateral join does, check this older blog post by the Optimizer Development Group: http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html

Since 12c (or maybe a little earlier than that) there were some optimizer enhancements to lateral joins, called decorrelation. In essence it means a lateral join, which can be seen as a kind of correlated subquery, is rewritten into a uncorrelated subquery under certain specific circumstances.

I believe this rewrite is almost always wrong. If the developer on purpose decides to use the lateral keyword (same goes for cross apply and outer apply) the optimizer should not try to correct this.

Other authors have written about problems with decorrelation before.
See

The case demonstrated here is just another example where decorrelation has gone wrong. An optimizer hint should not change the result of a query, and even more so should not change whether the query is formulated correctly or not. There are exceptions to this rule, where the intention of the hint is to change the outcome, for example ignore_row_on_dupkey_index or fresh_mv. But the (undocumented) DECORRELATE/NO_DECORRELATE hints are no such cases.

workaround

There are two ways to disable the decorrelation and get the expected ora- error message.

a) use the NO_DECORRELATE hint.

SQL> select /*+ no_decorrelate(@"SEL$2") */
  2   a.x, c.y
  3  from  a,
  4       lateral (select b.x, max (b.y) y from b where b.x = a.x) c
  5  where a.x = c.x;
     lateral (select b.x, max (b.y) y from b where b.x = a.x) c
                     *
ERROR at line 4:
ORA-00937: not a single-group group function

b) switch the decorrelation feature off using the hidden (underscore) parameter _optimizer_ansi_join_lateral_enhance

SQL> alter session set "_optimizer_ansi_join_lateral_enhance"=false;

Session altered.

SQL> select a.x, c.y
  2  from  a,
  3       lateral (select b.x, max (b.y) y from b where b.x = a.x) c
  4  where a.x = c.x;
     lateral (select b.x, max (b.y) y from b where b.x = a.x) c
                     *
ERROR at line 3:
ORA-00937: not a single-group group function

 

conclusion

Although LATERAL joins are a nifty feature, always be aware that such features often come with bugs.

2 thoughts on “lateral join – decorrelation gone wrong

  1. Hello Sven,
    Regardless of the optimizer decision to decorrelate or not the query, it is not exactly clear for me why are you using the MAX in the LATERAL query.

    The SQL parser, of course, should have had to complain about the syntax error, before any optimization
    happens at all.
    But, supposing that your purpose was to write a correct query, you can correct the error by yourself.
    If your purpose was to return all the b.y values, then simply omit the MAX.
    If your purpose is to return only the highest (max) b.y, then add a GROUP BY to the LATERAL query.

    It is weird that the optimizer steps in at all, before making sure that the query syntax is correct.

    Cheers & Best Regards,
    Iudith Mentzel

    • Hi Iudith,

      of cause the example is very simplistic and one can challenge the usefulness of the statement. Remember this is for demo purposes only. In this case the solution would be to remove the column y.b from the subquery.

      However grouped subqueries similar to this one are often a case where lateral joins are very useful in general.
      The example arose from a way more complex real world scenario including JSON_TABLE access and some other aggregation. And although the statement was working, it stopped working when used as the driving select of a materialized view.

      I fully agree that is is very weird that we see query syntax not validated before any query transformations kick in. It seem that transformations that convert ANSI to Oracle syntax (an all the lateral optimizations are part of this process) happen before the syntax parsing is complete. If we trust the oracle docs ( https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/sql-processing.html#GUID-873A7B2C-CD17-428A-8AE2-5B08906E45FB ) it would also mean, that those transformations happen before the semantic checks are done. Which might open up some other possible quirks.

      Best Regards,
      Sven

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.