What Data Type is Returned by a Mathematical Function?

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).