If you define a view to be "select * from tablename", shouldn't you be able to leave the view untouched if you ever change the column list in that table?
Until now, every view at my work had been defined with a set column list, so whenever we updated a table we had to update the views as well. But now we're testing out a few views with "select *", thinking that would mean we can leave them alone.
But after updating a couple column names on the tables, now a select from the views gives a 'column not found' error, that is looking for whatever column no longer exists in the table.
Was this a bad expectation on our part, or a bug in TD12?
A view is like a virtual table. A view does not contain any data, but, its defintion is stored in the data dictionary. Also, VIEW columns are explicitly enumerated when a view is defined.
'*' specifies that all columns in the FROM clause will be returned. If we declare a VIEW with a SELECT * option for the first time, then, all the columns will be enumerated for that view at the first time. If we had Col1,Col2,Col3... as our column names while defining the view, we are making an entry in the data dictionary for the view with those columns. After some time, if our column names in the table are changed to Col1_new,Col2_new,Col3_new... (or) we selected col1,col3,col5,col6..., then, the old view entry in the data dictionary still remains unaltered. Manual intervening is necessary at this point to say that the column names have been changed.
So, whenever we modify(change) the column names, we need to specify a mechanism to update the definitions.
Regarding the bug issue-> Essentially, it is not a bug. It is one way of providing restriction on data access also. Suppose, I created a view X_HR_VIEW with "Select *" on a HR table on Day1. I come back on Day 11 and then I add SALARY and BONUS information to the same HR table. Then, my view X_HR_VIEW will show this new information also. But, generally, we do not want that information to be shown. If we did not get the error messages from Teradata, we would not notice this issue.
I am sure about what I have written here. It came out of the manuals and my own understanding. Forum members, please correct me if I am wrong.
Below scenrios are helpful in understanding view's behavior with respect to the table change(s).
Scenario 1 View definition - SELECT a,b,c,d from Table. Action on table : Adding new columns Action in view: View need to refresh else, it wont capture newly added columns. No syntax error. View would continue showing column set of the table which existed prior to new column addition.
Scenario 2 View definition - SELECT a,b,c,d from Table Action on table : dropping newly added columns(added above) which are still not reflecting in view definition Action on view : No impact on view
Scenario 3 View definition - SELECT a,b,c,d from Table Action on table : dropping columns where are covered in view. Action on view : View need to refresh else resulting a syntax error.
Scenario 4 View definition - SELECT a,b,c,d from Table Action on table : Altering data type of a column. Say data type of column d is altered from date to timestamp(0). Action on view : No View refresh needed. It will show column d with timestamp(0) values.
Scenario 5 View definition - SELECT a,b,c,d from Table Action on table : drop table , recreate table with same DDL structure Action on view : No View refresh needed.
Scenario 6 View definition - SELECT a,b,c,d from Table Action on table : drop table , recreate table with same DDL structure with 2 additional columns. Action on view : View need to refresh else, it wont capture newly added columns. No syntax error. View would continue showing column set of the table which existed prior to new column addition.