1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
#
# BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a
# table is updated twice
#
CREATE TABLE t1(
pk INT,
a INT,
b INT,
PRIMARY KEY (pk)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (0,0,0);
UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
SELECT * FROM t1;
pk a b
0 0 0
CREATE VIEW v1 AS SELECT * FROM t1;
UPDATE v1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
SELECT * FROM t1;
pk a b
0 0 0
UPDATE t1 AS A, t1 AS B SET A.a = 1, B.b = 2;
# Should be (0,1,2)
SELECT * FROM t1;
pk a b
0 1 2
DROP VIEW v1;
DROP TABLE t1;
#
# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS
# UPDATED TWICE
#
CREATE TABLE t1 (
col_int_key int,
pk int,
col_int int,
key(col_int_key),
primary key (pk)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,2,3);
CREATE TABLE t2 (
col_int_key int,
pk_1 int,
pk_2 int,
col_int int,
key(col_int_key),
primary key (pk_1,pk_2)
) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1,2,3,4);
UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
SELECT * FROM t1;
col_int_key pk col_int
1 2 3
UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
SELECT * FROM t2;
col_int_key pk_1 pk_2 col_int
1 2 3 4
DROP TABLE t1,t2;
#
#Bug 11757486 - 49539: NON-DESCRIPTIVE ERR (ERROR 0 FROM STORAGE ENGINE)
# WITH MULTI-TABLE UPDATE
#
CREATE TABLE table_11757486 (field1 tinyint) ENGINE=INNODB;
INSERT INTO table_11757486 VALUES (0),(0);
SET SESSION SQL_MODE=default;
UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1;
Warnings:
Warning 1264 Out of range value for column 'field1' at row 1
Warning 1264 Out of range value for column 'field1' at row 2
UPDATE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1;
ERROR 22003: Out of range value for column 'field1' at row 1
UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1;
Warnings:
Warning 1264 Out of range value for column 'field1' at row 1
Warning 1264 Out of range value for column 'field1' at row 2
DROP TABLE table_11757486;
#
# Bug#20748537 INNODB: FAILING ASSERTION: NODE->PCUR->REL_POS == BTR_PCUR_ON
#
CREATE TABLE t1(c1 INT) ENGINE=INNODB;
INSERT INTO t1 VALUES (0),(0),(1);
CREATE TABLE t2(b INT) ENGINE=INNODB;
INSERT INTO t2 VALUES(0),(0);
ANALYZE TABLE t1,t2;
Table Op Msg_type Msg_text
test.t1 analyze status OK
test.t2 analyze status OK
EXPLAIN UPDATE t1,t2 SET t2.b=0;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
Warnings:
Note 1003 update `test`.`t1` join `test`.`t2` set `test`.`t2`.`b` = 0
SET @old_buf_result=@@sql_buffer_result;
SET sql_buffer_result=ON;
EXPLAIN UPDATE t1,t2 SET t2.b=0;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 UPDATE t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
Warnings:
Note 1003 update `test`.`t1` join `test`.`t2` set `test`.`t2`.`b` = 0
UPDATE t1,t2 SET t2.b=0;
SET sql_buffer_result=@old_buf_result;
DROP TABLE t1,t2;
#
# Bug#28716103 - MULTI-TABLE UPDATE OF PRIMARY KEY RESULTS IN ERROR 1032
# "CAN'T FIND RECORD"
#
CREATE TABLE t1 (c1 CHAR(255) NOT NULL);
CREATE TABLE t2 (c1 CHAR(255) NOT NULL, c2 CHAR(255) NOT NULL,
c3 CHAR(255) NOT NULL, PRIMARY KEY (c1, c2, c3));
INSERT INTO t1 VALUES ('x'), ('x');
INSERT INTO t2 VALUES ('x', '', '');
UPDATE t1, t2 SET t2.c2 = 'y', t2.c3 = 'y' WHERE t2.c1 = t1.c1;
SELECT * FROM t2;
c1 c2 c3
x y y
DROP TABLE t1, t2;
#
# Bug#30674616 - UPDATE QUERY FAILS WITH "ERROR 1071 (42000): SPECIFIED
# KEY WAS TOO LONG; MAX KEY LENGTH IS 1024 BYTES"
#
CREATE TABLE t1(c1 VARCHAR(350), c2 VARCHAR(350),
c3 INT, PRIMARY KEY(c1,c2)) charset=utf8mb4;
INSERT INTO t1 VALUES ('a','a',1);
INSERT INTO t1 VALUES ('a','b',2);
INSERT INTO t1 VALUES ('a','c',3);
INSERT INTO t1 VALUES ('a','d',4);
INSERT INTO t1 VALUES ('a','e',5);
INSERT INTO t1 VALUES ('a','f',6);
INSERT INTO t1 VALUES ('a','g',7);
INSERT INTO t1 VALUES ('a','h',8);
INSERT INTO t1 VALUES ('a','i',9);
INSERT INTO t1 VALUES ('a','j',10);
INSERT INTO t1 VALUES ('a','k',11);
SET @@SESSION.tmp_table_size=1024;
SET @@SESSION.internal_tmp_mem_storage_engine=MEMORY;
UPDATE t1 a, t1 b SET a.c3=22 WHERE a.c1 = b.c1;
SELECT COUNT(*) FROM t1 WHERE c3=22;
COUNT(*)
11
SET @@SESSION.tmp_table_size=DEFAULT;
SET @@SESSION.internal_tmp_mem_storage_engine=DEFAULT;
DROP TABLE t1;
|
