MySQL Forums
Forum List  »  Quality Assurance

Why does CONV(x, 10, 2) depend on connection charset?
Posted by: Alexander Alfimov
Date: August 30, 2008 11:15AM

Hi!

I have a problem working with binary strings using utf8 connection charset. Please take a look at queries below:

mysql> show create table com_categories\G
*************************** 1. row ***************************
Table: com_categories
Create Table: CREATE TABLE `com_categories` (
`category_ID` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`description` text,
`category_bit_mask` bit(64) default NULL,
PRIMARY KEY (`category_ID`),
KEY `category_bit_mask` (`category_bit_mask`)
) ENGINE=MyISAM AUTO_INCREMENT=38 DEFAULT CHARSET=utf8

THis is a table with bit mask field.

Problem:
mysql> set names 'latin1';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT distinct conv(c.category_bit_mask, 10, 2) AS `category_bit_mask` FROM `com_categories_test` AS c ORDER BY c.category_bit_mask ASC;
+----------------------------+
| category_bit_mask |
+----------------------------+
| 1 |
| 10 |
| 100 |
| 1000 |
| 10000 |
| 100000 |
| 1000000 |
| 10000000 |
| 100000000 |
| 1000000000 |
| 10000000000 |
| 100000000000 |
| 1000000000000 |
| 10000000000000 |
| 100000000000000 |
| 1000000000000000 |
| 10000000000000000 |
| 100000000000000000 |
| 1000000000000000000 |
| 10000000000000000000 |
| 100000000000000000000 |
| 1000000000000000000000 |
| 10000000000000000000000 |
| 100000000000000000000000 |
| 10000000000000000000000000 |
+----------------------------+
25 rows in set (0.00 sec)

We can see 25 different masks when latin1 charset is used.

But once we switch to utf8, MySQL finds only 21 distinct mask:
mysql> SELECT distinct conv(c.category_bit_mask, 10, 2) AS `category_bit_mask` FROM `com_categories_test` AS c ORDER BY c.category_bit_mask ASC;
+-----------------------+
| category_bit_mask |
+-----------------------+
| 1 |
| 10 |
| 100 |
| 1000 |
| 10000 |
| 100000 |
| 1000000 |
| 10000000 |
| 100000000 |
| 1000000000 |
| 10000000000 |
| 100000000000 |
| 1000000000000 |
| 10000000000000 |
| 100000000000000 |
| 1000000000000000 |
| 10000000000000000 |
| 100000000000000000 |
| 1000000000000000000 |
| 10000000000000000000 |
| 100000000000000000000 |
+-----------------------+
21 rows in set (0.00 sec)

At the same moment:
mysql> SELECT conv(c.category_bit_mask, 10, 2) AS `category_bit_mask` FROM `com_categories_test` AS c ORDER BY c.category_bit_mask ASC;
+----------------------------+
| category_bit_mask |
+----------------------------+
| 1 |
| 10 |
| 100 |
| 1000 |
| 10000 |
| 100000 |
| 1000000 |
| 10000000 |
| 100000000 |
| 1000000000 |
| 10000000000 |
| 100000000000 |
| 1000000000000 |
| 10000000000000 |
| 100000000000000 |
| 1000000000000000 |
| 10000000000000000 |
| 100000000000000000 |
| 1000000000000000000 |
| 10000000000000000000 |
| 100000000000000000000 |
| 1000000000000000000000 |
| 10000000000000000000000 |
| 100000000000000000000000 |
| 10000000000000000000000000 |
+----------------------------+
25 rows in set (0.00 sec)

Please advise if this is a bug, feature or something I'm doing wrong.

I'm using MySQL 5.0.45 Source distribution

Thank you.



Edited 1 time(s). Last edit at 08/30/2008 11:17AM by Alexander Alfimov.

Options: ReplyQuote


Subject
Views
Written By
Posted
Why does CONV(x, 10, 2) depend on connection charset?
4532
August 30, 2008 11:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.