Edit File - File Manager

Edit File: type_bit_innodb.result

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
drop table if exists t1;
create table t1 (a bit(65)) engine=innodb;
ERROR 42000: Display width out of range for column '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
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 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 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	27	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	8	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
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
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
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

Back to File Manager