What Data Type is Returned by a Mathematical Function?

Posted in: 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.

4 Comments. Leave new

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/

Reply

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

Reply
Nicklas Westerlund
August 28, 2008 2:42 am

Thanks. Especially –column-type-info is very handy, I already like that option.

Reply
What Data Type is Returned by a Mathematical Function? « I’m just a simple DBA on a complex production system
September 5, 2008 9:24 pm

[…] by a Mathematical Function? September 6, 2008 Filed under: tips — prodlife @ 2:24 am This question was asked last week by Nicklas Westerlund. His attempts to answer the question are fascinating and […]

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *