Oracle avg in group by not sorting properly

Question

We are receiving incorrect result ordering when using an order by on an aggregate function in a group by clause on Oracle 12.2. After playing around with it we have found that the query only works when it is phrased in certain ways (see below).

So here are our questions:

1) Why is ordering by avg acting finicky? Are the queries acting as expected based on some documented logic / limitation? Does it have something to do with the underlying data type being number(16) without decimal places?

2) Why does using an alias in query 4 make it work while query 3 does not work?

3) Why do the queries work better when ordering by ascending duration? It's not shown below but query 2 works when asc even though it does not work desc. Query 1 does not work with asc.

In the below examples note that duration is a number(16).

Query 1: Avg function order by function

select
    name,
    avg(duration)
from table1
join table2 on table1.table2_id = table2.id
where duration is not null
group by name
order by avg(duration) desc

-- Query 1 result (wrong)
(some name) 1224417.83471074
(some name) 33568438.1548673
(some name) 3928150.12809406
(some name) 1434939.13464658
(some name) 269338.574638521

Query 2: Avg function order by alias

-- Query 2: order by avg alias
select
    name,
    avg(duration) avg
from table1
join table2 on table1.table2_id = table2.id
where duration is not null
group by name
order by avg desc

-- Query 2 result (wrong)
-- See query 1 result

-- Note: For some reason this query works correctly when ordered asc

Query 3: Avg function with cast order by function

select
    name,
    to_number(avg(duration))
from table1
join table2 on table1.table2_id = table2.id
where duration is not null
group by name
order by to_number(avg(duration)) desc

-- Query 3 result (wrong)
-- See query 1 result

Query 4: Avg function with cast order by alias

select
    name,
    to_number(avg(duration)) avg
from table1
join table2 on table1.table2_id = table2.id
where duration is not null
group by name
order by avg desc

-- Query 4 results (correct)
(some name) 562654936
(some name) 498804314
(some name) 263681023
(some name) 245531731
(some name) 188103278
-- the values with decimals show up later in the right order

Query 5 & 6: Avg function with/without cast with ordering in an external query

select * from (
    select
        name,
        to_number(avg(duration)) avg -- works without to_number as well
    from table1
    join table2 on table1.table2_id = table2.id
    where duration is not null
    group by name
) order by avg desc

-- Query 5 & 6 results (correct)
-- See query 4 results

Answer 1

We have traced this down to what we believe is a bug in the Oracle optimizer. It happens when the optimizer chooses to use a view called VW_GBC_5. Observe the explain plans below:

With Issue

-- This produces incorrect result set ordering
select 
    /*+ qb_name(m) place_group_by(@m) */ 
    name,
    avg(duration)
from table1
join table2 on table1.table2_id = table2.id
where duration is not null
group by name
order by avg(duration) desc;

-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                | 35540 |  3366K|       |  9433   (1)| 00:00:01 |
|   1 |  SORT ORDER BY         |                | 35540 |  3366K|  3800K|  9433   (1)| 00:00:01 |
|   2 |   HASH GROUP BY        |                | 35540 |  3366K|  3800K|  9433   (1)| 00:00:01 |
|*  3 |    HASH JOIN           |                | 35540 |  3366K|       |  7852   (1)| 00:00:01 |
|   4 |     VIEW               | VW_GBC_5       | 35540 |  1145K|       |  7510   (2)| 00:00:01 |
|   5 |      HASH GROUP BY     |                | 35540 |   416K|       |  7510   (2)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL| TABLE1         |  1225K|    14M|       |  7461   (1)| 00:00:01 |
|   7 |     TABLE ACCESS FULL  | TABLE2         | 38955 |  2434K|       |   342   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Without Issue

-- This produces correct result set ordering
select 
    /*+ qb_name(m) no_place_group_by(@m) */ 
    name,
    avg(duration)
from table1
join table2 on table1.table2_id = table2.id
where duration is not null
group by name
order by avg(duration) desc;

-----------------------------------------------------------------------------------------------
| Id  | Operation            | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                | 38412 |  2850K|       | 25624   (1)| 00:00:02 |
|   1 |  SORT ORDER BY       |                | 38412 |  2850K|    98M| 25624   (1)| 00:00:02 |
|   2 |   HASH GROUP BY      |                | 38412 |  2850K|    98M| 25624   (1)| 00:00:02 |
|*  3 |    HASH JOIN         |                |  1225K|    88M|  2896K|  9345   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TABLE2         | 38955 |  2434K|       |   342   (1)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| TABLE1         |  1225K|    14M|       |  7461   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Workarounds

1. Rewrite query (see original question)

2. Disable _simple_view_merging

   alter session set "_simple_view_merging"=false;

3. Switch to a different optimizer version

   alter session set optimizer_features_enable='12.1.0.2';

4. Use the no_place_group_by optimizer hint