diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb_index_large_prefix.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb_index_large_prefix.result | 185 |
1 files changed, 185 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb_index_large_prefix.result b/mysql-test/suite/innodb/r/innodb_index_large_prefix.result new file mode 100644 index 00000000000..6e2d3527ee9 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_index_large_prefix.result @@ -0,0 +1,185 @@ +set global innodb_file_format="Barracuda"; +set global innodb_file_per_table=1; +set global innodb_large_prefix=1; +create table worklog5743(a TEXT not null, primary key (a(1000))) +ROW_FORMAT=DYNAMIC, engine = innodb; +insert into worklog5743 values(repeat("a", 20000)); +update worklog5743 set a = (repeat("b", 16000)); +create index idx on worklog5743(a(2000)); +begin; +update worklog5743 set a = (repeat("x", 17000)); +select @@session.tx_isolation; +@@session.tx_isolation +REPEATABLE-READ +select a = repeat("x", 17000) from worklog5743; +a = repeat("x", 17000) +0 +select a = repeat("b", 16000) from worklog5743; +a = repeat("b", 16000) +1 +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +@@session.tx_isolation +READ-UNCOMMITTED +select a = repeat("x", 17000) from worklog5743; +a = repeat("x", 17000) +1 +rollback; +drop table worklog5743; +create table worklog5743(a1 int, a2 TEXT not null) +ROW_FORMAT=DYNAMIC, engine = innodb; +create index idx on worklog5743(a1, a2(2000)); +insert into worklog5743 values(9, repeat("a", 10000)); +begin; +update worklog5743 set a1 = 1000; +select @@session.tx_isolation; +@@session.tx_isolation +REPEATABLE-READ +explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE worklog5743 ref idx idx 5 const 1 Using where +select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; +a1 a2 = repeat("a", 10000) +9 1 +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +@@session.tx_isolation +READ-UNCOMMITTED +select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; +a1 a2 = repeat("a", 10000) +rollback; +drop table worklog5743; +create table worklog5743(a1 int, a2 TEXT not null) +ROW_FORMAT=DYNAMIC, engine = innodb; +create index idx on worklog5743(a1, a2(50)); +insert into worklog5743 values(9, repeat("a", 10000)); +begin; +update worklog5743 set a1 = 1000; +select @@session.tx_isolation; +@@session.tx_isolation +REPEATABLE-READ +explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE worklog5743 ref idx idx 5 const 1 Using where +select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; +a1 a2 = repeat("a", 10000) +9 1 +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +@@session.tx_isolation +READ-UNCOMMITTED +select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; +a1 a2 = repeat("a", 10000) +rollback; +drop table worklog5743; +create table worklog5743_2(a1 int, a2 TEXT not null) +ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2, engine = innodb; +create table worklog5743_4(a1 int, a2 TEXT not null) +ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, engine = innodb; +create index idx1 on worklog5743_2(a1, a2(942)); +ERROR HY000: Too big row +create index idx1 on worklog5743_2(a1, a2(940)); +create index idx1 on worklog5743_4(a1, a2(1966)); +ERROR HY000: Too big row +create index idx1 on worklog5743_4(a1, a2(1964)); +insert into worklog5743_2 values(9, repeat("a", 10000)); +insert into worklog5743_4 values(9, repeat("a", 10000)); +begin; +update worklog5743_2 set a1 = 1000; +update worklog5743_4 set a1 = 1000; +select @@session.tx_isolation; +@@session.tx_isolation +REPEATABLE-READ +explain select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE worklog5743_2 ref idx1 idx1 5 const 1 Using where +select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; +a1 a2 = repeat("a", 10000) +9 1 +select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; +a1 a2 = repeat("a", 10000) +9 1 +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +@@session.tx_isolation +READ-UNCOMMITTED +select a1, a2 = repeat("a", 10000) from worklog5743_2 where a1 = 9; +a1 a2 = repeat("a", 10000) +select a1, a2 = repeat("a", 10000) from worklog5743_4 where a1 = 9; +a1 a2 = repeat("a", 10000) +rollback; +drop table worklog5743_2; +drop table worklog5743_4; +create table worklog5743(a1 int, a2 varchar(3000)) +ROW_FORMAT=DYNAMIC, engine = innodb; +create index idx on worklog5743(a1, a2); +insert into worklog5743 values(9, repeat("a", 3000)); +begin; +update worklog5743 set a1 = 1000; +select @@session.tx_isolation; +@@session.tx_isolation +REPEATABLE-READ +explain select a1 from worklog5743 where a1 = 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE worklog5743 ref idx idx 5 const 1 Using where; Using index +select a1 from worklog5743 where a1 = 9; +a1 +9 +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +@@session.tx_isolation +READ-UNCOMMITTED +select a1 from worklog5743 where a1 = 9; +a1 +rollback; +drop table worklog5743; +create table worklog5743(a TEXT not null, primary key (a(1000))) +engine = innodb; +ERROR HY000: Index column size too large. The maximum column size is 767 bytes. +create table worklog5743(a TEXT) engine = innodb; +create index idx on worklog5743(a(1000)); +ERROR HY000: Index column size too large. The maximum column size is 767 bytes. +create index idx on worklog5743(a(725)); +insert into worklog5743 values(repeat("a", 20000)); +begin; +insert into worklog5743 values(repeat("b", 20000)); +update worklog5743 set a = (repeat("x", 25000)); +select @@session.tx_isolation; +@@session.tx_isolation +REPEATABLE-READ +select a = repeat("a", 20000) from worklog5743; +a = repeat("a", 20000) +1 +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +select @@session.tx_isolation; +@@session.tx_isolation +READ-UNCOMMITTED +select a = repeat("x", 25000) from worklog5743; +a = repeat("x", 25000) +1 +1 +rollback; +drop table worklog5743; +create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb; +create index idx on worklog5743(a(3073)); +Warnings: +Warning 1071 Specified key was too long; max key length is 3072 bytes +Warning 1071 Specified key was too long; max key length is 3072 bytes +create index idx2 on worklog5743(a(3072)); +show create table worklog5743; +Table Create Table +worklog5743 CREATE TABLE `worklog5743` ( + `a` text NOT NULL, + KEY `idx` (`a`(3072)), + KEY `idx2` (`a`(3072)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +drop table worklog5743; +create table worklog5743(a TEXT not null) engine = innodb; +create index idx on worklog5743(a(768)); +ERROR HY000: Index column size too large. The maximum column size is 767 bytes. +create index idx2 on worklog5743(a(767)); +drop table worklog5743; +SET GLOBAL innodb_file_format=Antelope; +SET GLOBAL innodb_file_per_table=0; +SET GLOBAL innodb_file_format_max=Antelope; +SET GLOBAL innodb_large_prefix=0; |