1# 2# Tests for "ANALYZE $statement" feature 3# 4 5# Fix that analyze delete with join doesn't add extra WHERE clause. 6set join_cache_level=2; 7 8--disable_warnings 9drop table if exists t0,t1,t2,t3; 10--enable_warnings 11 12create table t0 (a int) engine=myisam; 13INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 14 15create table t1 (a int) engine=myisam; 16INSERT INTO t1 select * from t0; 17 18--echo # Try a few basic selects to see that r_rows and r_filtered columns work 19analyze select * from t1; 20analyze select * from t1 where a<5; 21analyze select * from t1 where a>100; 22 23--echo # ANALYZE DELETE will delete rows: 24analyze delete from t1 where a in (2,3,4); 25select * from t1; 26drop table t1; 27 28--echo # ANALYZE UPDATE will make updates: 29create table t1(a int, b int); 30insert into t1 select a,a from t0; 31analyze update t1 set b=100+b where a in (6,7,8); 32select * from t1; 33drop table t1; 34 35--echo # Check that UNION works 36create table t1(a int, b int); 37insert into t1 select a,a from t0; 38analyze (select * from t1 A where a<5) union (select * from t1 B where a in (5,6)); 39analyze (select * from t1 A where a<5) union (select * from t1 B where a in (1,2)); 40drop table t1; 41drop table t0; 42 43--echo # 44--echo # Try a subquery. 45--echo # 46create table t0 (a int, b int); 47insert into t0 values 48 (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 49 50create table t1 (a int, b int); 51insert into t1 values (1,1),(2,2),(3,3); 52 53# 54# t1 t0 55# a=1 (0,1) 2 rows 56# a=2 (0,1,2) 3 rows 57# a=3 (0,1,2,3) 4 rows 58# 59# TOTAL TOTAL= 9 rows. 3 executions, avg=3 rows. 60# WHERE is satisfied for 1 row per query, which gives filtered=33.3 61 62--echo # See .test file for the right values of r_rows and r_filtered. 63analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1; 64 65--echo # Try a subquery that is never executed 66analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1 where t1.a > 5; 67 68drop table t0, t1; 69 70--echo # 71--echo # Tests for join buffering 72--echo # 73create table t0 (a int, b int); 74insert into t0 values 75 (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 76create table t1 like t0; 77insert into t1 select * from t0; 78 79explain select * from t0, t1 where t0.a<5 and t1.a<5; 80--echo # These should have filtered=50 81analyze select * from t0, t1 where t0.a<5 and t1.a<5; 82 83explain select * from t0, t1 where t0.a<5 and t1.b=t0.b; 84--echo # Now, t1 should have filtered=10 85analyze select * from t0, t1 where t0.a<5 and t1.b=t0.b; 86 87explain select * from t0, t1 where t0.a<5 and t1.a<5 and t1.b=t0.b; 88--echo # Now, t1 should have filtered=10 89analyze select * from t0, t1 where t0.a<5 and t1.a<5 and t1.b=t0.b; 90 91--echo # TODO: Check what is counted for "range checked for each record". 92 93--echo # 94--echo # Test for joins 95--echo # 96create table t2 (key1 int, key2x int, col1 int, key(key1), key(key2x)); 97insert into t2 select A.a + 10 *B.a +100 * C.a, 98 (A.a + 10 *B.a +100 * C.a)*2, 99 A.a + 10 *B.a +100 * C.a 100 from t0 A, t0 B, t0 C; 101 102--echo # This always has matches, filtered=100%. 103analyze select * from t1,t2 where t2.key1=t1.a; 104 105--echo # This shows r_rows=0. It is actually 0.5 (should r_rows be changed to double?) 106analyze select * from t1,t2 where t2.key2x=t1.a; 107 select * from t1,t2 where t2.key2x=t1.a; 108 109--echo # This has t2.filtered=40% (there are 5 values: {0,1,2,3,4}. two of them have mod=0) 110analyze select * from t1,t2 where t2.key2x=t1.a and mod(t2.col1,4)=0; 111 112drop table t0,t1,t2; 113 114--echo # 115--echo # Check non-merged derived tables 116--echo # 117create table t0 (a int, b int); 118insert into t0 values 119 (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 120 121update t0 set b=b/3; 122analyze select * from (select count(*),max(a),b from t0 group by b) T; 123drop table t0; 124 125--echo # 126--echo # Check ORDER/GROUP BY 127--echo # 128create table t0 (a int, b int); 129insert into t0 values 130 (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); 131 132analyze select count(*),max(a),b from t0 where a<7 group by b; 133drop table t0; 134 135--echo # 136--echo # Check multi-table UPDATE/DELETE. 137--echo # 138create table t0 (a int, b int); 139create table t1 (a int, b int); 140insert into t0 values (0,0),(2,2),(4,4), (8,8); 141insert into t1 values (0,0),(2,2), (6,6); 142 143analyze select * from t0,t1 where t0.a=t1.a; 144 145analyze update t0,t1 set t1.b=5555 where t0.a=t1.a; 146select * from t1; 147 148analyze delete t1 from t1, t0 where t0.a=t1.a; 149select * from t1; 150 151drop table t0, t1; 152 153--echo # 154--echo # MDEV-6393: ANALYZE SELECT crashes in Explain_query::print_explain with a non-existing column 155--echo # 156create table t1 (i int); 157insert into t1 values (1),(2); 158--error ER_BAD_FIELD_ERROR 159analyze select a from t1; 160 161--error ER_BAD_FIELD_ERROR 162analyze delete from t1 where a=2; 163 164--error ER_BAD_FIELD_ERROR 165analyze update t1 set a=2; 166 167create table t2 like t1; 168insert into t2 select * from t1; 169 170--error ER_BAD_FIELD_ERROR 171analyze update t2,t1 set t2.i=5 where t2.a=t1.a; 172 173--error ER_BAD_FIELD_ERROR 174analyze delete t1 from t2,t1 where t2.a=t1.a; 175 176drop table t1, t2; 177--echo # 178--echo # MDEV-6395: ANALYZE UPDATE/DELETE with impossible where does not produce any output 179--echo # 180create table t1 (a int, b int, key(a)); 181insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5); 182 183analyze delete from t1 where 1 > 2; 184analyze delete from t1 where a > 30 and a < 10; 185 186analyze update t1 set b=12345 where 1 > 2; 187analyze update t1 set b=12345 where a > 30 and a < 10; 188 189drop table t1; 190--echo # 191--echo # MDEV-6398: ANALYZE UPDATE does not populate r_rows 192--echo # 193create table t1 (i int); 194insert into t1 values (1),(2),(3),(4); 195analyze update t1 set i=8; 196drop table t1; 197 198--echo # 199--echo # Check ANALYZE SELECT INTO 200--echo # 201create table t1 (i int); 202insert into t1 values (1); 203analyze select * into @var from t1 ; 204drop table t1; 205 206--echo # 207--echo # MDEV-6394: ANALYZE DELETE .. RETURNING fails with ERROR 2027 Malformed packet 208--echo # 209create table t1 (i int); 210analyze delete from t1 returning *; 211drop table t1; 212 213--echo # 214--echo # MDEV-6396: ANALYZE INSERT/REPLACE is accepted, but does not produce a plan 215--echo # 216create table t1 (a int primary key, b int); 217analyze insert into t1 values (1,1); 218select * from t1; 219 220analyze replace t1 values (1,2); 221select * from t1; 222drop table t1; 223 224--echo # 225--echo # MDEV-6400 "ANALYZE SELECT ... INTO @var" doesn't set @var 226--echo # 227create table t1(a int); 228insert into t1 values (1),(2); 229 230analyze select a into @var from t1 where a <2 ; 231--error ER_TOO_MANY_ROWS 232analyze select a into @var from t1 ; 233 234analyze insert into t1 select * from t1; 235 236analyze select * into outfile '../../tmp/data1.tmp' from t1; 237--remove_file $MYSQLTEST_VARDIR/tmp/data1.tmp 238 239drop table t1; 240 241 242--echo # 243--echo # MDEV-7024: Assertion `! is_set()' failed in 244--echo # Diagnostics_area::set_eof_status on executing ANALYZE SELECT via PS 245--echo # 246 247create table t1(a int); 248prepare stmt from "analyze select * from t1"; 249execute stmt; 250drop table t1; 251 252--echo # 253--echo # MDEV-7674: ANALYZE shows r_rows=0 254--echo # 255 256create table t1(a int); 257insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 258 259create table t2 (a int, key(a)); 260insert into t2 values (0),(1); 261 262analyze select * from t1 straight_join t2 force index(a) where t2.a=t1.a; 263 264drop table t1,t2; 265 266--echo # 267--echo # MDEV-8063: Unconditional ANALYZE DELETE does not delete rows 268--echo # 269 270create table t1 (i int); 271insert into t1 values (1),(2); 272analyze delete from t1; 273select * from t1; 274 275insert into t1 values (1),(2); 276explain delete from t1; 277select * from t1; 278 279drop table t1; 280