Why does CONV(x, 10, 2) depend on connection charset?
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.
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.