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

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. 

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.

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.

sure: will check. Thanks

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