The amazing ANSI join syntax quirk

The kind of quirks I love: those that you can find a workaround for without having to wait for a patch.

I am talking Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production running on some unix flavor (I ignore which flavor, I've no access to the unix box).

You execute the following query and it works.

select f.cuaa,f.id_fgra, d.id_cons, c.id_part, p.id_prt, 

       sdo_sam.simplify_geometry(p.shape,0.005,2) as shape_sim,

       p.cod_na, p.fol, p.part

  from pcat c

  join cons d on (d.id_fgra = c.id_fgra)

  join cons_part t on (t.id_cons = d.id_cons and t.id_pcat = c.id_pcat)

  join fgra f on (f.id_fgra = d.id_fgra)

  join trkc k on (k.id_prt = c.id_part)

  join part p on (p.id_prt = k.id_prt)

where f.cua = 'AFVTZZ78M11C499O'

 and k.fin > sysdate

 and p.fin_val > sysdate

 and c.init < sysdate

 and c.fin > sysdate

 and c.valid < sysdate;

Then you execute:

insert into mp_part (cua, id_fgra, id_cons, id_part, id_prt, shape_sim, cod_na, fol, part)

select f.cua,f.id_fgra, d.id_cons, c.id_part, p.id_prt, 

       sdo_sam.simplify_geometry(p.shape,0.005,2) as shape_sim,

       p.cod_na, p.fol, p.part

  from pcat c

  join cons d on (d.id_fgra = c.id_fgra)

  join cons_part t on (t.id_cons = d.id_cons and t.id_pcat = c.id_pcat)

  join fgra f on (f.id_fgra = d.id_fgra)

  join trkc k on (k.id_prt = c.id_part)

  join part p on (p.id_prt = k.id_prt)

where f.cua = 'AFVTZZ78M11C499O'

 and k.fin > sysdate

 and p.fin_val > sysdate

 and c.init < sysdate

 and c.fin > sysdate

 and c.valid < sysdate;

SQL Error: No more data to read from socket
it caused a core dump.

Then you try 

create table test as

select f.cua,f.id_fgra, d.id_cons, c.id_part, p.id_prt, 

       sdo_sam.simplify_geometry(p.shape,0.005,2) as shape_sim,

       p.cod_na, p.fol, p.part

  from pcat c

  join cons d on (d.id_fgra = c.id_fgra)

  join cons_part t on (t.id_cons = d.id_cons and t.id_pcat = c.id_pcat)

  join fgra f on (f.id_fgra = d.id_fgra)

  join trkc k on (k.id_prt = c.id_part)

  join part p on (p.id_prt = k.id_prt)

where f.cua = 'AFVTZZ78M11C499O'

 and k.fin > sysdate

 and p.fin_val > sysdate

 and c.init < sysdate

 and c.fin > sysdate

 and c.valid < sysdate;

and it works without a hitch.

It turns out that the problem is with the INSERT SELECT ANSI join syntax combined with a spatial function call in the projection list.

If I rewrite the query with the traditional Oracle syntax, it runs smoothly.