SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / Arithmetic 1 / Latest Posts

RE: Arithmetic 1

manik123 — Tue, 29 Jan 2013 04:00:55 GMT

nice question.....Manikandanps------Go fast as possible---------------

RE: Arithmetic 1

Britt Cluff — Wed, 14 Mar 2012 20:01:59 GMT

Good question. Thanks for submitting.

RE: Arithmetic 1

muni.reddy — Sun, 11 Mar 2012 08:46:41 GMT

For any numeric operation to be successful, atleast one of the operand must be a number data type.e.g. select '6'+'4'*'8'/'2' will fail where as select '6'+4*'8'/'2' will succeed.Thus it is obvious that the execution expression 2 fails.

RE: Arithmetic 1

Koen Verbeeck — Mon, 05 Mar 2012 00:36:40 GMT

Interesting question, thanks.

RE: Arithmetic 1

Hardy21 — Thu, 01 Mar 2012 21:46:47 GMT

Nice question. Thanks.

RE: Arithmetic 1

KWymore — Thu, 01 Mar 2012 09:20:15 GMT

Nice question, thanks!

RE: Arithmetic 1

OzYbOi d(-_-)b — Wed, 29 Feb 2012 17:29:52 GMT

tks for the question

RE: Arithmetic 1

IgorMi — Wed, 29 Feb 2012 16:35:51 GMT

Hi All,I tried to make a conclusion based on the data type precedence, but did not manage.If you see the data type precedencehttp://msdn.microsoft.com/en-us/library/ms190276.aspx, it is also ambiguous for "%" showing different behavior against some cases for "*" and "/".* (Multiply), / (Division), % (Modulo) belong to level 2, while the others are in lower levels and for which there are no errors.In any case, interesting example!

RE: Arithmetic 1

Revenant — Wed, 29 Feb 2012 14:31:00 GMT

Very nice, Ron - thanks!

RE: Arithmetic 1

DBA_Dom — Wed, 29 Feb 2012 10:43:33 GMT

Nice question that I think most of have run into at some point by accident. Thanks.

RE: Arithmetic 1

Rose Bud — Wed, 29 Feb 2012 10:21:29 GMT

Rock-solid question thanks to Ron and his staff of reviewers!

RE: Arithmetic 1

L' Eomot Inversé — Wed, 29 Feb 2012 09:25:54 GMT

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.

RE: Arithmetic 1

Jason Selburg — Wed, 29 Feb 2012 08:56:24 GMT

Good question, but I feel compelled to point out that all statements would fail on a Case Sensitive Collation because the variable @b is declared, but @B 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. :-D

RE: Arithmetic 1

SQLRNNR — Wed, 29 Feb 2012 08:53:30 GMT

Thanks Ron

RE: Arithmetic 1

EL Jerry — Wed, 29 Feb 2012 07:37:28 GMT

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.

RE: Arithmetic 1

Hugo Kornelis — Wed, 29 Feb 2012 07:29:18 GMT

[quote]I could not locate a valid explanation[/quote]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.

RE: Arithmetic 1

Carlo Romagnano — Wed, 29 Feb 2012 06:39:30 GMT

This script demonstrates some problem with implicit conversion:[code="sql"]declare @v varchar(30),@f float -- here you think it is all right and it is set @f = 1.12 set @v = @f select @v -- here you think it is all right, but you get the wrong result set @f = 123456.12 set @v = @f select @v -- worst and worst set @f = 12345677.12 set @v = @f select @v -- correct solution set @f = 12345677.12 set @v = CAST(@f AS DECIMAL(16,2)) select @v [/code]

RE: Arithmetic 1

honza.mf — Wed, 29 Feb 2012 06:03:23 GMT

[quote][b]jcb (2/29/2012)[/b][hr]Yes! Implicit convertion is evil.Even worst when converting dates and using fancy collations and no US-en.[/quote]Fancy collations :-P Tomorow morning in Bois de Boulogne :-D

RE: Arithmetic 1

Thomas Abraham — Wed, 29 Feb 2012 05:31:26 GMT

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

RE: Arithmetic 1

rfr.ferrari — Wed, 29 Feb 2012 04:28:43 GMT

good question!!!thanks Ron!!!

RE: Arithmetic 1

jcb — Wed, 29 Feb 2012 04:17:36 GMT

I'm pretty sure SQL ill not multiply chars '1' * '4' it demands at least one numeric type to decide for the result type.Yes! Implicit convertion is evil.Even worst when converting dates and using fancy collations and no US-en.

RE: Arithmetic 1

Keld Laursen (VFL) — Wed, 29 Feb 2012 03:59:35 GMT

I would thing the devil were in the details.In fact a lot of the questions would fail if not for the fact that the numeric in the question is in fact a numeric.The trap, if we can call it that, is that no none-character values were there to coax the cast. If #2 had been constructed like the others, thus having the number '4' as a number in stead of a string, the statement would have done the cast and no error would have followed.Inserting a character i e.g. #4 would of course cause that one to fail as well.

RE: Arithmetic 1

cengland0 — Wed, 29 Feb 2012 03:24:18 GMT

[quote][b]Raghavendra Mudugal (2/29/2012)[/b][hr]It gives me this error on the 2nd blockMsg 8117, Level 16, State 1, Line 3Operand data type char is invalid for multiply operator.I guess E stands for ERROR?[/quote]You wouldn't have had to guess if you read the statement after the SQL:[quote]For simplity in creating the possible answers each answer is composed of a comma separated list [b]and if execution of one of the above generates an error the letter E will be included in the list.[/b] [/quote]

RE: Arithmetic 1

call.copse — Wed, 29 Feb 2012 03:09:12 GMT

[quote][b]Carlo Romagnano (2/29/2012)[/b][hr]This kind of error also is independent from version of sqlserver.You can not use - * / operators with chars[/quote]Well, you obviously can - as long as one of the other operators is a numeric type, so that SQL can decide what to attempt to convert the chars to. That's the level of guidance that SQL expects and it seems a decent compromise, rather than just making it so it will attempt a conversion to a non-specified type.

RE: Arithmetic 1

Toreador — Wed, 29 Feb 2012 02:34:59 GMT

Yet another question that tells us not to rely on implicit conversion...

RE: Arithmetic 1

Carlo Romagnano — Wed, 29 Feb 2012 01:54:41 GMT

This kind of error also is independent from version of sqlserver.You can not use - * / operators with chars

RE: Arithmetic 1

Raghavendra Mudugal — Wed, 29 Feb 2012 01:48:01 GMT

It gives me this error on the 2nd blockMsg 8117, Level 16, State 1, Line 3Operand data type char is invalid for multiply operator.I guess E stands for ERROR?

RE: Arithmetic 1

BrainDonor — Wed, 29 Feb 2012 01:45:20 GMT

I particularly like the fact that a QOTD was verified by several people before publication.

RE: Arithmetic 1

Christy M — Wed, 29 Feb 2012 01:41:55 GMT

[quote][b]raulggonzalez (2/29/2012)[/b][hr]The explanation I see is that on answers 1,3,4,5,6 implicit data types conversion happens because of mixing INT and CHAR types.On the answer 2, we just try to multiply 2 CHAR, therefore an error is raised up.Good question![/quote]Yeah, I have to agree, multiplying strings is the cause. It errors before it even tries to implicitly cast the answer to an int.

RE: Arithmetic 1

raulggonzalez — Wed, 29 Feb 2012 01:37:54 GMT

The explanation I see is that on answers 1,3,4,5,6 implicit data types conversion happens because of mixing INT and CHAR types.On the answer 2, we just try to multiply 2 CHAR, therefore an error is raised up.Good question!

RE: Arithmetic 1

Michael Riemer — Wed, 29 Feb 2012 00:38:55 GMT

Very interesting question. Learnt something new again.

RE: Arithmetic 1

Ramana Reddy P — Tue, 28 Feb 2012 22:10:59 GMT

Good Question....

RE: Arithmetic 1

baabhu — Tue, 28 Feb 2012 20:43:11 GMT

Good Question. Back to the basics. +1 to the total.

Arithmetic 1

bitbucket-25253 — Tue, 28 Feb 2012 20:41:26 GMT

Comments posted to this topic are about the item [B]Arithmetic 1[/B]