Description:
There is wrong method to select large SET as bitfield :
MySQL Doc reference :
https://dev.mysql.com/doc/refman/5.5/en/set.html
MySQL stores SET values numerically, with the low-order bit of the stored value corresponding to the first set member. If you retrieve a SET value in a numeric context, the value retrieved has bits set corresponding to the set members that make up the column value. For example, you can retrieve numeric values from a SET column like this:
mysql> SELECT set_col+0 FROM tbl_name;
This select don’t work correct with sets using values >= 0x1000000000000000
Mysql ver 5.5 ( not tested with higher versions ) – but same as in doc page.
Example of wrong query
SELECT set_col+0 FROM tbl_name;
800000000000000 UKRAINE
1000000000000000 URUGUAY
2000000000000000 UNITED_STATES
4000000000000000 VENEZUELA
FFFFFFFFFFFFFFFF SOUTH_AFRICA
FFFFFFFFFFFFFFFF UNKNOWN,EUROPE,NORTH_AM,SOUTH_AM,ASIA,OCEANIA,AFRICA,ARGENTINA,AUSTRALIA,BELGIUM,BULGARIA,BELARUS,BRAZIL,CANADA,CHILE,CHINA,COLOMBIA,COSTA_RICA,CZECH_REPUBLIC,GERMANY,DOMINICAN_REPUBLIC,ECUADOR,EGYPT,SPAIN,FRANCE,UN
ITED_KINGDOM,GREECE,GUATEMALA,HONG_KONG,CROATIA,HUNGARY,INDONESIA,INDIA,IRELAND,IRAN,ISRAEL,ITALY,JAPAN,KAZAKHSTAN,LITHUANIA,MEXICO,MALAYSIA,NORWAY,NETHERLANDS,NEW_ZEALAND,PAKISTAN,PERU,PHILIPPINES,POLAND,PORTUGAL,ROMANIA,RUSSIA,SAUDI_ARABIA,SLOVAKIA,SLOVENIA,SWEDEN,THAILAND,TURKEY,TAIWAN,UKRAINE,URUGUAY,UNITED_STATES,VENEZUELA,SOUTH_AFRICA
2408402400040200 BELGIUM,CZECH_REPUBLIC,IRAN,JAPAN,PERU,RUSSIA,TAIWAN,UNITED_STATES
2408402400040200 ARGENTINA,AUSTRALIA,CZECH_REPUBLIC,IRAN,JAPAN,PERU,RUSSIA,TAIWAN,UNITED_STATES
408402400040200 BELGIUM,CZECH_REPUBLIC,IRAN,JAPAN,PERU,RUSSIA,TAIWAN
408402400040180 ARGENTINA,AUSTRALIA,CZECH_REPUBLIC,IRAN,JAPAN,PERU,RUSSIA,TAIWAN
Example of working query
select hex(cast(cs_countries as unsigned)) as v, cs_countries from countries
800000000000000 UKRAINE
1000000000000000 URUGUAY
2000000000000000 UNITED_STATES
4000000000000000 VENEZUELA
8000000000000000 SOUTH_AFRICA
FFFFFFFFFFFFFFFF (all values set ( same as on example 1)
2408402400040200 BELGIUM,CZECH_REPUBLIC,IRAN,JAPAN,PERU,RUSSIA,TAIWAN,UNITED_STATES
2408402400040180 ARGENTINA,AUSTRALIA,CZECH_REPUBLIC,IRAN,JAPAN,PERU,RUSSIA,TAIWAN,UNITED_STATES
408402400040200 BELGIUM,CZECH_REPUBLIC,IRAN,JAPAN,PERU,RUSSIA,TAIWAN
408402400040180 ARGENTINA,AUSTRALIA,CZECH_REPUBLIC,IRAN,JAPAN,PERU,RUSSIA,TAIWAN
How to repeat:
SQL code example :
MySQL
CREATE TABLE if not exists `countries` (
`cs_countries` set('UNKNOWN','EUROPE','NORTH_AM','SOUTH_AM','ASIA','OCEANIA','AFRICA','ARGENTINA','AUSTRALIA','BELGIUM','BULGARIA','BELARUS','BRAZIL','CANADA','CHILE','CHINA','COLOMBIA','COSTA_RICA','CZECH_REPUBLIC','GERMANY','DOMINICAN_REPUBLIC','ECUADOR','EGYPT','SPAIN','FRANCE','UNITED_KINGDOM','GREECE','GUATEMALA','HONG_KONG','CROATIA','HUNGARY','INDONESIA','INDIA','IRELAND','IRAN','ISRAEL','ITALY','JAPAN','KAZAKHSTAN','LITHUANIA','MEXICO','MALAYSIA','NORWAY','NETHERLANDS','NEW_ZEALAND','PAKISTAN','PERU','PHILIPPINES','POLAND','PORTUGAL','ROMANIA','RUSSIA','SAUDI_ARABIA','SLOVAKIA','SLOVENIA','SWEDEN','THAILAND','TURKEY','TAIWAN','UKRAINE','URUGUAY','UNITED_STATES','VENEZUELA','SOUTH_AFRICA') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
truncate table countries;
insert into countries values('UNKNOWN'),('EUROPE'),('NORTH_AM'),('SOUTH_AM'),('ASIA'),('OCEANIA'),('AFRICA'),('ARGENTINA'),('AUSTRALIA'),('BELGIUM'),('BULGARIA'),('BELARUS'),('BRAZIL'),('CANADA'),('CHILE'),('CHINA'),('COLOMBIA'),('COSTA_RICA'),('CZECH_REPUBLIC'),('GERMANY'),('DOMINICAN_REPUBLIC'),('ECUADOR'),('EGYPT'),('SPAIN'),('FRANCE'),('UNITED_KINGDOM'),('GREECE'),('GUATEMALA'),('HONG_KONG'),('CROATIA'),('HUNGARY'),('INDONESIA'),('INDIA'),('IRELAND'),('IRAN'),('ISRAEL'),('ITALY'),('JAPAN'),('KAZAKHSTAN'),('LITHUANIA'),('MEXICO'),('MALAYSIA'),('NORWAY'),('NETHERLANDS'),('NEW_ZEALAND'),('PAKISTAN'),('PERU'),('PHILIPPINES'),('POLAND'),('PORTUGAL'),('ROMANIA'),('RUSSIA'),('SAUDI_ARABIA'),('SLOVAKIA'),('SLOVENIA'),('SWEDEN'),('THAILAND'),('TURKEY'),('TAIWAN'),('UKRAINE'),('URUGUAY'),('UNITED_STATES'),('VENEZUELA'),('SOUTH_AFRICA');
insert into countries values('UNKNOWN,EUROPE,NORTH_AM,SOUTH_AM,ASIA,OCEANIA,AFRICA,ARGENTINA,AUSTRALIA,BELGIUM,BULGARIA,BELARUS,BRAZIL,CANADA,CHILE,CHINA,COLOMBIA,COSTA_RICA,CZECH_REPUBLIC,GERMANY,DOMINICAN_REPUBLIC,ECUADOR,EGYPT,SPAIN,FRANCE,UNITED_KINGDOM,GREECE,GUATEMALA,HONG_KONG,CROATIA,HUNGARY,INDONESIA,INDIA,IRELAND,IRAN,ISRAEL,ITALY,JAPAN,KAZAKHSTAN,LITHUANIA,MEXICO,MALAYSIA,NORWAY,NETHERLANDS,NEW_ZEALAND,PAKISTAN,PERU,PHILIPPINES,POLAND,PORTUGAL,ROMANIA,RUSSIA,SAUDI_ARABIA,SLOVAKIA,SLOVENIA,SWEDEN,THAILAND,TURKEY,TAIWAN,UKRAINE,URUGUAY,UNITED_STATES,VENEZUELA,SOUTH_AFRICA');
-- return same (incorrect) values for "select cs_countries+0 from countries"
insert into countries values('BELGIUM,CZECH_REPUBLIC,IRAN,JAPAN,PERU,RUSSIA,TAIWAN,UNITED_STATES');
insert into countries values('ARGENTINA,AUSTRALIA,CZECH_REPUBLIC,IRAN,JAPAN,PERU,RUSSIA,TAIWAN,UNITED_STATES');
-- return correct values for "select cs_countries+0 from countries" ( without UNITED_STATES == 0x2000000000000000 )
insert into countries values('BELGIUM,CZECH_REPUBLIC,IRAN,JAPAN,PERU,RUSSIA,TAIWAN');
insert into countries values('ARGENTINA,AUSTRALIA,CZECH_REPUBLIC,IRAN,JAPAN,PERU,RUSSIA,TAIWAN');
-- incorrect select ( from doc https://dev.mysql.com/doc/refman/5.5/en/set.html )
select hex(cs_countries+0) as v, cs_countries from countries;
-- also incorrect select
select hex(cast(cs_countries+0 as unsigned)) as v, cs_countries from countries;
-- correct select
select hex(cast(cs_countries as unsigned)) as v, cs_countries from countries;
Suggested fix:
To export mysql sets as bitfields, please use this select :
MySQL
mysql>select hex(cast(cs_countries as unsigned)) as v, cs_countries from countries
instead of select from documentation
mysql> SELECT set_col+0 FROM tbl_name;
