# What Data Type is Returned by a Mathematical Function?

Posted in: MySQL, Technical Track

Or, “Missing information in the MySQL Manual”.

Just earlier today, I was using `POW()`, which I’ve grown quite fond of, simply because it makes life easier. I prefer using it like `SELECT 512*POW(1024,2)` to find out the number of bytes to put in a variable, for example.

First, let’s take a look at the `POW` function:

```Name: 'POW'
Description:
Syntax:
POW(X,Y)

Returns the value of X raised to the power of Y.```

Okay, so it gives us a value; but what about the data type? Let’s take `512*POW(1024,2)` as an example.

```5067 (blogs) > SELECT 512*POW(1024,2) AS example;
+-----------+
| example   |
+-----------+
| 536870912 |
+-----------+
1 row in set (0.00 sec)```

What is that? Well, it sure does look like an `INT` at this point, doesn’t it?

```5067 (blogs) > CREATE TABLE post1184_1 (a INT UNSIGNED);
Query OK, 0 rows affected (0.10 sec)

5067 (blogs) > INSERT INTO post1184_1 (a) VALUES (512*POW(1024,2));
Query OK, 1 row affected (0.03 sec)```

Yup, fair enough, we could insert it into an `INT`. But what is it really? Considering that it surely can give us something else, let’s take a look:

```5067 (blogs) > CREATE TABLE post1184_2 AS SELECT POW(1024,2) AS pow;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

5067 (blogs) > SHOW CREATE TABLE post1184_2\G
*************************** 1. row ***************************
Table: post1184_2
Create Table: CREATE TABLE `post1184_2` (
`pow` double default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)```

Oh, so all of a sudden, it’s a double. I do agree with this though, as the value should be a double, but shouldn’t this be documented somewhere?

Now, for my original purpose, I can do something like this to set my variable: `SET GLOBAL <variable> = CAST(512*POW(1024,2) AS UNSIGNED);` — which will work, or even `SET GLOBAL <variable> = 512 * ABS(1024*1024);`, or perhaps you’d prefer the more “standard” method: `SET GLOBAL <variable> = 512 *1024*1024;`.

Also, it seems the default message for a `DOUBLE` exceeding `1.7976931348623157E+308` has changed between versions 5.0.67 and 6.0.6:

```5067 (blogs) > SET SESSION sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

5067 (blogs) > SELECT POW(18446744073709551615,16) AS example;
+---------+
| example |
+---------+
|     inf |
+---------+
1 row in set (0.01 sec)

606 (blogs) > SET SESSION sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

606 (blogs) > SELECT POW(18446744073709551615,16) AS example;
+---------+
| example |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)```

I’m not sure which one I prefer, really. `inf` is not really correct, as there can be a larger value (for example, to the power of 17 instead of 16, in this case) but is `NULL` right? `NULL` doesn’t sound right to me, I would prefer something like a SQL error here, something like “Error: Value out of bounds”.

Let’s get back to how to find out a data type returned by a function. Sure, we can take a look at the source. But, when I go to the documentation, I would really like to see what is the data type being returned.

Let’s take another example, the `ABS()` function — it should return the absolute value, but what’s the data type? Performing the same task as with `POW()` we get a few different results:

```5067 (blogs) > DROP TABLE IF EXISTS post1184_3; CREATE TABLE post1184_3 AS SELECT ABS(-3*-3) AS abs;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

5067 (blogs) > SHOW CREATE TABLE post1184_3 \G
*************************** 1. row ***************************
Table: post1184_3
Create Table: CREATE TABLE `post1184_3` (
`abs` int(3) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

5067 (blogs) > DROP TABLE IF EXISTS post1184_3; CREATE TABLE post1184_3 AS SELECT ABS(-3*-3.1) AS abs;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

5067 (blogs) > SHOW CREATE TABLE post1184_3 \G
*************************** 1. row ***************************
Table: post1184_3
Create Table: CREATE TABLE `post1184_3` (
`abs` decimal(3,1) NOT NULL default '0.0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

5067 (blogs) > DROP TABLE IF EXISTS post1184_3; CREATE TABLE post1184_3 AS SELECT ABS(-3*-30000000000000000) AS abs;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

5067 (blogs) > SHOW CREATE TABLE post1184_3 \G
*************************** 1. row ***************************
Table: post1184_3
Create Table: CREATE TABLE `post1184_3` (
`abs` bigint(19) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

5067 (blogs) >```

So, true to its word, `ABS()` really does return the absolute value. The documentation also states that this is safe for use with `BIGINT`, but there is no such note on `POW()`.

Finally, I’d like to ask you if you have another, better method of finding out what data type is returned (no, looking at the source does not count).

Interested in working with Nicklas? Schedule a tech call.

#### Nicklas Westerlund

Baron just posted on this very subject not two weeks ago (and I happened to be reading it today, which is why I remember):

http://www.xaprb.com/blog/2008/08/13/how-to-emulate-the-typeof-function-in-mysql/

Especially, look at Sergei’s comment to that blog post:

“You can also start mysql command line client with â€“column-type-info (-T in old versions). Itâ€™ll show correct values as reported by the protocol.”