linux-traipu team mailing list archive

Thread
Date
[Bug 823848] [NEW] MD5() does not work correctly with CONCAT() and CONCAT_WS()

To: linux-traipu@xxxxxxxxxxxxxxxxxxx
From: Michal Sulik <823848@xxxxxxxxxxxxxxxxxx>
Date: Wed, 10 Aug 2011 10:48:45 -0000
Reply-to: Bug 823848 <823848@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Public bug reported:

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.

** Affects: drizzle
     Importance: Undecided
         Status: New


** Tags: concat concatws md5

-- 
You received this bug notification because you are a member of UBUNTU -
AL - BR, which is subscribed to Drizzle.
https://bugs.launchpad.net/bugs/823848

Title:
  MD5() does not work correctly with CONCAT() and CONCAT_WS()

Status in A Lightweight SQL Database for Cloud Infrastructure and Web Applications:
  New

Bug description:
  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.

To manage notifications about this bug go to:
https://bugs.launchpad.net/drizzle/+bug/823848/+subscriptions
Follow ups

[Bug 823848] [NEW] MD5() does not work correctly with CONCAT() and CONCAT_WS()
From: Michal Sulik, 2011-08-10
References

[Bug 823848] [NEW] MD5() does not work correctly with CONCAT() and CONCAT_WS()
From: Michal Sulik, 2011-08-10