Bug #54668 User variable assignments get wrong type
Submitted: 21 Jun 2010 13:36 Modified: 27 Jul 2010 0:50
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.5.3+ OS:Any
Assigned to: Alexander Barkov
Triage: Triaged: D2 (Serious)

[21 Jun 2010 13:36] Paul Dubois
Description:
While investigating the effects of WL#2649 (Number-to-string conversions) to write its documentation, I ran across some anomalies, which Bar agrees are wrong behavior.

The functions I investigated are those named in this part of WL#2649:

"
After checking with MySQL's connectors people, Sergei Golubchik
suggested that the following functions, which return VARBINARY now,
should return VARCHAR (with connection character set:
MD5, OLD_PASSWORD, PASSWORD, SHA, SHA1, SHA2. The following
functions, which return LONGBLOB now, should return VARCHAR
(with connection character set):
ASTEXT, ASWKT.
"

Here is my test script, which has two parts:

select charset(md5('a')),collation(md5('a'));
select charset(old_password('a')),collation(old_password('a'));
select charset(password('a')),collation(password('a'));
select charset(sha('a')),collation(sha('a'));
select charset(sha1('a')),collation(sha1('a'));
select charset(astext(point(1,2))),collation(astext(point(1,2)));
select charset(aswkt(point(1,2))),collation(aswkt(point(1,2)));

set @x=md5('a');select charset(@x),collation(@x);
set @x=old_password('a');select charset(@x),collation(@x);
set @x=password('a');select charset(@x),collation(@x);
set @x=sha('a');select charset(@x),collation(@x);
set @x=sha1('a');select charset(@x),collation(@x);
set @x=astext(point(1,2));select charset(@x),collation(@x);
set @x=aswkt(point(1,2));select charset(@x),collation(@x);

The script tests the charset and collation of each function directly,
and when the result is assigned to a user variable as intermediary.
For reference, I tested 5.0 and 5.1, as well as 5.5.2 and 5.5.3
(the 5.5.x releases before and after the WL#2649 work).

Part 1:

5.0.92: All functions return binary/binary, as expected
5.1.49: Same
5.5.2: Same
5.5.3: All functions return utf8/utf8_general_ci, as expected (my locale is utf8)

So that is all well and good, I think. Things get more
interesting when we assign the function result to a user
variable and test that.

Part 2:

In most cases, the charset and collation of @x is binary/binary.
This is expected before 5.5.3, but not expected as of 5.5.3.
So when this occurs it is a bug.

But there are some exceptions, which I think might also indicate
some kind of problem:

5.0.92: For old_password() and password(): latin1/latin1_swedish_ci
5.1.49: Same
5.5.2: For old_password() and password(): utf8/utf8_general_ci
5.5.3: For old_password() and password(): latin1/latin1_swedish_ci

Now that's weird. For 5.0, 5.1, and 5.5.2, it might be that the
connection charset/collation are being used. But then why the
change back from utf8 to latin1 in 5.5.3? (I have checked my
5.5.3 installation and mysql uses utf8 as the connection
charset.)

Bar's reply to my observations:

"
I think we should fix it in 5.5.3, to make user variables
return the same with the functions themself.
"

How to repeat:
Run my test script.
[21 Jun 2010 14:10] Valerii Kravchuk
Verified just as described with recent mysql-trunk on Mac OS X. See file uploaded.
[21 Jun 2010 14:11] Valerii Kravchuk
Demonstration of the bug

Attachment: bug54668.txt (text/plain), 12.61 KiB.

[23 Jun 2010 5:29] Alexander Barkov
This is an unexpected regression.
[8 Jul 2010 14:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/113146

3099 Alexander Barkov	2010-07-08
      Bug#54668 User variable assignments get wrong type
      Problem: Item_str_ascii_func::val_str() did not set
      charset of the returned value properly.
      
        mysql-test/include/ctype_numconv.inc
        mysql-test/r/ctype_binary.result
        mysql-test/r/ctype_cp1251.result
        mysql-test/r/ctype_latin1.result
        mysql-test/r/ctype_ucs.result
        - Adding tests
      
        sql/item_strfunc.cc
        - Adding initialization of charset
[9 Jul 2010 5:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/113204

3104 Alexander Barkov	2010-07-09
      Bug#54668 User variable assignments get wrong type
      Problem: Item_str_ascii_func::val_str() did not set
      charset of the returned value properly.
        
        mysql-test/include/ctype_numconv.inc
        mysql-test/r/ctype_binary.result
        mysql-test/r/ctype_cp1251.result
        mysql-test/r/ctype_latin1.result
        mysql-test/r/ctype_ucs.result
        - Adding tests
        
        sql/item_strfunc.cc
        - Adding initialization of charset
[9 Jul 2010 6:04] Alexander Barkov
Pushed into mysql-trunk-bugfixing (5.5.6-m3)
Pushed into mysql-next-mr-bugfixing (5.6.99-m4)
[23 Jul 2010 12:26] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:33] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[27 Jul 2010 0:50] Paul Dubois
Noted in 5.5.6 changelog.

Assignments of the PASSWORD() or OLD_PASSWORD() function to a user
variable did not preserve the character set of the function return
value.