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.


3 comments:

Anonymous said...

hello

how you identify the problem ?
is it bug ?

did you try something wrapping like

insert ...
with t as( < your query >)
select * from t

?

Byte64 said...

Hi,
it is definitely a bug, I didn't try any further variation, I stick to the good ole oracle syntax, I am not an ANSI enthusiast, although I can write the queries in both ways.

Flavio

Anonymous said...

In the query that failed, there is a missing quote after
where f.cua = 'AFVTZZ78M11C499O

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