SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent; SET GLOBAL innodb_stats_persistent=0; select 0 + b'1'; 0 + b'1' 1 select 0 + b'0'; 0 + b'0' 0 select 0 + b'000001'; 0 + b'000001' 1 select 0 + b'000011'; 0 + b'000011' 3 select 0 + b'000101'; 0 + b'000101' 5 select 0 + b'000000'; 0 + b'000000' 0 select 0 + b'10000000'; 0 + b'10000000' 128 select 0 + b'11111111'; 0 + b'11111111' 255 select 0 + b'10000001'; 0 + b'10000001' 129 select 0 + b'1000000000000000'; 0 + b'1000000000000000' 32768 select 0 + b'1111111111111111'; 0 + b'1111111111111111' 65535 select 0 + b'1000000000000001'; 0 + b'1000000000000001' 32769 create table t1 (a bit(65)) engine=innodb; ERROR 42000: Display width out of range for 'a' (max = 64) create table t1 (a bit(0)) engine=innodb; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bit(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; create table t1 (a bit(64)) engine=innodb; insert into t1 values (b'1111111111111111111111111111111111111111111111111111111111111111'), (b'1000000000000000000000000000000000000000000000000000000000000000'), (b'0000000000000000000000000000000000000000000000000000000000000001'), (b'1010101010101010101010101010101010101010101010101010101010101010'), (b'0101010101010101010101010101010101010101010101010101010101010101'); select hex(a) from t1; hex(a) FFFFFFFFFFFFFFFF 8000000000000000 1 AAAAAAAAAAAAAAAA 5555555555555555 drop table t1; create table t1 (a bit) engine=innodb; insert ignore into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001'); Warnings: Warning 1264 Out of range value for column 'a' at row 4 select hex(a) from t1; hex(a) 0 1 0 1 1 alter table t1 add unique (a); ERROR 23000: Duplicate entry '' for key 'a' drop table t1; create table t1 (a bit(2)) engine=innodb; insert ignore into t1 values (b'00'), (b'01'), (b'10'), (b'100'); Warnings: Warning 1264 Out of range value for column 'a' at row 4 select a+0 from t1; a+0 0 1 2 3 alter table t1 add key (a); explain select a+0 from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 2 NULL 4 Using index select a+0 from t1; a+0 0 1 2 3 drop table t1; create table t1 (a bit(7), b bit(9), key(a, b)) engine=innodb; insert into t1 values (94, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122, 118), (0, 177), (75, 42), (108, 67), (79, 349), (59, 188), (68, 206), (49, 345), (118, 380), (111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36), (116, 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499), (30, 83), (5, 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403), (44, 307), (68, 454), (57, 135); explain select a+0 from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 38 Using index select a+0 from t1; a+0 0 4 5 9 23 24 28 29 30 31 34 44 49 56 57 59 60 61 68 68 75 77 78 79 87 88 94 94 104 106 108 111 116 118 119 122 123 127 explain select b+0 from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 38 Using index select b+0 from t1; b+0 177 245 178 363 36 398 499 399 83 438 202 307 345 379 135 188 343 152 206 454 42 133 123 349 351 411 46 468 280 446 67 368 390 380 368 118 411 403 explain select a+0, b+0 from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 38 Using index select a+0, b+0 from t1; a+0 b+0 0 177 4 245 5 178 9 363 23 36 24 398 28 499 29 399 30 83 31 438 34 202 44 307 49 345 56 379 57 135 59 188 60 343 61 152 68 206 68 454 75 42 77 133 78 123 79 349 87 351 88 411 94 46 94 468 104 280 106 446 108 67 111 368 116 390 118 380 119 368 122 118 123 411 127 403 explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 2 NULL # Using where; Using index; Using filesort select a+0, b+0 from t1 where a > 40 and b > 200 order by 1; a+0 b+0 44 307 49 345 56 379 60 343 68 206 68 454 79 349 87 351 88 411 94 468 104 280 106 446 111 368 116 390 118 380 119 368 123 411 127 403 explain select a+0, b+0 from t1 where a > 40 and a < 70 order by 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 2 NULL 9 Using where; Using index; Using filesort select a+0, b+0 from t1 where a > 40 and a < 70 order by 2; a+0 b+0 57 135 61 152 59 188 68 206 44 307 60 343 49 345 56 379 68 454 set @@max_length_for_sort_data=0; Warnings: Warning 1292 Truncated incorrect max_length_for_sort_data value: '0' select a+0, b+0 from t1 where a > 40 and a < 70 order by 2; a+0 b+0 57 135 61 152 59 188 68 206 44 307 60 343 49 345 56 379 68 454 select hex(min(a)) from t1; hex(min(a)) 0 select hex(min(b)) from t1; hex(min(b)) 24 select hex(min(a)), hex(max(a)), hex(min(b)), hex(max(b)) from t1; hex(min(a)) hex(max(a)) hex(min(b)) hex(max(b)) 0 7F 24 1F3 drop table t1; create table t1 (a int not null, b bit, c bit(9), key(a, b, c)) engine=innodb; insert into t1 values (4, NULL, 1), (4, 0, 3), (2, 1, 4), (1, 1, 100), (4, 0, 23), (4, 0, 54), (56, 0, 22), (4, 1, 100), (23, 0, 1), (4, 0, 34); select a+0, b+0, c+0 from t1; a+0 b+0 c+0 1 1 100 2 1 4 4 NULL 1 4 0 3 4 0 23 4 0 34 4 0 54 4 1 100 23 0 1 56 0 22 select hex(min(b)) from t1 where a = 4; hex(min(b)) 0 select hex(min(c)) from t1 where a = 4 and b = 0; hex(min(c)) 3 select hex(max(b)) from t1; hex(max(b)) 1 select a+0, b+0, c+0 from t1 where a = 4 and b = 0 limit 2; a+0 b+0 c+0 4 0 3 4 0 23 select a+0, b+0, c+0 from t1 where a = 4 and b = 1; a+0 b+0 c+0 4 1 100 select a+0, b+0, c+0 from t1 where a = 4 and b = 1 and c=100; a+0 b+0 c+0 4 1 100 select a+0, b+0, c+0 from t1 order by b desc; a+0 b+0 c+0 2 1 4 1 1 100 4 1 100 4 0 3 4 0 23 4 0 54 56 0 22 23 0 1 4 0 34 4 NULL 1 select a+0, b+0, c+0 from t1 order by c; a+0 b+0 c+0 4 NULL 1 23 0 1 4 0 3 2 1 4 56 0 22 4 0 23 4 0 34 4 0 54 1 1 100 4 1 100 drop table t1; create table t1(a bit(2), b bit(2)) engine=innodb; insert into t1 (a) values (0x01), (0x03), (0x02); update t1 set b= concat(a); select a+0, b+0 from t1; a+0 b+0 1 1 3 3 2 2 drop table t1; create table t1 (a bit(7), key(a)) engine=innodb; insert into t1 values (44), (57); select a+0 from t1; a+0 44 57 drop table t1; create table t1 (a bit(3), b bit(12)) engine=innodb; insert into t1 values (7,(1<<12)-2), (0x01,0x01ff); select hex(a),hex(b) from t1; hex(a) hex(b) 7 FFE 1 1FF select hex(concat(a)),hex(concat(b)) from t1; hex(concat(a)) hex(concat(b)) 07 0FFE 01 01FF drop table t1; create table t1(a int, b bit not null) engine=innodb; alter table t1 add primary key (a); drop table t1; create table t1 (a bit, b bit(10)) engine=innodb; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bit(1) DEFAULT NULL, `b` bit(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci alter table t1 engine=heap; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bit(1) DEFAULT NULL, `b` bit(10) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci alter table t1 engine=innodb; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bit(1) DEFAULT NULL, `b` bit(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; create table t1 (a bit(7)) engine=innodb; insert into t1 values (0x60); select * from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 a a 16 7 1 Y 32 0 63 a ` drop table t1; End of 5.0 tests create table t1(f1 bit(2) not null default b'10',f2 bit(14) not null default b'11110000111100'); insert into t1 (f1) values (default); insert into t1 values (b'',b''),('',''); select hex(f1), hex(f2) from t1; hex(f1) hex(f2) 2 3C3C 0 0 0 0 drop table t1; SET GLOBAL innodb_stats_persistent=@save_stats_persistent;