Bug #15409 Columns with SET datatype with 64-element sets may not be updated with integers
Submitted: 2 Dec 2005 2:41 Modified: 14 Mar 2008 20:57
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:4.1.17-BK, 4.1.14 OS:Linux (Linux)
Assigned to: Gleb Shchepa CPU Architecture:Any

[2 Dec 2005 2:41] [ name withheld ]
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.
[4 Dec 2005 15:26] Valeriy Kravchuk
Thank you for a bug report. Verified just as described on 4.1.17-BK (ChangeSet@1.2478, 2005-12-01 14:26:19+02:00) on Linux.
[3 Jul 2007 22:36] Kolbe Kegel
Also happening on 5.0.42.
[6 Feb 2008 4:03] Matt Rowe
Also affects 5.0.45 on 64bit Ubuntu. Though, I have to ask: 2 years?
[7 Feb 2008 10:11] 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/41857

ChangeSet@1.2600, 2008-02-07 14:10:14+04:00, gshchepa@host.loc +3 -0
  Fixed bug#15409: Columns with 64-element SET may not be updated with integers.
  
  SET column storing procedure has been modified to be 64bit-clean.
[8 Feb 2008 7:59] 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/41915

ChangeSet@1.2600, 2008-02-08 16:04:01+04:00, gshchepa@host.loc +3 -0
  Fixed bug#15409: Columns with 64-element SET may not be updated with integers.
  
  SET column storing procedure has been modified to be 64bit-clean.
[13 Mar 2008 19:28] Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:35] Bugs System
Pushed into 5.1.24-rc
[13 Mar 2008 19:42] Bugs System
Pushed into 5.0.60
[14 Mar 2008 20:57] Jon Stephens
Documented in the 5.0.60, 5.1.24, and 6.0.5 changelogs as follows:

        A SET column whose definition specified 64 elements
        could not be updated using integer values.
[29 Mar 2008 19:41] Jon Stephens
Bugfix also documented in 5.1.23-ndb-6.3.11 changelog.