Debian Codegraph @

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

raw
1
2 #
3 # Test multi update with different join methods
4 #
5
6 CREATE TABLE bug38999_1 (a int not null primary key, b int not null, key (b)) engine=innodb;
7 CREATE TABLE bug38999_2 (a int not null primary key, b int not null, key (b)) engine=innodb;
8 INSERT INTO bug38999_1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12);
9 INSERT INTO bug38999_2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
10
11 # Full join, without key
12 update bug38999_1,bug38999_2 set bug38999_1.a=bug38999_1.a+100;
13 --sorted_result
14 select * from bug38999_1;
15
16 # unique key
17 update bug38999_1,bug38999_2 set bug38999_1.a=bug38999_1.a+100 where bug38999_1.a=101;
18 --sorted_result
19 select * from bug38999_1;
20
21 # ref key
22 update bug38999_1,bug38999_2 set bug38999_1.b=bug38999_1.b+10 where bug38999_1.b=2;
23 --sorted_result
24 select * from bug38999_1;
25
26 # Run ANALYZE in order to increase the likelihood of getting the same
27 # query plan on every run of the next update statement.
28 # Note: With different query plans, the next update statement can produce
29 # different results that are still valid.
30
31 --disable_query_log
32 --disable_result_log
33 ANALYZE TABLE bug38999_1,bug38999_2;
34 --enable_result_log
35 --enable_query_log
36
37 # Range key (in bug38999_1)
38 # To ensure stable test case: The use of force index (b) is added in order
39 # to ensure that this index is used. If this index is not used, the order
40 # of the assigments can change and a different but still valid result can be
41 # produced from this statement.
42 update bug38999_1 force index (b) straight_join bug38999_2
43 set bug38999_1.b=bug38999_1.b+2,
44 bug38999_2.b=bug38999_1.b+10
45 where bug38999_1.b between 3 and 5 and bug38999_1.a=bug38999_2.a+100;
46 --sorted_result
47 select * from bug38999_1;
48 # It is unspecified whether the assignment of bug38999_2.b sees the
49 # modification of bug38999_1.b on the same row. The hypergraph optimizer
50 # chooses a different plan (not using range scan on bug38999_1.b, as that
51 # prevents immediate update) and does the assignments in a different
52 # order.
53 --skip_if_hypergraph
54 --sorted_result
55 select * from bug38999_2;
56
57 drop table bug38999_1,bug38999_2;
58
59 --echo #
60 --echo # Bug#54475 improper error handling causes cascading crashing failures in innodb/ndb
61 --echo #
62 CREATE TABLE t1(f1 INT) ENGINE=INNODB;
63 INSERT INTO t1 VALUES(1);
64 --error ER_OPERAND_COLUMNS
65 UPDATE (SELECT ((SELECT 1 FROM t1), 1) FROM t1 WHERE (SELECT 1 FROM t1)) x, (SELECT 1) AS d SET d.f1 = 1;
66 DROP TABLE t1;