Bug #794 String Comparison Failure
Submitted: 5 Jul 2003 6:25 Modified: 5 Jul 2003 8:24
Reporter: Paul DeMarco Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.13 OS:Linux (Linux Redhat 7.3)
Assigned to: CPU Architecture:Any

[5 Jul 2003 6:25] Paul DeMarco
Description:
The following simple queries demonstrate the problem:

SELECT 'test' = 'test';
SELECT 'test' = HEX('test');
SELECT 'test' = x'74657374';

It reports a value of 1 for the first query (clearly), but 0 for the second and third query, the method of hex encoding doesn't matter.  This also does not work when comparing a column against a hex encoded string in the where clause of a query, but the SELECT example is a simpler case for demonstration.

How to repeat:
run the query above

Suggested fix:
unknown
[5 Jul 2003 7:34] Alexander Keremidarski
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

SELECT 'test' = 'test';      -> true for  obvious reason
SELECT 'test' = HEX('test'); -> false because:

mysql> SELECT 'test', HEX('test');
+------+-------------+
| test | HEX('test') |
+------+-------------+
| test | 74657374    |
+------+-------------+

SELECT 'test' = x'74657374'; -> false. Docummented behaviour: 

Manual Chapter

6.1.1.3 Hexadecimal Values

...

In string context these act like a binary string ...
                                   ^^^^^^

Therefore:

mysql> SELECT 'test', x'74657374', 'test' = x'74657374' AS test1, 'test' = BINARY x'74657374' AS test2, 'test' = CAST(x'74657374' AS CHAR) AS test3;
+------+-------------+-------+-------+-------+
| test | x'74657374' | test1 | test2 | test3 |
+------+-------------+-------+-------+-------+
| test | test        |     0 |     1 |     1 |
+------+-------------+-------+-------+-------+

Same with 0x notation.
mysql> SELECT 'test', 0x74657374, 'test' = 0x74657374 AS test1, 'test' = BINARY 0x74657374 AS test2, 'test' = CAST(0x74657374 AS CHAR) AS test3;
+------+------------+-------+-------+-------+
| test | 0x74657374 | test1 | test2 | test3 |
+------+------------+-------+-------+-------+
| test | test       |     0 |     1 |     1 |
+------+------------+-------+-------+-------+

The only concern here is how standard compliant this behaviour is.
[5 Jul 2003 8:24] Sergei Golubchik
actually, in

SELECT x'74657374' = 'test';

x'74657374' is used in *numeric* context (comparison with string is not strong enough for string context). You have to convert it to string explicitly by using CAST (or by using it in any string context, as in CONCAT(x'74657374'). Note that in MySQL 4.1 (and in MySQL 4.0 when using the `--new' option) the default type of of a hexadecimal value is a string, so that

SELECT x'74657374' = 'test';

will return 1.
[5 Jul 2003 8:39] Paul DeMarco
thanks to both of you for your quick responses, I can see where you are coming from that the hex value is in a numeric context by default, I had assummed that comparing it to a string would promote it to a string context.  I'm not sure that the assumption is completely illogical, but can at least understand the behavior as it works now.  Our software system uses hex strings as an easy way for encoding any string value, rather than escaping it specific to each dbms.
[5 Jul 2003 16:16] Paul DeMarco
When I compare an integer data type to a real data type, then its promoted and if the values are equal the comparison is true.

I thing this should behave the same way, the hex value defaults to a numeric context (fine).  Then when it is used in a way that is different then that (ie a string comparison) it should be promoted as such (or otherwise converted).

Is this not one of the implicit data type conversions that happen (I can see that its not from the behavior and I assume this was intended).

So the only question that I have left is, should the query parse comparing integer type to string type, or give an error.  Is that compliant?  I know other DBMS give an error and some dont, just not sure what the standard actually is.
[7 Jul 2003 0:19] Alexander Keremidarski
Paul, your questions went beyond topic of your bug report :)

Just one more comment.

In 4.1 x'' and 0x notations return binary string so result will be as you expect it.

In 4.0 you can have this behaviour if you start mysqld with --new option

Quote from our SQL standards expert:

"As for the SQL standard, it is more restrictive:
X'....' is supposed to be for binary string
literals, not character string literals, so one
would have to use CAST if inserting into a CHAR
column."