Description:
With a 64-element set, it seems to be impossible to assign values by integer, in the manner shown here:
http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html
INSERT INTO set_test(myset) VALUES(7);
INSERT INTO set_test(myset) VALUES('Travel,Sports,Dancing');
The lower one works, the upper one doesn't - for 64-element sets. Here's the warning that you get.
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
Remove an element and the problem goes away: 63-element sets are fine. Is it something to do with the 64th bit implying negativity?
By the way, it's useful to be able to use integers, of course, when you're OR-ing together several results with BIT_OR, and then updating or inserting that value into another 64-element set column.
How to repeat:
mysql> CREATE TABLE set_test(rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, myset SET('Travel','Sports','Dancing','Fine Dining'));
mysql> insert into set_test values (1,1);
Query OK, 1 row affected, 1 warning (0.00 sec)
/* make the set column a 64-element set */
mysql> alter table set_test modify myset set('f00','f01','f02','f03','f04','f05','f06','f07','f08','f09','f10','f11','f12','f13','f14','f15','f16','f17','f18','f19','f20','f21','f22','f23','f24','f25','f26','f27','f28','f29','f30','f31','f32','f33','f34','f35','f36','f37','f38','f39','f40','f41','f42','f43','f44','f45','f46','f47','f48','f49','f50','f51','f52','f53','f54','f55','f56','f57','f58','f59','f60','f61','f62','f63');
mysql> update set_test set myset=1124252 where rowid=1; Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 1
mysql> select * from set_test;
+-------+-------+
| rowid | myset |
+-------+-------+
| 1 | |
+-------+-------+
1 row in set (0.01 sec)
/* make the set column a 63-element set */
mysql> alter table set_test modify myset set('f00','f01','f02','f03','f04','f05','f06','f07','f08','f09','f10','f11','f12','f13','f14','f15','f16','f17','f18','f19','f20','f21','f22','f23','f24','f25','f26','f27','f28','f29','f30','f31','f32','f33','f34','f35','f36','f37','f38','f39','f40','f41','f42','f43','f44','f45','f46','f47','f48','f49','f50','f51','f52','f53','f54','f55','f56','f57','f58','f59','f60','f61','f62');
mysql> update set_test set myset=1124252 where rowid=1; Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from set_test;
+-------+-----------------------------------------+
| rowid | myset |
+-------+-----------------------------------------+
| 1 | f02,f03,f04,f07,f08,f09,f10,f13,f16,f20 |
+-------+-----------------------------------------+
1 row in set (0.00 sec)
Suggested fix:
Allow 64-element SETs to handle large 64-bit unsigned integers for updates and inserts.
