View return more data than the base table.

The view is a sub set of a base table. But sometimes it return more data than the base table. Sounds impossible. But happened to us 4 times in Vertica. Had open a case with support. No vail. We load data to a staging schema. At the end we swap the schema. So we will always have data available even the load fail (too damn often). I am not sure how the view get more data (may be the previous schema) or where it gets from. But our users can find out from online report and tell us from time to time. Once we refresh the view (recreate or replace), everything back to normal. This is not good for our credibility. Hope someone might have some insights. Thanks, Yongzhi


  • Options
    Prasanta_PalPrasanta_Pal - Select Field - Employee
    What is the Support Case Number?
  • Options
    Hi Yongzhi,

    All you do is DROP/CREATE the view and the data set changes?  That's interesting behavior.  I would do this the next time you see this happen:

    - Leave the current view in place for the moment
    - Create a new view with a different name
    - Capture the EXPLAIN output for both views and compare the plans - check projection names and schemas and the details of the plans
    - If that doesn't show anything, then PROFILE the queries on both views, then save the profiling data for both queries in a table or two off to the side so that you preserve that data
    - Check the profiling data to see where the row counts differ - start by summing up row counts by path_id, operator across all nodes to see where the counts differ.


  • Options
    Yes. I will take your suggestions next time when it happens again. Thanks for your reply.
  • Options
    Case 00019612 is the latest one. After many rounds of discussion, I think we have some ideas what had happened. We will definitely check the definition of the views next time. Thanks, Yongzhi
  • Options
    The difference in row counts were due to Schema Swap.
    Swappping schemas does not change the view definitions.
    Both the schemas having exact same tables and views caused the confusion.

Leave a Comment

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