Options
optimizer execution plan join problem vertica 7
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...
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...
0
Comments
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
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
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