Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 327 insertions, 0 deletions

diff --git a/mysql-test/main/index_merge_myisam.test b/mysql-test/main/index_merge_myisam.test
new file mode 100644
index 00000000..0437f913
--- /dev/null
+++ b/mysql-test/main/index_merge_myisam.test
@@ -0,0 +1,327 @@
+# t/index_merge_myisam.test
+#
+# Index merge tests
+#
+# Last update:
+# 2006-08-07 ML test refactored (MySQL 5.1)
+#               Main code of several index_merge tests
+#                            -> include/index_merge*.inc
+#               wrapper t/index_merge_innodb.test sources now several 
+#               include/index_merge*.inc files
+#
+--source include/no_valgrind_without_big.inc
+
+SET DEFAULT_STORAGE_ENGINE = MyISAM;
+# MyISAM supports Merge tables
+let $merge_table_support= 1;
+
+set @optimizer_switch_save= @@optimizer_switch;
+
+set optimizer_switch='rowid_filter=off';
+set optimizer_switch='index_merge_sort_intersection=off';
+
+--source include/index_merge1.inc
+--source include/index_merge_ror.inc
+--source include/index_merge2.inc
+--source include/index_merge_2sweeps.inc
+--source include/index_merge_ror_cpk.inc
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, c int, filler char(100), 
+                 key(a), key(b), key(c));
+insert into t1 select 
+  A.a * B.a*10 + C.a*100, 
+  A.a * B.a*10 + C.a*100,
+  A.a, 
+  'filler'
+from t0 A, t0 B, t0 C;
+
+--echo This should use union:
+explain select * from t1 where a=1 or b=1;
+
+--echo This should use ALL:
+set optimizer_switch='default,index_merge=off,rowid_filter=off';
+explain select * from t1 where a=1 or b=1;
+
+--echo This should use sort-union:
+set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
+explain select * from t1 where a=1 or b=1;
+
+--echo This will use sort-union:
+set optimizer_switch=default;
+explain select * from t1 where a<1 or b <1;
+
+--echo This should use ALL:
+set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off';
+explain select * from t1 where a<1 or b <1;
+
+
+--echo This should use ALL:
+set optimizer_switch='default,index_merge=off,rowid_filter=off';
+explain select * from t1 where a<1 or b <1;
+
+--echo This will use sort-union:
+set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
+explain select * from t1 where a<1 or b <1;
+
+alter table t1 add d int, add key(d);
+update t1 set d=a;
+
+--echo This will use sort_union:
+set optimizer_switch=default;
+explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
+
+--echo And if we disable sort_union, union:
+set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off';
+explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
+
+drop table t1;
+
+# Now test that intersection can be disabled
+create table t1 (
+  a int, b int, c int,
+  filler1 char(200), filler2 char(200), 
+  key(a),key(b),key(c)
+); 
+
+insert into t1 
+select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a, 'foo', 'bar' 
+from t0 A, t0 B, t0 C, t0 D where D.a<5;
+
+--echo This should be intersect:
+set optimizer_switch=default;
+explain select * from t1 where a=10 and b=10;
+
+--echo No intersect when index_merge is disabled:
+set optimizer_switch='default,index_merge=off,rowid_filter=off';
+explain select * from t1 where a=10 and b=10;
+
+--echo No intersect if it is disabled:
+set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off,rowid_filter=off';
+explain select * from t1 where a=10 and b=10;
+
+--echo Do intersect when union was disabled
+set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
+explain select * from t1 where a=10 and b=10;
+
+--echo Do intersect when sort_union was disabled
+set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off';
+explain select * from t1 where a=10 and b=10;
+
+# Now take union-of-intersection and see how we can disable parts of it
+--echo This will use intersection inside a union:
+set optimizer_switch=default;
+explain select * from t1 where a=10 and b=10 or c=10;
+
+--echo Should be only union left:
+set optimizer_switch='default,index_merge_intersection=off,rowid_filter=off';
+explain select * from t1 where a=10 and b=10 or c=10;
+
+--echo This will switch to sort-union (intersection will be gone, too,
+--echo   that's a known limitation:
+set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
+explain select * from t1 where a=10 and b=10 or c=10;
+
+set optimizer_switch=default;
+
+drop table t0, t1;
+
+
+--echo #
+--echo # BUG#834514 Assertion `!table || (!table->read_set || bitmap_is_set(...' with aggregates
+--echo #
+CREATE TABLE t1 ( a int , b int, c int, KEY (b), PRIMARY KEY (a)) ;
+INSERT INTO t1 VALUES (1,4,0),(5,0,0),(6,7,0),(7,7,0),(8,1,0),(9,7,0),(10,1,0);
+
+CREATE TABLE t2 ( b int, c int, KEY (c,b)) ;
+INSERT INTO t2 VALUES (7,0),(1,0),(7,0),(1,0);
+
+CREATE TABLE t3 ( a int ) ;
+
+SELECT COUNT(DISTINCT t2.b), CONCAT(t1.c)
+FROM t1, t2
+WHERE (t2.c = t1.c)
+AND (
+        t1.b IN ( 4 )
+        OR t1.a = 137
+        AND EXISTS ( SELECT a FROM t3 )
+)
+GROUP BY 2;
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # MDEV-4556 Server crashes in SEL_ARG::rb_insert with index_merge+index_merge_sort_union, FORCE INDEX
+--echo #
+CREATE TABLE t1 (
+  pk int,
+  code char(2),
+  population_rate int,
+  area_rate int,
+  primary key (pk),
+  index (code),
+  key (population_rate),
+  key (area_rate)
+);
+
+INSERT INTO t1 VALUES  (1,'WI',20, 23), (2, 'WA', 13, 18);
+
+EXPLAIN
+SELECT * FROM t1 FORCE INDEX ( PRIMARY, population_rate, area_rate, code ) 
+WHERE pk = 1 OR population_rate = 1 OR ( area_rate IN ( 1,2 ) OR area_rate IS NULL )
+AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND 'TX');
+
+SELECT * FROM t1 FORCE INDEX ( PRIMARY, population_rate, area_rate, code ) 
+WHERE pk = 1 OR population_rate = 1 OR ( area_rate IN ( 1,2 ) OR area_rate IS NULL )
+AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND 'TX');
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-5069: Server crashes in SEL_ARG::increment_use_count with index_merge+index_merge_sort_union, FORCE INDEX
+--echo #
+
+CREATE TABLE t1 (
+ c1 varchar(64),
+ i int,
+ pk integer auto_increment,
+ c2 varchar(64),
+ index (c1),
+ index (i),
+ primary key (pk),
+ key (c2)) ENGINE=myisam;
+
+ALTER TABLE t1 DISABLE KEYS;
+
+INSERT INTO t1 VALUES 
+('West Virginia', 6121, NULL, 'California'),('Georgia', 60177, NULL, 'Arkansas'),
+('Delaware', 90, NULL, 'Oregon'),('Wyoming', 7, NULL, 'Missouri'),(
+'Delaware', 2, NULL, 'Utah'),('Wisconsin', 0, NULL, 'Iowa'),
+('Kansas', 0, NULL, 'Florida'),('Ohio', 34358, NULL, 'Colorado'),
+('Maine', 118, NULL, 'Texas'),('Mississippi', 0, NULL, 'Georgia'),
+('Tennessee', 4, NULL, 'N/A'),('Georgia', 0, NULL, 'New Hampshire'),
+('Wyoming', 2, NULL, 'N/A'),('Florida', 0, NULL, 'Arizona'),
+('Rhode Island', -24002, NULL, 'Michigan'),('Alabama', 142, NULL, 'Indiana'),
+('Colorado', 0, NULL, 'Louisiana'),('Michigan', 21194, NULL, 'Louisiana'),
+('Oklahoma', 31475, NULL, 'Alabama'),('Pennsylvania', 0, NULL, 'Oklahoma'),
+('Texas', 0, NULL, 'Texas'),('West Virginia', 5, NULL, 'Utah'),
+('Florida', 49653, NULL, 'Kentucky'),('Tennessee', 19075, NULL, 'Oregon'),
+('Maine', 3, NULL, 'Kansas, Kentucky, Iowa'),('Iowa', 1, NULL, 'South Dakota'),
+('Kansas', -4037, NULL, 'Virginia'),('Delaware', 22550, NULL, 'Utah'),
+('Illinois', 14634, NULL, 'South Carolina, Colorado'),
+('Kansas', 6, NULL, 'South Dakota'),('Delaware', 9, NULL, ''),
+('', 0, NULL, 'Utah, Delaware, Florida, Georgia, Nevada'),
+('Colorado', 8, NULL, 'Montana'),('Maryland', 2689, NULL, 'Hawaii'),
+('Florida', -12306, NULL, 'Delaware'),
+('Indiana', 38567, NULL, 'Iowa, Minnesota, Maine'),
+('Oklahoma', 9, NULL, 'Delaware, Kansas, Oregon, Nebraska, Maryland, Minnesota'),
+('Tennessee', 12460, NULL, NULL),('Kentucky', 0, NULL, 'Ohio'),
+('Nevada', 7, NULL, 'Vermont, Oregon, Oklahoma, Montana'),
+('Nebraska', 61966, NULL, 'Nevada'),('Alaska', 131, NULL, 'Louisiana, Maine'),
+('Wisconsin', 4, NULL, 'Nevada'),('South Carolina', 0, NULL, 'Washington'),
+('West Virginia', 51314, NULL, 'Ohio'),('Louisiana', 0, NULL, ''),
+('Pennsylvania', 0, NULL, 'Iowa, Idaho'),('Arkansas', 14010, NULL, 'Indiana'),
+('Wyoming', -15514, NULL, 'Maine'),('Georgia', 0, NULL, 'N/A'),
+('Kentucky', 1, NULL, 'Idaho'),('Wyoming', 60249, NULL, 'Indiana, Iowa'),
+('Pennsylvania', 69, NULL, 'W'),
+('New Mexico', 11480, NULL, 'Florida, Georgia, Hawaii'),
+('South Carolina', 9, NULL, 'Iowa'),('Virginia', 0, NULL, 'Connecticut'),
+('Mississippi', 19749, NULL, 'Rhode Island'),('Illinois', 5, NULL, 'Virginia'),
+('Texas', -1749, NULL, 'Tennessee'),('Arizona', 28, NULL, 'California'),
+('Florida', 62151, NULL, 'Kansas'),('California', 172, NULL, 'SC'),
+('New Jersey', 0, NULL, 'North Carolina'),('Wyoming', 4, NULL, 'I'),
+('Kansas', 10683, NULL, 'California'),('Arkansas', -14275, NULL, 'K'),
+('Arizona', 5, NULL, 'California, Delaware, Rhode Island, Maryland'),
+('Florida', 0, NULL, 'T'),('Alaska', 241, NULL, 'Virginia');
+
+ALTER TABLE t1 ENABLE KEYS;
+
+# note: (pk is not null and (pk <1 or pk>1)) below is a sargable form of pk!=1
+EXPLAIN
+SELECT  * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) 
+WHERE pk = 255 OR i = 22 OR (pk  IN (1 , 136) AND c2  IN ('c' , 'w') AND (c1
+NOT BETWEEN 'e' AND 'i' OR  c2 > 'g'))  OR (pk is not null and (pk <1 or pk>1))  ;
+
+DROP TABLE t1;
+
+set optimizer_switch= @optimizer_switch_save;
+
+--echo #
+--echo # MDEV-21932: ROR union with index_merge_sort_union=off
+--echo #
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t0 select a+10 from t0;
+insert into t0 select a+20 from t0;
+insert into t0 select a+40 from t0;
+insert into t0 select a+80 from t0;
+insert into t0 select a+160 from t0;
+delete from t0 where a > 300;
+
+create table t1 (
+  f1 int, f2 int, f3 int, f4 int,
+  primary key (f1),  key (f3),  key(f4)
+) engine=myisam;
+insert into t1 select a+100, a+100, a+100, a+100 from t0;
+insert into t1 VALUES (9,0,2,6), (9930,0,0,NULL);
+analyze table t1;
+
+set optimizer_switch='index_merge_sort_union=off';
+set optimizer_switch='index_merge_union=on';
+
+let $q1=
+select * from t1
+   where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
+              (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+eval explain $q1;
+eval $q1;
+
+insert into t1 values (52,0,1,0),(53,0,1,0);
+insert into t1 values (50,0,1,0),(51,0,1,0);
+insert into t1 values (48,0,1,0),(49,0,1,0);
+insert into t1 values (46,0,1,0),(47,0,1,0);
+insert into t1 values (44,0,1,0),(45,0,1,0);
+analyze table t1;
+
+let $q2=
+select * from t1
+   where (( f3 = 1 or f1 = 7 )  and f1 < 10) or
+              (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+eval explain $q2;
+eval $q2;
+
+drop table t0,t1;
+
+set optimizer_switch= @optimizer_switch_save;
+
+--echo #
+--echo # MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union
+--echo #
+
+create table t0
+(
+  key1 int not null,
+  INDEX i1(key1)
+);
+
+insert into t0 select * from seq_1_to_1024;
+alter table t0 add key2 int not null, add index i2(key2);
+alter table t0 add key3 int not null, add index i3(key3);
+alter table t0 add key8 int not null, add index i8(key8);
+
+update t0 set key2=key1,key3=key1,key8=1024-key1;
+analyze table t0;
+
+set @optimizer_switch_save=@@optimizer_switch;
+set optimizer_switch='derived_merge=off,derived_with_keys=off';
+explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
+select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
+set optimizer_use_condition_selectivity=2;
+explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
+select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
+set @@optimizer_switch= @optimizer_switch_save;
+drop table t0;
+
+--echo # End of 10.1 tests