Monday, August 28, 2017

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.


No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles