MD5() does not work correctly with CONCAT() and CONCAT_WS()
Concatenating anything with MD5() of any integer in some cases gives MD5 hash of an empty string.
I'm using latest stable version: 2011.03.13.
How to repeat:
Just run drizzle client and make some queries:
1. Correct hash:
drizzle> SELECT MD5(1);
+----------------------------------+
| MD5(1) |
+----------------------------------+
| c4ca4238a0b923820dcc509a6f75849b |
+----------------------------------+
2. Correct hash:
drizzle> SELECT CONCAT(MD5(1));
+----------------------------------+
| CONCAT(MD5(1)) |
+----------------------------------+
| c4ca4238a0b923820dcc509a6f75849b |
+----------------------------------+
3. Incorrect hash (actually it is a hash of an empty string):
drizzle> SELECT CONCAT(' ', MD5(1));
+-----------------------------------+
| CONCAT(' ', MD5(1)) |
+-----------------------------------+
| d41d8cd98f00b204e9800998ecf8427e |
+-----------------------------------+
4. This should be FALSE:
drizzle> SELECT MD5(2) = TRIM(CONCAT(' ', MD5(1)));
+-------------------------------------+
| MD5(2) == TRIM(CONCAT(' ', MD5(1))) |
+-------------------------------------+
| 1 |
+-------------------------------------+
5. Correct hash:
drizzle> SELECT CONCAT(MD5(1), ' ');
+-----------------------------------+
| CONCAT(MD5(1), ' ') |
+-----------------------------------+
| c4ca4238a0b923820dcc509a6f75849b |
+-----------------------------------+
6. Wrong hash:
drizzle> SELECT CONCAT(' ', MD5(CONCAT(1, 'x')));
+-----------------------------------+
| CONCAT(' ', MD5(CONCAT(1, 'x'))) |
+-----------------------------------+
| d41d8cd98f00b204e9800998ecf8427e |
+-----------------------------------+
7. Second hash is wrong:
drizzle> SELECT CONCAT(MD5(1), ' ', MD5(1), ' ', MD5(1), ' ', MD5(1), ' ', MD5(1));
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT(MD5(1), ' ', MD5(1), ' ', MD5(1), ' ', MD5(1), ' ', MD5(1)) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| c4ca4238a0b923820dcc509a6f75849b d41d8cd98f00b204e9800998ecf8427e c4ca4238a0b923820dcc509a6f75849b c4ca4238a0b923820dcc509a6f75849b c4ca4238a0b923820dcc509a6f75849b |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8. Second hash is wrong:
drizzle> SELECT CONCAT_WS(' ', MD5(1), MD5(1), MD5(1), MD5(1), MD5(1));
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT_WS(' ', MD5(1), MD5(1), MD5(1), MD5(1), MD5(1)) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| c4ca4238a0b923820dcc509a6f75849b d41d8cd98f00b204e9800998ecf8427e c4ca4238a0b923820dcc509a6f75849b c4ca4238a0b923820dcc509a6f75849b c4ca4238a0b923820dcc509a6f75849b |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9. Another problem is selecting from a table with an integer colum. MD5 hash in the first row is wrong, whilst following rows are all right. How to repeat this:
drizzle> CREATE TABLE `tab` (`a` INT DEFAULT NULL);
drizzle> INSERT INTO `tab`(`a`) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Wrong result in the first row (see fourth column):
drizzle> SELECT `a`, MD5(`a`), CONCAT(MD5(`a`)), CONCAT(' ', MD5(`a`)), CONCAT(MD5(`a`), ' ') FROM `tab` ORDER BY `a`;
+------+----------------------------------+----------------------------------+-----------------------------------+-----------------------------------+
| a | MD5(`a`) | CONCAT(MD5(`a`)) | CONCAT(' ', MD5(`a`)) | CONCAT(MD5(`a`), ' ') |
+------+----------------------------------+----------------------------------+-----------------------------------+-----------------------------------+
| 1 | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b | d41d8cd98f00b204e9800998ecf8427e | c4ca4238a0b923820dcc509a6f75849b |
| 2 | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c |
| 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |
| 4 | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c |
| 5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 |
| 6 | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc |
| 7 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 |
| 8 | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d |
| 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |
| 10 | d3d9446802a44259755d38e6d163e820 | d3d9446802a44259755d38e6d163e820 | d3d9446802a44259755d38e6d163e820 | d3d9446802a44259755d38e6d163e820 |
+------+----------------------------------+----------------------------------+-----------------------------------+-----------------------------------+
10. Ordering by some column allways gives incorrect result in the first row (see fourth column):
drizzle> SELECT `a`, MD5(`a`), CONCAT(MD5(`a`)), CONCAT(' ', MD5(`a`)), CONCAT(MD5(`a`), ' ') FROM `tab` ORDER BY `a` DESC;
+------+----------------------------------+----------------------------------+-----------------------------------+-----------------------------------+
| a | MD5(`a`) | CONCAT(MD5(`a`)) | CONCAT(' ', MD5(`a`)) | CONCAT(MD5(`a`), ' ') |
+------+----------------------------------+----------------------------------+-----------------------------------+-----------------------------------+
| 10 | d3d9446802a44259755d38e6d163e820 | d3d9446802a44259755d38e6d163e820 | d41d8cd98f00b204e9800998ecf8427e | d3d9446802a44259755d38e6d163e820 |
| 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |
| 8 | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d |
| 7 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 |
| 6 | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc |
| 5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 |
| 4 | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c |
| 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |
| 2 | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c |
| 1 | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b |
+------+----------------------------------+----------------------------------+-----------------------------------+-----------------------------------+
11. Ordering by any expression gives wrong result in the first row (see fourth column):
drizzle> SELECT `a`, MD5(`a`), CONCAT(MD5(`a`)), CONCAT(' ', MD5(`a`)), CONCAT(MD5(`a`), ' ') FROM `tab` ORDER BY `a` * `a` % 10 DESC;
+------+----------------------------------+----------------------------------+-----------------------------------+-----------------------------------+
| a | MD5(`a`) | CONCAT(MD5(`a`)) | CONCAT(' ', MD5(`a`)) | CONCAT(MD5(`a`), ' ') |
+------+----------------------------------+----------------------------------+-----------------------------------+-----------------------------------+
| 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | d41d8cd98f00b204e9800998ecf8427e | eccbc87e4b5ce2fe28308fd9f2a7baf3 |
| 7 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 |
| 6 | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc |
| 4 | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c |
| 5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 |
| 2 | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c |
| 8 | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d |
| 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |
| 1 | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b |
| 10 | d3d9446802a44259755d38e6d163e820 | d3d9446802a44259755d38e6d163e820 | d3d9446802a44259755d38e6d163e820 | d3d9446802a44259755d38e6d163e820 |
+------+----------------------------------+----------------------------------+-----------------------------------+-----------------------------------+
12. However ordering by RAND() gives incorrect hash in the row with a = 1 (not the first row):
drizzle> SELECT `a`, MD5(`a`), CONCAT(MD5(`a`)), CONCAT(' ', MD5(`a`)), CONCAT(MD5(`a`), ' ') FROM `tab` ORDER BY RAND();
+------+----------------------------------+----------------------------------+-----------------------------------+-----------------------------------+
| a | MD5(`a`) | CONCAT(MD5(`a`)) | CONCAT(' ', MD5(`a`)) | CONCAT(MD5(`a`), ' ') |
+------+----------------------------------+----------------------------------+-----------------------------------+-----------------------------------+
| 8 | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d |
| 7 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 |
| 2 | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c |
| 6 | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc |
| 4 | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c |
| 1 | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b | d41d8cd98f00b204e9800998ecf8427e | c4ca4238a0b923820dcc509a6f75849b |
| 10 | d3d9446802a44259755d38e6d163e820 | d3d9446802a44259755d38e6d163e820 | d3d9446802a44259755d38e6d163e820 | d3d9446802a44259755d38e6d163e820 |
| 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |
| 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |
| 5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 |
+------+----------------------------------+----------------------------------+-----------------------------------+-----------------------------------+
13. The same problem is when one tries to update a row. The first row will be updated with a hash of an empty string, though remaining rows have correct hash. How to repeat this:
drizzle> ALTER TABLE `tab` ADD `b` VARCHAR(50);
drizzle> UPDATE `tab` SET `b` = CONCAT('hash ', MD5(`a`));
drizzle> SELECT * FROM `tab`;
+------+---------------------------------------+
| a | b |
+------+---------------------------------------+
| 1 | hash d41d8cd98f00b204e9800998ecf8427e |
| 2 | hash c81e728d9d4c2f636f067f89cc14862c |
| 3 | hash eccbc87e4b5ce2fe28308fd9f2a7baf3 |
| 4 | hash a87ff679a2f3e71d9181a67b7542122c |
| 5 | hash e4da3b7fbbce2345d7772b0674a318d5 |
| 6 | hash 1679091c5a880faf6fb5e6087eb1b2dc |
| 7 | hash 8f14e45fceea167a5a36dedd4bea2543 |
| 8 | hash c9f0f895fb98ab9159f51fd0297e236d |
| 9 | hash 45c48cce2e2d7fbdea1afc51c7c6ad26 |
| 10 | hash d3d9446802a44259755d38e6d163e820 |
+------+---------------------------------------+
14. If one updates row by row, then rows are updated incorrectly:
drizzle> UPDATE `tab` SET `b` = CONCAT('hash ', MD5(`a`)) WHERE `a` = 1;
drizzle> UPDATE `tab` SET `b` = CONCAT('hash ', MD5(`a`)) WHERE `a` = 2;
drizzle> UPDATE `tab` SET `b` = CONCAT('hash ', MD5(`a`)) WHERE `a` = 3;
drizzle> UPDATE `tab` SET `b` = CONCAT('hash ', MD5(`a`)) WHERE `a` = 4;
drizzle> UPDATE `tab` SET `b` = CONCAT('hash ', MD5(`a`)) WHERE `a` = 5;
drizzle> SELECT * FROM `tab`;
+------+---------------------------------------+
| a | b |
+------+---------------------------------------+
| 1 | hash d41d8cd98f00b204e9800998ecf8427e |
| 2 | hash d41d8cd98f00b204e9800998ecf8427e |
| 3 | hash d41d8cd98f00b204e9800998ecf8427e |
| 4 | hash d41d8cd98f00b204e9800998ecf8427e |
| 5 | hash d41d8cd98f00b204e9800998ecf8427e |
| 6 | hash 1679091c5a880faf6fb5e6087eb1b2dc |
| 7 | hash 8f14e45fceea167a5a36dedd4bea2543 |
| 8 | hash c9f0f895fb98ab9159f51fd0297e236d |
| 9 | hash 45c48cce2e2d7fbdea1afc51c7c6ad26 |
| 10 | hash d3d9446802a44259755d38e6d163e820 |
+------+---------------------------------------+
15. The problem of wrong MD5 hash does not occur when one calculates hash of a string:
drizzle> SELECT MD5(2), CONCAT(' ', MD5('2'));
+----------------------------------+-----------------------------------+
| MD5(2) | CONCAT(' ', MD5('2')) |
+----------------------------------+-----------------------------------+
| c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c |
+----------------------------------+-----------------------------------+
15a. This is TRIE:
drizzle> SELECT MD5('2') == TRIM(CONCAT(' ', MD5('2')));
+-----------------------------------------+
| MD5('2') == TRIM(CONCAT(' ', MD5('2'))) |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
15b. This should be TRUE:
drizzle> SELECT MD5(2) == TRIM(CONCAT(' ', MD5('2')));
+---------------------------------------+
| MD5(2) == TRIM(CONCAT(' ', MD5('2'))) |
+---------------------------------------+
| 0 |
+---------------------------------------+
15c. This is FALSE:
drizzle> SELECT MD5('2') == TRIM(CONCAT(' ', MD5('1')));
+-----------------------------------------+
| MD5('2') == TRIM(CONCAT(' ', MD5('1'))) |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
16. Concatenate of hash of an integer treated as char gives incorrect hash again:
drizzle> SELECT CONCAT(' ', MD5(CAST(1 AS CHAR)));
+-----------------------------------+
| CONCAT(' ', MD5(CAST(1 AS CHAR))) |
+-----------------------------------+
| d41d8cd98f00b204e9800998ecf8427e |
+-----------------------------------+
Best wishes,
Michał Sulik.
