1set join_cache_level=2; 2drop table if exists t0,t1,t2,t3; 3create table t0 (a int) engine=myisam; 4INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 5create table t1 (a int) engine=myisam; 6INSERT INTO t1 select * from t0; 7# Try a few basic selects to see that r_rows and r_filtered columns work 8analyze select * from t1; 9id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 101 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 100.00 11analyze select * from t1 where a<5; 12id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 131 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 50.00 Using where 14analyze select * from t1 where a>100; 15id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 161 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 0.00 Using where 17# ANALYZE DELETE will delete rows: 18analyze delete from t1 where a in (2,3,4); 19id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 201 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 30.00 Using where 21select * from t1; 22a 230 241 255 266 277 288 299 30drop table t1; 31# ANALYZE UPDATE will make updates: 32create table t1(a int, b int); 33insert into t1 select a,a from t0; 34analyze update t1 set b=100+b where a in (6,7,8); 35id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 361 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 30.00 Using where 37select * from t1; 38a b 390 0 401 1 412 2 423 3 434 4 445 5 456 106 467 107 478 108 489 9 49drop table t1; 50# Check that UNION works 51create table t1(a int, b int); 52insert into t1 select a,a from t0; 53analyze (select * from t1 A where a<5) union (select * from t1 B where a in (5,6)); 54id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 551 PRIMARY A ALL NULL NULL NULL NULL 10 10.00 100.00 50.00 Using where 562 UNION B ALL NULL NULL NULL NULL 10 10.00 100.00 20.00 Using where 57NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 7.00 NULL NULL 58analyze (select * from t1 A where a<5) union (select * from t1 B where a in (1,2)); 59id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 601 PRIMARY A ALL NULL NULL NULL NULL 10 10.00 100.00 50.00 Using where 612 UNION B ALL NULL NULL NULL NULL 10 10.00 100.00 20.00 Using where 62NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 5.00 NULL NULL 63drop table t1; 64drop table t0; 65# 66# Try a subquery. 67# 68create table t0 (a int, b int); 69insert into t0 values 70(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 71create table t1 (a int, b int); 72insert into t1 values (1,1),(2,2),(3,3); 73# See .test file for the right values of r_rows and r_filtered. 74analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1; 75id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 761 PRIMARY t1 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 772 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 3.00 100.00 33.33 Using where 78# Try a subquery that is never executed 79analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1 where t1.a > 5; 80id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 811 PRIMARY t1 ALL NULL NULL NULL NULL 3 3.00 100.00 0.00 Using where 822 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 NULL 100.00 NULL Using where 83drop table t0, t1; 84# 85# Tests for join buffering 86# 87create table t0 (a int, b int); 88insert into t0 values 89(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 90create table t1 like t0; 91insert into t1 select * from t0; 92explain select * from t0, t1 where t0.a<5 and t1.a<5; 93id select_type table type possible_keys key key_len ref rows Extra 941 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where 951 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) 96# These should have filtered=50 97analyze select * from t0, t1 where t0.a<5 and t1.a<5; 98id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 991 SIMPLE t0 ALL NULL NULL NULL NULL 10 10.00 100.00 50.00 Using where 1001 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 50.00 Using where; Using join buffer (flat, BNL join) 101explain select * from t0, t1 where t0.a<5 and t1.b=t0.b; 102id select_type table type possible_keys key key_len ref rows Extra 1031 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where 1041 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) 105# Now, t1 should have filtered=10 106analyze select * from t0, t1 where t0.a<5 and t1.b=t0.b; 107id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1081 SIMPLE t0 ALL NULL NULL NULL NULL 10 10.00 100.00 50.00 Using where 1091 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 10.00 Using where; Using join buffer (flat, BNL join) 110explain select * from t0, t1 where t0.a<5 and t1.a<5 and t1.b=t0.b; 111id select_type table type possible_keys key key_len ref rows Extra 1121 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where 1131 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) 114# Now, t1 should have filtered=10 115analyze select * from t0, t1 where t0.a<5 and t1.a<5 and t1.b=t0.b; 116id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1171 SIMPLE t0 ALL NULL NULL NULL NULL 10 10.00 100.00 50.00 Using where 1181 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 10.00 Using where; Using join buffer (flat, BNL join) 119# TODO: Check what is counted for "range checked for each record". 120# 121# Test for joins 122# 123create table t2 (key1 int, key2x int, col1 int, key(key1), key(key2x)); 124insert into t2 select A.a + 10 *B.a +100 * C.a, 125(A.a + 10 *B.a +100 * C.a)*2, 126A.a + 10 *B.a +100 * C.a 127from t0 A, t0 B, t0 C; 128# This always has matches, filtered=100%. 129analyze select * from t1,t2 where t2.key1=t1.a; 130id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1311 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 100.00 Using where 1321 SIMPLE t2 ref key1 key1 5 test.t1.a 1 1.00 100.00 100.00 133# This shows r_rows=0. It is actually 0.5 (should r_rows be changed to double?) 134analyze select * from t1,t2 where t2.key2x=t1.a; 135id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1361 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 100.00 Using where 1371 SIMPLE t2 ref key2x key2x 5 test.t1.a 1 0.50 100.00 100.00 138select * from t1,t2 where t2.key2x=t1.a; 139a b key1 key2x col1 1400 0 0 0 0 1412 2 1 2 1 1424 4 2 4 2 1436 6 3 6 3 1448 8 4 8 4 145# This has t2.filtered=40% (there are 5 values: {0,1,2,3,4}. two of them have mod=0) 146analyze select * from t1,t2 where t2.key2x=t1.a and mod(t2.col1,4)=0; 147id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1481 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 100.00 Using where 1491 SIMPLE t2 ref key2x key2x 5 test.t1.a 1 0.50 100.00 40.00 Using where 150drop table t0,t1,t2; 151# 152# Check non-merged derived tables 153# 154create table t0 (a int, b int); 155insert into t0 values 156(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 157update t0 set b=b/3; 158analyze select * from (select count(*),max(a),b from t0 group by b) T; 159id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1601 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 4.00 100.00 100.00 1612 DERIVED t0 ALL NULL NULL NULL NULL 10 10.00 100.00 100.00 Using temporary; Using filesort 162drop table t0; 163# 164# Check ORDER/GROUP BY 165# 166create table t0 (a int, b int); 167insert into t0 values 168(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 169analyze select count(*),max(a),b from t0 where a<7 group by b; 170id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1711 SIMPLE t0 ALL NULL NULL NULL NULL 10 10.00 100.00 70.00 Using where; Using temporary; Using filesort 172drop table t0; 173# 174# Check multi-table UPDATE/DELETE. 175# 176create table t0 (a int, b int); 177create table t1 (a int, b int); 178insert into t0 values (0,0),(2,2),(4,4), (8,8); 179insert into t1 values (0,0),(2,2), (6,6); 180analyze select * from t0,t1 where t0.a=t1.a; 181id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1821 SIMPLE t1 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 1831 SIMPLE t0 ALL NULL NULL NULL NULL 4 4.00 100.00 16.67 Using where; Using join buffer (flat, BNL join) 184analyze update t0,t1 set t1.b=5555 where t0.a=t1.a; 185id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1861 SIMPLE t1 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 1871 SIMPLE t0 ALL NULL NULL NULL NULL 4 4.00 100.00 16.67 Using where 188select * from t1; 189a b 1900 5555 1912 5555 1926 6 193analyze delete t1 from t1, t0 where t0.a=t1.a; 194id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1951 SIMPLE t1 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 1961 SIMPLE t0 ALL NULL NULL NULL NULL 4 4.00 100.00 16.67 Using where 197select * from t1; 198a b 1996 6 200drop table t0, t1; 201# 202# MDEV-6393: ANALYZE SELECT crashes in Explain_query::print_explain with a non-existing column 203# 204create table t1 (i int); 205insert into t1 values (1),(2); 206analyze select a from t1; 207ERROR 42S22: Unknown column 'a' in 'field list' 208analyze delete from t1 where a=2; 209ERROR 42S22: Unknown column 'a' in 'where clause' 210analyze update t1 set a=2; 211ERROR 42S22: Unknown column 'a' in 'field list' 212create table t2 like t1; 213insert into t2 select * from t1; 214analyze update t2,t1 set t2.i=5 where t2.a=t1.a; 215ERROR 42S22: Unknown column 't2.a' in 'where clause' 216analyze delete t1 from t2,t1 where t2.a=t1.a; 217ERROR 42S22: Unknown column 't2.a' in 'where clause' 218drop table t1, t2; 219# 220# MDEV-6395: ANALYZE UPDATE/DELETE with impossible where does not produce any output 221# 222create table t1 (a int, b int, key(a)); 223insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5); 224analyze delete from t1 where 1 > 2; 225id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2261 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 227analyze delete from t1 where a > 30 and a < 10; 228id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2291 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 230analyze update t1 set b=12345 where 1 > 2; 231id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2321 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 233analyze update t1 set b=12345 where a > 30 and a < 10; 234id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2351 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 236drop table t1; 237# 238# MDEV-6398: ANALYZE UPDATE does not populate r_rows 239# 240create table t1 (i int); 241insert into t1 values (1),(2),(3),(4); 242analyze update t1 set i=8; 243id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2441 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 245drop table t1; 246# 247# Check ANALYZE SELECT INTO 248# 249create table t1 (i int); 250insert into t1 values (1); 251analyze select * into @var from t1 ; 252id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2531 SIMPLE t1 system NULL NULL NULL NULL 1 NULL 100.00 NULL 254drop table t1; 255# 256# MDEV-6394: ANALYZE DELETE .. RETURNING fails with ERROR 2027 Malformed packet 257# 258create table t1 (i int); 259analyze delete from t1 returning *; 260id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2611 SIMPLE t1 ALL NULL NULL NULL NULL 0 0.00 100.00 100.00 262drop table t1; 263# 264# MDEV-6396: ANALYZE INSERT/REPLACE is accepted, but does not produce a plan 265# 266create table t1 (a int primary key, b int); 267analyze insert into t1 values (1,1); 268id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2691 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL 270select * from t1; 271a b 2721 1 273analyze replace t1 values (1,2); 274id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2751 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL 276select * from t1; 277a b 2781 2 279drop table t1; 280# 281# MDEV-6400 "ANALYZE SELECT ... INTO @var" doesn't set @var 282# 283create table t1(a int); 284insert into t1 values (1),(2); 285analyze select a into @var from t1 where a <2 ; 286id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2871 SIMPLE t1 ALL NULL NULL NULL NULL 2 2.00 100.00 50.00 Using where 288analyze select a into @var from t1 ; 289ERROR 42000: Result consisted of more than one row 290analyze insert into t1 select * from t1; 291id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2921 SIMPLE t1 ALL NULL NULL NULL NULL 2 2.00 100.00 100.00 Using temporary 293analyze select * into outfile '../../tmp/data1.tmp' from t1; 294id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2951 SIMPLE t1 ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 296drop table t1; 297# 298# MDEV-7024: Assertion `! is_set()' failed in 299# Diagnostics_area::set_eof_status on executing ANALYZE SELECT via PS 300# 301create table t1(a int); 302prepare stmt from "analyze select * from t1"; 303execute stmt; 304id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 3051 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 306drop table t1; 307# 308# MDEV-7674: ANALYZE shows r_rows=0 309# 310create table t1(a int); 311insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 312create table t2 (a int, key(a)); 313insert into t2 values (0),(1); 314analyze select * from t1 straight_join t2 force index(a) where t2.a=t1.a; 315id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 3161 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 100.00 Using where 3171 SIMPLE t2 ref a a 5 test.t1.a 2 0.20 100.00 100.00 Using index 318drop table t1,t2; 319# 320# MDEV-8063: Unconditional ANALYZE DELETE does not delete rows 321# 322create table t1 (i int); 323insert into t1 values (1),(2); 324analyze delete from t1; 325id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 3261 SIMPLE NULL NULL NULL NULL NULL NULL 2 NULL NULL NULL Deleting all rows 327select * from t1; 328i 329insert into t1 values (1),(2); 330explain delete from t1; 331id select_type table type possible_keys key key_len ref rows Extra 3321 SIMPLE NULL NULL NULL NULL NULL NULL 2 Deleting all rows 333select * from t1; 334i 3351 3362 337drop table t1; 338