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.)

asked 17 Jun '10, 17:32

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%


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.

permanent link

answered 17 Jun '10, 17:54

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273
accept rate: 40%

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?

(17 Jun '10, 19:45) Volker Barth
1

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.

(18 Jun '10, 12:56) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25
×5
×4
×2

question asked: 17 Jun '10, 17:32

question was seen: 1,052 times

last updated: 17 Jun '10, 17:54