Erreur "Not a GROUP BY expression" lors de l'utilisation d'appels de fonctions personnalisées dans une sous-requête factorisée

Question

Supposons que je dispose des tableaux suivants (ignorez qu'ils sont essentiellement identiques, la configuration réelle est plus complexe que cela) :

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 
)

Ensuite, j'ai cette question :

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)

Pour une raison quelconque, cette requête, lorsqu'elle est exécutée sur Oracle 12.2.0.1.0, génère une erreur de type

ORA-00979 : pas d'expression GROUP BY

erreur avec une ligne pointant vers une ligne avec un appel à get_week_start .

En le manipulant, j'ai également découvert ce qui suit :

• Les sous-requêtes pour aligned_in y aligned_out peuvent fonctionner parfaitement seuls
• Suppression de l'appel à get_week_start La clause group by sans cet appel à la projection fonctionne (mais change évidemment beaucoup de choses sur la façon dont cette requête est écrite et exécutée).
• (La chose qui me laisse le plus perplexe) Cette requête exacte, sans aucune modification, fonctionne parfaitement sur Oracle 11.2.0.2.0.
• La plupart des informations sur ORA-00979 n'est pas très utile car il ne semble pas du tout applicable à ma requête

Ici, le get_week_start est une fonction assez simple qui permet de déterminer le début d'une semaine de travail à un moment donné. Place (il s'agit des données du client). En raison de sa définition, cette fonction n'est pas déterministe. Cependant, j'ai entendu des suggestions selon lesquelles de telles fonctions devraient être marquées comme déterministes, et j'ai essayé de le faire juste pour voir ce qui se passait - et cela n'a pas aidé.

Pourquoi cela se produit-il ?

Qu'est-ce qui a changé entre les versions 11.2.0 et 12.2.0 ? Ai-je oublié une option de configuration ? Est-ce que cela peut être corrigé sans réécrire la requête ?

Editar :

Exemple de version de get_week_start comme demandé dans les commentaires :

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;

Exemples de tableaux pour place y 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

J'essaierais 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
),
...

Une autre approche :

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
),  
-- ...