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	Email Updates:
Status:	Closed
Category:	Server: Docs	Severity:	S3 (Non-critical)
Version:	all	OS:	Any
Assigned to:	Jon Stephens	Target Version:
Triage:	D4 (Minor)

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