Bit type support for non-MyISAM tables.

1 files changed, 123 insertions, 0 deletions

diff --git a/mysql-test/t/type_bit_innodb.test b/mysql-test/t/type_bit_innodb.test
new file mode 100644
index 00000000000..6410b98fcd0
--- /dev/null
+++ b/mysql-test/t/type_bit_innodb.test
@@ -0,0 +1,123 @@
+--source include/have_innodb.inc
+#
+# testing of the BIT column type
+#
+
+select 0 + b'1';
+select 0 + b'0';
+select 0 + b'000001';
+select 0 + b'000011';
+select 0 + b'000101';
+select 0 + b'000000';
+select 0 + b'10000000';
+select 0 + b'11111111';
+select 0 + b'10000001';
+select 0 + b'1000000000000000';
+select 0 + b'1111111111111111';
+select 0 + b'1000000000000001';
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+--error 1074
+create table t1 (a bit(65)) engine=innodb;
+
+create table t1 (a bit(0)) engine=innodb;
+show create table t1;
+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;
+drop table t1;
+
+create table t1 (a bit) engine=innodb;
+insert into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001');
+select hex(a) from t1;
+--error 1062
+alter table t1 add unique (a);
+drop table t1;
+
+create table t1 (a bit(2)) engine=innodb;
+insert into t1 values (b'00'), (b'01'), (b'10'), (b'100');
+select a+0 from t1;
+alter table t1 add key (a);
+explain select a+0 from t1;
+select a+0 from t1;
+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;
+select a+0 from t1;
+explain select b+0 from t1;
+select b+0 from t1;
+explain select a+0, b+0 from t1;
+select a+0, b+0 from t1;
+explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
+select a+0, b+0 from t1 where a > 40 and b > 200 order by 1;
+explain select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
+select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
+set @@max_length_for_sort_data=0;
+select a+0, b+0 from t1 where a > 40 and a < 70 order by 2;
+select hex(min(a)) from t1;
+select hex(min(b)) from t1;
+select hex(min(a)), hex(max(a)), hex(min(b)), hex(max(b)) from t1;
+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;
+select hex(min(b)) from t1 where a = 4;
+select hex(min(c)) from t1 where a = 4 and b = 0;
+select hex(max(b)) from t1;
+select a+0, b+0, c+0 from t1 where a = 4 and b = 0 limit 2;
+select a+0, b+0, c+0 from t1 where a = 4 and b = 1;
+select a+0, b+0, c+0 from t1 where a = 4 and b = 1 and c=100;
+select a+0, b+0, c+0 from t1 order by b desc;
+select a+0, b+0, c+0 from t1 order by c;
+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;
+drop table t1;
+
+# Some magic numbers
+
+create table t1 (a bit(7), key(a)) engine=innodb;
+insert into t1 values (44), (57);
+select a+0 from t1;
+drop table t1;
+
+#
+# Test conversion to and from strings
+#
+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;
+select hex(concat(a)),hex(concat(b)) from t1;
+drop table t1;
+
+#
+# Bug #9571: problem with primary key creation
+#
+
+create table t1(a int, b bit not null) engine=innodb;
+alter table t1 add primary key (a);
+drop table t1;