optimizer execution plan join problem vertica 7

Massimo_Loporch

Strange but


with vertica 7 this query does not work anymore 

  select  count(f.cod_negozio)  from  ods.dim_offerta oa   left  join olap_vendite.fact_vendggall f        join ods.dim_offerta o on  (f.cod_articolo)=  (o.cod_articolo) and (f.cod_negozio) =  (o.cod_negozio ) and (f.aaaammgg between o.aaaammgg_inizio and o.aaaammgg_fine)  and  (f.cod_negozio) = '080814'   on f.cod_articolo=oa.cod_articolo and f.cod_negozio= (oa.cod_negozio)  where oa.offerta='2014/1002' and (oa.cod_negozio = '080814') and   oa.cod_articolo = ('445347') 

but if i do:

...
 join ods.dim_offerta o on  (f.cod_articolo)=  (o.cod_articolo) and (f.cod_negozio) =  trim(o.cod_negozio ) and ...

or

...
 join ods.dim_offerta o on  (f.cod_articolo)=  (o.cod_articolo) and trim(f.cod_negozio) =   (o.cod_negozio ) and  ...


then runs fine !!!

obviously subjected fields to trim() contain no trailing spaces...

Bhawana

Hi Massimo,

Could you please explain what do you mean by "does not work anymore ". What is the error it is giving in 7.0.

Is the query not completing or hung? It immediately giving any error.


Regards,

Bhawana

Massimo_Loporch

I apologize for the barbaric reporting that I did ...
for a while, I've had panic attacks : - )

the fact is that the query does not work in the sense that it returns no results when it should, in fact applying a function to any of the fields to join returns the correct result ie 63 records in my case ...

Tell me what you need to reproduce the problem ...

Ciao

Massimo_Loporch

I apologize for the barbaric reporting that I did ... 
for a while, I've had panic attacks : - )

the fact is that the query does not work in the sense that it returns no results when it should, in fact applying a function to any of the fields to join returns the correct result ie 63 records in my case ...

Tell me what you need to reproduce the problem ...

Ciao