1# Tests for eq_range_index_dive_limit variable:
2#   test that index dives are not done when index
3#   statistics is requested
4
5--source include/have_debug.inc
6
7SET eq_range_index_dive_limit=default;
8SELECT @@eq_range_index_dive_limit;
9
10CREATE TABLE t1
11(
12  /* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
13  st_a int,
14  swt1a int,
15  swt2a int,
16
17  st_b int,
18  swt1b int,
19  swt2b int,
20
21  key sta_swt12a(st_a,swt1a,swt2a),
22  key sta_swt1a(st_a,swt1a),
23  key sta_swt2a(st_a,swt2a),
24  key sta_swt21a(st_a,swt2a,swt1a),
25
26  key st_a(st_a),
27  key stb_swt1a_2b(st_b,swt1b,swt2a),
28  key stb_swt1b(st_b,swt1b),
29  key st_b(st_b)
30) ;
31
32ALTER TABLE t1 DISABLE KEYS;
33
34--disable_query_log
35--echo #
36--echo # Printing of many insert into t1 disabled.
37--echo #
38let $1=3;
39while ($1)
40{
41  let $2=3;
42  while ($2)
43  {
44    let $3=3;
45    while ($3)
46    {
47      let $equal_records=30;
48      while ($equal_records)
49      {
50        eval insert into t1 select $1, $2, $3, $1 ,$2, $3;
51        dec $equal_records;
52      }
53      dec $3;
54    }
55    dec $2;
56  }
57 dec $1;
58}
59--enable_query_log
60ALTER TABLE t1 ENABLE KEYS;
61ANALYZE TABLE t1;
62
63--echo #
64--echo # Run index_merge queries two times: 1) with index dives
65--echo #                                    2) with index statistics
66--echo #
67let $iterations=2;
68while ($iterations)
69{
70  dec $iterations;
71
72  --echo
73  explain
74  select * from t1
75  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1 limit 5;
76
77  select * from t1
78  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1 limit 5;
79
80  --echo
81  explain
82  select * from t1
83  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 limit 5;
84
85  select * from t1
86  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 limit 5;
87
88  --echo
89  explain
90  select * from t1
91  where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1 limit 5;
92
93  select * from t1
94  where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1 limit 5;
95
96  --echo
97  SET eq_range_index_dive_limit=1;
98  SET SESSION DEBUG="+d,crash_records_in_range";
99}
100
101DROP TABLE t1;
102