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: | |
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
[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."