November 18, 2008

263 words 2 mins read

In MySQL: x^y != x raised to the y

As the title states, the caret (^) is not used as an exponent in MySQL. I’m not certain what exactly it does (and couldn’t find any documentation after a quick search), and would love to find out what its purpose is.

I ran into this problem today when I noticed that MySQL’s math results (using ^) didn’t match my own. After triple checking my own math, I added lots of parens and still got nowhere, so I checked the Operator Precedence and sure enough it listed the caret (^) as falling between “*, /, DIV, %, MOD” and “- (unary minus), ~ (unary bit inversion)". This made me think that surely I have a typo in my code somewhere since if the operator is mentioned it should be supported. WRONG.

Convinced that something had gone horribly awry I began to look through the other math function available and switched to using POW and SQRT which instantly remedied the problem.

After getting my equations working I began to try to find more information on the symbol that caused me so much frustration. Alas, as mentioned above, I could find no specific information, other than some hints that it might be used for RegEx or as XOR, though after running the query below, I remain unconvinced.

SELECT 1^0, 1^1, 1^2, 1^3, 1^4, 1^5, TRUE^FALSE, TRUE^TRUE, FALSE^FALSE, FALSE^TRUE

<th>

1^1

<th>

1^2

<th>

1^3

<th>

1^4

<th>

1^5

<th>

TRUE^FALSE

<th>

TRUE^TRUE

<th>

FALSE^FALSE

<th>

FALSE^TRUE

<td>

</td>

<td>

3

<td>

2

<td>

5

<td>

4

<td>

1

<td>

</td>

<td>

</td>

<td>

1

1^0
1