Debian Codegraph @

mysql-test/suite/innodb/r/mdev-14846.result

raw
1 CREATE TABLE t1 (
2 pk INT,
3 f1 VARCHAR(10) NOT NULL,
4 f2 VARCHAR(10) NULL,
5 f3 INT UNSIGNED NULL,
6 KEY (f1),
7 PRIMARY KEY (pk)
8 ) ENGINE=InnoDB;
9 CREATE OR REPLACE ALGORITHM=MERGE VIEW v4 AS SELECT * FROM t1;
10 INSERT INTO t1 VALUES (1,'k','g',6),(2,'y','r',0),(3,'t','q',1),(4,'a','r',NULL),(5,'z','t',NULL);
11 CREATE TABLE t2 (f VARCHAR(10) NULL) ENGINE=InnoDB;
12 INSERT INTO t2 VALUES (NULL),('g'),('e'),('g');
13 CREATE TABLE t3 (
14 f1 VARCHAR(10) NOT NULL,
15 f2 VARCHAR(10) NULL,
16 f3 INT UNSIGNED NULL
17 ) ENGINE=InnoDB;
18 INSERT INTO t3 VALUES ('k','n',9),('y','b',8),('m','w',6);
19 CREATE TABLE t4 (f INT NULL) ENGINE=InnoDB;
20 INSERT INTO t4 VALUES (8),(9);
21 UPDATE t1 SET t1.pk = -109 WHERE t1.f1 IN ( SELECT 'a' FROM t4 WHERE f >= 1 );
22 SET DEBUG_SYNC='now SIGNAL con1_dml';
23 connect con1,localhost,root,,test;
24 SET DEBUG_SYNC='now WAIT_FOR con1_dml';
25 begin;
26 SELECT * FROM t1 for update;
27 pk f1 f2 f3
28 -109 a r NULL
29 1 k g 6
30 2 y r 0
31 3 t q 1
32 5 z t NULL
33 SET DEBUG_SYNC='now SIGNAL default_dml';
34 SET DEBUG_SYNC='now SIGNAL con2_dml';
35 connection default;
36 SET DEBUG_SYNC='now WAIT_FOR default_dml';
37 explain UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) SET alias1.f3 = 59 WHERE ( EXISTS ( SELECT t1.f3 FROM t1 IGNORE INDEX (f1) WHERE t1.f1 = alias1.f1 ) ) OR alias2.f1 = 'h';
38 id select_type table type possible_keys key key_len ref rows Extra
39 1 PRIMARY alias1 ALL NULL NULL NULL NULL #
40 1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using where
41 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where
42 UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) SET alias1.f3 = 59 WHERE ( EXISTS ( SELECT t1.f3 FROM t1 IGNORE INDEX (f1) WHERE t1.f1 = alias1.f1 ) ) OR alias2.f1 = 'h';
43 connect con2,localhost,root,,test;
44 set debug_sync='now WAIT_FOR con2_dml';
45 SET DEBUG_SYNC='now SIGNAL con1_dml2';
46 disconnect con2;
47 connection con1;
48 SET DEBUG_SYNC='now WAIT_FOR con1_dml2';
49 explain UPDATE v4, t1 SET t1.pk = 76 WHERE t1.f2 IN ( SELECT t2.f FROM t2 INNER JOIN t3 );
50 id select_type table type possible_keys key key_len ref rows Extra
51 1 PRIMARY t1 index NULL f1 42 NULL # Using index
52 1 PRIMARY t1 ALL NULL NULL NULL NULL #
53 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 43 func #
54 2 MATERIALIZED t3 ALL NULL NULL NULL NULL #
55 2 MATERIALIZED t2 ALL NULL NULL NULL NULL #
56 UPDATE v4, t1 SET t1.pk = 76 WHERE t1.f2 IN ( SELECT t2.f FROM t2 INNER JOIN t3 );
57 connection default;
58 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
59 connection con1;
60 COMMIT;
61 disconnect con1;
62 connection default;
63 DROP VIEW v4;
64 DROP TABLE t1, t2, t3, t4;
65 set debug_sync= reset;