From: AL

To: Editor

 

A database student of mine told me about this web site yesterday. I've enjoyed reading for a few hours and have added it to my links bar so I can stay current.  I'm glad that there is a debunking force out there.  It will be a useful reference for me in the future.

 

I am wondering about views and their behavior regarding modifications to the base tables on which they are defined.  Take this simple example:

 

CREATE TABLE a

 (field1 INT PRIMARY KEY,

  field2 CHAR(10))

 

CREATE VIEW va

  AS SELECT *

     FROM a

     WHERE field2 = /* some chars */

 

ALTER TABLE a

 ADD COLUMN field3 INT

 

SELECT *

FROM va

 

FIELD3 is excluded.

 

I know that this behavior is in line with the ANSI spec.  But why?  What is the rationale behind this?

 

It seems to me that if I wanted VA to include specific columns, I would have (and should have) explicitly enumerated them in the defining query.  Since I specifically wrote "SELECT *", it means that I explicitly want all the columns in the base table.  So when the schema of the base table changes, why don't I see that in the results of querying the view?  If I drop and recreate the view I'll get that.  But I shouldn't have to.  In SQL Server I can run SP_REFRESHVIEW to address this.  But again, I shouldn't have to.

I think (hope?) there must be a reasonable rationale behind this behavior, but I'm at a loss as to what it could be.  Any insight would be appreciated.

 

 

From: Fabian Pascal

To: AL

 

Glad you enjoy it. Hope you will support us in the effort when we start our commercial DATABASE FOUNDATIONS SERIES.

 

Don't always look for rationale in SQL, or the standard. It's a language created by a committee ignorant of fundamentals and relational principles, to the point of making sure that the word ‘relational’ does not appear even once in the standard. I could tell you stories that you would not believe.

 

Don't use SELECT * with views.

 

 

From: AL

 

I can think of circumstances where I really want all columns in the base table to be returned from a view, regardless of what they are, or how recently they were added.  There can be plenty of logical complexity to hide from the user/programmer/report write/... in the WHERE clause that warrants a view.

 

I've been thinking about adding a trigger to a system catalog table that would call SP_REFRESHVIEW for all user-defined views whenever a schema change occurred in a user-defined base table.  (This is in SQL Server, but even if Oracle doesn't have a similar stored procedure, it wouldn't be hard to write.)  I haven't yet tried this, but I suspect it will work.

 

 

From: Fabian Pascal

 

Agreed, but SQL is a language written by people who really did not have a clue about RM. Some are still at IBM, now pushing XML. So what can you expect from those?

 

Techniques like the one you suggest is exactly what RM intended to avoid.

Chris Date Comments: If what you say about the standard is true—and on reflection it probably is—my guess is simply to have a precise definition of CREATE VIEW. Note that, since SQL as originally defined did not properly support relational closure, defining references to views (e.g. in SELECT expressions), as being replaced by view-defining expressions would somewhere lead to syntax errors!

 

Posted 07/04/03

 

 

 

[ABOUT] [QUOTES] [LINKS]