# Exponent Engima

• And after experimenting with the power function I have found that Power(15,15) comes up with the wrong answer - I get 437893890380859392 and the correct answer is 437893890380859375.

I wonder why and it makes me think that perhaps a few other functions in SQL server also generate the wrong answer.

Tim Brimelow

• @a^@a is a bitwise XOR, not an exponent operation. Any value XOR'd with itself is always 0. Subtracting 17 makes it -17, which is a negative number, and an invlaid input for the SQRT() function.

Actually, the explanation is not correct, even though the answer is.

If you remember operator precedence, - comes before ^, so @a^@a - 17 actually translates to @a ^ (@a - 17) or 16 ^ -1 which is -17 (same result but different logic).

Urbis, an urban transformation company

• tim_brimelow (8/7/2008)

And after experimenting with the power function I have found that Power(15,15) comes up with the wrong answer - I get 437893890380859392 and the correct answer is 437893890380859375.

I wonder why and it makes me think that perhaps a few other functions in SQL server also generate the wrong answer.

Tim Brimelow

I think that you are hitting the limits of the Float data type (which POWER() uses).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc.
[/font]
[font="Verdana"] "Performance is our middle name."[/font]

• Iggy (8/7/2008)

@a^@a is a bitwise XOR, not an exponent operation. Any value XOR'd with itself is always 0. Subtracting 17 makes it -17, which is a negative number, and an invlaid input for the SQRT() function.

Actually, the explanation is not correct, even though the answer is.

If you remember operator precedence, - comes before ^, so @a^@a - 17 actually translates to @a ^ (@a - 17) or 16 ^ -1 which is -17 (same result but different logic).

Excellent point. However, this seems to be a true case of the doc being wrong:

Try executing the following:

`declare @a bigint`

`Set @a = 17`

`Select @a^@a-17 as [QotD], (@a^@a)-17 as [^ first], @a^(@a-17) as [- first]`

I get the following results:

`QotD ^ first - first`

`-------------------- -------------------- --------------------`

`-17 -17 17`

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc.
[/font]
[font="Verdana"] "Performance is our middle name."[/font]

• rbarryyoung (8/7/2008)

Iggy (8/7/2008)

@a^@a is a bitwise XOR, not an exponent operation. Any value XOR'd with itself is always 0. Subtracting 17 makes it -17, which is a negative number, and an invlaid input for the SQRT() function.

Actually, the explanation is not correct, even though the answer is.

If you remember operator precedence, - comes before ^, so @a^@a - 17 actually translates to @a ^ (@a - 17) or 16 ^ -1 which is -17 (same result but different logic).

Excellent point. However, this seems to be a true case of the doc being wrong:

Try executing the following:

`declare @a bigint`

`Set @a = 17`

`Select @a^@a-17 as [QotD], (@a^@a)-17 as [^ first], @a^(@a-17) as [- first]`

I get the following results:

`QotD ^ first - first`

`-------------------- -------------------- --------------------`

`-17 -17 17`

excellent test, interesting how the QotD way yields different result to the - first. any thoughts anyone? 😎

Urbis, an urban transformation company

• I've tested rbarryyoung's code again with a + instead, and it seems it does the ^ first, then the + sign, which indeed contradicts the BOL.

Urbis, an urban transformation company

• Yeah, I tested a bunch of them, and the simplest answer is that ( ^, |, and & ) are really precedence 3 and ( +, - ) are really precedence 5.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc.
[/font]
[font="Verdana"] "Performance is our middle name."[/font]

• I checked the 2008 doc and apparently, they are all supposed to be precedence 3: http://technet.microsoft.com/en-us/library/ms190276(SQL.100).aspx.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc.
[/font]
[font="Verdana"] "Performance is our middle name."[/font]

• ah, that explains now. thanks for picking that up. We sure find mistakes in BOL (how scary to think how often we refer to it!)

Urbis, an urban transformation company

• mtassin (8/7/2008)

Sorin Petcu (8/7/2008)

Allof you, you have had wrong! I know from school, and everybody knows, that sqrt function did not support negative numbers (except complex numbers). But with this in your mind, you will be wrong in computers world. You should be guided by documentation of language. And this documentation is clear enough: sqrt supports negative float numbers. I know, you will say about ansi sql and other stuff like superior programming languages. But this issue is obviously clear.

No... because SQRT returns a float.

AND THE FLOAT DATATYPE DOES NOT SUPPORT COMPLEX/IMAGINARY NUMBERS.

Hence SQRT cannot receive a negative number as an input and a domain error will occur because the result of the SQRT(-1) or SQRT(-.01) is outside of the domain of a FLOAT datatype.

Again, other guy who make wrong suppositions. Theoretical, from math point of view, "SQRT cannot receive a negative number as an input", but here, we are talking about computer universe. Please, lay down the theory and read carrefully the documentation (BOL). On the other side, FLOAT has negative values, also.

In Theory, theory and practice are the same...In practice, they are not.
• "Exclusive or" ouch - I was thinking exponential also.

Arguments expressing dismay that mixing numeric types provide incorrect results, hmmm, isn't that a big part of programming?

declare

@x money

set @x=9.99

set @x=@x+.00489999

Print @x --no change

set @x=9.999

Print @x --duh - 10

Jamie

• Sorin Petcu (8/8/2008)

mtassin (8/7/2008)

Sorin Petcu (8/7/2008)

Allof you, you have had wrong! I know from school, and everybody knows, that sqrt function did not support negative numbers (except complex numbers). But with this in your mind, you will be wrong in computers world. You should be guided by documentation of language. And this documentation is clear enough: sqrt supports negative float numbers. I know, you will say about ansi sql and other stuff like superior programming languages. But this issue is obviously clear.

No... because SQRT returns a float.

AND THE FLOAT DATATYPE DOES NOT SUPPORT COMPLEX/IMAGINARY NUMBERS.

Hence SQRT cannot receive a negative number as an input and a domain error will occur because the result of the SQRT(-1) or SQRT(-.01) is outside of the domain of a FLOAT datatype.

Again, other guy who make wrong suppositions. Theoretical, from math point of view, "SQRT cannot receive a negative number as an input", but here, we are talking about computer universe. Please, lay down the theory and read carrefully the documentation (BOL). On the other side, FLOAT has negative values, also.

This is nothing to do with theory. Read Books Online about the float data type and tell me where it supports complex numbers. The return data type of SQRT is a float, a float data type cannot contain an imaginary or a complex number.

The Square Root of -1 is i, which is an imaginary/complex number. It cannot be returned as a float data type because it is out of the domain of the float data type.

It's right there in BOL.

Books Online

Returns the square root of the given expression.

Syntax

SQRT ( float_expression )

Arguments

float_expression

An expression whose type can be implicitly converted to float.

Return Value

float

And here is the BOL entry for the float data type.

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

Note:

The SQL-92 synonym for real is float(24).

Data type Range Storage

float

- 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

Depends on the value of n

real

- 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38

4 Bytes

Transact-SQL Syntax Conventions

Where is i listed as an allowed value for a float data type? If the SQRT function returns a float value, the return value has to be within the allowed values for it. The value of SQRT(-1) is outside of the allowed returnable values for the function.

This isn't theory... this is fact.

As a much simpler approach, take a calculator and try to take the SQRT of -1... if you have a really neato calculator you'll get a complex number back, if you don't you'll get an error.

http://en.wikipedia.org/wiki/Imaginary_number

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

• mtassin (8/8/2008)

Sorin Petcu (8/8/2008)

mtassin (8/7/2008)

Sorin Petcu (8/7/2008)

Allof you, you have had wrong! I know from school, and everybody knows, that sqrt function did not support negative numbers (except complex numbers). But with this in your mind, you will be wrong in computers world. You should be guided by documentation of language. And this documentation is clear enough: sqrt supports negative float numbers. I know, you will say about ansi sql and other stuff like superior programming languages. But this issue is obviously clear.

No... because SQRT returns a float.

AND THE FLOAT DATATYPE DOES NOT SUPPORT COMPLEX/IMAGINARY NUMBERS.

Hence SQRT cannot receive a negative number as an input and a domain error will occur because the result of the SQRT(-1) or SQRT(-.01) is outside of the domain of a FLOAT datatype.

Again, other guy who make wrong suppositions. Theoretical, from math point of view, "SQRT cannot receive a negative number as an input", but here, we are talking about computer universe. Please, lay down the theory and read carefully the documentation (BOL). On the other side, FLOAT has negative values, also.

This is nothing to do with theory. Read Books Online about the float data type and tell me where it supports complex numbers. The return data type of SQRT is a float, a float data type cannot contain an imaginary or a complex number.

The Square Root of -1 is i, which is an imaginary/complex number. It cannot be returned as a float data type because it is out of the domain of the float data type.

It's right there in BOL.

Books Online

Returns the square root of the given expression.

Syntax

SQRT ( float_expression )

Arguments

float_expression

An expression whose type can be implicitly converted to float.

Return Value

float

And here is the BOL entry for the float data type.

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

Note:

The SQL-92 synonym for real is float(24).

Data type Range Storage

float

- 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

Depends on the value of n

real

- 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38

4 Bytes

Transact-SQL Syntax Conventions

Where is i listed as an allowed value for a float data type? If the SQRT function returns a float value, the return value has to be within the allowed values for it. The value of SQRT(-1) is outside of the allowed returnable values for the function.

This isn't theory... this is fact.

As a much simpler approach, take a calculator and try to take the SQRT of -1... if you have a really neato calculator you'll get a complex number back, if you don't you'll get an error.

http://en.wikipedia.org/wiki/Imaginary_number%5B/quote%5D

Again you have fallen in mistake. Again you assume the fact that

"The return data type of SQRT is a float, a float data type cannot contain an imaginary or a complex number.

The Square Root of -1 is i, which is an imaginary/complex number."

The computer didn't know nothing about complex numbers. You assume that SQRT function, which is written by a computer programmer, should return i for -1 argument. Why? you wrong here. I could write my SQRT function how my muscles want. It is about programming and not about theory. Negative numbers are represented in computer different way by the way you know from theory. So, please don't mix the math theory with what is inside a computer.

In Theory, theory and practice are the same...In practice, they are not.
• Sorin Petcu (8/8/2008)

The Square Root of -1 is i, which is an imaginary/complex number."

The computer didn't know nothing about complex numbers. You assume that SQRT function, which is written by a computer programmer, should return i for -1 argument. Why? you wrong here. I could write my SQRT function how my muscles want. It is about programming and not about theory. Negative numbers are represented in computer different way by the way you know from theory. So, please don't mix the math theory with what is inside a computer.

We're not talking about negative numbers, negative numbers are just the input to a function, we're talking about the output of the function.

The Square Root of a Negative number (the output of the function) is represented in mathematics as an imaginary (pure) or complex (mixed a +bi) number.

The SQRT function used by SQL server returns a float. The Float data type is not able to contain a complex number. Thus the return data type of the SQRT of -1 is out of the domain of the float data type and we get an error message. How the function determines this (either it actually tries to calculate it and gets an error after attempting to find this value, or if it throws the error as soon as it sees the negative I don't know.. and frankly, I don't care).

I do know that -1 * -1 = 1 and that SQRT(1) = +/- 1.

If you think that SQRT(-1) is a negative number... tell me what negative number squared yeilds a negative number, because what I was taught in math was that -1*-1 = 1. In fact,

`SELECT -1 * -1`

On my SQL box that returns 1, not -1. So -1 Squared = 1... and SQRT(-1) is a domain error.

If you write your SQRT function to behave in a way that violates how mathematics work, then your company wouldn't be able to sell the software.

That would be like if I decided that my multiplication function should randomly divide all results by .75 because I feel like it. Yah I could write that function, but nobody would sell it, and if they did, it would be acknowledged as a bug (see Intel chips with broken floating point mathematics circa 1994), in the case of software like SQL server this would result in a hotfix released because if your math functions don't follow the rules of mathematics, then companies don't use them.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

• mtassin (8/8/2008)

We're not talking about negative numbers, negative numbers are just the input to a function, we're talking about the output of the function.

The Square Root of a Negative number (the output of the function) is represented in mathematics as an imaginary (pure) or complex (mixed a +bi) number.

The SQRT function used by SQL server returns a float. The Float data type is not able to contain a complex number. Thus the return data type of the SQRT of -1 is out of the domain of the float data type and we get an error message. How the function determines this (either it actually tries to calculate it and gets an error after attempting to find this value, or if it throws the error as soon as it sees the negative I don't know.. and frankly, I don't care).

I think you don't understand or you don't want to understand.

Let me see: how it is represented the number -5 in computer? as binar form as 1111111111111111111111111111111111111111111111111111111111111011 and this number would be reconverted as 18446744073709551611 in decimal format.

So, SQRT(-5) would be what? guess what:

select SQRT(18446744073709551611) = 4294967296

So we are talking only about numbers and not math theory. You said forth and forth about complex numbers. Wrong! Why you want to receive a complex number from a function built in a programming language? Why you want all the time that float number can't be asimilated as complex number? It is nothing to do with complex numbers in a programming language or an engine like sql server. Complex numbers are in math theory. So wake up!

In Theory, theory and practice are the same...In practice, they are not.

Viewing 15 posts - 31 through 45 (of 52 total)