Does a computed expression based on a user-defined data type "lose" the user-defined type quality?

[#1]

Yep, I'm sorry for the complicated question title but couldn't do better:)

This is a follow-up from my answer to that question:

When doing any kind of computation with a user-defined type, the result seems to lose that user-defined type. (At least sa_describe_query implies so.) That is sometimes inevatible, when the result won't fit in the user-defined domain because the domain range would be to small, e.g. when adding two money values, and the sum requires an additional digit.

In other cases, the result would fit in the domain type, e.g. when limiting a char type to a smaller length with LEFT() or when adding 0 to a numeric type as in

select column_number, domain_name_with_size, user_type_name
from sa_describe_query('select cast(1 as money), cast(1 as money) + 0')

returns

column_number,domain_name_with_size,user_type_name
1,'numeric(19,4)','money'
2,'numeric(20,4)',

(Note: Casting the computed expression back to the user-defined type as in "cast(cast(1 as money) + 0 as money)" works as expected, i.e. then sa_describe_query returns the user-defined type.)

General question:

Is this "losing the user-defined type" in the course of any computation by design? (I realize that a "smarter approach" would be very sophisticated to implement.)

[MarkCulp · Accepted solution]

The result type of a computation - i.e. an expression - is computed by a complicated set of rules and any user-defined type at one (or more) of the leaf nodes in the expression is not known at the root of the expression.

[VolkerBarth · Accepted solution]

To clarify: Does that mean a double "Yes" - an expression can't be of a user-defined type (unless explicitly casted), and this behaviour is by design?

[MarkCulp · Accepted solution]

Yes to both. The fact that sa_describe_query figures out the user type of a column is fairly easy, but once there is an expression node in the parse tree then it becomes a much more difficult task and the user defined type information is lost. Remember that the type evaluation is done on an annotated parse tree and that extra digits are generally required to maintain accuracy in a computation, hence why the the user domain is not carried up in the parse tree.