1# 2# testing of the BIT column type 3# 4 5select 0 + b'1'; 6select 0 + b'0'; 7select 0 + b'000001'; 8select 0 + b'000011'; 9select 0 + b'000101'; 10select 0 + b'000000'; 11select 0 + b'10000000'; 12select 0 + b'11111111'; 13select 0 + b'10000001'; 14select 0 + b'1000000000000000'; 15select 0 + b'1111111111111111'; 16select 0 + b'1000000000000001'; 17 18--disable_warnings 19drop table if exists t1,t2; 20--enable_warnings 21 22--error 1439 23create table t1 (a bit(65)); 24 25create table t1 (a bit(0)); 26show create table t1; 27drop table t1; 28 29create table t1 (a bit(64)); 30insert into t1 values 31(b'1111111111111111111111111111111111111111111111111111111111111111'), 32(b'1000000000000000000000000000000000000000000000000000000000000000'), 33(b'0000000000000000000000000000000000000000000000000000000000000001'), 34(b'1010101010101010101010101010101010101010101010101010101010101010'), 35(b'0101010101010101010101010101010101010101010101010101010101010101'); 36select hex(a) from t1; 37drop table t1; 38 39create table t1 (a bit); 40insert into t1 values (b'0'), (b'1'), (b'000'), (b'100'), (b'001'); 41select hex(a) from t1; 42--error ER_DUP_ENTRY 43alter table t1 add unique (a); 44drop table t1; 45 46create table t1 (a bit(2)); 47insert into t1 values (b'00'), (b'01'), (b'10'), (b'100'); 48select a+0 from t1; 49alter table t1 add key (a); 50explain select a+0 from t1; 51select a+0 from t1; 52drop table t1; 53 54create table t1 (a bit(7), b bit(9), key(a, b)); 55insert into t1 values 56(94, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122, 118), (0, 177), 57(75, 42), (108, 67), (79, 349), (59, 188), (68, 206), (49, 345), (118, 380), 58(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36), 59(116, 390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499), 60(30, 83), (5, 178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403), 61(44, 307), (68, 454), (57, 135); 62explain select a+0 from t1; 63select a+0 from t1; 64explain select b+0 from t1; 65select b+0 from t1; 66explain select a+0, b+0 from t1; 67select a+0, b+0 from t1; 68explain select a+0, b+0 from t1 where a > 40 and b > 200 order by 1; 69select a+0, b+0 from t1 where a > 40 and b > 200 order by 1; 70explain select a+0, b+0 from t1 where a > 40 and a < 70 order by 2; 71select a+0, b+0 from t1 where a > 40 and a < 70 order by 2; 72set @@max_length_for_sort_data=0; 73select a+0, b+0 from t1 where a > 40 and a < 70 order by 2; 74select hex(min(a)) from t1; 75select hex(min(b)) from t1; 76select hex(min(a)), hex(max(a)), hex(min(b)), hex(max(b)) from t1; 77drop table t1; 78 79create table t1 (a int not null, b bit, c bit(9), key(a, b, c)); 80insert into t1 values 81(4, NULL, 1), (4, 0, 3), (2, 1, 4), (1, 1, 100), (4, 0, 23), (4, 0, 54), 82(56, 0, 22), (4, 1, 100), (23, 0, 1), (4, 0, 34); 83select a+0, b+0, c+0 from t1; 84select hex(min(b)) from t1 where a = 4; 85select hex(min(c)) from t1 where a = 4 and b = 0; 86select hex(max(b)) from t1; 87select a+0, b+0, c+0 from t1 where a = 4 and b = 0 limit 2; 88select a+0, b+0, c+0 from t1 where a = 4 and b = 1; 89select a+0, b+0, c+0 from t1 where a = 4 and b = 1 and c=100; 90select a+0, b+0, c+0 from t1 order by b desc; 91select a+0, b+0, c+0 from t1 order by c; 92drop table t1; 93 94create table t1(a bit(2), b bit(2)); 95insert into t1 (a) values (0x01), (0x03), (0x02); 96update t1 set b= concat(a); 97select a+0, b+0 from t1; 98drop table t1; 99 100# Some magic numbers 101 102create table t1 (a bit(7), key(a)); 103insert into t1 values (44), (57); 104select a+0 from t1; 105drop table t1; 106 107# 108# Test conversion to and from strings 109# 110create table t1 (a bit(3), b bit(12)); 111insert into t1 values (7,(1<<12)-2), (0x01,0x01ff); 112select hex(a),hex(b) from t1; 113select hex(concat(a)),hex(concat(b)) from t1; 114drop table t1; 115 116# 117# Bug #9571: problem with primary key creation 118# 119 120create table t1(a int, b bit not null); 121alter table t1 add primary key (a); 122drop table t1; 123 124# 125# myisam <-> heap 126# 127 128create table t1 (a bit(19), b bit(5)); 129insert into t1 values (1000, 10), (3, 8), (200, 6), (2303, 2), (12345, 4), (1, 0); 130select a+0, b+0 from t1; 131alter table t1 engine=heap; 132select a+0, b+0 from t1; 133alter table t1 add key(a, b); 134select a+0, b+0 from t1; 135alter table t1 engine=myisam; 136select a+0, b+0 from t1; 137create table t2 engine=heap select * from t1; 138select a+0, b+0 from t2; 139drop table t1; 140create table t1 select * from t2; 141select a+0, b+0 from t1; 142drop table t1, t2; 143 144# 145# Bug #10179: problem with NULLs and default values 146# 147 148create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1), 149 g bit(1) NOT NULL default 1, h char(1) default 'a'); 150insert into t1 set a=1; 151select hex(g), h from t1; 152drop table t1; 153 154create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1), 155 g bit(1) NOT NULL default 1); 156insert into t1 set a=1; 157select hex(g) from t1; 158drop table t1; 159 160create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1), 161 h char(1) default 'a') engine=myisam; 162insert into t1 set a=1; 163select h from t1; 164drop table t1; 165 166# 167# Bug #10539 168# 169 170create table t1 (a bit(8)) engine=heap; 171insert ignore into t1 values ('1111100000'); 172select a+0 from t1; 173drop table t1; 174 175# 176# Bug #11091: union 177# 178 179create table t1 (a bit(7)); 180insert into t1 values (120), (0), (111); 181select a+0 from t1 union select a+0 from t1; 182select a+0 from t1 union select NULL; 183select NULL union select a+0 from t1; 184create table t2 select a from t1 union select a from t1; 185select a+0 from t2; 186show create table t2; 187drop table t1, t2; 188 189# 190# Bug #11572: view 191# 192 193create table t1 (id1 int(11), b1 bit(1)); 194create table t2 (id2 int(11), b2 bit(1)); 195insert into t1 values (1, 1), (2, 0), (3, 1); 196insert into t2 values (2, 1), (3, 0), (4, 0); 197create algorithm=undefined view v1 as 198 select b1+0, b2+0 from t1, t2 where id1 = id2 and b1 = 0 199 union 200 select b1+0, b2+0 from t1, t2 where id1 = id2 and b2 = 1; 201select * from v1; 202drop table t1, t2; 203drop view v1; 204 205# 206# Bug #10617: bulk-insert 207# 208 209create table t1(a bit(4)); 210insert into t1(a) values (1), (2), (5), (4), (3); 211insert into t1 select * from t1; 212select a+0 from t1; 213drop table t1; 214 215# 216# join 217# 218 219create table t1 (a1 int(11), b1 bit(2)); 220create table t2 (a2 int(11), b2 bit(2)); 221insert into t1 values (1, 1), (2, 0), (3, 1), (4, 2); 222insert into t2 values (2, 1), (3, 0), (4, 1), (5, 2); 223select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2; 224select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2 order by a1; 225select a1, a2, b1+0, b2+0 from t1 join t2 on b1 = b2; 226select sum(a1), b1+0, b2+0 from t1 join t2 on b1 = b2 group by b1 order by 1; 227select 1 from t1 join t2 on b1 = b2 group by b1 order by 1; 228select b1+0,sum(b1), sum(b2) from t1 join t2 on b1 = b2 group by b1 order by 1; 229drop table t1, t2; 230 231# 232# Bug #13601: Wrong field length reported for BIT fields 233# 234create table t1 (a bit(7)); 235insert into t1 values (0x60); 236--enable_metadata 237select * from t1; 238--disable_metadata 239drop table t1; 240 241# 242# Bug#15583: BIN()/OCT()/CONV() do not work with BIT values 243# 244create table bug15583(b BIT(8), n INT); 245insert into bug15583 values(128, 128); 246insert into bug15583 values(null, null); 247insert into bug15583 values(0, 0); 248insert into bug15583 values(255, 255); 249select hex(b), bin(b), oct(b), hex(n), bin(n), oct(n) from bug15583; 250select 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; 251select hex(b + 0), bin(b + 0), oct(b + 0), hex(n), bin(n), oct(n) from bug15583; 252select conv(b, 10, 2), conv(b + 0, 10, 2) from bug15583; 253drop table bug15583; 254 255# 256# Bug #22271: data casting may affect data stored in the next column(s?) 257# 258 259create table t1(a bit(1), b smallint unsigned); 260insert ignore into t1 (b, a) values ('2', '1'); 261select hex(a), b from t1; 262drop table t1; 263 264# 265# type was not properly initialized, which caused key_copy to fail 266# 267 268create table t1(bit_field bit(2), int_field int, key a(bit_field)); 269insert into t1 values (1,2); 270handler t1 open as t1; 271handler t1 read a=(1); 272handler t1 close; 273drop table t1; 274 275# 276# Bug #30219: GROUP BY a column of the BIT type 277# 278 279CREATE TABLE t1 (b BIT(2), a VARCHAR(5)); 280INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z"); 281SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b; 282DROP TABLE t1; 283 284CREATE TABLE t1 (a CHAR(5), b BIT(2)); 285INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z"); 286SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b; 287DROP TABLE t1; 288 289CREATE TABLE t1 (a INT, b BIT(2)); 290INSERT INTO t1 (b, a) VALUES (1, 1), (3, 2), (0, 3), (3, 4); 291SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b; 292DROP TABLE t1; 293 294# 295# Bug#30245: A wrong type of a BIT field is reported when grouped by it. 296# 297CREATE TABLE t1 (b BIT); 298INSERT INTO t1 (b) VALUES (1), (0); 299--enable_metadata 300--replace_column 1 # 301SELECT DISTINCT b FROM t1; 302--replace_column 1 # 303SELECT b FROM t1 GROUP BY b; 304--disable_metadata 305DROP TABLE t1; 306 307# 308# BUG#30324 Wrong query result for COUNT(DISTINCT(bit_column)) 309# 310CREATE TABLE t1 (a int, b bit(2)); 311INSERT INTO t1 VALUES (3, 2), (2, 3), (2, 0), (3, 2), (3, 1); 312SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; 313DROP TABLE t1; 314 315create table t2 (a int, b bit(2), c char(10)); 316INSERT INTO t2 VALUES (3, 2, 'two'), (2, 3, 'three'), (2, 0, 'zero'), 317 (3, 2, 'two'), (3, 1, 'one'); 318SELECT COUNT(DISTINCT b,c) FROM t2 GROUP BY a; 319DROP TABLE t2; 320 321# 322# BUG#32556 assert in "using index for group-by" : is_last_prefix <= 0, 323# file .\opt_range.cc 324 325CREATE TABLE t1(a BIT(13), KEY(a)); 326--disable_warnings 327INSERT IGNORE INTO t1(a) VALUES 328(65535),(65525),(65535),(65535),(65535),(65535),(65535),(65535),(65535),(65535); 329ANALYZE TABLE t1; 330--enable_warnings 331 332EXPLAIN SELECT 1 FROM t1 GROUP BY a; 333SELECT 1 FROM t1 GROUP BY a; 334 335DROP TABLE t1; 336 337# 338# Bug#37799 SELECT with a BIT column in WHERE clause returns unexpected result 339# 340 341CREATE TABLE t1 (b BIT NOT NULL, i2 INTEGER NOT NULL, s VARCHAR(255) NOT NULL); 342INSERT INTO t1 VALUES(0x01,100,''), (0x00,300,''), (0x01,200,''), (0x00,100,''); 343SELECT HEX(b), i2 FROM t1 WHERE (i2>=100 AND i2<201) AND b=TRUE; 344 345CREATE TABLE t2 (b1 BIT NOT NULL, b2 BIT NOT NULL, i2 INTEGER NOT NULL, 346 s VARCHAR(255) NOT NULL); 347INSERT INTO t2 VALUES (0x01,0x00,100,''), (0x00,0x01,300,''), 348 (0x01,0x00,200,''), (0x00,0x01,100,''); 349SELECT HEX(b1), i2 FROM t2 WHERE (i2>=100 AND i2<201) AND b1=TRUE; 350SELECT HEX(b2), i2 FROM t2 WHERE (i2>=100 AND i2<201) AND b2=FALSE; 351SELECT HEX(b1), HEX(b2), i2 FROM t2 352 WHERE (i2>=100 AND i2<201) AND b1=TRUE AND b2=FALSE; 353 354DROP TABLE t1, t2; 355 356# 357# Bug #35796 SHOW CREATE TABLE and default value for BIT field 358# 359CREATE TABLE IF NOT EXISTS t1 ( 360f1 bit(2) NOT NULL default b'10', 361f2 bit(14) NOT NULL default b'11110000111100' 362) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 363SHOW CREATE TABLE t1; 364DROP TABLE t1; 365 366CREATE TABLE IF NOT EXISTS t1 ( 367f1 bit(2) NOT NULL default b'' 368) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 369SHOW CREATE TABLE t1; 370DROP TABLE t1; 371 372# 373# Bug#31399 Wrong query result when doing join buffering over BIT fields 374# 375create table t1bit7 (a1 bit(7) not null) engine=MyISAM; 376create table t2bit7 (b1 bit(7)) engine=MyISAM; 377 378insert into t1bit7 values (b'1100000'); 379insert into t1bit7 values (b'1100001'); 380insert into t1bit7 values (b'1100010'); 381insert into t2bit7 values (b'1100001'); 382insert into t2bit7 values (b'1100010'); 383insert into t2bit7 values (b'1100110'); 384 385select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; 386drop table t1bit7, t2bit7; 387 388create table t1bit7 (a1 bit(15) not null) engine=MyISAM; 389create table t2bit7 (b1 bit(15)) engine=MyISAM; 390 391insert into t1bit7 values (b'110000011111111'); 392insert into t1bit7 values (b'110000111111111'); 393insert into t1bit7 values (b'110001011111111'); 394insert into t2bit7 values (b'110000111111111'); 395insert into t2bit7 values (b'110001011111111'); 396insert into t2bit7 values (b'110011011111111'); 397 398select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; 399drop table t1bit7, t2bit7; 400 401 402--echo # 403--echo # Bug42803: Field_bit does not have unsigned_flag field, 404--echo # can lead to bad memory access 405--echo # 406CREATE TABLE t1 (a BIT(7), b BIT(9), KEY(a, b)); 407INSERT INTO t1 VALUES(0, 0), (5, 3), (5, 6), (6, 4), (7, 0); 408EXPLAIN SELECT a+0, b+0 FROM t1 WHERE a > 4 and b < 7 ORDER BY 2; 409DROP TABLE t1; 410 411 412--echo End of 5.0 tests 413 414# 415# Bug #28631: problem after alter 416# 417create table t1(a bit(7)); 418insert into t1 values(0x40); 419alter table t1 modify column a bit(8); 420select hex(a) from t1; 421insert into t1 values(0x80); 422select hex(a) from t1; 423create index a on t1(a); 424insert into t1 values(0x81); 425select hex(a) from t1; 426show create table t1; 427drop table t1; 428 429--echo # 430--echo # Bug#50591 bit(31) causes Duplicate entry '1-NULL' for key 'group_key' 431--echo # 432CREATE TABLE t1(a INT, b BIT(7) NOT NULL); 433INSERT INTO t1 VALUES (NULL, 0),(NULL, 0); 434SELECT SUM(a) FROM t1 GROUP BY b, a; 435DROP TABLE t1; 436 437CREATE TABLE t1(a INT, b BIT(7) NOT NULL, c BIT(8) NOT NULL); 438INSERT INTO t1 VALUES (NULL, 0, 0),(NULL, 0, 0); 439SELECT SUM(a) FROM t1 GROUP BY c, b, a; 440DROP TABLE t1; 441 442--echo End of 5.1 tests 443 444--echo # 445--echo # Start of 10.1 tests 446--echo # 447 448--echo # 449--echo # MDEV-8867 Wrong field type or metadata for COALESCE(bit_column, 1) 450--echo # 451 452CREATE TABLE t1 (val bit(1)); 453INSERT INTO t1 VALUES (0); 454CREATE TABLE t2 AS SELECT COALESCE(val, 1) AS c FROM t1; 455SELECT * FROM t2; 456SHOW CREATE TABLE t2; 457DROP TABLE t2; 458--enable_metadata 459SELECT COALESCE(val, 1) FROM t1; 460--disable_metadata 461DROP TABLE t1; 462 463--echo # 464--echo # MDEV-18452 ASAN unknown-crash in Field::set_default upon SET bit_column = DEFAULT 465--echo # 466 467CREATE TABLE t1 (b BIT(20)) ENGINE=MyISAM; 468INSERT INTO t1 VALUES (0); 469UPDATE t1 SET b = DEFAULT; 470DROP TABLE t1; 471 472--echo # 473--echo # End of 10.1 tests 474--echo # 475 476 477--echo # 478--echo # Start of 10.2 tests 479--echo # 480 481--echo # 482--echo # MDEV-9334 ALTER from DECIMAL to BIGINT UNSIGNED returns a wrong result 483--echo # 484 485CREATE TABLE t1 (a DECIMAL(30,0)); 486INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED)); 487ALTER TABLE t1 MODIFY a BIT(64); 488SELECT a+0 FROM t1; 489DROP TABLE IF EXISTS t1; 490 491 492--echo # 493--echo # End of 10.2 tests 494--echo # 495 496--echo # 497--echo # Start of 10.4 tests 498--echo # 499 500--echo # 501--echo # MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant 502--echo # 503 504CREATE TABLE t1 (a BIT(7), KEY(a)); 505INSERT INTO t1 VALUES (1),(2),(3),(4),(5); 506EXPLAIN SELECT * FROM t1 WHERE a=200; 507EXPLAIN SELECT * FROM t1 WHERE a<=>200; 508DROP TABLE t1; 509 510--echo # 511--echo # MDEV-23323 Rounding functions return a wrong data type for a BIT, ENUM, SET argument 512--echo # 513 514--vertical_results 515DELIMITER $$; 516BEGIN NOT ATOMIC 517 FOR i IN 1..64 518 DO 519 SELECT '-----', CONCAT('BIT(',i,')') AS Type; 520 EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (a BIT(64))','64', i); 521 INSERT IGNORE INTO t1 VALUES (0xFFFFFFFFFFFFFFFF); 522 CREATE TABLE t2 AS SELECT 523 a, 524 FLOOR(a) AS cf, 525 CEILING(a) AS cc, 526 ROUND(a) AS cr, 527 TRUNCATE(a,0) AS ct 528 FROM t1; 529 SHOW CREATE TABLE t2; 530 SELECT CAST(a AS UNSIGNED) AS a, cf, cc, cr, ct FROM t2; 531 DROP TABLE t2; 532 DROP TABLE t1; 533 END FOR; 534END; 535$$ 536DELIMITER ;$$ 537--horizontal_results 538 539 540--echo # 541--echo # End of 10.4 tests 542--echo # 543 544--echo # 545--echo # Start of 10.5 tests 546--echo # 547 548--echo # 549--echo # MDEV-20496 Assertion `field.is_sane()' failed in Protocol_text::store_field_metadata 550--echo # 551 552CREATE TABLE t1 (b BIT(1)); 553--disable_ps_protocol 554--enable_metadata 555SELECT MIN(CASE WHEN 0 THEN b END) FROM t1; 556--disable_metadata 557--enable_ps_protocol 558CREATE TABLE t2 AS SELECT MIN(CASE WHEN 0 THEN b END) FROM t1; 559SHOW CREATE TABLE t2; 560DROP TABLE t2; 561DROP TABLE t1; 562 563--echo # 564--echo # End of 10.5 tests 565--echo # 566