"Not a GROUP BY expression" error when using custom function calls in Factored Subquery

Question

Let's assume I have these tables (ignore that they are essentially the same, actual setup is more complex than this):

create table inbound (
    id number(19,0) not null,
    created_on timestamp(6),
    place_id number(19,0),
    qty_amount float(126),

    constraint "inbound_pk" primary key (id),
    constraint "inbound_place_FK" foreign key (place_id)
        references place (id) on delete cascade 
);

create table outbound (
    id number(19,0) not null,
    created_on timestamp(6),
    place_id number(19,0),
    qty_amount float(126),

    constraint "outbound_pk" primary key (id),
    constraint "outbound_place_FK" foreign key (place_id)
        references place (id) on delete cascade 
)

Then, I have this query:

with aligned_in (start_date, place_id, total) as (
    select
        get_week_start(place_id, created_on) start_date,
        place_id,
        sum(qty_amount) total
    from inbound
    where <....>
    group by
        get_week_start(place_id, created_on), place_id
),
aligned_out (start_date, place_id, total) as (
    select
        get_week_start(place_id, created_on) start_date,
        place_id,
        sum(qty_amount) total
    from outbound
    where <....>
    group by get_week_start(place_id, created_on), place_id
)

select
   start_date,
   place_id,
   aligned_in.total total_in,
   aligned_out.total total_out
from aligned_in
  left outer join aligned_out using(place_id, start_date)

For some reason, this query, when executed on Oracle 12.2.0.1.0, throws out a

ORA-00979: not a GROUP BY expression

error with line pointing at line with a call to get_week_start.

While fiddling with it, I've also discovered the following:

• The subqueries for aligned_in and aligned_out can be run completely fine by themselves
• Removing call to get_week_start from the subqueries' projection fixes it - the group by clause without this call in projection works (but obviously changes a lot about how this query written and executes)
• (The thing which I'm most confused about) This exact query without any alterations runs completely fine on Oracle 11.2.0.2.0
• Most info on ORA-00979 is not very useful because it doesn't appear applicable at all to my query

Here, the get_week_start is a pretty simple function to find out what the start of a business week would be at a given Place (this is customer's data). Due to how it's defined, this function is not deterministic. However, I did run into suggestions that such functions should be marked deterministic, and did try doing that just to see what happens - and that did not help.

So, why is this happening?

What changed beween versions 11.2.0 and 12.2.0 that caused this? Am I missing some configuration option? Can this be fixed without rewriting the query?

Edit:

Sample version of get_week_start as requested in comments:

create function get_week_start(place_id number, week_day date)
    return date
as
    start_date date;
begin
    begin
        select
            trunc(next_day(week_day, o.business_week_start)) - 7
                into start_date
        from place
            inner join place_owner o on o.id = place.owner_id
        where place.id = place_id;

        return stat_date;
    exception
    when others then return null;
    end;
end get_week_start;

Sample tables for place and place_owner:

create table place_owner (
    id number(19,0) not null,
    name varchar2(255) not null,
    business_week_start varchar2(64) not null,

    constraint "place_owner_pk" primary key (id)
);

create table place (
    id number(19, 0) not null,
    name varchar2(255) not null,
    owner_id number(19,0) not null,

    constraint "place_pk" primary key (id),
    constraint "place_unq" unique (owner_id, name),
    constraint "place_owner_fk" foreign key (owner_id)
        references place_owner (id) on delete cascade
);

Answer 1

I would try CROSS/OUTER APPLY (Oracle 12c):

with aligned_in (start_date, place_id, total) as (
    select
        s.start_date,
        place_id,
        sum(qty_amount) total
    from inbound
    cross apply (SELECT get_week_start(place_id, created_at) AS start_date 
                FROM dual) s 
    where <....>
    group by
         s.start_date, place_id
),
...

Another approach:

with aligned_in (start_date, place_id, total) as (
    SELECT start_date,
           place_id,
           sum(qty_amount) total
    FROM (select get_week_start(place_id, created_at) AS  start_date,
                 place_id,
                 qty_amount
           from inbound
           where <....>) sub
    group by start_date, place_id
),  
-- ...