SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent; SET GLOBAL innodb_stats_persistent=0; 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 create table t1 (a bit(65)) engine=innodb; ERROR 42000: Display width out of range for '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 COLLATE=latin1_swedish_ci 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 ignore 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 ignore 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 # 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)) 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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 create table t1(f1 bit(2) not null default b'10',f2 bit(14) not null default b'11110000111100'); insert into t1 (f1) values (default); insert into t1 values (b'',b''),('',''); select hex(f1), hex(f2) from t1; hex(f1) hex(f2) 2 3C3C 0 0 0 0 drop table t1; SET GLOBAL innodb_stats_persistent=@save_stats_persistent; # # MDEV-32203 Raise notes when an index cannot be used on data type mismatch # SET note_verbosity=unusable_keys; CREATE TABLE t1 (indexed_col BIT(64), KEY(indexed_col)) ENGINE=InnoDB; FOR i IN 1..31 DO INSERT INTO t1 VALUES (CONCAT(20230100+i)); END FOR; $$ SELECT * FROM t1 WHERE indexed_col=20230101; indexed_col SELECT * FROM t1 WHERE indexed_col=20230101102030; indexed_col SELECT * FROM t1 WHERE indexed_col=20230101102030.1; indexed_col SELECT * FROM t1 WHERE indexed_col=20230101102030.1e0; indexed_col SELECT * FROM t1 WHERE indexed_col='10:20:30'; indexed_col SELECT * FROM t1 WHERE indexed_col='2001-01-01'; indexed_col SELECT * FROM t1 WHERE indexed_col='2001-01-01 10:20:30'; indexed_col SELECT * FROM t1 WHERE indexed_col=DATE'2001-01-01'; indexed_col Warnings: Note 1105 Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `bit` = "DATE'2001-01-01'" of type `date` SELECT * FROM t1 WHERE indexed_col=TIME'10:20:30'; indexed_col Warnings: Note 1105 Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `bit` = "TIME'10:20:30'" of type `time` Warning 1292 Truncated incorrect time value: '20230101' Warning 1292 Truncated incorrect time value: '20230102' Warning 1292 Truncated incorrect time value: '20230103' Warning 1292 Truncated incorrect time value: '20230104' Warning 1292 Truncated incorrect time value: '20230105' Warning 1292 Truncated incorrect time value: '20230106' Warning 1292 Truncated incorrect time value: '20230107' Warning 1292 Truncated incorrect time value: '20230108' Warning 1292 Truncated incorrect time value: '20230109' Warning 1292 Truncated incorrect time value: '20230110' Warning 1292 Truncated incorrect time value: '20230111' Warning 1292 Truncated incorrect time value: '20230112' Warning 1292 Truncated incorrect time value: '20230113' Warning 1292 Truncated incorrect time value: '20230114' Warning 1292 Truncated incorrect time value: '20230115' Warning 1292 Truncated incorrect time value: '20230116' Warning 1292 Truncated incorrect time value: '20230117' Warning 1292 Truncated incorrect time value: '20230118' Warning 1292 Truncated incorrect time value: '20230119' Warning 1292 Truncated incorrect time value: '20230120' Warning 1292 Truncated incorrect time value: '20230121' Warning 1292 Truncated incorrect time value: '20230122' Warning 1292 Truncated incorrect time value: '20230123' Warning 1292 Truncated incorrect time value: '20230124' Warning 1292 Truncated incorrect time value: '20230125' Warning 1292 Truncated incorrect time value: '20230126' Warning 1292 Truncated incorrect time value: '20230127' Warning 1292 Truncated incorrect time value: '20230128' Warning 1292 Truncated incorrect time value: '20230129' Warning 1292 Truncated incorrect time value: '20230130' Warning 1292 Truncated incorrect time value: '20230131' SELECT * FROM t1 WHERE indexed_col=TIMESTAMP'2001-01-01 10:20:30'; indexed_col Warnings: Note 1105 Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `bit` = "TIMESTAMP'2001-01-01 10:20:30'" of type `datetime` SELECT * FROM t1 WHERE indexed_col=0x00; indexed_col SELECT * FROM t1 WHERE indexed_col=_utf8mb3'0' COLLATE utf8mb3_bin; indexed_col CREATE TABLE t2 (not_indexed_col INT); INSERT INTO t2 VALUES (20230101),(20230102); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col DROP TABLE t2; CREATE TABLE t2 (not_indexed_col INT UNSIGNED); INSERT INTO t2 VALUES (20230101),(20230102); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col DROP TABLE t2; CREATE TABLE t2 (not_indexed_col BIGINT); INSERT INTO t2 VALUES (20230101102030),(20230101102031); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col DROP TABLE t2; CREATE TABLE t2 (not_indexed_col BIGINT UNSIGNED); INSERT INTO t2 VALUES (20230101102030),(20230101102031); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col DROP TABLE t2; CREATE TABLE t2 (not_indexed_col DECIMAL(30,6)); INSERT INTO t2 VALUES (20230101102030),(20230101102031); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col DROP TABLE t2; CREATE TABLE t2 (not_indexed_col FLOAT); INSERT INTO t2 VALUES (20230101102030),(20230101102031); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col DROP TABLE t2; CREATE TABLE t2 (not_indexed_col DOUBLE); INSERT INTO t2 VALUES (20230101102030),(20230101102031); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col DROP TABLE t2; CREATE TABLE t2 (not_indexed_col DATE); INSERT INTO t2 VALUES ('2023-01-01'),('2023-01-02'); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col 20230101 2023-01-01 20230102 2023-01-02 Warnings: Note 1105 Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `bit` = "`t2`.`not_indexed_col`" of type `date` DROP TABLE t2; CREATE TABLE t2 (not_indexed_col DATETIME); INSERT INTO t2 VALUES ('2023-01-01 00:00:00'),('2023-01-01 00:00:01'); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col 20230101 2023-01-01 00:00:00 Warnings: Note 1105 Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `bit` = "`t2`.`not_indexed_col`" of type `datetime` DROP TABLE t2; CREATE TABLE t2 (not_indexed_col TIMESTAMP); INSERT INTO t2 VALUES ('2023-01-01 00:00:00'),('2023-01-01 00:00:01'); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col 20230101 2023-01-01 00:00:00 Warnings: Note 1105 Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `bit` = "`t2`.`not_indexed_col`" of type `timestamp` DROP TABLE t2; CREATE TABLE t2 (not_indexed_col VARBINARY(32)); INSERT INTO t2 VALUES (0x30),(0x31); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col DROP TABLE t2; CREATE TABLE t2 (not_indexed_col VARCHAR(32)); INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02'); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col Warnings: Warning 1292 Truncated incorrect INTEGER value: '2001-01-01' Warning 1292 Truncated incorrect INTEGER value: '2001-01-02' DROP TABLE t2; CREATE TABLE t2 (not_indexed_col VARCHAR(32) CHARACTER SET utf8mb3); INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02'); SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col; indexed_col not_indexed_col Warnings: Warning 1292 Truncated incorrect INTEGER value: '2001-01-01' Warning 1292 Truncated incorrect INTEGER value: '2001-01-02' DROP TABLE t2; DROP TABLE t1; SET note_verbosity=DEFAULT;