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

Moderator: NorbertKrupa

Post Reply
vishnu
Newbie
Newbie
Posts: 1
Joined: Tue Jul 19, 2016 11:32 pm

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

Post by vishnu » Tue Jul 19, 2016 11:51 pm

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 and table3 are populated using another unix kron job.

Code: Select all

SELECT 
primary_id
,b
,w
,c FROM (
SELECT
primary_id
,b
,w
,c
,r
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 

can you please your previous experience or any pointers are appreciated.

Thanks in advance

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

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

Post by NorbertKrupa » Sun Jul 24, 2016 10:59 pm

Rows that are being returned are most likely not yet purged.
Checkout vertica.tips for more Vertica resources.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

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

Post by scutter » Mon Jul 25, 2016 12:30 pm

> Rows that are being returned are most likely not yet purged.

Rows that have been deleted won’t be returned in the result set. SELECT statements default to being executed on the latest epoch, which won’t include the deleted records. Whether or not deleted records have been purged is irrelevant, since deleted records will be excluded unless you use historical queries to query for a specific epoch or time, and that only works as far back as the current AHM epoch.

—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

Post Reply

Return to “General”