19
select 0 + b'10000000';
22
select 0 + b'11111111';
25
select 0 + b'10000001';
28
select 0 + b'1000000000000000';
29
0 + b'1000000000000000'
31
select 0 + b'1111111111111111';
32
0 + b'1111111111111111'
34
select 0 + b'1000000000000001';
35
0 + b'1000000000000001'
37
drop table if exists t1;
38
create table t1 (a bit(65)) engine=innodb;
39
ERROR 42000: Display width out of range for column 'a' (max = 64)
40
create table t1 (a bit(0)) engine=innodb;
43
t1 CREATE TABLE `t1` (
44
`a` bit(1) DEFAULT NULL
45
) ENGINE=InnoDB DEFAULT CHARSET=latin1
47
create table t1 (a bit(64)) engine=innodb;
49
(b'1111111111111111111111111111111111111111111111111111111111111111'),
50
(b'1000000000000000000000000000000000000000000000000000000000000000'),
51
(b'0000000000000000000000000000000000000000000000000000000000000001'),
52
(b'1010101010101010101010101010101010101010101010101010101010101010'),
53
(b'0101010101010101010101010101010101010101010101010101010101010101');
54
select hex(a) from t1;
62
create table t1 (a bit) engine=innodb;
63
insert into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001');
65
Warning 1264 Out of range value for column 'a' at row 4
66
select hex(a) from t1;
73
alter table t1 add unique (a);
74
ERROR 23000: Duplicate entry '' for key 'a'
76
create table t1 (a bit(2)) engine=innodb;
77
insert into t1 values (b'00'), (b'01'), (b'10'), (b'100');
79
Warning 1264 Out of range value for column 'a' at row 4
86
alter table t1 add key (a);
87
explain select a+0 from t1;
88
id select_type table type possible_keys key key_len ref rows Extra
89
1 SIMPLE t1 index NULL a 2 NULL 4 Using index
97
create table t1 (a bit(7), b bit(9), key(a, b)) engine=innodb;
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);
105
explain select a+0 from t1;
106
id select_type table type possible_keys key key_len ref rows Extra
107
1 SIMPLE t1 index NULL a 5 NULL 38 Using index
148
explain select b+0 from t1;
149
id select_type table type possible_keys key key_len ref rows Extra
150
1 SIMPLE t1 index NULL a 5 NULL 38 Using index
191
explain select a+0, b+0 from t1;
192
id select_type table type possible_keys key key_len ref rows Extra
193
1 SIMPLE t1 index NULL a 5 NULL 38 Using index
194
select a+0, b+0 from t1;
234
explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
235
id select_type table type possible_keys key key_len ref rows Extra
236
1 SIMPLE t1 range a a 2 NULL 19 Using where; Using index; Using filesort
237
select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
257
explain select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
258
id select_type table type possible_keys key key_len ref rows Extra
259
1 SIMPLE t1 range a a 2 NULL 8 Using where; Using index; Using filesort
260
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
271
set @@max_length_for_sort_data=0;
273
Warning 1292 Truncated incorrect max_length_for_sort_data value: '0'
274
select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
285
select hex(min(a)) from t1;
288
select hex(min(b)) from t1;
291
select hex(min(a)), hex(max(a)), hex(min(b)), hex(max(b)) from t1;
292
hex(min(a)) hex(max(a)) hex(min(b)) hex(max(b))
295
create table t1 (a int not null, b bit, c bit(9), key(a, b, c)) engine=innodb;
296
insert 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);
299
select a+0, b+0, c+0 from t1;
311
select hex(min(b)) from t1 where a = 4;
314
select hex(min(c)) from t1 where a = 4 and b = 0;
317
select hex(max(b)) from t1;
320
select a+0, b+0, c+0 from t1 where a = 4 and b = 0 limit 2;
324
select a+0, b+0, c+0 from t1 where a = 4 and b = 1;
327
select a+0, b+0, c+0 from t1 where a = 4 and b = 1 and c=100;
330
select a+0, b+0, c+0 from t1 order by b desc;
342
select a+0, b+0, c+0 from t1 order by c;
355
create table t1(a bit(2), b bit(2)) engine=innodb;
356
insert into t1 (a) values (0x01), (0x03), (0x02);
357
update t1 set b= concat(a);
358
select a+0, b+0 from t1;
364
create table t1 (a bit(7), key(a)) engine=innodb;
365
insert into t1 values (44), (57);
371
create table t1 (a bit(3), b bit(12)) engine=innodb;
372
insert into t1 values (7,(1<<12)-2), (0x01,0x01ff);
373
select hex(a),hex(b) from t1;
377
select hex(concat(a)),hex(concat(b)) from t1;
378
hex(concat(a)) hex(concat(b))
382
create table t1(a int, b bit not null) engine=innodb;
383
alter table t1 add primary key (a);
385
create table t1 (a bit, b bit(10)) engine=innodb;
386
show create table t1;
388
t1 CREATE TABLE `t1` (
389
`a` bit(1) DEFAULT NULL,
390
`b` bit(10) DEFAULT NULL
391
) ENGINE=InnoDB DEFAULT CHARSET=latin1
392
alter table t1 engine=heap;
393
show create table t1;
395
t1 CREATE TABLE `t1` (
396
`a` bit(1) DEFAULT NULL,
397
`b` bit(10) DEFAULT NULL
398
) ENGINE=MEMORY DEFAULT CHARSET=latin1
399
alter table t1 engine=innodb;
400
show create table t1;
402
t1 CREATE TABLE `t1` (
403
`a` bit(1) DEFAULT NULL,
404
`b` bit(10) DEFAULT NULL
405
) ENGINE=InnoDB DEFAULT CHARSET=latin1
407
create table t1 (a bit(7)) engine=innodb;
408
insert into t1 values (0x60);
410
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
411
def test t1 t1 a a 16 7 1 Y 32 0 63