Arithmetic 1

Question

Post reply

Arithmetic 1

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Thomas Abraham SSChampion Points: 10761 More actions · Answer 1

Excellent question. Code parsing for a reason, with clear explanation. Thanks bitbucket!

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

honza.mf SSCertifiable Points: 5519 More actions · Answer 2

jcb (2/29/2012)
Yes! Implicit convertion is evil.
Even worst when converting dates and using fancy collations and no US-en.

Fancy collations 😛 Tomorow morning in Bois de Boulogne 😀

See, understand, learn, try, use efficient
© Dr.Plch

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 3

This script demonstrates some problem with implicit conversion:

declare @v-2 varchar(30)

,@f float

-- here you think it is all right and it is

set @f = 1.12

set @v-2 = @f

select @v-2

-- here you think it is all right, but you get the wrong result

set @f = 123456.12

set @v-2 = @f

select @v-2

-- worst and worst

set @f = 12345677.12

set @v-2 = @f

select @v-2

-- correct solution

set @f = 12345677.12

set @v-2 = CAST(@f AS DECIMAL(16,2))

select @v-2

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

I could not locate a valid explanation

And hyet, the explanation is simple. I lack the time to find the proper Books Online pages at this time, but they are in fact easy to find.

In five out of six batches, one operand is a character value and the other is an integer value. The rules of data type precedence specify (look up: "Data type precedence" in books online) that in such a case, the character operand (lower precedence) is converted to int (higher precedence) before the operator is invoked. The operator then operates on two integer values.

In the batch that errors, both operands are string. No data type conversion is required, and the operator is invoked. If the operator would have been +, that would have resulted in string concatenation. But the operator * is undefined for string operands. So, no bugs, no incorrect answer; SQL Server is operating is intended and as documented.

Overall a good question, but a disappointing explanation. Also, the subtle differences in the form of the batches made it harder to see what was going on. I almost jumped to the conclusion that all batches combined an int and a char value; the only reason that I continued to lkook deeper was that I figured that there had to be a catch somewhere. With u uniform format for all the questions, it would have been easier to spot the differences and focus on the skill the question indended to test.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

EL Jerry SSCertifiable Points: 7063 More actions · Answer 5

Thank you for the question!!

I agree with all others that having at least one numeric causes implicit conversion for the char values, but 2 chars cannot be multiplied.

Yes, we have to be very careful with implicit conversion.

I remember in VAX VMS BASIC you could do operations with numbers as chars, and they had a much higher precision that numeric data types (e.g. I was able to calculate PI with over 100 decimals), that was long ago, and I haven't seen any other language have this feature.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Thanks Ron

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 7

Good question, but I feel compelled to point out that all statements would fail on a Case Sensitive Collation because the variable @b-2 is declared, but @b-2 is referenced. Also in example #5 @a is declared and @a is referenced.

In my opinion as rare as it may be to be in a case sensitive database, one should always be consistent in coding.

**** I'm not trying to bust your chops, just making a point. 😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Nice question.

I think the reason for the error in the second batch is that in order to do implicit conversion for arguments of an operand the compiler must know what type is required (maybe this is what Hugo was trying to say); this is true for all operators, in particular for all arithmetic operators (+,-,*,/,%) with only the + case failing to produce an error when both arguments have string types, and that only because the '+' symbol is overloaded with sting concatenation as well as with the various numeric variants of addition (unrounded, or with various different kinds of rounding).

POWER of course is different: in T-SQL it isn't an operator (it's a function), so the same rules don't apply. The first argument is explicitly required to be float (ie float(53)) or something that can be explicitly converted to it, so there's no issue with implicit conversion for that. The second parameter breaks the rules that would apply if POWER were an operator, because in theory compiler doesn't know what it has to be converted to (it can have any exact or approximate numeric type except bit, or any type implicitly convertible to an exact or approximate numeric), but I suspect that in practise it is converted to float (it is probably documented somewhere, but I've no idea where) and if my suspicion is justified the specification could have been written the same way for the second parameter as for the first and we would then be able to have an operator-style syntax for it (** or ^ or ? as in many languages) while maintaining the implicit conversion rules, instead of having to use function syntax for this arithmetic operator.

Tom