1# include/index_merge_ror_cpk.inc
2#
3# Clustered PK ROR-index_merge tests
4#
5# Note: The comments/expectations refer to InnoDB.
6#       They might be not valid for other storage engines.
7#
8# Last update:
9# 2006-08-02 ML test refactored
10#               old name was t/index_merge_ror_cpk.test
11#               main code went into include/index_merge_ror_cpk.inc
12#
13
14--source include/have_sequence.inc
15
16--echo #---------------- Clustered PK ROR-index_merge tests -----------------------------
17
18create table t1
19(
20  pk1 int not null,
21  pk2 int not null,
22
23  key1 int not null,
24  key2 int not null,
25
26  pktail1ok  int not null,
27  pktail2ok  int not null,
28  pktail3bad int not null,
29  pktail4bad int not null,
30  pktail5bad int not null,
31
32  pk2copy int not null,
33  badkey  int not null,
34
35  filler1 char (200),
36  filler2 char (200),
37  key (key1),
38  key (key2),
39
40  /* keys with tails from CPK members */
41  key (pktail1ok, pk1),
42  key (pktail2ok, pk1, pk2),
43  key (pktail3bad, pk2, pk1),
44  key (pktail4bad, pk1, pk2copy),
45  key (pktail5bad, pk1, pk2, pk2copy),
46
47  primary key (pk1, pk2)
48);
49
50--disable_query_log
51begin;
52let $1=10000;
53while ($1)
54{
55  eval insert into t1 values ($1 div 10,$1 mod 100,   $1/100,$1/100,   $1/100,$1/100,$1/100,$1/100,$1/100, $1 mod 100, $1/1000,'filler-data-$1','filler2');
56  dec $1;
57}
58commit;
59--enable_query_log
60
61# Verify that range scan on CPK is ROR
62# (use index_intersection because it is impossible to check that for index union)
63explain select * from t1 where pk1 = 1 and pk2 < 80  and key1=0;
64# CPK scan + 1 ROR range scan is a special case
65select * from t1 where pk1 = 1 and pk2 < 80  and key1=0;
66
67# Verify that CPK fields are considered to be covered by index scans
68explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
69select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
70
71# Verify that CPK is always used for index intersection scans
72# (this is because it is used as a filter, not for retrieval)
73explain select * from t1 where badkey=1 and key1=10;
74set @tmp_index_merge_ror_cpk=@@optimizer_switch;
75set optimizer_switch='extended_keys=off';
76--replace_column 9 ROWS
77explain select * from t1 where pk1 < 7500 and key1 = 10;
78set optimizer_switch=@tmp_index_merge_ror_cpk;
79
80# Verify that keys with 'tails' of PK members are ok.
81explain select * from t1 where pktail1ok=1 and key1=10;
82explain select * from t1 where pktail2ok=1 and key1=10;
83
84# Note: The following is actually a deficiency, it uses sort_union currently.
85#       This comment refers to InnoDB and is probably not valid for other engines.
86explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
87
88# The expected rows differs a bit from platform to platform
89--replace_result 98 ROWS 99 ROWS
90explain select * from t1 where pktail3bad=1 and key1=10;
91explain select * from t1 where pktail4bad=1 and key1=10;
92explain select * from t1 where pktail5bad=1 and key1=10;
93
94# Test for problem with innodb key values prefetch buffer:
95explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
96select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
97
98drop table t1;
99# Testcase for BUG#4984
100create table t1
101(
102  RUNID varchar(22),
103  SUBMITNR varchar(5),
104  ORDERNR char(1),
105  PROGRAMM varchar(8),
106  TESTID varchar(4),
107  UCCHECK char(1),
108  ETEXT varchar(80),
109  ETEXT_TYPE char(1),
110  INFO char(1),
111  SEVERITY tinyint(3),
112  TADIRFLAG char(1),
113  PRIMARY KEY  (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
114  KEY `TVERM~KEY`  (PROGRAMM,TESTID,UCCHECK)
115) DEFAULT CHARSET=latin1;
116
117update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''
118WHERE
119 `RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
120 `TESTID`='' AND `UCCHECK`='';
121
122drop table t1;
123
124--echo #
125--echo # Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB
126--echo #
127CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1));
128INSERT INTO t1 VALUES (2);
129CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1),
130                 PRIMARY KEY (f1), KEY (f2), KEY (f3) );
131INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, '');
132
133SELECT t1.f1 FROM t1
134WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
135
136EXPLAIN SELECT t1.f1 FROM t1
137WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
138
139DROP TABLE t1,t2;
140