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