index_merge_ror.inc 9.73 KB
Newer Older
unknown's avatar
unknown committed
1
# include/index_merge_ror.inc
2
#
unknown's avatar
unknown committed
3
#  ROR-index_merge tests.
4
#
unknown's avatar
unknown committed
5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
# The variable
#     $engine_type       -- storage engine to be tested
# has to be set before sourcing this script.
#
# Note: The comments/expectations refer to MyISAM.
#       They might be not valid for other storage engines.
#
# Last update:
# 2006-08-02 ML test refactored
#               old name was t/index_merge_ror.test
#               main code went into include/index_merge_ror.inc
#

--echo #---------------- ROR-index_merge tests -----------------------

eval SET SESSION STORAGE_ENGINE = $engine_type;

22
--disable_warnings
23
drop table if exists  t0,t1,t2;
24 25 26 27
--enable_warnings
create table t1
(
  /* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
28 29 30
  st_a int not null default 0,
  swt1a int not null default 0,
  swt2a int not null default 0,
31

32 33 34
  st_b int not null default 0,
  swt1b int not null default 0,
  swt2b int not null default 0,
35 36 37 38 39 40 41 42 43 44 45 46 47 48

  /* fields/keys for row retrieval tests */
  key1 int,
  key2 int,
  key3 int,
  key4 int,

  /* make rows much bigger then keys */
  filler1 char (200),
  filler2 char (200),
  filler3 char (200),
  filler4 char (200),
  filler5 char (200),
  filler6 char (200),
unknown's avatar
unknown committed
49

50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
  /* order of keys is important */
  key sta_swt12a(st_a,swt1a,swt2a),
  key sta_swt1a(st_a,swt1a),
  key sta_swt2a(st_a,swt2a),
  key sta_swt21a(st_a,swt2a,swt1a),

  key st_a(st_a),
  key stb_swt1a_2b(st_b,swt1b,swt2a),
  key stb_swt1b(st_b,swt1b),
  key st_b(st_b),

  key(key1),
  key(key2),
  key(key3),
  key(key4)
) ;

unknown's avatar
unknown committed
67
# Fill table
68
create table t0 as select * from t1;
unknown's avatar
unknown committed
69 70
--disable_query_log
--echo # Printing of many insert into t0 values (....) disabled.
71 72 73 74 75 76
let $cnt=1000;
while ($cnt)
{
  eval insert into t0 values (1, 2, 3, 1, 2, 3, 0, 0, 0, 0, 'data1', 'data2', 'data3', 'data4', 'data5', 'data6');
  dec $cnt;
}
unknown's avatar
unknown committed
77
--enable_query_log
78 79

alter table t1 disable keys;
unknown's avatar
unknown committed
80 81
--disable_query_log
--echo # Printing of many insert into t1 select .... from t0 disabled.
82 83 84 85
let $1=4;
while ($1)
{
  let $2=4;
unknown's avatar
unknown committed
86
  while ($2)
87 88 89 90 91 92 93 94 95 96 97 98
  {
    let $3=4;
    while ($3)
    {
      eval insert into t1 select $1, $2, $3, $1 ,$2, $3, key1, key2, key3, key4, filler1, filler2, filler3, filler4, filler5, filler6 from t0;
      dec $3;
    }
    dec $2;
  }
 dec $1;
}

unknown's avatar
unknown committed
99
--echo # Printing of many insert into t1 (...) values (....) disabled.
100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116
# Row retrieval tests
# -1 is used for values 'out of any range we are using'
# insert enough rows for index intersection to be used for (key1,key2)
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 100, 100,'key1-key2-key3-key4');
let $cnt=400;
while ($cnt)
{
  eval insert into t1 (key1, key2, key3, key4, filler1) values (100, -1, 100, -1,'key1-key3');
  dec $cnt;
}
let $cnt=400;
while ($cnt)
{
  eval insert into t1 (key1, key2, key3, key4, filler1) values (-1, 100, -1, 100,'key2-key4');
  dec $cnt;
}
--enable_query_log
unknown's avatar
unknown committed
117
alter table t1 enable keys;
118 119
select count(*) from t1;

unknown's avatar
unknown committed
120
# One row results tests for cases where a single row matches all conditions
121 122 123 124 125
explain select key1,key2 from t1 where key1=100 and key2=100;
select key1,key2 from t1 where key1=100 and key2=100;
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;

unknown's avatar
unknown committed
126
# Several-rows results
127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');

#  ROR-intersection, not covering
explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
select key1,key2,filler1 from t1 where key1=100 and key2=100;

#  ROR-intersection, covering
explain select key1,key2 from t1 where key1=100 and key2=100;
select key1,key2 from t1 where key1=100 and key2=100;

#  ROR-union of ROR-intersections
explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;

#  3-way ROR-intersection
explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;

#  ROR-union(ROR-intersection, ROR-range)
insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;

# Run some ROR updates/deletes
select key1,key2, filler1 from t1 where key1=100 and key2=100;
update t1 set filler1='to be deleted' where key1=100 and key2=100;
update t1 set key1=200,key2=200 where key1=100 and key2=100;
delete from t1 where key1=200 and key2=200;
select key1,key2,filler1 from t1 where key2=100 and key2=200;

# ROR-union(ROR-intersection) with one of ROR-intersection giving empty
# results
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;

delete from t1 where key3=100 and key4=100;

# ROR-union with all ROR-intersections giving empty results
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;

# ROR-intersection with empty result
explain select key1,key2 from t1 where key1=100 and key2=100;
select key1,key2 from t1 where key1=100 and key2=100;

# ROR-union tests with various cases.
unknown's avatar
unknown committed
176
#  All scans returning duplicate rows:
177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1');
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2');
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3');

explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;

insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');

explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;

insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');

explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;

##
unknown's avatar
unknown committed
195
## Optimizer tests
196 197 198 199 200 201 202 203 204
##

# Check that the shortest key is used for ROR-intersection, covering and non-covering.
explain select * from t1 where st_a=1 and st_b=1;
explain select st_a,st_b from t1 where st_a=1 and st_b=1;

# Check if "ingore index" syntax works
explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;

unknown's avatar
unknown committed
205
# Do many tests
206
# Check that keys that don't improve selectivity are skipped.
unknown's avatar
unknown committed
207
#
208

unknown's avatar
unknown committed
209
# Different value on 32 and 64 bit
210
--replace_result sta_swt12a sta_swt21a sta_swt12a, sta_swt12a,
211 212 213 214 215 216
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;

explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;

explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;

unknown's avatar
unknown committed
217
explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
218 219
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;

unknown's avatar
unknown committed
220
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
221 222
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;

unknown's avatar
unknown committed
223
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b)
224 225
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;

unknown's avatar
unknown committed
226
explain select * from t1
227 228
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;

unknown's avatar
unknown committed
229
explain select * from t1
230 231
  where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;

unknown's avatar
unknown committed
232
explain select st_a from t1
233 234
  where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;

unknown's avatar
unknown committed
235
explain select st_a from t1
236 237 238 239
  where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;

drop table t0,t1;

240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265
# 'Partially' covered fields test

create table t2 (
  a char(10),
  b char(10),
  filler1 char(255),
  filler2 char(255),
  key(a(5)),
  key(b(5))
);

--disable_query_log
let $1=8;
while ($1)
{
  eval insert into t2 values (repeat(char($1+64), 8),repeat(char($1+64), 8),'filler1', 'filler2');
  dec $1;
}
insert into t2 select * from t2;
insert into t2 select * from t2;
--enable_query_log

# The table row buffer is reused. Fill it with rows that don't match.
select count(a) from t2 where a='BBBBBBBB';
select count(a) from t2 where b='BBBBBBBB';

unknown's avatar
unknown committed
266
# BUG#1:
267
--replace_result a a_or_b b a_or_b
268 269 270 271 272 273 274
explain select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA';

insert into t2 values ('ab', 'ab', 'uh', 'oh');
explain select a from t2 where a='ab';
drop table t2;
275 276 277 278 279 280 281 282 283 284 285 286 287 288 289

#
# BUG#25048 - ERROR 126 : Incorrect key file for table '.XXXX.MYI'; try to
#             repair it
#
CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '',
KEY(c1), KEY(c2), KEY(c3));
INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
INSERT INTO t1 VALUES(0,0,0);
CREATE TABLE t2(c1 int);
INSERT INTO t2 VALUES(1);
DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0;
SELECT * FROM t1;
DROP TABLE t1,t2;