MySQL Bugs: #65651: SET improvement

Bug #65651	SET improvement
Submitted:	18 Jun 2012 10:52	Modified:	29 Jun 2012 6:39
Reporter:	Angel Tsvetkov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S5 (Performance)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	SET

Description:
How can increase limitation of 64 max members of SET?
(A SET can have a maximum of 64 different members.)

How to repeat:
How can increase limitation of 64 max members of SET?
(A SET can have a maximum of 64 different members.)

There is no way to do this currently. Probably entirely new implementation for SET data type will be needed in this case. 

What maximum number of different elements would you like to see supported?

I would also like this limit to be flexible / increased (having just reached it on a system coded 14 years ago with a lot of code written by someone else, but relying heavily on a number of sets!)

What are the reasons for the current 64 limit, could it be increased by configuration? Why have a limitation at all?

Probably current limit has something to do with 8 byte numbers and bit operations used. 

In any case, this is a reasonable feature request for a new implementation to support sets with more items.

Binary bitmaps in mysql currently can go up to 1024 members, using a BINARY(128) field, and a function like:
insert into table_with_bitmap
select $other_field_values,unhex(hex(sum(pow(2,id))))
from item_dimension
where item_name in ('item1','item3','item7');

However actually using the data like that is sloppy.  With the mathematical limit of mysql being <2^1024, SET type could feasibly store up to 1024 unique values with some changes to how it's stored.  It already uses variable length (up to 8 bytes) using behind the scenes numeric types to store (tinyint-bigint), but stored as binary data it could be byte by byte variable from 1B to 128B.

One possible requirement, however, would be to eliminate the 64b math limitation on bitwise operations, assuming that is the same math used behind the scenes when mysql stores set fields.