1select 0 + b'1'; 20 + b'1' 31 4select 0 + b'0'; 50 + b'0' 60 7select 0 + b'000001'; 80 + b'000001' 91 10select 0 + b'000011'; 110 + b'000011' 123 13select 0 + b'000101'; 140 + b'000101' 155 16select 0 + b'000000'; 170 + b'000000' 180 19select 0 + b'10000000'; 200 + b'10000000' 21128 22select 0 + b'11111111'; 230 + b'11111111' 24255 25select 0 + b'10000001'; 260 + b'10000001' 27129 28select 0 + b'1000000000000000'; 290 + b'1000000000000000' 3032768 31select 0 + b'1111111111111111'; 320 + b'1111111111111111' 3365535 34select 0 + b'1000000000000001'; 350 + b'1000000000000001' 3632769 37drop table if exists t1; 38create table t1 (a bit(65)) engine=innodb; 39ERROR 42000: Display width out of range for 'a' (max = 64) 40create table t1 (a bit(0)) engine=innodb; 41show create table t1; 42Table Create Table 43t1 CREATE TABLE `t1` ( 44 `a` bit(1) DEFAULT NULL 45) ENGINE=InnoDB DEFAULT CHARSET=latin1 46drop table t1; 47create table t1 (a bit(64)) engine=innodb; 48insert into t1 values 49(b'1111111111111111111111111111111111111111111111111111111111111111'), 50(b'1000000000000000000000000000000000000000000000000000000000000000'), 51(b'0000000000000000000000000000000000000000000000000000000000000001'), 52(b'1010101010101010101010101010101010101010101010101010101010101010'), 53(b'0101010101010101010101010101010101010101010101010101010101010101'); 54select hex(a) from t1; 55hex(a) 56FFFFFFFFFFFFFFFF 578000000000000000 581 59AAAAAAAAAAAAAAAA 605555555555555555 61drop table t1; 62create table t1 (a bit) engine=innodb; 63insert ignore into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001'); 64Warnings: 65Warning 1264 Out of range value for column 'a' at row 4 66select hex(a) from t1; 67hex(a) 680 691 700 711 721 73alter table t1 add unique (a); 74ERROR 23000: Duplicate entry '' for key 'a' 75drop table t1; 76create table t1 (a bit(2)) engine=innodb; 77insert ignore into t1 values (b'00'), (b'01'), (b'10'), (b'100'); 78Warnings: 79Warning 1264 Out of range value for column 'a' at row 4 80select a+0 from t1; 81a+0 820 831 842 853 86alter table t1 add key (a); 87explain select a+0 from t1; 88id select_type table type possible_keys key key_len ref rows Extra 891 SIMPLE t1 index NULL a 2 NULL 4 Using index 90select a+0 from t1; 91a+0 920 931 942 953 96drop table t1; 97create table t1 (a bit(7), b bit(9), key(a, b)) engine=innodb; 98insert into t1 values 99(94, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122, 118), (0, 177), 100(75, 42), (108, 67), (79, 349), (59, 188), (68, 206), (49, 345), (118, 380), 101(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36), 102(116, 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499), 103(30, 83), (5, 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403), 104(44, 307), (68, 454), (57, 135); 105explain select a+0 from t1; 106id select_type table type possible_keys key key_len ref rows Extra 1071 SIMPLE t1 index NULL a 5 NULL 38 Using index 108select a+0 from t1; 109a+0 1100 1114 1125 1139 11423 11524 11628 11729 11830 11931 12034 12144 12249 12356 12457 12559 12660 12761 12868 12968 13075 13177 13278 13379 13487 13588 13694 13794 138104 139106 140108 141111 142116 143118 144119 145122 146123 147127 148explain select b+0 from t1; 149id select_type table type possible_keys key key_len ref rows Extra 1501 SIMPLE t1 index NULL a 5 NULL 38 Using index 151select b+0 from t1; 152b+0 153177 154245 155178 156363 15736 158398 159499 160399 16183 162438 163202 164307 165345 166379 167135 168188 169343 170152 171206 172454 17342 174133 175123 176349 177351 178411 17946 180468 181280 182446 18367 184368 185390 186380 187368 188118 189411 190403 191explain select a+0, b+0 from t1; 192id select_type table type possible_keys key key_len ref rows Extra 1931 SIMPLE t1 index NULL a 5 NULL 38 Using index 194select a+0, b+0 from t1; 195a+0 b+0 1960 177 1974 245 1985 178 1999 363 20023 36 20124 398 20228 499 20329 399 20430 83 20531 438 20634 202 20744 307 20849 345 20956 379 21057 135 21159 188 21260 343 21361 152 21468 206 21568 454 21675 42 21777 133 21878 123 21979 349 22087 351 22188 411 22294 46 22394 468 224104 280 225106 446 226108 67 227111 368 228116 390 229118 380 230119 368 231122 118 232123 411 233127 403 234explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1; 235id select_type table type possible_keys key key_len ref rows Extra 2361 SIMPLE t1 range a a 2 NULL # Using where; Using index; Using filesort 237select a+0, b+0 from t1 where a > 40 and b > 200 order by 1; 238a+0 b+0 23944 307 24049 345 24156 379 24260 343 24368 206 24468 454 24579 349 24687 351 24788 411 24894 468 249104 280 250106 446 251111 368 252116 390 253118 380 254119 368 255123 411 256127 403 257explain select a+0, b+0 from t1 where a > 40 and a < 70 order by 2; 258id select_type table type possible_keys key key_len ref rows Extra 2591 SIMPLE t1 range a a 2 NULL 9 Using where; Using index; Using filesort 260select a+0, b+0 from t1 where a > 40 and a < 70 order by 2; 261a+0 b+0 26257 135 26361 152 26459 188 26568 206 26644 307 26760 343 26849 345 26956 379 27068 454 271set @@max_length_for_sort_data=0; 272Warnings: 273Warning 1292 Truncated incorrect max_length_for_sort_data value: '0' 274select a+0, b+0 from t1 where a > 40 and a < 70 order by 2; 275a+0 b+0 27657 135 27761 152 27859 188 27968 206 28044 307 28160 343 28249 345 28356 379 28468 454 285select hex(min(a)) from t1; 286hex(min(a)) 2870 288select hex(min(b)) from t1; 289hex(min(b)) 29024 291select hex(min(a)), hex(max(a)), hex(min(b)), hex(max(b)) from t1; 292hex(min(a)) hex(max(a)) hex(min(b)) hex(max(b)) 2930 7F 24 1F3 294drop table t1; 295create table t1 (a int not null, b bit, c bit(9), key(a, b, c)) engine=innodb; 296insert into t1 values 297(4, NULL, 1), (4, 0, 3), (2, 1, 4), (1, 1, 100), (4, 0, 23), (4, 0, 54), 298(56, 0, 22), (4, 1, 100), (23, 0, 1), (4, 0, 34); 299select a+0, b+0, c+0 from t1; 300a+0 b+0 c+0 3011 1 100 3022 1 4 3034 NULL 1 3044 0 3 3054 0 23 3064 0 34 3074 0 54 3084 1 100 30923 0 1 31056 0 22 311select hex(min(b)) from t1 where a = 4; 312hex(min(b)) 3130 314select hex(min(c)) from t1 where a = 4 and b = 0; 315hex(min(c)) 3163 317select hex(max(b)) from t1; 318hex(max(b)) 3191 320select a+0, b+0, c+0 from t1 where a = 4 and b = 0 limit 2; 321a+0 b+0 c+0 3224 0 3 3234 0 23 324select a+0, b+0, c+0 from t1 where a = 4 and b = 1; 325a+0 b+0 c+0 3264 1 100 327select a+0, b+0, c+0 from t1 where a = 4 and b = 1 and c=100; 328a+0 b+0 c+0 3294 1 100 330select a+0, b+0, c+0 from t1 order by b desc; 331a+0 b+0 c+0 3322 1 4 3331 1 100 3344 1 100 3354 0 3 3364 0 23 3374 0 54 33856 0 22 33923 0 1 3404 0 34 3414 NULL 1 342select a+0, b+0, c+0 from t1 order by c; 343a+0 b+0 c+0 3444 NULL 1 34523 0 1 3464 0 3 3472 1 4 34856 0 22 3494 0 23 3504 0 34 3514 0 54 3521 1 100 3534 1 100 354drop table t1; 355create table t1(a bit(2), b bit(2)) engine=innodb; 356insert into t1 (a) values (0x01), (0x03), (0x02); 357update t1 set b= concat(a); 358select a+0, b+0 from t1; 359a+0 b+0 3601 1 3613 3 3622 2 363drop table t1; 364create table t1 (a bit(7), key(a)) engine=innodb; 365insert into t1 values (44), (57); 366select a+0 from t1; 367a+0 36844 36957 370drop table t1; 371create table t1 (a bit(3), b bit(12)) engine=innodb; 372insert into t1 values (7,(1<<12)-2), (0x01,0x01ff); 373select hex(a),hex(b) from t1; 374hex(a) hex(b) 3757 FFE 3761 1FF 377select hex(concat(a)),hex(concat(b)) from t1; 378hex(concat(a)) hex(concat(b)) 37907 0FFE 38001 01FF 381drop table t1; 382create table t1(a int, b bit not null) engine=innodb; 383alter table t1 add primary key (a); 384drop table t1; 385create table t1 (a bit, b bit(10)) engine=innodb; 386show create table t1; 387Table Create Table 388t1 CREATE TABLE `t1` ( 389 `a` bit(1) DEFAULT NULL, 390 `b` bit(10) DEFAULT NULL 391) ENGINE=InnoDB DEFAULT CHARSET=latin1 392alter table t1 engine=heap; 393show create table t1; 394Table Create Table 395t1 CREATE TABLE `t1` ( 396 `a` bit(1) DEFAULT NULL, 397 `b` bit(10) DEFAULT NULL 398) ENGINE=MEMORY DEFAULT CHARSET=latin1 399alter table t1 engine=innodb; 400show create table t1; 401Table Create Table 402t1 CREATE TABLE `t1` ( 403 `a` bit(1) DEFAULT NULL, 404 `b` bit(10) DEFAULT NULL 405) ENGINE=InnoDB DEFAULT CHARSET=latin1 406drop table t1; 407create table t1 (a bit(7)) engine=innodb; 408insert into t1 values (0x60); 409select * from t1; 410Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 411def test t1 t1 a a 16 7 1 Y 32 0 63 412a 413` 414drop table t1; 415End of 5.0 tests 416create table t1(f1 bit(2) not null default b'10',f2 bit(14) not null default b'11110000111100'); 417insert into t1 (f1) values (default); 418insert into t1 values (b'',b''),('',''); 419select hex(f1), hex(f2) from t1; 420hex(f1) hex(f2) 4212 3C3C 4220 0 4230 0 424drop table t1; 425