Debian Codegraph @

mysql-test/suite/innodb/t/innodb_stats_del_mark.test

raw
1 --echo #
2 --echo # Bug 23333990 PERSISTENT INDEX STATISTICS UPDATE BEFORE
3 --echo # TRANSACTION IS COMMITTED
4 --echo #
5
6 --source include/not_hypergraph.inc # Uses traditional EXPLAIN to fetch and assign row count to a variable
7 --source include/big_test.inc
8 --source include/not_valgrind.inc
9
10 --echo "Test 1:- Uncommited delete test"
11 CREATE TABLE t1 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
12 val INT UNSIGNED NOT NULL,
13 INDEX (val)) ENGINE=INNODB
14 STATS_PERSISTENT=1,STATS_AUTO_RECALC=1;
15
16
17 INSERT INTO t1 (val) VALUES (4);
18 INSERT INTO t1 (val) SELECT VAL from t1;
19 INSERT INTO t1 (val) SELECT VAL from t1;
20 INSERT INTO t1 (val) SELECT VAL from t1;
21 INSERT INTO t1 (val) SELECT VAL from t1;
22 INSERT INTO t1 (val) SELECT VAL from t1;
23 INSERT INTO t1 (val) SELECT VAL from t1;
24 INSERT INTO t1 (val) SELECT VAL from t1;
25 INSERT INTO t1 (val) SELECT VAL from t1;
26 INSERT INTO t1 (val) SELECT VAL from t1;
27 INSERT INTO t1 (val) SELECT VAL from t1;
28 INSERT INTO t1 (val) SELECT VAL from t1;
29 INSERT INTO t1 (val) SELECT VAL from t1;
30 INSERT INTO t1 (val) SELECT VAL from t1;
31 INSERT INTO t1 (val) SELECT VAL from t1;
32 INSERT INTO t1 (val) SELECT VAL from t1;
33 INSERT INTO t1 (val) SELECT VAL from t1;
34 INSERT INTO t1 (val) SELECT VAL from t1;
35 INSERT INTO t1 (val) SELECT VAL from t1;
36 SELECT COUNT(*) FROM t1;
37
38 connect(con1, localhost, root,,);
39
40 --echo Connection 1
41 connection con1;
42 START TRANSACTION;
43 DELETE FROM t1;
44 send SELECT COUNT(*) FROM t1;
45
46 --echo Connection Default
47 connection default;
48 # To make test determinstic in case stats calculation is not
49 # triggered we will call analyze table
50 analyze table t1;
51 let $row_count= query_get_value(EXPLAIN SELECT * FROM t1 WHERE val=4, rows,1);
52 if ($row_count > 20000)
53 {
54 --echo Test correctly estimates the number of rows as > 20000
55 --echo even when in other uncommitted transaction
56 --echo all rows have been deleted.
57 }
58
59 if ($row_count < 20000)
60 {
61 --echo FAIL row count is $row_count
62 }
63 --echo Connection 1
64 connection con1;
65 reap;
66 commit;
67
68 --echo Connection deafult
69 connection default;
70
71 --echo Test 2:- Insert and rollback test
72 CREATE TABLE t2 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
73 val INT UNSIGNED NOT NULL,
74 INDEX (val)) ENGINE=INNODB
75 STATS_PERSISTENT=1,STATS_AUTO_RECALC=1;
76
77 --echo Connection 1
78 connection con1;
79
80 START TRANSACTION;
81
82 INSERT INTO t2 (val) VALUES (4);
83 INSERT INTO t2 (val) SELECT VAL from t2;
84 INSERT INTO t2 (val) SELECT VAL from t2;
85 INSERT INTO t2 (val) SELECT VAL from t2;
86 INSERT INTO t2 (val) SELECT VAL from t2;
87 INSERT INTO t2 (val) SELECT VAL from t2;
88 INSERT INTO t2 (val) SELECT VAL from t2;
89 INSERT INTO t2 (val) SELECT VAL from t2;
90 INSERT INTO t2 (val) SELECT VAL from t2;
91 INSERT INTO t2 (val) SELECT VAL from t2;
92 INSERT INTO t2 (val) SELECT VAL from t2;
93 INSERT INTO t2 (val) SELECT VAL from t2;
94 INSERT INTO t2 (val) SELECT VAL from t2;
95 INSERT INTO t2 (val) SELECT VAL from t2;
96 INSERT INTO t2 (val) SELECT VAL from t2;
97 INSERT INTO t2 (val) SELECT VAL from t2;
98 INSERT INTO t2 (val) SELECT VAL from t2;
99 INSERT INTO t2 (val) SELECT VAL from t2;
100 INSERT INTO t2 (val) SELECT VAL from t2;
101 send SELECT COUNT(*) FROM t2;
102
103 --echo Connection default
104 connection default;
105 select count(*) from t2;
106 analyze table t2;
107 let $row_count= query_get_value(EXPLAIN SELECT * FROM t2 WHERE val=4, rows,1);
108 if ($row_count > 20000)
109 {
110 --echo Test correctly estimates the number of rows as > 20000
111 --echo even when in other uncommitted transaction
112 --echo many rows are inserted.
113 }
114
115 --echo Connection 1
116 connection con1;
117 reap;
118 --echo Rollback the insert
119 rollback;
120
121 --echo Connection default
122 connection default;
123 let $row_count= query_get_value(EXPLAIN SELECT * FROM t2 WHERE val=4, rows,1);
124 if ($row_count <= 1)
125 {
126 --echo Test correctly estimates the number of rows as $row_count
127 --echo after rollback.
128 }
129
130 disconnect con1;
131 DROP TABLE t1,t2;