We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


optimizer execution plan join problem vertica 7 — Vertica Forum

optimizer execution plan join problem vertica 7

Massimo_LoporchMassimo_Loporch ✭
in General Discussion
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

  • BhawanaBhawana

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...