1 # Help file for avoid duplicated tests of different DEFAULT functions
3 # Needs $get_handler_status_counts defined
5 # let $get_handler_status_counts=
6 # SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
7 # WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
9 # And a table t3 where a is a DATETIME/DATE/TIME/TIMESTAMP column
10 # and b is a char/varchar() column.
15 SET TIMESTAMP = 1234567890;
16 INSERT INTO t3 (a) VALUES (NULL);
17 eval $get_handler_status_counts;
18 --echo
# 4 locks (1 table, 1 partition lock/unlock)
21 SET TIMESTAMP = 1234567891;
22 INSERT INTO t3 VALUES ();
23 eval $get_handler_status_counts;
24 --echo # 4 locks (1
table, 1 partition lock/unlock)
26 INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
27 eval $get_handler_status_counts;
28 --echo
# 4 locks (1 table, 1 partition lock/unlock)
30 INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
31 eval $get_handler_status_counts;
32 --echo
# 4 locks (1 table, 1 partition lock/unlock)
34 INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
35 eval $get_handler_status_counts;
36 --echo
# 6 locks (1 table, 2 partition lock/unlock)
39 SET TIMESTAMP = 1234567892;
40 INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
41 ON DUPLICATE
KEY UPDATE b = "DUP_KEY";
42 eval $get_handler_status_counts;
45 --echo # 8 locks (1
table, 3 partition lock/unlock)
46 --echo # No pruning due
to DEFAULT
function on partitioning column
47 --echo # 1 read_key + 1
delete + 2 write (1 failed + 1
ok)
48 --echo # 1
delete + 1 write due
to moved
to different partition
52 --echo # 4 locks (1
table, 1 partition lock/unlock)
53 --echo # 1 read_key + 1 update (same partition)
54 --echo # 1 (failed) write
57 INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01')
58 ON DUPLICATE
KEY UPDATE a = '2011-01-01 00:00:05', b = "DUP_KEY2";
59 eval $get_handler_status_counts;
60 --echo
# 8 locks (1 table, 3 partition lock/unlock)
61 --echo # No pruning due
to updating partitioning field.
62 --echo # 1 read_key + 1
delete + 2 write (1 failed + 1
ok)
63 --echo # 1
delete + 1 write due
to moved
to different partition
65 SET TIMESTAMP = 1234567893;
66 UPDATE t3 SET b =
'Updated' WHERE a =
'2011-01-01 00:00:02';
67 eval $get_handler_status_counts;
70 --echo # 8 locks (1
table, 3 partition lock/unlock)
71 --echo # No pruning due
to DEFAULT
function on partitioning column
72 --echo # 2 read_key + 1 read_rnd (1 read_key due
to index lookup,
73 --echo # 1 read_rnd + 1 read_key due
to positioning before update)
74 --echo # 1
delete + 1 write due
to moved
to different partition
75 --echo # + 1 (failed) write
79 --echo # 4 locks (1
table, 1 partition lock/unlock)
80 --echo # 1 read_key + 1 update (same partition)
81 --echo # 1 (failed) write
84 REPLACE INTO t3 VALUES (
'2011-01-01 00:00:04',
'Replace1');
85 eval $get_handler_status_counts;
86 --echo # 4 locks (1
table, 1 partition lock/unlock)
88 REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace2');
89 eval $get_handler_status_counts;
90 --echo
# 4 locks (1 table, 1 partition lock/unlock)
91 --echo # 1 read_key + 1 update + 1 failed write
94 --echo #
Test of
replace of
default PK (
delete might be needed first)
96 DELETE FROM t3 WHERE a = 0;
98 SET TIMESTAMP = 1234567894;
99 REPLACE INTO t3 (b) VALUES ('Replace3');
100 eval $get_handler_status_counts;
101 --echo
# 4 locks (1 table, 1 partition lock/unlock)
103 SET TIMESTAMP = 1234567894;
104 REPLACE INTO t3 (b) VALUES ('Replace4');
105 eval $get_handler_status_counts;
106 --echo
# 4 locks (1 table, 1 partition lock/unlock)
107 --echo # 1 read_key + 1 update + 1 failed write