Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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...




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



  • 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
  • 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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.