MySQL Bugs: #15409: Columns with SET datatype with 64-element sets may not be updated with integers

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

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.

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.

Also happening on 5.0.42.

Also affects 5.0.45 on 64bit Ubuntu. Though, I have to ask: 2 years?

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.

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.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.60

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.

Bugfix also documented in 5.1.23-ndb-6.3.11 changelog.