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,t2; create table t1 (a bit(65)); ERROR 42000: Display width out of range for 'a' (max = 64) create table t1 (a bit(0)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bit(1) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; create table t1 (a bit(64)); 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); 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 '\x00' for key 'a' drop table t1; create table t1 (a bit(2)); 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)); 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 9 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)); 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)); 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)); 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)); 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); alter table t1 add primary key (a); drop table t1; create table t1 (a bit(19), b bit(5)); insert into t1 values (1000, 10), (3, 8), (200, 6), (2303, 2), (12345, 4), (1, 0); select a+0, b+0 from t1; a+0 b+0 1000 10 3 8 200 6 2303 2 12345 4 1 0 alter table t1 engine=heap; select a+0, b+0 from t1; a+0 b+0 1000 10 3 8 200 6 2303 2 12345 4 1 0 alter table t1 add key(a, b); select a+0, b+0 from t1; a+0 b+0 1000 10 3 8 200 6 2303 2 12345 4 1 0 alter table t1 engine=myisam; select a+0, b+0 from t1; a+0 b+0 1 0 3 8 200 6 1000 10 2303 2 12345 4 create table t2 engine=heap select * from t1; select a+0, b+0 from t2; a+0 b+0 1 0 3 8 200 6 1000 10 2303 2 12345 4 drop table t1; create table t1 select * from t2; select a+0, b+0 from t1; a+0 b+0 1 0 3 8 200 6 1000 10 2303 2 12345 4 drop table t1, t2; create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1), g bit(1) NOT NULL default 1, h char(1) default 'a'); insert into t1 set a=1; select hex(g), h from t1; hex(g) h 1 a drop table t1; create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1), g bit(1) NOT NULL default 1); insert into t1 set a=1; select hex(g) from t1; hex(g) 1 drop table t1; create table t1 (a int, b time, c tinyint, d bool, e char(10), f bit(1), h char(1) default 'a') engine=myisam; insert into t1 set a=1; select h from t1; h a drop table t1; create table t1 (a bit(8)) engine=heap; insert ignore into t1 values ('1111100000'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 select a+0 from t1; a+0 255 drop table t1; create table t1 (a bit(7)); insert into t1 values (120), (0), (111); select a+0 from t1 union select a+0 from t1; a+0 120 0 111 select a+0 from t1 union select NULL; a+0 120 0 111 NULL select NULL union select a+0 from t1; NULL NULL 120 0 111 create table t2 select a from t1 union select a from t1; select a+0 from t2; a+0 120 0 111 show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` bit(7) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1, t2; create table t1 (id1 int(11), b1 bit(1)); create table t2 (id2 int(11), b2 bit(1)); insert into t1 values (1, 1), (2, 0), (3, 1); insert into t2 values (2, 1), (3, 0), (4, 0); create algorithm=undefined view v1 as select b1+0, b2+0 from t1, t2 where id1 = id2 and b1 = 0 union select b1+0, b2+0 from t1, t2 where id1 = id2 and b2 = 1; select * from v1; b1+0 b2+0 0 1 drop table t1, t2; drop view v1; create table t1(a bit(4)); insert into t1(a) values (1), (2), (5), (4), (3); insert into t1 select * from t1; select a+0 from t1; a+0 1 2 5 4 3 1 2 5 4 3 drop table t1; create table t1 (a1 int(11), b1 bit(2)); create table t2 (a2 int(11), b2 bit(2)); insert into t1 values (1, 1), (2, 0), (3, 1), (4, 2); insert into t2 values (2, 1), (3, 0), (4, 1), (5, 2); select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2; a1 a2 b1+0 b2+0 2 2 0 1 3 3 1 0 4 4 2 1 select a1, a2, b1+0, b2+0 from t1 join t2 on a1 = a2 order by a1; a1 a2 b1+0 b2+0 2 2 0 1 3 3 1 0 4 4 2 1 select a1, a2, b1+0, b2+0 from t1 join t2 on b1 = b2; a1 a2 b1+0 b2+0 1 2 1 1 3 2 1 1 2 3 0 0 1 4 1 1 3 4 1 1 4 5 2 2 select sum(a1), b1+0, b2+0 from t1 join t2 on b1 = b2 group by b1 order by 1; sum(a1) b1+0 b2+0 2 0 0 4 2 2 8 1 1 select 1 from t1 join t2 on b1 = b2 group by b1 order by 1; 1 1 1 1 select b1+0,sum(b1), sum(b2) from t1 join t2 on b1 = b2 group by b1 order by 1; b1+0 sum(b1) sum(b2) 0 0 0 1 4 4 2 2 2 drop table t1, t2; create table t1 (a bit(7)); 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; create table bug15583(b BIT(8), n INT); insert into bug15583 values(128, 128); insert into bug15583 values(null, null); insert into bug15583 values(0, 0); insert into bug15583 values(255, 255); select hex(b), bin(b), oct(b), hex(n), bin(n), oct(n) from bug15583; hex(b) bin(b) oct(b) hex(n) bin(n) oct(n) 80 10000000 200 80 10000000 200 NULL NULL NULL NULL NULL NULL 0 0 0 0 0 0 FF 11111111 377 FF 11111111 377 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; should_be_onetrue should_be_onetrue should_be_onetrue 1 1 1 NULL NULL NULL 1 1 1 1 1 1 select hex(b + 0), bin(b + 0), oct(b + 0), hex(n), bin(n), oct(n) from bug15583; hex(b + 0) bin(b + 0) oct(b + 0) hex(n) bin(n) oct(n) 80 10000000 200 80 10000000 200 NULL NULL NULL NULL NULL NULL 0 0 0 0 0 0 FF 11111111 377 FF 11111111 377 select conv(b, 10, 2), conv(b + 0, 10, 2) from bug15583; conv(b, 10, 2) conv(b + 0, 10, 2) 10000000 10000000 NULL NULL 0 0 11111111 11111111 drop table bug15583; create table t1(a bit(1), b smallint unsigned); insert ignore into t1 (b, a) values ('2', '1'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 select hex(a), b from t1; hex(a) b 1 2 drop table t1; create table t1(bit_field bit(2), int_field int, key a(bit_field)); insert into t1 values (1,2); handler t1 open as t1; handler t1 read a=(1); bit_field int_field  2 handler t1 close; drop table t1; CREATE TABLE t1 (b BIT(2), a VARCHAR(5)); INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z"); SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b; b+0 COUNT(DISTINCT a) 0 1 1 1 3 2 DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), b BIT(2)); INSERT INTO t1 (b, a) VALUES (1, "x"), (3, "zz"), (0, "y"), (3, "z"); SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b; b+0 COUNT(DISTINCT a) 0 1 1 1 3 2 DROP TABLE t1; CREATE TABLE t1 (a INT, b BIT(2)); INSERT INTO t1 (b, a) VALUES (1, 1), (3, 2), (0, 3), (3, 4); SELECT b+0, COUNT(DISTINCT a) FROM t1 GROUP BY b; b+0 COUNT(DISTINCT a) 0 1 1 1 3 2 DROP TABLE t1; CREATE TABLE t1 (b BIT); INSERT INTO t1 (b) VALUES (1), (0); SELECT DISTINCT b FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 b b 16 1 1 Y 32 0 63 b # # SELECT b FROM t1 GROUP BY b; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 b b 16 1 1 Y 32 0 63 b # # DROP TABLE t1; CREATE TABLE t1 (a int, b bit(2)); INSERT INTO t1 VALUES (3, 2), (2, 3), (2, 0), (3, 2), (3, 1); SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a; COUNT(DISTINCT b) 2 2 DROP TABLE t1; create table t2 (a int, b bit(2), c char(10)); INSERT INTO t2 VALUES (3, 2, 'two'), (2, 3, 'three'), (2, 0, 'zero'), (3, 2, 'two'), (3, 1, 'one'); SELECT COUNT(DISTINCT b,c) FROM t2 GROUP BY a; COUNT(DISTINCT b,c) 2 2 DROP TABLE t2; CREATE TABLE t1(a BIT(13), KEY(a)); INSERT IGNORE INTO t1(a) VALUES (65535),(65525),(65535),(65535),(65535),(65535),(65535),(65535),(65535),(65535); ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT 1 FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL a 3 NULL 2 Using index for group-by SELECT 1 FROM t1 GROUP BY a; 1 1 DROP TABLE t1; CREATE TABLE t1 (b BIT NOT NULL, i2 INTEGER NOT NULL, s VARCHAR(255) NOT NULL); INSERT INTO t1 VALUES(0x01,100,''), (0x00,300,''), (0x01,200,''), (0x00,100,''); SELECT HEX(b), i2 FROM t1 WHERE (i2>=100 AND i2<201) AND b=TRUE; HEX(b) i2 1 100 1 200 CREATE TABLE t2 (b1 BIT NOT NULL, b2 BIT NOT NULL, i2 INTEGER NOT NULL, s VARCHAR(255) NOT NULL); INSERT INTO t2 VALUES (0x01,0x00,100,''), (0x00,0x01,300,''), (0x01,0x00,200,''), (0x00,0x01,100,''); SELECT HEX(b1), i2 FROM t2 WHERE (i2>=100 AND i2<201) AND b1=TRUE; HEX(b1) i2 1 100 1 200 SELECT HEX(b2), i2 FROM t2 WHERE (i2>=100 AND i2<201) AND b2=FALSE; HEX(b2) i2 0 100 0 200 SELECT HEX(b1), HEX(b2), i2 FROM t2 WHERE (i2>=100 AND i2<201) AND b1=TRUE AND b2=FALSE; HEX(b1) HEX(b2) i2 1 0 100 1 0 200 DROP TABLE t1, t2; CREATE TABLE IF NOT EXISTS t1 ( f1 bit(2) NOT NULL default b'10', f2 bit(14) NOT NULL default b'11110000111100' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f1` bit(2) NOT NULL DEFAULT b'10', `f2` bit(14) NOT NULL DEFAULT b'11110000111100' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci DROP TABLE t1; CREATE TABLE IF NOT EXISTS t1 ( f1 bit(2) NOT NULL default b'' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f1` bit(2) NOT NULL DEFAULT b'0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci DROP TABLE t1; create table t1bit7 (a1 bit(7) not null) engine=MyISAM; create table t2bit7 (b1 bit(7)) engine=MyISAM; insert into t1bit7 values (b'1100000'); insert into t1bit7 values (b'1100001'); insert into t1bit7 values (b'1100010'); insert into t2bit7 values (b'1100001'); insert into t2bit7 values (b'1100010'); insert into t2bit7 values (b'1100110'); select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; bin(a1) 1100001 1100010 drop table t1bit7, t2bit7; create table t1bit7 (a1 bit(15) not null) engine=MyISAM; create table t2bit7 (b1 bit(15)) engine=MyISAM; insert into t1bit7 values (b'110000011111111'); insert into t1bit7 values (b'110000111111111'); insert into t1bit7 values (b'110001011111111'); insert into t2bit7 values (b'110000111111111'); insert into t2bit7 values (b'110001011111111'); insert into t2bit7 values (b'110011011111111'); select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; bin(a1) 110000111111111 110001011111111 drop table t1bit7, t2bit7; # # Bug42803: Field_bit does not have unsigned_flag field, # can lead to bad memory access # CREATE TABLE t1 (a BIT(7), b BIT(9), KEY(a, b)); INSERT INTO t1 VALUES(0, 0), (5, 3), (5, 6), (6, 4), (7, 0); EXPLAIN SELECT a+0, b+0 FROM t1 WHERE a > 4 and b < 7 ORDER BY 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 2 NULL 4 Using where; Using index; Using filesort DROP TABLE t1; End of 5.0 tests create table t1(a bit(7)); insert into t1 values(0x40); alter table t1 modify column a bit(8); select hex(a) from t1; hex(a) 40 insert into t1 values(0x80); select hex(a) from t1; hex(a) 40 80 create index a on t1(a); insert into t1 values(0x81); select hex(a) from t1; hex(a) 40 80 81 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bit(8) DEFAULT NULL, KEY `a` (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; # # Bug#50591 bit(31) causes Duplicate entry '1-NULL' for key 'group_key' # CREATE TABLE t1(a INT, b BIT(7) NOT NULL); INSERT INTO t1 VALUES (NULL, 0),(NULL, 0); SELECT SUM(a) FROM t1 GROUP BY b, a; SUM(a) NULL DROP TABLE t1; CREATE TABLE t1(a INT, b BIT(7) NOT NULL, c BIT(8) NOT NULL); INSERT INTO t1 VALUES (NULL, 0, 0),(NULL, 0, 0); SELECT SUM(a) FROM t1 GROUP BY c, b, a; SUM(a) NULL DROP TABLE t1; End of 5.1 tests # # Start of 10.1 tests # # # MDEV-8867 Wrong field type or metadata for COALESCE(bit_column, 1) # CREATE TABLE t1 (val bit(1)); INSERT INTO t1 VALUES (0); CREATE TABLE t2 AS SELECT COALESCE(val, 1) AS c FROM t1; SELECT * FROM t2; c 0 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c` decimal(1,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t2; SELECT COALESCE(val, 1) FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def COALESCE(val, 1) 246 2 1 Y 32896 0 63 COALESCE(val, 1) 0 DROP TABLE t1; # # MDEV-18452 ASAN unknown-crash in Field::set_default upon SET bit_column = DEFAULT # CREATE TABLE t1 (b BIT(20)) ENGINE=MyISAM; INSERT INTO t1 VALUES (0); UPDATE t1 SET b = DEFAULT; DROP TABLE t1; # # End of 10.1 tests # # # Start of 10.2 tests # # # MDEV-9334 ALTER from DECIMAL to BIGINT UNSIGNED returns a wrong result # CREATE TABLE t1 (a DECIMAL(30,0)); INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED)); ALTER TABLE t1 MODIFY a BIT(64); SELECT a+0 FROM t1; a+0 18446744073709551615 DROP TABLE IF EXISTS t1; # # End of 10.2 tests # # # Start of 10.4 tests # # # MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant # CREATE TABLE t1 (a BIT(7), KEY(a)); INSERT INTO t1 VALUES (1),(2),(3),(4),(5); EXPLAIN SELECT * FROM t1 WHERE a=200; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN SELECT * FROM t1 WHERE a<=>200; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; # # MDEV-23323 Rounding functions return a wrong data type for a BIT, ENUM, SET argument # BEGIN NOT ATOMIC FOR i IN 1..64 DO SELECT '-----', CONCAT('BIT(',i,')') AS Type; EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (a BIT(64))','64', i); INSERT IGNORE INTO t1 VALUES (0xFFFFFFFFFFFFFFFF); CREATE TABLE t2 AS SELECT a, FLOOR(a) AS cf, CEILING(a) AS cc, ROUND(a) AS cr, TRUNCATE(a,0) AS ct FROM t1; SHOW CREATE TABLE t2; SELECT CAST(a AS UNSIGNED) AS a, cf, cc, cr, ct FROM t2; DROP TABLE t2; DROP TABLE t1; END FOR; END; $$ ----- ----- Type BIT(1) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(1) DEFAULT NULL, `cf` int(1) unsigned DEFAULT NULL, `cc` int(1) unsigned DEFAULT NULL, `cr` int(1) unsigned DEFAULT NULL, `ct` int(1) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 1 cf 1 cc 1 cr 1 ct 1 ----- ----- Type BIT(2) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(2) DEFAULT NULL, `cf` int(1) unsigned DEFAULT NULL, `cc` int(1) unsigned DEFAULT NULL, `cr` int(1) unsigned DEFAULT NULL, `ct` int(1) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 3 cf 3 cc 3 cr 3 ct 3 ----- ----- Type BIT(3) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(3) DEFAULT NULL, `cf` int(1) unsigned DEFAULT NULL, `cc` int(1) unsigned DEFAULT NULL, `cr` int(1) unsigned DEFAULT NULL, `ct` int(1) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 7 cf 7 cc 7 cr 7 ct 7 ----- ----- Type BIT(4) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(4) DEFAULT NULL, `cf` int(2) unsigned DEFAULT NULL, `cc` int(2) unsigned DEFAULT NULL, `cr` int(2) unsigned DEFAULT NULL, `ct` int(2) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 15 cf 15 cc 15 cr 15 ct 15 ----- ----- Type BIT(5) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(5) DEFAULT NULL, `cf` int(2) unsigned DEFAULT NULL, `cc` int(2) unsigned DEFAULT NULL, `cr` int(2) unsigned DEFAULT NULL, `ct` int(2) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 31 cf 31 cc 31 cr 31 ct 31 ----- ----- Type BIT(6) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(6) DEFAULT NULL, `cf` int(2) unsigned DEFAULT NULL, `cc` int(2) unsigned DEFAULT NULL, `cr` int(2) unsigned DEFAULT NULL, `ct` int(2) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 63 cf 63 cc 63 cr 63 ct 63 ----- ----- Type BIT(7) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(7) DEFAULT NULL, `cf` int(3) unsigned DEFAULT NULL, `cc` int(3) unsigned DEFAULT NULL, `cr` int(3) unsigned DEFAULT NULL, `ct` int(3) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 127 cf 127 cc 127 cr 127 ct 127 ----- ----- Type BIT(8) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(8) DEFAULT NULL, `cf` int(3) unsigned DEFAULT NULL, `cc` int(3) unsigned DEFAULT NULL, `cr` int(3) unsigned DEFAULT NULL, `ct` int(3) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 255 cf 255 cc 255 cr 255 ct 255 ----- ----- Type BIT(9) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(9) DEFAULT NULL, `cf` int(3) unsigned DEFAULT NULL, `cc` int(3) unsigned DEFAULT NULL, `cr` int(3) unsigned DEFAULT NULL, `ct` int(3) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 511 cf 511 cc 511 cr 511 ct 511 ----- ----- Type BIT(10) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(10) DEFAULT NULL, `cf` int(4) unsigned DEFAULT NULL, `cc` int(4) unsigned DEFAULT NULL, `cr` int(4) unsigned DEFAULT NULL, `ct` int(4) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 1023 cf 1023 cc 1023 cr 1023 ct 1023 ----- ----- Type BIT(11) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(11) DEFAULT NULL, `cf` int(4) unsigned DEFAULT NULL, `cc` int(4) unsigned DEFAULT NULL, `cr` int(4) unsigned DEFAULT NULL, `ct` int(4) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 2047 cf 2047 cc 2047 cr 2047 ct 2047 ----- ----- Type BIT(12) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(12) DEFAULT NULL, `cf` int(4) unsigned DEFAULT NULL, `cc` int(4) unsigned DEFAULT NULL, `cr` int(4) unsigned DEFAULT NULL, `ct` int(4) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 4095 cf 4095 cc 4095 cr 4095 ct 4095 ----- ----- Type BIT(13) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(13) DEFAULT NULL, `cf` int(4) unsigned DEFAULT NULL, `cc` int(4) unsigned DEFAULT NULL, `cr` int(4) unsigned DEFAULT NULL, `ct` int(4) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 8191 cf 8191 cc 8191 cr 8191 ct 8191 ----- ----- Type BIT(14) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(14) DEFAULT NULL, `cf` int(5) unsigned DEFAULT NULL, `cc` int(5) unsigned DEFAULT NULL, `cr` int(5) unsigned DEFAULT NULL, `ct` int(5) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 16383 cf 16383 cc 16383 cr 16383 ct 16383 ----- ----- Type BIT(15) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(15) DEFAULT NULL, `cf` int(5) unsigned DEFAULT NULL, `cc` int(5) unsigned DEFAULT NULL, `cr` int(5) unsigned DEFAULT NULL, `ct` int(5) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 32767 cf 32767 cc 32767 cr 32767 ct 32767 ----- ----- Type BIT(16) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(16) DEFAULT NULL, `cf` int(5) unsigned DEFAULT NULL, `cc` int(5) unsigned DEFAULT NULL, `cr` int(5) unsigned DEFAULT NULL, `ct` int(5) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 65535 cf 65535 cc 65535 cr 65535 ct 65535 ----- ----- Type BIT(17) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(17) DEFAULT NULL, `cf` int(6) unsigned DEFAULT NULL, `cc` int(6) unsigned DEFAULT NULL, `cr` int(6) unsigned DEFAULT NULL, `ct` int(6) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 131071 cf 131071 cc 131071 cr 131071 ct 131071 ----- ----- Type BIT(18) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(18) DEFAULT NULL, `cf` int(6) unsigned DEFAULT NULL, `cc` int(6) unsigned DEFAULT NULL, `cr` int(6) unsigned DEFAULT NULL, `ct` int(6) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 262143 cf 262143 cc 262143 cr 262143 ct 262143 ----- ----- Type BIT(19) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(19) DEFAULT NULL, `cf` int(6) unsigned DEFAULT NULL, `cc` int(6) unsigned DEFAULT NULL, `cr` int(6) unsigned DEFAULT NULL, `ct` int(6) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 524287 cf 524287 cc 524287 cr 524287 ct 524287 ----- ----- Type BIT(20) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(20) DEFAULT NULL, `cf` int(7) unsigned DEFAULT NULL, `cc` int(7) unsigned DEFAULT NULL, `cr` int(7) unsigned DEFAULT NULL, `ct` int(7) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 1048575 cf 1048575 cc 1048575 cr 1048575 ct 1048575 ----- ----- Type BIT(21) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(21) DEFAULT NULL, `cf` int(7) unsigned DEFAULT NULL, `cc` int(7) unsigned DEFAULT NULL, `cr` int(7) unsigned DEFAULT NULL, `ct` int(7) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 2097151 cf 2097151 cc 2097151 cr 2097151 ct 2097151 ----- ----- Type BIT(22) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(22) DEFAULT NULL, `cf` int(7) unsigned DEFAULT NULL, `cc` int(7) unsigned DEFAULT NULL, `cr` int(7) unsigned DEFAULT NULL, `ct` int(7) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 4194303 cf 4194303 cc 4194303 cr 4194303 ct 4194303 ----- ----- Type BIT(23) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(23) DEFAULT NULL, `cf` int(7) unsigned DEFAULT NULL, `cc` int(7) unsigned DEFAULT NULL, `cr` int(7) unsigned DEFAULT NULL, `ct` int(7) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 8388607 cf 8388607 cc 8388607 cr 8388607 ct 8388607 ----- ----- Type BIT(24) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(24) DEFAULT NULL, `cf` int(8) unsigned DEFAULT NULL, `cc` int(8) unsigned DEFAULT NULL, `cr` int(8) unsigned DEFAULT NULL, `ct` int(8) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 16777215 cf 16777215 cc 16777215 cr 16777215 ct 16777215 ----- ----- Type BIT(25) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(25) DEFAULT NULL, `cf` int(8) unsigned DEFAULT NULL, `cc` int(8) unsigned DEFAULT NULL, `cr` int(8) unsigned DEFAULT NULL, `ct` int(8) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 33554431 cf 33554431 cc 33554431 cr 33554431 ct 33554431 ----- ----- Type BIT(26) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(26) DEFAULT NULL, `cf` int(8) unsigned DEFAULT NULL, `cc` int(8) unsigned DEFAULT NULL, `cr` int(8) unsigned DEFAULT NULL, `ct` int(8) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 67108863 cf 67108863 cc 67108863 cr 67108863 ct 67108863 ----- ----- Type BIT(27) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(27) DEFAULT NULL, `cf` int(9) unsigned DEFAULT NULL, `cc` int(9) unsigned DEFAULT NULL, `cr` int(9) unsigned DEFAULT NULL, `ct` int(9) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 134217727 cf 134217727 cc 134217727 cr 134217727 ct 134217727 ----- ----- Type BIT(28) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(28) DEFAULT NULL, `cf` int(9) unsigned DEFAULT NULL, `cc` int(9) unsigned DEFAULT NULL, `cr` int(9) unsigned DEFAULT NULL, `ct` int(9) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 268435455 cf 268435455 cc 268435455 cr 268435455 ct 268435455 ----- ----- Type BIT(29) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(29) DEFAULT NULL, `cf` int(9) unsigned DEFAULT NULL, `cc` int(9) unsigned DEFAULT NULL, `cr` int(9) unsigned DEFAULT NULL, `ct` int(9) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 536870911 cf 536870911 cc 536870911 cr 536870911 ct 536870911 ----- ----- Type BIT(30) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(30) DEFAULT NULL, `cf` int(10) unsigned DEFAULT NULL, `cc` int(10) unsigned DEFAULT NULL, `cr` int(10) unsigned DEFAULT NULL, `ct` int(10) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 1073741823 cf 1073741823 cc 1073741823 cr 1073741823 ct 1073741823 ----- ----- Type BIT(31) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(31) DEFAULT NULL, `cf` int(10) unsigned DEFAULT NULL, `cc` int(10) unsigned DEFAULT NULL, `cr` int(10) unsigned DEFAULT NULL, `ct` int(10) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 2147483647 cf 2147483647 cc 2147483647 cr 2147483647 ct 2147483647 ----- ----- Type BIT(32) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(32) DEFAULT NULL, `cf` int(10) unsigned DEFAULT NULL, `cc` int(10) unsigned DEFAULT NULL, `cr` int(10) unsigned DEFAULT NULL, `ct` int(10) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 4294967295 cf 4294967295 cc 4294967295 cr 4294967295 ct 4294967295 ----- ----- Type BIT(33) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(33) DEFAULT NULL, `cf` bigint(10) unsigned DEFAULT NULL, `cc` bigint(10) unsigned DEFAULT NULL, `cr` bigint(10) unsigned DEFAULT NULL, `ct` bigint(10) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 8589934591 cf 8589934591 cc 8589934591 cr 8589934591 ct 8589934591 ----- ----- Type BIT(34) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(34) DEFAULT NULL, `cf` bigint(11) unsigned DEFAULT NULL, `cc` bigint(11) unsigned DEFAULT NULL, `cr` bigint(11) unsigned DEFAULT NULL, `ct` bigint(11) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 17179869183 cf 17179869183 cc 17179869183 cr 17179869183 ct 17179869183 ----- ----- Type BIT(35) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(35) DEFAULT NULL, `cf` bigint(11) unsigned DEFAULT NULL, `cc` bigint(11) unsigned DEFAULT NULL, `cr` bigint(11) unsigned DEFAULT NULL, `ct` bigint(11) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 34359738367 cf 34359738367 cc 34359738367 cr 34359738367 ct 34359738367 ----- ----- Type BIT(36) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(36) DEFAULT NULL, `cf` bigint(11) unsigned DEFAULT NULL, `cc` bigint(11) unsigned DEFAULT NULL, `cr` bigint(11) unsigned DEFAULT NULL, `ct` bigint(11) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 68719476735 cf 68719476735 cc 68719476735 cr 68719476735 ct 68719476735 ----- ----- Type BIT(37) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(37) DEFAULT NULL, `cf` bigint(12) unsigned DEFAULT NULL, `cc` bigint(12) unsigned DEFAULT NULL, `cr` bigint(12) unsigned DEFAULT NULL, `ct` bigint(12) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 137438953471 cf 137438953471 cc 137438953471 cr 137438953471 ct 137438953471 ----- ----- Type BIT(38) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(38) DEFAULT NULL, `cf` bigint(12) unsigned DEFAULT NULL, `cc` bigint(12) unsigned DEFAULT NULL, `cr` bigint(12) unsigned DEFAULT NULL, `ct` bigint(12) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 274877906943 cf 274877906943 cc 274877906943 cr 274877906943 ct 274877906943 ----- ----- Type BIT(39) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(39) DEFAULT NULL, `cf` bigint(12) unsigned DEFAULT NULL, `cc` bigint(12) unsigned DEFAULT NULL, `cr` bigint(12) unsigned DEFAULT NULL, `ct` bigint(12) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 549755813887 cf 549755813887 cc 549755813887 cr 549755813887 ct 549755813887 ----- ----- Type BIT(40) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(40) DEFAULT NULL, `cf` bigint(13) unsigned DEFAULT NULL, `cc` bigint(13) unsigned DEFAULT NULL, `cr` bigint(13) unsigned DEFAULT NULL, `ct` bigint(13) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 1099511627775 cf 1099511627775 cc 1099511627775 cr 1099511627775 ct 1099511627775 ----- ----- Type BIT(41) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(41) DEFAULT NULL, `cf` bigint(13) unsigned DEFAULT NULL, `cc` bigint(13) unsigned DEFAULT NULL, `cr` bigint(13) unsigned DEFAULT NULL, `ct` bigint(13) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 2199023255551 cf 2199023255551 cc 2199023255551 cr 2199023255551 ct 2199023255551 ----- ----- Type BIT(42) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(42) DEFAULT NULL, `cf` bigint(13) unsigned DEFAULT NULL, `cc` bigint(13) unsigned DEFAULT NULL, `cr` bigint(13) unsigned DEFAULT NULL, `ct` bigint(13) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 4398046511103 cf 4398046511103 cc 4398046511103 cr 4398046511103 ct 4398046511103 ----- ----- Type BIT(43) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(43) DEFAULT NULL, `cf` bigint(13) unsigned DEFAULT NULL, `cc` bigint(13) unsigned DEFAULT NULL, `cr` bigint(13) unsigned DEFAULT NULL, `ct` bigint(13) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 8796093022207 cf 8796093022207 cc 8796093022207 cr 8796093022207 ct 8796093022207 ----- ----- Type BIT(44) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(44) DEFAULT NULL, `cf` bigint(14) unsigned DEFAULT NULL, `cc` bigint(14) unsigned DEFAULT NULL, `cr` bigint(14) unsigned DEFAULT NULL, `ct` bigint(14) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 17592186044415 cf 17592186044415 cc 17592186044415 cr 17592186044415 ct 17592186044415 ----- ----- Type BIT(45) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(45) DEFAULT NULL, `cf` bigint(14) unsigned DEFAULT NULL, `cc` bigint(14) unsigned DEFAULT NULL, `cr` bigint(14) unsigned DEFAULT NULL, `ct` bigint(14) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 35184372088831 cf 35184372088831 cc 35184372088831 cr 35184372088831 ct 35184372088831 ----- ----- Type BIT(46) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(46) DEFAULT NULL, `cf` bigint(14) unsigned DEFAULT NULL, `cc` bigint(14) unsigned DEFAULT NULL, `cr` bigint(14) unsigned DEFAULT NULL, `ct` bigint(14) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 70368744177663 cf 70368744177663 cc 70368744177663 cr 70368744177663 ct 70368744177663 ----- ----- Type BIT(47) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(47) DEFAULT NULL, `cf` bigint(15) unsigned DEFAULT NULL, `cc` bigint(15) unsigned DEFAULT NULL, `cr` bigint(15) unsigned DEFAULT NULL, `ct` bigint(15) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 140737488355327 cf 140737488355327 cc 140737488355327 cr 140737488355327 ct 140737488355327 ----- ----- Type BIT(48) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(48) DEFAULT NULL, `cf` bigint(15) unsigned DEFAULT NULL, `cc` bigint(15) unsigned DEFAULT NULL, `cr` bigint(15) unsigned DEFAULT NULL, `ct` bigint(15) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 281474976710655 cf 281474976710655 cc 281474976710655 cr 281474976710655 ct 281474976710655 ----- ----- Type BIT(49) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(49) DEFAULT NULL, `cf` bigint(15) unsigned DEFAULT NULL, `cc` bigint(15) unsigned DEFAULT NULL, `cr` bigint(15) unsigned DEFAULT NULL, `ct` bigint(15) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 562949953421311 cf 562949953421311 cc 562949953421311 cr 562949953421311 ct 562949953421311 ----- ----- Type BIT(50) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(50) DEFAULT NULL, `cf` bigint(16) unsigned DEFAULT NULL, `cc` bigint(16) unsigned DEFAULT NULL, `cr` bigint(16) unsigned DEFAULT NULL, `ct` bigint(16) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 1125899906842623 cf 1125899906842623 cc 1125899906842623 cr 1125899906842623 ct 1125899906842623 ----- ----- Type BIT(51) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(51) DEFAULT NULL, `cf` bigint(16) unsigned DEFAULT NULL, `cc` bigint(16) unsigned DEFAULT NULL, `cr` bigint(16) unsigned DEFAULT NULL, `ct` bigint(16) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 2251799813685247 cf 2251799813685247 cc 2251799813685247 cr 2251799813685247 ct 2251799813685247 ----- ----- Type BIT(52) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(52) DEFAULT NULL, `cf` bigint(16) unsigned DEFAULT NULL, `cc` bigint(16) unsigned DEFAULT NULL, `cr` bigint(16) unsigned DEFAULT NULL, `ct` bigint(16) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 4503599627370495 cf 4503599627370495 cc 4503599627370495 cr 4503599627370495 ct 4503599627370495 ----- ----- Type BIT(53) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(53) DEFAULT NULL, `cf` bigint(16) unsigned DEFAULT NULL, `cc` bigint(16) unsigned DEFAULT NULL, `cr` bigint(16) unsigned DEFAULT NULL, `ct` bigint(16) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9007199254740991 cf 9007199254740991 cc 9007199254740991 cr 9007199254740991 ct 9007199254740991 ----- ----- Type BIT(54) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(54) DEFAULT NULL, `cf` bigint(17) unsigned DEFAULT NULL, `cc` bigint(17) unsigned DEFAULT NULL, `cr` bigint(17) unsigned DEFAULT NULL, `ct` bigint(17) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 18014398509481983 cf 18014398509481983 cc 18014398509481983 cr 18014398509481983 ct 18014398509481983 ----- ----- Type BIT(55) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(55) DEFAULT NULL, `cf` bigint(17) unsigned DEFAULT NULL, `cc` bigint(17) unsigned DEFAULT NULL, `cr` bigint(17) unsigned DEFAULT NULL, `ct` bigint(17) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 36028797018963967 cf 36028797018963967 cc 36028797018963967 cr 36028797018963967 ct 36028797018963967 ----- ----- Type BIT(56) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(56) DEFAULT NULL, `cf` bigint(17) unsigned DEFAULT NULL, `cc` bigint(17) unsigned DEFAULT NULL, `cr` bigint(17) unsigned DEFAULT NULL, `ct` bigint(17) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 72057594037927935 cf 72057594037927935 cc 72057594037927935 cr 72057594037927935 ct 72057594037927935 ----- ----- Type BIT(57) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(57) DEFAULT NULL, `cf` bigint(18) unsigned DEFAULT NULL, `cc` bigint(18) unsigned DEFAULT NULL, `cr` bigint(18) unsigned DEFAULT NULL, `ct` bigint(18) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 144115188075855871 cf 144115188075855871 cc 144115188075855871 cr 144115188075855871 ct 144115188075855871 ----- ----- Type BIT(58) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(58) DEFAULT NULL, `cf` bigint(18) unsigned DEFAULT NULL, `cc` bigint(18) unsigned DEFAULT NULL, `cr` bigint(18) unsigned DEFAULT NULL, `ct` bigint(18) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 288230376151711743 cf 288230376151711743 cc 288230376151711743 cr 288230376151711743 ct 288230376151711743 ----- ----- Type BIT(59) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(59) DEFAULT NULL, `cf` bigint(18) unsigned DEFAULT NULL, `cc` bigint(18) unsigned DEFAULT NULL, `cr` bigint(18) unsigned DEFAULT NULL, `ct` bigint(18) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 576460752303423487 cf 576460752303423487 cc 576460752303423487 cr 576460752303423487 ct 576460752303423487 ----- ----- Type BIT(60) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(60) DEFAULT NULL, `cf` bigint(19) unsigned DEFAULT NULL, `cc` bigint(19) unsigned DEFAULT NULL, `cr` bigint(19) unsigned DEFAULT NULL, `ct` bigint(19) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 1152921504606846975 cf 1152921504606846975 cc 1152921504606846975 cr 1152921504606846975 ct 1152921504606846975 ----- ----- Type BIT(61) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(61) DEFAULT NULL, `cf` bigint(19) unsigned DEFAULT NULL, `cc` bigint(19) unsigned DEFAULT NULL, `cr` bigint(19) unsigned DEFAULT NULL, `ct` bigint(19) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 2305843009213693951 cf 2305843009213693951 cc 2305843009213693951 cr 2305843009213693951 ct 2305843009213693951 ----- ----- Type BIT(62) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(62) DEFAULT NULL, `cf` bigint(19) unsigned DEFAULT NULL, `cc` bigint(19) unsigned DEFAULT NULL, `cr` bigint(19) unsigned DEFAULT NULL, `ct` bigint(19) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 4611686018427387903 cf 4611686018427387903 cc 4611686018427387903 cr 4611686018427387903 ct 4611686018427387903 ----- ----- Type BIT(63) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(63) DEFAULT NULL, `cf` bigint(19) unsigned DEFAULT NULL, `cc` bigint(19) unsigned DEFAULT NULL, `cr` bigint(19) unsigned DEFAULT NULL, `ct` bigint(19) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9223372036854775807 cf 9223372036854775807 cc 9223372036854775807 cr 9223372036854775807 ct 9223372036854775807 ----- ----- Type BIT(64) Table t2 Create Table CREATE TABLE `t2` ( `a` bit(64) DEFAULT NULL, `cf` bigint(20) unsigned DEFAULT NULL, `cc` bigint(20) unsigned DEFAULT NULL, `cr` bigint(20) unsigned DEFAULT NULL, `ct` bigint(20) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 18446744073709551615 cf 18446744073709551615 cc 18446744073709551615 cr 18446744073709551615 ct 18446744073709551615 # # End of 10.4 tests # # # Start of 10.5 tests # # # MDEV-20496 Assertion `field.is_sane()' failed in Protocol_text::store_field_metadata # CREATE TABLE t1 (b BIT(1)); SELECT MIN(CASE WHEN 0 THEN b END) FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def MIN(CASE WHEN 0 THEN b END) 8 1 0 Y 32928 0 63 MIN(CASE WHEN 0 THEN b END) NULL CREATE TABLE t2 AS SELECT MIN(CASE WHEN 0 THEN b END) FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `MIN(CASE WHEN 0 THEN b END)` bigint(1) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t2; DROP TABLE t1; # # End of 10.5 tests #