path: root/mysql-test/main/long_unique.test

let datadir=`select @@datadir`;
--source include/have_partition.inc

#
# MDEV-371 Unique indexes for blobs
#

--echo #Structure of tests
--echo #First we will check all option for
--echo #table containing single unique column
--echo #table containing keys like unique(a,b,c,d) etc
--echo #then table containing 2 blob unique etc
set @allowed_packet= @@max_allowed_packet;
--echo #table with single long blob column;
create table t1(a blob  unique );
insert into t1 values(1),(2),(3),(56),('sachin'),('maria'),(123456789034567891),(null),(null),(123456789034567890);
--echo #blob with primary key not allowed
--error ER_TOO_LONG_KEY
create table t2(a blob,primary key(a(10000)));
--error ER_TOO_LONG_KEY
create table t3(a varchar(10000) primary key);

--error ER_DUP_ENTRY
insert into t1 values(2);
--echo #table structure;
desc t1;
show create table t1;
query_vertical show keys from t1;
replace_result $datadir DATADIR;
exec $MYISAMCHK -d $datadir/test/t1;
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE,COLUMN_KEY,EXTRA,COLUMN_COMMENT,IS_GENERATED,GENERATION_EXPRESSION from information_schema.columns where table_schema = 'test' and table_name = 't1';
select * from information_schema.statistics where table_schema = 'test' and table_name = 't1';
query_vertical select   * from information_schema.key_column_usage where table_schema= 'test' and table_name= 't1';
--echo # table select we should not be able to see db_row_hash_column;
select * from t1 order by a;
--error ER_BAD_FIELD_ERROR
select db_row_hash_1 from t1;
--echo #duplicate entry test;
--error ER_DUP_ENTRY
insert into t1 values(2);
--error ER_DUP_ENTRY
insert into t1 values('sachin');
--error ER_DUP_ENTRY
insert into t1 values(123456789034567891);
select * from t1 order by a;
insert into t1 values(11),(22),(33);
--error ER_DUP_ENTRY
insert into t1 values(12),(22);
select * from t1 order by a;
insert into t1 values(repeat('s',4000*10)),(repeat('s',4001*10));
--error ER_DUP_ENTRY
insert into t1 values(repeat('m',4000*10)),(repeat('m',4000*10));
insert into t1 values(repeat('m',4001)),(repeat('m',4002));
truncate table t1;
insert into t1 values(1),(2),(3),(4),(5),(8),(7);
replace_result $datadir DATADIR;
exec $MYISAMCHK -d $datadir/test/t1;
--echo #now some alter commands;
alter table t1 add column b int;
desc t1;
show create table t1;
--error ER_DUP_ENTRY
insert into t1 values(1,2);
--error ER_DUP_ENTRY
insert into t1 values(2,2);
--error ER_BAD_FIELD_ERROR
select db_row_hash_1 from t1;
--echo #now try to change db_row_hash_1 column;
--error ER_CANT_DROP_FIELD_OR_KEY
alter table t1 drop column db_row_hash_1;
--error ER_CANT_DROP_FIELD_OR_KEY
alter table t1 add column d int , add column e int , drop column db_row_hash_1;
--error ER_BAD_FIELD_ERROR
alter table t1 modify column db_row_hash_1 int ;
--error ER_BAD_FIELD_ERROR
alter table t1 add column a int , add column b int, modify column db_row_hash_1 int ;
--error ER_BAD_FIELD_ERROR
alter table t1 change column db_row_hash_1 dsds int;
--error ER_BAD_FIELD_ERROR
alter table t1 add column asd int, change column db_row_hash_1 dsds int;
alter table t1 drop column b , add column c int;
show create table t1;

--echo #now add some column with name db_row_hash;
alter table t1 add column db_row_hash_1 int unique;
show create table t1;
--error ER_DUP_ENTRY
insert into t1 values(45,1,55),(46,1,55);
--error ER_DUP_ENTRY
insert into t1 values(45,1,55),(45,1,55);
alter table t1 add column db_row_hash_2 int, add column db_row_hash_3 int;
desc t1;
--echo #this should also drop the unique index ;
alter table t1 drop column a;
show create table t1;
show keys from t1;
--echo #add column with unique index on blob ;
alter table t1 add column a blob unique;
show create table t1;
--echo # try to change the blob unique name;
alter table t1 change column a aa blob ;
show create table t1;
show keys from t1;
--echo # try to change the blob unique datatype;
--echo #this will change index to b tree;
alter table t1 modify  column aa  int ;
show create table t1;
show keys from t1;
alter table t1 add column clm blob unique;
--echo #try changing the name ;
alter table t1 change column clm clm_changed blob;
show create table t1;
show keys from t1;
--echo #now drop the unique key;
alter table t1 drop key clm;
show create table t1;
show keys from t1;
drop table t1;

create table t1 (a TEXT CHARSET latin1 COLLATE latin1_german2_ci unique);
desc t1;
show keys from t1;
 insert into t1 values ('ae');
--error ER_DUP_ENTRY
insert into t1 values ('AE');
insert  into t1 values ('Ä');
drop table t1;
create table t1 (a int primary key, b blob unique);
desc t1;
show keys from t1;
insert into t1 values(1,1),(2,2),(3,3);
--error ER_DUP_ENTRY
insert into t1 values(1,1);
--error ER_DUP_ENTRY
insert into t1 values(7,1);
drop table t1;

--echo #table with multiple  long blob column and varchar text column ;
create table t1(a blob unique, b int , c blob unique , d text unique , e varchar(3000) unique);
insert into t1 values(1,2,3,4,5),(2,11,22,33,44),(3111,222,333,444,555),(5611,2222,3333,4444,5555),
('sachin',341,'fdf','gfgfgfg','hghgr'),('maria',345,'frter','dasd','utyuty'),
(123456789034567891,353534,53453453453456,64565464564564,45435345345345),
(123456789034567890,43545,657567567567,78967657567567,657567567567567676);

--echo #table structure;
desc t1;
show create table t1;
show keys from t1;
replace_result $datadir DATADIR;
exec $MYISAMCHK -d $datadir/test/t1;
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE,COLUMN_KEY,EXTRA,COLUMN_COMMENT,IS_GENERATED,GENERATION_EXPRESSION  from information_schema.columns where table_schema = 'test' and table_name = 't1';
select * from information_schema.statistics where table_schema = 'test' and table_name = 't1';
select * from information_schema.key_column_usage where table_schema= 'test' and table_name= 't1';
--echo #table select we should not be able to see db_row_hash_1 column;
select * from t1 order by a;
--error ER_BAD_FIELD_ERROR
select db_row_hash_1 from t1;
--error ER_BAD_FIELD_ERROR
select db_row_hash_2 from t1;
--error ER_BAD_FIELD_ERROR
select db_row_hash_3 from t1;
--echo #duplicate entry test;
--error ER_DUP_ENTRY
insert into t1 values(21,2,3,42,51);
--error ER_DUP_ENTRY
insert into t1 values('sachin',null,null,null,null);
--error ER_DUP_ENTRY
insert into t1 values(1234567890345671890,4353451,6575675675617,789676575675617,657567567567567676);
select * from t1 order by a;
insert into t1 values(repeat('s',4000*10),100,repeat('s',4000*10),repeat('s',4000*10),
repeat('s',400)),(repeat('s',4001*10),1000,repeat('s',4001*10),repeat('s',4001*10),
repeat('s',2995));
--error ER_DUP_ENTRY
insert into t1 values(repeat('m',4000*11),10,repeat('s',4000*11),repeat('s',4000*11),repeat('s',2995));
truncate table t1;
insert into t1 values(1,2,3,4,5),(2,11,22,33,44),(3111,222,333,444,555),(5611,2222,3333,4444,5555);
--echo #now some alter commands;
alter table t1 add column f int;
desc t1;
show create table t1;
--echo #unique key should not break;
--error ER_DUP_ENTRY
insert into t1 values(1,2,3,4,5,6);
--error ER_BAD_FIELD_ERROR
select db_row_hash_1 , db_row_hash_2, db_row_hash_3 from t1;
--echo #now try to change db_row_hash_1 column;
--error ER_CANT_DROP_FIELD_OR_KEY
alter table t1 drop column db_row_hash_1, drop column db_row_hash_2, drop column db_row_hash_3;
--error ER_CANT_DROP_FIELD_OR_KEY
alter table t1 add column dg int , add column ef int , drop column db_row_hash_1;
--error ER_BAD_FIELD_ERROR
alter table t1 modify column db_row_hash_1 int , modify column db_row_hash_2 int, modify column db_row_hash_3 int;
--error ER_BAD_FIELD_ERROR
alter table t1 add column ar int , add column rb int, modify column db_row_hash_1 int , modify column db_row_hash_3 int;
--error ER_BAD_FIELD_ERROR
alter table t1 change column db_row_hash_1 dsds int , change column  db_row_hash_2  dfdf int , change column db_row_hash_3 gdfg int ;
--error ER_BAD_FIELD_ERROR
alter table t1 add column asd int, drop column a, change column db_row_hash_1 dsds int, change db_row_hash_3 fdfdfd int;
alter table t1 drop column b , add column g int;
show create table t1;

--echo #now add some column with name db_row_hash;
alter table t1 add column db_row_hash_1 int unique;
alter table t1 add column db_row_hash_2 int unique;
alter table t1 add column db_row_hash_3 int unique;
show create table t1;

alter table t1 add column db_row_hash_7 int, add column db_row_hash_5 int , add column db_row_hash_4 int ;
alter table t1 drop column db_row_hash_7,drop column db_row_hash_3, drop column db_row_hash_4;
desc t1;
--echo #this should not break anything;
--error ER_DUP_ENTRY
insert into t1 values(1,2,3,4,5,6,23,5,6);
--echo #this should also drop the unique index;
alter table t1 drop column a, drop column c;
show create table t1;
show keys from t1;
--echo #add column with unique index on blob;
alter table t1 add column a blob unique;
show create table t1;
show keys from t1;
--echo #try to change the blob unique column name;
--echo #this will change index to b tree;
alter table t1 modify  column a  int ,  modify column e int;
show create table t1;
show keys from t1;
alter table t1 add column clm1 blob unique,add column clm2 blob unique;
--echo #try changing the name; 
alter table t1 change column clm1 clm_changed1 blob, change column clm2 clm_changed2 blob;
show create table t1;
show keys from t1;
--echo #now drop the unique key;
alter table t1 drop key clm1, drop key clm2;
show create table t1;
show keys from t1;
drop table t1;
--echo #now the table with key on multiple columns; the ultimate test;
create table t1(a blob, b int , c varchar(2000) , d text  , e varchar(3000) , f longblob , g int , h text ,
  unique(a,b,c), unique(c,d,e),unique(e,f,g,h), unique(b,d,g,h));

insert into t1 values(1,1,1,1,1,1,1,1),(2,2,2,2,2,2,2,2),(3,3,3,3,3,3,3,3),(4,4,4,4,4,4,4,4),(5,5,5,5,5,5,5,5),
('maria',6,'maria','maria','maria','maria',6,'maria'),('mariadb',7,'mariadb','mariadb','mariadb','mariadb',8,'mariadb')
,(null,null,null,null,null,null,null,null),(null,null,null,null,null,null,null,null);

--echo #table structure;
desc t1;
show create table t1;
show keys from t1;
replace_result $datadir DATADIR;
exec $MYISAMCHK -d $datadir/test/t1;
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE,COLUMN_KEY,EXTRA,COLUMN_COMMENT,IS_GENERATED,GENERATION_EXPRESSION  from information_schema.columns where table_schema = 'test' and table_name = 't1';
select * from information_schema.statistics where table_schema = 'test' and table_name = 't1';
select * from information_schema.key_column_usage where table_schema= 'test' and table_name= 't1';
--echo # table select we should not be able to see db_row_hash_1 column;
select * from t1 order by a;
--error ER_BAD_FIELD_ERROR
select db_row_hash_1 from t1;
--error ER_BAD_FIELD_ERROR
select db_row_hash_2 from t1;
--error ER_BAD_FIELD_ERROR
select db_row_hash_3 from t1;
--echo #duplicate entry test;
--echo #duplicate keys entry;
--error ER_DUP_ENTRY
insert into t1 values(1,1,1,0,0,0,0,0);
--error ER_DUP_ENTRY
insert into t1 values(0,0,1,1,1,0,0,0);
--error ER_DUP_ENTRY
insert into t1 values(0,0,0,0,1,1,1,1);
--error ER_DUP_ENTRY
insert into t1 values(1,1,1,1,1,0,0,0);
--error ER_DUP_ENTRY
insert into t1 values(0,0,0,0,1,1,1,1);
--error ER_DUP_ENTRY
insert into t1 values(1,1,1,1,1,1,1,1);
--error ER_BAD_FIELD_ERROR
select db_row_hash_1,db_row_hash_2,db_row_hash_3,db_row_hash_4,db_row_hash_5 from t1;
--error ER_CANT_DROP_FIELD_OR_KEY
alter table t1 drop column db_row_hash_1, drop column db_row_hash_2, drop column db_row_hash_3;
--error ER_CANT_DROP_FIELD_OR_KEY
alter table t1 add column dg int , add column ef int , drop column db_row_hash_1;
--error ER_BAD_FIELD_ERROR
alter table t1 modify column db_row_hash_1 int , modify column db_row_hash_2 int, modify column db_row_hash_3 int;
--error ER_BAD_FIELD_ERROR
alter table t1 add column ar int , add column rb int, modify column db_row_hash_1 int , modify column db_row_hash_3 int;
--error ER_BAD_FIELD_ERROR
alter table t1 change column db_row_hash_1 dsds int , change column  db_row_hash_2  dfdf int , change column db_row_hash_3 gdfg int ;
--error ER_BAD_FIELD_ERROR
alter table t1 add column asd int, drop column a, change column db_row_hash_1 dsds int, change db_row_hash_3 fdfdfd int;

show create table t1;
--echo # add column named db_row_hash_*;
alter table t1 add column db_row_hash_7 int , add column db_row_hash_5 int,
 add column db_row_hash_1 int, add column db_row_hash_2 int;
show create table t1;
show keys from t1;
alter table t1 drop column db_row_hash_7  , drop column db_row_hash_5 ,
 drop column db_row_hash_1, drop column db_row_hash_2 ;
show create table t1;
show keys from t1;

--echo #try to change column names;
alter table t1 change column a aa blob , change column b bb blob , change column d dd blob;
show create table t1;
show keys from t1;
alter table t1 change column aa a blob , change column bb b blob , change column dd d blob;
show create table t1;
show keys from t1;

--echo #now we will change the data type to int and varchar limit so that we no longer  require hash_index;
--echo #on key a_b_c;
alter table t1 modify column a varchar(20) , modify column b varchar(20) , modify column c varchar(20);
show create table t1;
show keys from t1;
--echo #change it back;
alter table t1 modify column a blob , modify column b blob , modify column c blob;
show create table t1;
show keys from t1;

--echo #try to delete blob column in unique;
truncate table t1;
## this feature removed in 10.2
#alter table t1 drop column a, drop column b, drop  column c;
#show create table t1;
#show keys from t1;
--echo #now try to delete keys;
alter table t1 drop key c, drop key e;
show create table t1;
show keys from t1;
drop table t1;

--echo #now alter table containing some data basically some tests with ignore;
create table t1 (a blob);
insert into t1 values(1),(2),(3);
--echo #normal alter table;
alter table t1 add unique key(a);
alter table t1 drop key a;
truncate table t1;
insert into t1 values(1),(1),(2),(2),(3);
--error ER_DUP_ENTRY
alter table t1 add unique key(a);
alter ignore table t1 add unique key(a);
select * from t1 order by a;
--error ER_DUP_ENTRY
insert into t1 values(1);
show create table t1;
show keys from t1;
drop table t1;

--echo #Now with multiple keys;
create table t1(a blob , b blob, c blob , d blob , e int);
insert into t1 values (1,1,1,1,1);
insert into t1 values (1,1,1,1,1);
insert into t1 values (2,1,1,1,1);
insert into t1 values (2,2,2,2,2);
insert into t1 values (3,3,4,4,4);
insert into t1 values (4,4,4,4,4);
--error ER_DUP_ENTRY
alter table t1 add unique key(a,c), add unique key(b,d), add unique key(e);
alter ignore  table t1 add unique key(a,c), add unique key(b,d), add unique key(e);
select * from t1 order by a;
--error ER_DUP_ENTRY
insert into t1 values (1,12,1,13,14);
--error ER_DUP_ENTRY
insert into t1 values (12,1,14,1,14);
--error ER_DUP_ENTRY
insert into t1 values (13,12,13,14,4);
show create table t1;
show keys from t1;
drop table t1;

--echo #visibility of db_row_hash 
create table t1 (a blob unique , b blob unique);
desc t1;
insert into t1 values(1,19);
insert into t1 values(2,29);
insert into t1 values(3,39);
insert into t1 values(4,49);
create table t2 (DB_ROW_HASH_1 int, DB_ROW_HASH_2 int);
insert into t2 values(11,1);
insert into t2 values(22,2);
insert into t2 values(33,3);
insert into t2 values(44,4);
select * from t1 order by a;
select * from t2 order by DB_ROW_HASH_1;
--error ER_BAD_FIELD_ERROR
select DB_ROW_HASH_1, DB_ROW_HASH_2 from t1;
--echo #bug
select DB_ROW_HASH_1, DB_ROW_HASH_2 from t1,t2;
--error ER_BAD_FIELD_ERROR
select * from t1 where DB_ROW_HASH_1 in (select DB_ROW_HASH_1 from t2);
select DB_ROW_HASH_1, DB_ROW_HASH_2 from t1,t2 where DB_ROW_HASH_1 in (select DB_ROW_HASH_1 from t2);
select * from t2 where DB_ROW_HASH_1 in (select DB_ROW_HASH_1 from t1);
--error ER_BAD_FIELD_ERROR
select DB_ROW_HASH_1 from t1,t2 where t1.DB_ROW_HASH_1 = t2.DB_ROW_HASH_2;
select DB_ROW_HASH_1 from t1 inner join t2 on t1.a = t2.DB_ROW_HASH_2;
drop table t1,t2;

--echo #very long blob entry;
SET @@GLOBAL.max_allowed_packet=67108864;

connect ('newcon', localhost, root,,);
--connection newcon
show variables like 'max_allowed_packet';
create table t1(a longblob unique, b longblob , c longblob , unique(b,c));
desc t1;
show create table t1;
show keys from t1;
insert into t1 values(concat(repeat('sachin',10000000),'1'),concat(repeat('sachin',10000000),'1'),
concat(repeat('sachin',10000000),'1'));
insert into t1 values(concat(repeat('sachin',10000000),'2'),concat(repeat('sachin',10000000),'2'),
concat(repeat('sachin',10000000),'1'));
--error ER_DUP_ENTRY
insert into t1 values(concat(repeat('sachin',10000000),'2'),concat(repeat('sachin',10000000),'2'),
concat(repeat('sachin',10000000),'4'));
--error ER_DUP_ENTRY
insert into t1 values(concat(repeat('sachin',10000000),'3'),concat(repeat('sachin',10000000),'1'),
concat(repeat('sachin',10000000),'1'));
drop table t1;

--echo #long key unique with different key length
create table t1(a blob, unique(a(3000)));
desc t1;
show keys from t1;
show create table t1;
insert into t1 value(concat(repeat('s',3000),'1'));
--error ER_DUP_ENTRY
insert into t1 value(concat(repeat('s',3000),'2'));
insert into t1 value(concat(repeat('a',3000),'2'));
drop table t1;

create table t1(a varchar(4000), b longblob , c varchar(5000), d longblob, 
unique(a(3500), b), unique(c(4500), d));
desc t1;
show create table t1;
show keys from t1;
drop table t1;
disconnect newcon;
--connection default
SET @@GLOBAL.max_allowed_packet=4194304;
--echo #ext bug
create table t1(a int primary key, b blob unique, c int, d blob , index(c));
show create table t1;
insert into t1 values(1,23,1,33);
--error ER_DUP_ENTRY
insert into t1 values(2,23,1,33);
drop table t1;
create table t2 (a blob unique , c int , index(c));
show create table t2;
insert into t2 values(1,1);
insert into t2 values(2,1);
drop table t2;
--echo #not null test
create table t1(a blob unique not null);
desc t1;
show create table t1;
insert into t1 values(1);
insert into t1 values(3);
--error ER_DUP_ENTRY
insert into t1 values(1);
drop table t1;
create table t1(a int primary key, b blob unique , c blob unique not null);
insert into t1 values(1,1,1);
--error ER_DUP_ENTRY
insert into t1 values(2,1,2);
--error ER_DUP_ENTRY
insert into t1 values(3,3,1);
drop table t1;
create table t1 (a blob unique not null, b blob not null, c blob not null, unique(b,c));
desc t1;
show create table t1;
insert into t1 values (1, 2, 3);
insert into t1 values (2, 1, 3);
--error ER_DUP_ENTRY
insert into t1 values (2, 1, 3);
drop table t1;

--echo #partition
--error ER_BLOB_FIELD_IN_PART_FUNC_ERROR
create table t1(a blob unique) partition by hash(a);
--echo #key length > 2^16 -1
--error ER_TOO_LONG_KEYPART
create table t1(a blob, unique(a(65536)));
create table t1(a blob, unique(a(65535)));
show create table t1;
drop table t1;

--echo #64 indexes
--let $create_table=create table t1 (
--let $insert_data_1=insert into t1 values(
--let $insert_data_2=insert into t1 values(
--let $count= 63
--let $index= 0
while ($count)
{
  --let $create_table=$create_table a$count blob unique,
  --let $insert_data_1=$insert_data_1 $count,
  --let $insert_data_2=$insert_data_2 $index,
  --dec $count
  --inc $index
}
--let $create_table=$create_table a blob unique);
--let $insert_data_1=$insert_data_1 0);
--let $insert_data_2=$insert_data_2 63);

--eval $create_table
--eval $insert_data_1
--error ER_DUP_ENTRY
--eval $insert_data_1
--eval $insert_data_2
--error ER_DUP_ENTRY
--eval $insert_data_2
drop table t1;

set @@GLOBAL.max_allowed_packet= @allowed_packet;