1# ==== Purpose ==== 2# 3# Test under what circumstances there is a warning for unsafe 4# statements on the following form: 5# 6# [INSERT...SELECT | REPLACE...SELECT | DELETE | UPDATE] ... ORDER BY ... LIMIT 7# 8# INSERT...SELECT...LIMIT should give a warning because the order of 9# rows may differ between master and slave, so the LIMIT may select a 10# different set of rows on master and slave. However, if there is an 11# 'ORDER BY primary_key', then the order is known and there should not 12# be any warning. The same is true for REPLACE...SELECT, DELETE, and 13# UPDATE. In REPLACE...SELECT and INSERT...SELECT, the select may be 14# compound using UNION or JOIN or subqueries in the FROM clause. So 15# we test LIMIT in various places in the subqueries. 16# 17# We also test various forms of ORDER BY ... LIMIT. If there is one 18# ORDER BY column and it is a PRIMARY KEY or NOT NULL UNIQUE KEY, then 19# the order is deterministic. If there is one ORDER BY column and it 20# is neither a PRIMARY KEY nor a NOT NULL UNIQUE KEY, then the order 21# is not deterministic. If the ORDER BY consists of several columns, 22# and a prefix of the ORDER BY columns is a PRIMARY KEY or NOT NULL 23# UNIQUE KEY, then the order is deterministic. If the ORDER BY 24# consists of several columns, and no prefix of the ORDER BY COLUMNS 25# is a PRIMARY KEY or NOT NULL UNIQUE KEY, then the order is not 26# deterministic. 27 28source include/have_log_bin.inc; 29source include/have_binlog_format_statement.inc; 30 31--disable_query_log 32call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 33--enable_query_log 34 35# 36# Create the tables for test 37# Where: 38# - nokey is a column not part of key 39# - pk is column of the primary key 40# - nuk is a column of UNIQUE KEY, which can be NULL. 41# - nnuk is a column of NOT NULL UNIQUE KEY 42# - nnuk1 and nnuk2 are NOT NULL columns, and togather forms a UNIQUE KEY. 43# - nnuk_str_long is a VARCHAR UNIQUE KEY column that is longer than MAX_LENGTH_SORT in this test. 44# - nnuk_str_short is a VARCHAR UNIQUE KEY column that is shorter than MAX_LENGTH_SORT in this test. 45# 46CREATE TABLE t (nokey INT, pk INT PRIMARY KEY, nnuk INT NOT NULL UNIQUE KEY, nuk INT UNIQUE KEY, 47 nnuk1 INT NOT NULL, nnuk2 INT NOT NULL, UNIQUE KEY(nnuk1, nnuk2), 48 nnuk_str_long VARCHAR(512) NOT NULL UNIQUE KEY, 49 nnuk_str_short VARCHAR(50) NOT NULL UNIQUE KEY); 50CREATE TABLE t1 LIKE t; 51CREATE TABLE t2 LIKE t; 52CREATE TABLE t3 LIKE t; 53CREATE TABLE t4 LIKE t; 54 55 56CREATE TABLE queries (query VARCHAR(1024) NOT NULL); 57CREATE TABLE result_queries (id INT AUTO_INCREMENT PRIMARY KEY, query VARCHAR(1024) NOT NULL); 58CREATE TABLE limits (`limit` VARCHAR(256) NOT NULL); 59 60# 61# Iterate through the following queries 62# 63# [LIMIT_1] and [LIMIT_2] will iterate over the strings in table `limits` 64# 65INSERT INTO queries(query) VALUES 66 ('UPDATE t SET nokey = nokey + 10 [LIMIT_1]'), 67 ('UPDATE t SET nokey = nokey + 10 WHERE nokey=1 [LIMIT_1]'), 68 ('UPDATE t SET nokey = nokey + 10 WHERE nuk=1 [LIMIT_1]'), 69 ('UPDATE t SET nokey = nokey + 10 WHERE nnuk=1 [LIMIT_1]'), 70 ('UPDATE t SET nokey = nokey + 10 WHERE pk=1 [LIMIT_1]'), 71 72 ('DELETE FROM t [LIMIT_1]'), 73 ('DELETE FROM t WHERE nokey=1 [LIMIT_1]'), 74 ('DELETE FROM t WHERE nuk=1 [LIMIT_1]'), 75 ('DELETE FROM t WHERE nnuk=1 [LIMIT_1]'), 76 ('DELETE FROM t WHERE pk=1 [LIMIT_1]'), 77 78 ('REPLACE INTO t SELECT * FROM t1 [LIMIT_1]'), 79 ('REPLACE INTO t SELECT * FROM t1 WHERE nokey=1 [LIMIT_1]'), 80 ('REPLACE INTO t SELECT * FROM t1 WHERE nuk=1 [LIMIT_1]'), 81 ('REPLACE INTO t SELECT * FROM t1 WHERE nnuk=1 [LIMIT_1]'), 82 ('REPLACE INTO t SELECT * FROM t1 WHERE pk=1 [LIMIT_1]'), 83 84 ('INSERT INTO t SELECT * FROM t1 [LIMIT_1]'), 85 ('INSERT INTO t SELECT * FROM t1 WHERE nokey=1 [LIMIT_1]'), 86 ('INSERT INTO t SELECT * FROM t1 WHERE nuk=1 [LIMIT_1]'), 87 ('INSERT INTO t SELECT * FROM t1 WHERE nnuk=1 [LIMIT_1]'), 88 ('INSERT INTO t SELECT * FROM t1 WHERE pk=1 [LIMIT_1]'), 89 90 ('INSERT INTO t (SELECT * FROM t1) UNION (SELECT * FROM t2) [LIMIT_1]'), 91 ('INSERT INTO t (SELECT * FROM t1 [LIMIT_1]) UNION (SELECT * FROM t2)'), 92 ('INSERT INTO t (SELECT * FROM t1) UNION (SELECT * FROM t2 [LIMIT_1])'), 93 ('INSERT INTO t (SELECT * FROM t1 [LIMIT_1]) UNION (SELECT * FROM t2 [LIMIT_2])'), 94 95 ('INSERT INTO t SELECT * FROM (SELECT * FROM t1 [LIMIT_1]) AS subselect [LIMIT_2]'), 96 ('INSERT INTO t SELECT * FROM t1 WHERE pk IN (SELECT t2.pk FROM t2 WHERE t1.pk = t2.pk) [LIMIT_1]'), 97 ('INSERT INTO t SELECT t1.* FROM t1, t2 [LIMIT_1]'), 98 ('INSERT INTO t SELECT t1.* FROM t1, t2 WHERE t1.nokey=t2.nokey [LIMIT_1]'), 99 ('INSERT INTO t SELECT t1.* FROM t1, t2 WHERE t1.nuk=t2.nuk [LIMIT_1]'), 100 ('INSERT INTO t SELECT t1.* FROM t1, t2 WHERE t1.nnuk=t2.nnuk [LIMIT_1]'), 101 ('INSERT INTO t SELECT t1.* FROM t1, t2 WHERE t1.pk=t2.pk [LIMIT_1]'), 102 103 ('INSERT INTO t SELECT t1.* FROM t1 NATURAL JOIN t2 [LIMIT_1]'), 104 ('INSERT INTO t SELECT t1.* FROM t1 JOIN t2 USING(pk) [LIMIT_1]'), 105 ('INSERT INTO t SELECT t1.* FROM t1 JOIN t2 ON t1.nnuk1=t2.nnuk1 AND t1.nnuk2=t2.nnuk2 [LIMIT_1]'), 106 ('INSERT INTO t SELECT t1.* FROM t1 JOIN t2 ON t1.nnuk1=t2.nnuk2 AND t1.nnuk2=t2.nnuk1 [LIMIT_1]'), 107 ('INSERT INTO t SELECT t1.* FROM t1 JOIN (t2 JOIN t3 USING (pk)) USING(pk) [LIMIT_1]'), 108 ('INSERT INTO t SELECT t1.* FROM t1 JOIN (t2 JOIN (t3 JOIN t4 USING (pk)) ON t2.pk=t3.pk) ON t1.pk=t4.pk [LIMIT_1]'), 109 ('INSERT INTO t SELECT t1.* FROM t1 JOIN (t2 JOIN (t3 JOIN t4 USING (pk)) ON t2.nnuk=t3.nnuk) ON t1.nnuk1=t4.nnuk1 AND t1.nnuk2=t4.nnuk2 [LIMIT_1]'), 110 ('INSERT INTO t SELECT t1.* FROM t1 JOIN (t2 JOIN (t3 JOIN t4 USING (pk)) ON t2.nnuk=t3.nnuk) ON t1.nnuk1=t4.nnuk1 AND t1.nnuk2=t4.nnuk2 AND t2.pk=1 [LIMIT_1]'), 111 ('INSERT INTO t SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.pk=t2.pk [LIMIT_1]'), 112 ('INSERT INTO t SELECT t2.* FROM t1 RIGHT JOIN t2 ON t1.pk=t2.pk [LIMIT_1]'); 113 114 115# 116# Let the LIMIT cluase ([LIMIT_1] and [LIMIT_2]) iterate through the following strings: 117# 118INSERT INTO limits (`limit`) VALUES 119 ('LIMIT 0'), 120 ('LIMIT 1'), 121 ('ORDER BY nokey LIMIT 1'), 122 ('ORDER BY nuk LIMIT 1'), 123 ('ORDER BY nnuk1 LIMIT 1'), 124 ('ORDER BY nnuk LIMIT 1'), 125 ('ORDER BY nnuk_str_short LIMIT 1'), 126 ('ORDER BY nnuk_str_long LIMIT 1'), 127 ('ORDER BY pk LIMIT 1'), 128 ('ORDER BY nnuk1, nnuk2 LIMIT 1'), 129 ('ORDER BY nnuk1, nnuk2, nokey LIMIT 1'), 130 ('ORDER BY nnuk1, nokey, nnuk2 LIMIT 1'); 131 132 133DELIMITER |; 134 135# 136# This function is used to generate the queries by replace [LIMIT_1] 137# and [LIMIT_2] with values in table `limits` for values in table 138# `queries`. And the result will be save in table `result_queries`. 139# 140 141CREATE PROCEDURE gen_queries() 142BEGIN 143 DECLARE done INT DEFAULT 0; 144 DECLARE q VARCHAR(1024); 145 DECLARE limit1, limit2 VARCHAR(256); 146 DECLARE qcur CURSOR FOR SELECT * FROM queries; 147 DECLARE lcur1 CURSOR FOR SELECT * FROM limits; 148 DECLARE lcur2 CURSOR FOR SELECT * FROM limits; 149 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 150 151 OPEN qcur; 152 153 FETCH qcur INTO q; 154 WHILE done <> 1 DO 155 OPEN lcur1; 156 FETCH lcur1 INTO limit1; 157 WHILE done <> 1 DO 158 159 IF LOCATE('[LIMIT_2]', q) > 0 THEN 160 OPEN lcur2; 161 FETCH lcur2 INTO limit2; 162 WHILE done <> 1 DO 163 SELECT REPLACE(REPLACE(q, '[LIMIT_1]', limit1), '[LIMIT_2]', limit2) INTO @query; 164 FETCH lcur2 INTO limit2; 165 END WHILE; 166 CLOSE lcur2; 167 SET done = 0; 168 ELSE 169 SELECT REPLACE(q, '[LIMIT_1]', limit1) INTO @query; 170 END IF; 171 INSERT INTO result_queries set query=@query; 172 FETCH lcur1 INTO limit1; 173 END WHILE; 174 CLOSE lcur1; 175 SET done = 0; 176 FETCH qcur INTO q; 177 END WHILE; 178 CLOSE qcur; 179END| 180 181DELIMITER ;| 182 183call gen_queries(); 184 185# 186# Execute the generated queries 187# 188let $count= `SELECT COUNT(*) FROM result_queries`; 189let $id=1; 190SET MAX_SORT_LENGTH=50; 191while (`SELECT $id <= $count`) 192{ 193 disable_query_log; 194 TRUNCATE t; 195 TRUNCATE t1; 196 TRUNCATE t2; 197 TRUNCATE t3; 198 TRUNCATE t4; 199 200 # INSERT INTO t VALUES (1, 1, 1, 1, 1, 1), (2, 2, 2, 2, 2, 2); 201 # INSERT INTO t1 VALUES (3, 3, 3, 3, 3, 3), (4, 4, 4, 4, 4, 4); 202 # INSERT INTO t2 VALUES (5, 5, 5, 5, 5, 5), (6, 6, 6, 6, 6, 6); 203 # INSERT INTO t3 VALUES (7, 7, 7, 7, 7, 7), (8, 8, 8, 8, 8, 8); 204 # INSERT INTO t4 VALUES (9, 9, 9, 9, 9, 9), (10, 10, 10, 10, 10, 10); 205 206 INSERT INTO t VALUES (1, 1, 1, 1, 1, 1, "a", "a"), (2, 2, 2, 2, 2, 2, "b", "b"); 207 INSERT INTO t1 VALUES (3, 3, 3, 3, 3, 3, "c", "c"), (4, 4, 4, 4, 4, 4, "d", "d"); 208 INSERT INTO t2 VALUES (5, 5, 5, 5, 5, 5, "e", "e"), (6, 6, 6, 6, 6, 6, "f", "f"); 209 INSERT INTO t3 VALUES (7, 7, 7, 7, 7, 7, "g", "g"), (8, 8, 8, 8, 8, 8, "h", "h"); 210 INSERT INTO t4 VALUES (9, 9, 9, 9, 9, 9, "i", "i"), (10, 10, 10, 10, 10, 10, "j", "j"); 211 212 let $query= `SELECT query FROM result_queries WHERE id=$id`; 213 enable_query_log; 214 215 eval $query; 216 let $rows= `SELECT COUNT(*) FROM t`; 217 echo ROWS in table t: $rows; 218 inc $id; 219} 220SET MAX_SORT_LENGTH=DEFAULT; 221 222# 223# Cleanup 224# 225DROP TABLE t, t1, t2, t3, t4; 226DROP TABLE queries, result_queries, limits; 227DROP PROCEDURE gen_queries; 228