MySQL Bugs: #17313: N'xxx' and _utf8'xxx' are not equivalent

Bug #17313	N'xxx' and _utf8'xxx' are not equivalent
Submitted:	10 Feb 2006 23:30	Modified:	7 Sep 2006 0:45
Reporter:	Andreas Knab	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.0.19-BK, 5.0.18-nt	OS:	Linux (Linux, Windows XP)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
The documentation (http://dev.mysql.com/doc/refman/5.0/en/charset-national.html) says

  These two statements are equivalent:
  SELECT N'some text';
  SELECT _utf8'some text';

At least when using Connector/Net, that does not seem to be true.  When inserting values into a UTF8 column, the following statements yield different results:

  INSERT INTO test VALUES (N'\\'); -- inserts two backspaces
  INSERT INTO test VALUES (_utf8'\\'); -- inserts only one

Even worse:

  INSERT INTO test VALUES (N'Côte d\'Ivoire'); -- syntax error
  INSERT INTO test VALUES (_utf8'Côte d\'Ivoire'); -- works

How to repeat:
I created a simple table like this

create table test (
item varchar(255)
) default charset=utf8;

and then ran the statements listed above.

Suggested fix:
Make N'xxx' and _utf8'xxx' truly equivalent or update the documentation if there really is supposed to be a difference. Thanks!

Thank you for a bug report. Verified just as described with 5.0.19-BK on Linux:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table test;
Query OK, 0 rows affected (0.02 sec)

mysql> create table test (
    -> item varchar(255)
    -> ) default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test VALUES (N'\\');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (_utf8'\\');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+
| item |
+------+
| \\   |
| \    |
+------+
2 rows in set (0.00 sec)

If this behaviour is intended, it should be clearly described in the manual, as opposed to current incorrect description.

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/5187

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/9804

ChangeSet@1.2221, 2006-07-31 13:03:22+05:00, bar@mysql.com +3 -0
  Bug#17313: N'xxx' and _utf8'xxx' are not equivalent
    
    N'xxx' didn't unescape backslashes and double apostrophs.
    
    Fixed to reuse get_text() when parsing N'xxx', which
    makes N'xxx' and _utf8'xxx' work the same, like the manual states.

Pushed into 5.0.25-kt again,
as my patch seems to have been lost
due to recent re-clone.

Pushed into 5.0.25 main

This tagged as 5.1 and it's not a P1. Please furnish a 5.1.x version number in which the fix will appear. Thanks!

pushed to 5.0.25 and 5.1.12

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25 & 5.1.12 changelogs.