Vertica Query returns zero records and when re-run returns some records

Vertica query returns zero records and when re-run same query, it returns some records.
This behavior is random but frequent.

Background on query :

Below query is executed using vsql command and run as unix korn job
table1, table2, table3 and table4 are populated using another unix kron job.


,c FROM (
FROM table1
JOIN table2
JOIN table3
) sub WHERE r=1
AND primary_id IN (SELECT DISTINCT primary_id FROM table4 )
GROUP BY  primary_id ,b, w,c

Any pointers are appreciated, Thanks in advance


  • Options


    Looks like query we see is not actual query, but simplified version.


    Provided information is very limited and it is hard to pinpoint issue.


    Here are 3 things I have on my mind:


    1. Cron job, which is loading table1, table2 and table3 does it in separate transactions. When query run with load cron job in progress joins can return unexpected results.


    2. If your join conditions have TIMESTAMP field in them check time on every node in the cluster. If time on nodes out of sync you may get very unusual results


    3. you not mentioning table4. data changes in table4 will greatly impact results of this query. 

  • Options

    Thank you for your response !


    Sorry; I could not paste original query as it might have some restrictions.

    so i converted it to generic.


    point #1 : I am same opinion too. i am varifying schedules of all tables with this query.

    will keep everyone posted.


    point#2 :  i need to figureout; how to do it


    point#3 : edited my original question: table 4 is also updated as corn job at different schedule.





  • Options


    On point #2:


    To check time you need to ssh in to every node on the cluster and check server system time.


    Vertica recommends network time protocol (NTP) daemon running on all nodes.

    If NTP daemon running and functioning normally then you should be fine.


  • Options

    sure: will check. Thanks

  • Options

    sorry for delayed response.


    Suggested solution did not work.

    For shortterm, we are re-running failed jobs.

    For longterm, we are planning to optimize long running jobs and reduce ingestion load by tweaking job schedules.


    Thanks for your help guys


Leave a Comment

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