Databases, such as relational databases, are commonly used for managing large amounts of data, and a database user, such as a developer or end-user, can access, modify or add data within the database by way of a query. For example, in the Structured Query Language (“SQL”), a query may have what is referred to as multi-part identifiers in the conventional “dot notation,” such as “A.B.C.D. ” As the name implies, such identifiers has are composed of multiple parts (e.g., A, B, C and D) separated by “dots,” or periods. Such a multi-part identifier is typically read from right to left, where the rightmost part is a column name, preceded by a table name, schema name and database name. Thus, in the above example, D would be the column name, C the table name, B the schema name and A the database name. Less than the entire path may be specified if the user is currently using the database within one of the specified levels. For example, if the user is currently operating in the table in which the desired information resides, then simply entering D as the query would result in the desired column. If the user is operating in the same database, but in a different schema, then a query of B.C.D would be required.
As databases become more advanced, a user is provided with greater flexibility, both in terms of the types of data that may be requested and the manner in which a query may be made to request the data. For example, User Defined Types (“UDTs”) are user-defined, structured data types that may be implemented using the conventional dot notation discussed above. A multi-part identifier of A.B.C may be used to query a database for a “property C of property B of column A” or a “property C of column B of table A.” These are in addition to the “legacy” multi-part identifier meaning discussed above.
A database that is attempting to resolve the A.B.C multi-part identifier discussed above does not know which of the above-noted interpretations the user is intending to achieve. In most cases, only one of the interpretations will achieve a result (i.e., will “bind successfully”). This is because typically there will only be one interpretation that will bind for every identifier specified in a query. Thus, one interpretation of the A.B.C multi-part identifier may look for column C of table B of schema A. If one or more of the identifiers do not correspond to a value in the database, then the database will assume that this interpretation is not correct. The database may then attempt to bind an interpretation of the A.B.C query that looks for a property C of property B of column A, or for a property C of column B of table A, and so forth. If only one of the interpretations binds successfully, the database may assume that such an interpretation is the user's desired interpretation, and may return a result.
Unfortunately, in some scenarios more than one interpretation may bind successfully. Using the above A.B.C multi-part identifier example, if the database being queried has both a column C of table B of schema A, and a property C of property B of column A, the database will not know which interpretation was desired by the user. Conventionally, this conflict was resolved by using the “most local” interpretation. In other words, the interpretation that contains the least number of column prefixes would be assumed to be the correct interpretation. In the above example, such an assumption would give the highest precedence to the interpretation of “property C of property B of column A,” followed by “property C of column B of table A,” and then “column C of table B of schema A.” Once this assumption is made, the result is returned to the user.
Such an algorithm has a significant shortcoming. Namely, if the assumption returns an incorrect result (e.g., property C instead of the intended column C), the user may not know that the returned result is incorrect. This shortcoming is particularly acute when the returned result is in the same format as the intended result. For example, if the returned and intended results are both numerical values, there may be no way for a user to realize that the returned result is incorrect by merely looking at the result. The incorrect result might then go on to corrupt other data if it is used in calculations or the like. Tracking down the source of such an incorrect result may prove to be extremely difficult for a user because there will be no outward sign that the multi-part identifier resulted in two possible interpretations.
In other scenarios, none of the possible interpretations bind successfully. Conventionally, in such a case a generic error message such as “could not find A.B.C” would be issued. Such a message is typically not very helpful to a user because it does not describe the interpretation(s) that were attempted, and what the exact problem with each interpretation was.
Accordingly, it is desirable to have a mechanism for discerning user intent when interpreting a multi-part identifier such as a UDT. In addition, it is desirable to have a mechanism that provides useful error messages to a user in the event of an interpretation conflict, failure or the like. The present invention satisfies these needs.