Re: Avoiding New Fields Causing Ambiguity Errors

From: Paul Vernon <paul.vernon_at_ukk.ibmm.comm>
Date: Mon, 13 May 2002 17:22:25 +0100
Message-ID: <abopeg$102q$1_at_sp15at20.hursley.ibm.com>


> There is the possibility when adding a new field that an existing
> query(s) in an application(s) might "break" due to field name
> ambiguity being introduced. Other than relying on regression testing,
> approaches to avoid or reduce this include:

Whether in SQL or in some proper relational query language, any schema change has the potential to "break" existing queries. The addition of an attribute being just one example.

Many query tools that I use have a 'check for schema changes' function. Generally this checks the tools copy of the db schema with the current db schema, allowing the user/administrator to decide how to reconcile any changes.

As has been mentioned, fully qualifying column names is good practice in SQL. I would agree that this does make queries more verbose and a I would like to see a better solution in any non SQL language.

My guess is that you would register all "canned" queries against the database. Then, during any schema alteration, all the canned queries would be checked for "breakage". Depending on authorisation, the user modifying the schema would be given chooses for altering the broken queries to make them work again (and hopefully maintain their meaning).

I suspect that folks working on schema evolution would have comments on this. From a temporal viewpoint, remember that a "canned" query is in fact a different query each time it is executed. I.e. A join B is in fact "A join B at time X" then a bit latter it is "A join B at time Y". What this means is that unless you ban for all time any schema changes that could affect A join B, "canning" such a query does not guarantee that the query is even executable in the future.

Regards
Paul Vernon Received on Mon May 13 2002 - 18:22:25 CEST

Original text of this message