Same Table and Projection Name
I was able to create same super projection with same name of table name. I also created custom projection on the same table with appropriate select columns and sorting on them.
This project was running for two weeks, where I tend to load the date into table using an ETL process and later query that table for our reporting use.
Recently after load completion, I found that query: select * from table_name is returning result but select specific_columns from table is not showing any result. Using explain, I found select * from table is pointing to super projection (which has same name as of table name) and select specific_columns from table is pointing to custom projection.
Below is suspecting reason for this behavior -
1. Vertica optimizer was not able to identify during insert statement that it's inserting to a table or directly to a projection and it did direct insertion into the super projection (which has same name as of table name)
2. Here, table is left blank and all data is with projection only.
3. When I executed select * from table_name, it eventually fired as select * from projection_name, because both "names" are same.
4. When I fired select specific_column from table, optimizer tries to identify the data from custom projection (which was blank because table was not loaded (instead projection was loaded in #3)) no record returned.
5. If my suspicion is true, why did vertica (Version 9.1) allowed me to create same projection name as of table name and if it did how can optimizer distinguish between the two while DDL operations.
Finally I dropped the custom projection, rename the super projection, reload the table and everything starts working without any issue.