MySQL Bugs: #32164: Numeric functions listed as string functions

Bug #32164	Numeric functions listed as string functions
Submitted:	7 Nov 2007 16:15	Modified:	9 Nov 2007 11:43
Reporter:	David Newcomb (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	all	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
It took me ages to find the useful functions associated with switching numbers from one base to another, eg HEX(), CONV(). I had to use Google to find out that it was in your string functions area and not the numeric area.

How to repeat:
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

Suggested fix:
They are operations on numbers and so should be with all the other functions which act on number.

I agree with the submitter - the description of HEX(), for example, speaks of string representations (hence its placement in the string functions, one would imagine) and numeric representations, when what's really meant are hexadecimal and decimal representations of numbers.

Verified, assigned to myself, Stefan as lead.

No. Where's "the hexadecimal and decimal representations of numbers" in the following example ?

mysql> select hex('asdfghjqqqqqqqqqqqqqqqqqqqqqq');
+------------------------------------------------------------+
| hex('asdfghjqqqqqqqqqqqqqqqqqqqqqq')                       |
+------------------------------------------------------------+
| 6173646667686A71717171717171717171717171717171717171717171 | 
+------------------------------------------------------------+

To quote the manual:
"
HEX(N_or_S)
If N_or_S is a number, returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,16).

If N_or_S is a string, returns a hexadecimal string representation of N_or_S where each character in N_or_S is converted to two hexadecimal digits.
"

If N_or_S is a number! I can't say I've ever needed to translate an ascii string into hex. HEX(N) is basically short hand for CONV(N, 10, 16), which specifically works with numbers. The fact that HEX(N) accepts a string outside [0-F] is IMO a bug and it should return NULL, but that is a discussion for a different place.

You are basically exploiting an "undefined" result that happens to produce something that you want.

The manual does not say that "HEX(N) is basically short hand for CONV(N, 10, 16), which specifically works with numbers".

On the other hand, it explicitly says that the argument can be a string, and the result is a "a hexadecimal string representation of N_or_S where each character in N_or_S is converted to two hexadecimal digits."

Ok, but I haven't ever had to convert a string to a hex string. But what about CONV()? This converts numbers between bases. You can't say that just because it produces a string(which is still a number really) at the end that it is a string operation.

Yes, CONV() is questionable. I agree that it can be seen as a numeric function.
That's why I didn't change the status to "Not a bug" :)

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

The fact that garbage (here defined as 'any sequence of characters including anything other than the digits 0-9') can be fed to HEX() without producing an error would seem to me to reflect a bug in this function's implementation. This quirk, however, has nothing to do with what people want to use this function *for*.

The HEX() function expects a decimal number, and returns its hexadecimal representation. If you feed it a string, it returns nonsense:

mysql> select hex(12);
+---------+
| hex(12) |
+---------+
| C       |
+---------+
1 row in set (0.06 sec)

mysql> select hex('12');
+-----------+
| hex('12') |
+-----------+
| 3132      |
+-----------+
1 row in set (0.00 sec)

Yes, I know what the Manual says... But perhaps .01% of all users are interested in what happens when you feed this function a string; for the remainder, it's at best a useless side effect, and at worst, something to trip over. A sensible implementation would attempt to convert a string to a decimal number (as OCT() does), and bail with an error if the conversion couldn't be made.

Whether or not you agree with the preceding statement, I think there's little room to dispute the fact that the overwhelming majority of users will wish to employ this function to obtain the hexadecimal representation of a decimal *number*. 

Most people look at this way: "I need to perform a certain operation on a *number*. I think a sensible place to look for such a function would be in a section of the Manual called 'Numeric Functions' or 'Math Functions'." 

And I agree with them.

Following discussion with Sergei and Paul, I've re-opened this bug. (A lot of people use HEX() to encode strings.)

I'm going to revert the changes I made yesterday and see if I can't come up with a better solution.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

New fix:

  Adds link to HEX() in mathematical-functions (but leaves main description in string-functions)

  Moves OCT() and CONV() to mathematical-functions