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

[10 Feb 2006 23:30] Andreas Knab
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!
[12 Feb 2006 8:56] Valeriy Kravchuk
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.
[20 Apr 2006 6:04] 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/5187
[31 Jul 2006 8:03] 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/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.
[7 Aug 2006 6:43] Alexander Barkov
Pushed into 5.0.25-kt again,
as my patch seems to have been lost
due to recent re-clone.
[16 Aug 2006 20:57] Reggie Burnett
Pushed into 5.0.25 main
[18 Aug 2006 13:05] Jon Stephens
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!
[18 Aug 2006 14:52] Reggie Burnett
pushed to 5.0.25 and 5.1.12
[7 Sep 2006 0:45] Jon Stephens
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.