MySQL Bugs: #54668: User variable assignments get wrong type

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	CPU Architecture:	Any

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.

Verified just as described with recent mysql-trunk on Mac OS X. See file uploaded.

Demonstration of the bug

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

This is an unexpected regression.

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

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

Pushed into mysql-trunk-bugfixing (5.5.6-m3)
Pushed into mysql-next-mr-bugfixing (5.6.99-m4)

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)

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.