163 lines
6.4 KiB
SQL
163 lines
6.4 KiB
SQL
--source include/have_debug.inc
|
|
--source include/have_debug_sync.inc
|
|
--source include/not_windows.inc
|
|
--source include/have_grep.inc
|
|
|
|
--disable_query_log
|
|
--disable_result_log
|
|
--disable_warnings
|
|
|
|
connect (ce, localhost, root,,test);
|
|
connect (cq, localhost, root,,test);
|
|
connection cq;
|
|
SET GLOBAL innodb_stats_persistent=on;
|
|
|
|
|
|
CREATE TABLE t1 (a int PRIMARY KEY, b varchar(128), KEY (b)) ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 13 ;
|
|
CREATE TABLE t2 (a int PRIMARY KEY AUTO_INCREMENT, b varchar(128)) ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 13 ;
|
|
INSERT INTO t1 VALUES (1, 'First row, p1') ;
|
|
INSERT INTO t1 VALUES (0, 'First row, p0'), (2, 'First row, p2'), (3, 'First row, p3'), (4, 'First row, p4') ;
|
|
INSERT INTO t1 VALUES (1 * 13, 'Second row, p0'), (2 * 13, 'Third row, p0'), (3 * 13, 'Fourth row, p0'), (4 * 13, 'Fifth row, p0') ;
|
|
INSERT INTO t2 VALUES (NULL, 'First auto-inc row') ;
|
|
INSERT INTO t2 (b) VALUES ('Second auto-inc row') ;
|
|
INSERT INTO t2 VALUES (10, 'First row, p10') ;
|
|
UPDATE t2 SET b = CONCAT(b, ', UPDATED') WHERE a = 10 ;
|
|
INSERT INTO t1 VALUES (65, 'No duplicate') ON DUPLICATE KEY UPDATE b = CONCAT(b, ', INSERT_DUP_KEY_UPDATE') ;
|
|
INSERT INTO t1 VALUES (65, 'No duplicate') ON DUPLICATE KEY UPDATE b = CONCAT(b, ', INSERT_DUP_KEY_UPDATE') ;
|
|
INSERT INTO t1 VALUES (78, 'No duplicate') ON DUPLICATE KEY UPDATE a = a + 13, b = CONCAT(b, ', INSERT_DUP_KEY_UPDATE') ;
|
|
INSERT INTO t1 VALUES (78, 'No duplicate') ON DUPLICATE KEY UPDATE a = a + 13, b = CONCAT(b, ', INSERT_DUP_KEY_UPDATE') ;
|
|
INSERT INTO t1 VALUES (78, 'No duplicate') ON DUPLICATE KEY UPDATE a = a + 13, b = CONCAT(b, ', INSERT_DUP_KEY_UPDATE third') ;
|
|
INSERT INTO t1 VALUES (104, 'No duplicate') ON DUPLICATE KEY UPDATE a = a + 1 ;
|
|
INSERT INTO t1 VALUES (104, 'No duplicate') ON DUPLICATE KEY UPDATE a = a + 1 ;
|
|
INSERT INTO t1 VALUES (104, 'No duplicate 104') ON DUPLICATE KEY UPDATE a = a + 1 ;
|
|
REPLACE INTO t1 VALUES (5, 'REPLACE first') ;
|
|
REPLACE INTO t1 VALUES (5, 'REPLACE second') ;
|
|
UPDATE t1 SET b = CONCAT(b, ', updated 1') WHERE a IN (13, 26, 39, 52) ;
|
|
UPDATE t1 SET a = 99, b = CONCAT(b, ', updated 2 -> p8') WHERE a = 13 ;
|
|
UPDATE t1 SET a = 13 + 99, b = CONCAT(b, ', updated 3') WHERE a = 99 ;
|
|
UPDATE t1 SET a = a + 1, b = CONCAT(b, ', updated 4 -> p9') WHERE a = 112 ;
|
|
UPDATE t1 SET b = CONCAT(b, ', same as min(a) + 2 in t2') WHERE a = (SELECT MIN(a) + 2 FROM t2) ;
|
|
|
|
let $point= planned_single_update;let $query= UPDATE t1 SET b = CONCAT(b, ', max(a) in t2: ', (SELECT MAX(a) FROM t2)) WHERE a = 5 ;
|
|
--source include/explain_for_connection.inc
|
|
let $point= before_reset_query_plan;let $query= UPDATE t1, t2 SET t1.b = CONCAT(t1.b, ', t2.b:', t2.b), t2.b = CONCAT(t2.b, ', t1.b:', t1.b) WHERE t2.b = t1.b and t2.a = 4 ;
|
|
--source include/explain_for_connection.inc
|
|
|
|
DROP TABLE t1,t2;
|
|
|
|
CREATE TABLE t1 ( t3_id int NOT NULL, t1_id int NOT NULL, PRIMARY KEY (t1_id) ) ;
|
|
CREATE TABLE t2 ( t2_id int NOT NULL, t1_id int NOT NULL, b int NOT NULL, PRIMARY KEY (t2_id), UNIQUE KEY idx_t2_t1_b (t1_id,b) ) ENGINE=InnoDB ;
|
|
CREATE TABLE t3 ( t3_id int NOT NULL ) ;
|
|
INSERT INTO t3 VALUES (3) ;
|
|
let $point= before_reset_query_plan;let $query= SELECT (SELECT rs.t2_id FROM t2 rs WHERE rs.t1_id= (SELECT lt.t1_id FROM t1 lt WHERE lt.t3_id=a.t3_id) ORDER BY b DESC LIMIT 1) from t3 AS a ;
|
|
--source include/explain_for_connection.inc
|
|
DROP PROCEDURE IF EXISTS p1 ;
|
|
DROP TABLE t1,t2,t3 ;
|
|
|
|
###############################
|
|
#MTR FOR BUG#14581870 #
|
|
###############################
|
|
|
|
CREATE TABLE a1 (
|
|
col_int int(11) DEFAULT NULL,
|
|
pk int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (pk)
|
|
) DEFAULT CHARSET=latin1;
|
|
|
|
CREATE TABLE aa1 (
|
|
col_int int(11) DEFAULT NULL,
|
|
pk int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (pk)
|
|
) DEFAULT CHARSET=latin1;
|
|
|
|
let $query=SELECT DISTINCT alias2.col_int AS field1 , alias2.col_int AS field2 FROM a1 AS alias1 LEFT JOIN aa1 AS alias2 ON alias1.pk = alias2.pk WHERE ( alias1.col_int = 8 OR alias1 .pk <= alias1.col_int ) GROUP BY field1, field2 ORDER BY field1, field2 DESC;
|
|
--source include/explain_for_connection.inc
|
|
|
|
DROP TABLE a1;
|
|
DROP TABLE aa1;
|
|
|
|
###############################
|
|
#MTR FOR BUG#14586538 #
|
|
###############################
|
|
|
|
CREATE TABLE h2 (
|
|
pk int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (pk)
|
|
) AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
|
|
|
|
insert into h2 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
|
|
ANALYZE TABLE h2;
|
|
|
|
CREATE TABLE aa3 (
|
|
col_int_key int(11) DEFAULT NULL,
|
|
KEY col_int_key (col_int_key)
|
|
) DEFAULT CHARSET=latin1;
|
|
|
|
let $query=SELECT alias1.pk AS field1 FROM h2 AS alias1 LEFT JOIN aa3 AS alias2 ON alias1.pk = alias2.col_int_key WHERE alias1.pk <> 9 GROUP BY field1 ORDER BY field1 LIMIT 1 OFFSET 3;
|
|
--source include/explain_for_connection.inc
|
|
|
|
DROP TABLE h2;
|
|
DROP TABLE aa3;
|
|
|
|
###############################
|
|
#MTR FOR BUG#14591376 #
|
|
###############################
|
|
|
|
CREATE TABLE e1 (
|
|
col_int int(11) DEFAULT NULL,
|
|
pk int(11) NOT NULL AUTO_INCREMENT,
|
|
col_int_key int(11) DEFAULT NULL,
|
|
PRIMARY KEY (pk),
|
|
KEY col_int_key (col_int_key)
|
|
) AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
|
|
|
|
insert into e1 values (1, NULL,NULL);
|
|
insert into e1 values (2,6,NULL);
|
|
insert into e1 values (3,8,-1131610112);
|
|
insert into e1 values (4,2,-1009057792);
|
|
insert into e1 values (5,-1220345856,1);
|
|
insert into e1 values (6,NULL,-185204736);
|
|
|
|
ANALYZE TABLE e1;
|
|
|
|
CREATE TABLE cc2 (
|
|
col_int int(11) DEFAULT NULL
|
|
) AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
|
|
|
|
insert into cc2 values ( 685899776),(NULL);
|
|
|
|
ANALYZE TABLE CC2;
|
|
|
|
let $query=SELECT alias1.pk AS field1 FROM e1 AS alias1 RIGHT JOIN cc2 AS alias2 ON alias1.col_int = alias2.col_int WHERE alias1.col_int_key IN (8) ;
|
|
--source include/explain_for_connection.inc
|
|
|
|
DROP TABLE e1;
|
|
DROP TABLE cc2;
|
|
|
|
###############################
|
|
#MTR FOR Bug#15915676 #
|
|
###############################
|
|
|
|
CREATE TABLE t1 (
|
|
pk INT NOT NULL,
|
|
col_int INT,
|
|
col_varchar_key VARCHAR(10),
|
|
PRIMARY KEY (pk),
|
|
KEY col_varchar_key (col_varchar_key)
|
|
) ;
|
|
|
|
CREATE TABLE t2 (
|
|
pk INT NOT NULL,
|
|
col_varchar_key VARCHAR(10),
|
|
PRIMARY KEY (pk),
|
|
KEY col_varchar_key (col_varchar_key)
|
|
) ;
|
|
|
|
let $query=SELECT DISTINCT alias2.pk AS field1, alias2.pk AS field2, alias2.pk AS field3 FROM t1 AS alias1 RIGHT JOIN t2 AS alias2 ON alias1.col_varchar_key = alias2.col_varchar_key WHERE alias1.col_int >alias1.col_int HAVING field3 = 7 ORDER BY field1, field2, field3 LIMIT 10 OFFSET 1;
|
|
|
|
--source include/explain_for_connection.inc
|
|
--enable_warnings
|
|
drop table t1;
|
|
drop table t2;
|