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,t2;
38
create table t1 (a bit(65));
39
ERROR 42000: Display width out of range for column 'a' (max = 64)
40
create table t1 (a bit(0));
43
t1 CREATE TABLE `t1` (
44
`a` bit(1) DEFAULT NULL
45
) ENGINE=MyISAM DEFAULT CHARSET=latin1
47
create table t1 (a bit(64));
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);
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));
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));
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 27 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));
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));
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));
365
insert into t1 values (44), (57);
371
create table t1 (a bit(3), b bit(12));
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);
383
alter table t1 add primary key (a);
385
create table t1 (a bit(19), b bit(5));
386
insert into t1 values (1000, 10), (3, 8), (200, 6), (2303, 2), (12345, 4), (1, 0);
387
select a+0, b+0 from t1;
395
alter table t1 engine=heap;
396
select a+0, b+0 from t1;
404
alter table t1 add key(a, b);
405
select a+0, b+0 from t1;
413
alter table t1 engine=myisam;
414
select a+0, b+0 from t1;
422
create table t2 engine=heap select * from t1;
423
select a+0, b+0 from t2;
432
create table t1 select * from t2;
433
select a+0, b+0 from t1;
442
create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1),
443
g bit(1) NOT NULL default 1, h char(1) default 'a');
444
insert into t1 set a=1;
445
select hex(g), h from t1;
449
create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1),
450
g bit(1) NOT NULL default 1);
451
insert into t1 set a=1;
452
select hex(g) from t1;
456
create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1),
457
h char(1) default 'a') engine=myisam;
458
insert into t1 set a=1;
463
create table t1 (a bit(8)) engine=heap;
464
insert into t1 values ('1111100000');
466
Warning 1264 Out of range value for column 'a' at row 1
471
create table t1 (a bit(7));
472
insert into t1 values (120), (0), (111);
473
select a+0 from t1 union select a+0 from t1;
478
select a+0 from t1 union select NULL;
484
select NULL union select a+0 from t1;
490
create table t2 select a from t1 union select a from t1;
496
show create table t2;
498
t2 CREATE TABLE `t2` (
499
`a` bit(7) DEFAULT NULL
500
) ENGINE=MyISAM DEFAULT CHARSET=latin1
502
create table t1 (id1 int(11), b1 bit(1));
503
create table t2 (id2 int(11), b2 bit(1));
504
insert into t1 values (1, 1), (2, 0), (3, 1);
505
insert into t2 values (2, 1), (3, 0), (4, 0);
506
create algorithm=undefined view v1 as
507
select b1+0, b2+0 from t1, t2 where id1 = id2 and b1 = 0
509
select b1+0, b2+0 from t1, t2 where id1 = id2 and b2 = 1;
515
create table t1(a bit(4));
516
insert into t1(a) values (1), (2), (5), (4), (3);
517
insert into t1 select * from t1;
531
create table t1 (a1 int(11), b1 bit(2));
532
create table t2 (a2 int(11), b2 bit(2));
533
insert into t1 values (1, 1), (2, 0), (3, 1), (4, 2);
534
insert into t2 values (2, 1), (3, 0), (4, 1), (5, 2);
535
select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2;
540
select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2 order by a1;
545
select a1, a2, b1+0, b2+0 from t1 join t2 on b1 = b2;
553
select sum(a1), b1+0, b2+0 from t1 join t2 on b1 = b2 group by b1 order by 1;
558
select 1 from t1 join t2 on b1 = b2 group by b1 order by 1;
563
select b1+0,sum(b1), sum(b2) from t1 join t2 on b1 = b2 group by b1 order by 1;
569
create table t1 (a bit(7));
570
insert into t1 values (0x60);
572
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
573
def test t1 t1 a a 16 7 1 Y 32 0 63
577
create table bug15583(b BIT(8), n INT);
578
insert into bug15583 values(128, 128);
579
insert into bug15583 values(null, null);
580
insert into bug15583 values(0, 0);
581
insert into bug15583 values(255, 255);
582
select hex(b), bin(b), oct(b), hex(n), bin(n), oct(n) from bug15583;
583
hex(b) bin(b) oct(b) hex(n) bin(n) oct(n)
584
80 10000000 200 80 10000000 200
585
NULL NULL NULL NULL NULL NULL
587
FF 11111111 377 FF 11111111 377
588
select hex(b)=hex(n) as should_be_onetrue, bin(b)=bin(n) as should_be_onetrue, oct(b)=oct(n) as should_be_onetrue from bug15583;
589
should_be_onetrue should_be_onetrue should_be_onetrue
594
select hex(b + 0), bin(b + 0), oct(b + 0), hex(n), bin(n), oct(n) from bug15583;
595
hex(b + 0) bin(b + 0) oct(b + 0) hex(n) bin(n) oct(n)
596
80 10000000 200 80 10000000 200
597
NULL NULL NULL NULL NULL NULL
599
FF 11111111 377 FF 11111111 377
600
select conv(b, 10, 2), conv(b + 0, 10, 2) from bug15583;
601
conv(b, 10, 2) conv(b + 0, 10, 2)
607
create table t1(a bit(1), b smallint unsigned);
608
insert into t1 (b, a) values ('2', '1');
610
Warning 1264 Out of range value for column 'a' at row 1
611
select hex(a), b from t1;
615
create table t1(bit_field bit(2), int_field int, key a(bit_field));
616
insert into t1 values (1,2);
617
handler t1 open as t1;
618
handler t1 read a=(1);
623
CREATE TABLE t1 (b BIT(2), a VARCHAR(5));
624
INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z");
625
SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b;
626
b+0 COUNT(DISTINCT a)
631
CREATE TABLE t1 (a CHAR(5), b BIT(2));
632
INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z");
633
SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b;
634
b+0 COUNT(DISTINCT a)
639
CREATE TABLE t1 (a INT, b BIT(2));
640
INSERT INTO t1 (b, a) VALUES (1, 1), (3, 2), (0, 3), (3, 4);
641
SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b;
642
b+0 COUNT(DISTINCT a)
647
CREATE TABLE t1 (b BIT);
648
INSERT INTO t1 (b) VALUES (1), (0);
649
SELECT DISTINCT b FROM t1;
650
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
651
def test t1 t1 b b 16 1 1 Y 32 0 63
655
SELECT b FROM t1 GROUP BY b;
656
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
657
def test t1 t1 b b 16 1 1 Y 32 0 63
662
CREATE TABLE t1 (a int, b bit(2));
663
INSERT INTO t1 VALUES (3, 2), (2, 3), (2, 0), (3, 2), (3, 1);
664
SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
669
create table t2 (a int, b bit(2), c char(10));
670
INSERT INTO t2 VALUES (3, 2, 'two'), (2, 3, 'three'), (2, 0, 'zero'),
671
(3, 2, 'two'), (3, 1, 'one');
672
SELECT COUNT(DISTINCT b,c) FROM t2 GROUP BY a;
677
CREATE TABLE t1(a BIT(13), KEY(a));
678
INSERT INTO t1(a) VALUES
679
(65535),(65525),(65535),(65535),(65535),(65535),(65535),(65535),(65535),(65535);
680
EXPLAIN SELECT 1 FROM t1 GROUP BY a;
681
id select_type table type possible_keys key key_len ref rows Extra
682
1 SIMPLE t1 range NULL a 3 NULL 6 Using index for group-by
683
SELECT 1 FROM t1 GROUP BY a;
688
create table t1(a bit(7));
689
insert into t1 values(0x40);
690
alter table t1 modify column a bit(8);
691
select hex(a) from t1;
694
insert into t1 values(0x80);
695
select hex(a) from t1;
699
create index a on t1(a);
700
insert into t1 values(0x81);
701
select hex(a) from t1;
706
show create table t1;
708
t1 CREATE TABLE `t1` (
709
`a` bit(8) DEFAULT NULL,
711
) ENGINE=MyISAM DEFAULT CHARSET=latin1