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
0
Comments
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.
--Sharon
Swappping schemas does not change the view definitions.
Both the schemas having exact same tables and views caused the confusion.