1'# SET STATEMENT ..... FOR ....  TEST'
2DROP TABLE IF EXISTS t1;
3DROP FUNCTION IF EXISTS myProc;
4DROP PROCEDURE IF EXISTS p1;
5DROP PROCEDURE IF EXISTS p2;
6DROP PROCEDURE IF EXISTS p3;
7DROP PROCEDURE IF EXISTS p4;
8DROP PROCEDURE IF EXISTS p5;
9DROP TABLE IF EXISTS STATEMENT;
10'# Setup database'
11CREATE TABLE t1 (v1 INT, v2 INT) ENGINE=MyISAM;
12INSERT INTO t1 VALUES (1,2);
13INSERT INTO t1 VALUES (3,4);
14''
15'#------------------ STATEMENT Test 1 -----------------------#'
16'# Initialize variables to known setting'
17SET SESSION sort_buffer_size=100000;
18''
19'# Pre-STATEMENT variable value'
20SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
21Variable_name	Value
22sort_buffer_size	100000
23SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1;
24v1	v2
251	2
263	4
27''
28'# Post-STATEMENT variable value'
29SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
30Variable_name	Value
31sort_buffer_size	100000
32''
33'#------------------ STATEMENT Test 2 -----------------------#'
34'# Initialize variables to known setting'
35SET SESSION binlog_format=mixed;
36SET SESSION sort_buffer_size=100000;
37'# Pre-STATEMENT variable value'
38SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
39Variable_name	Value
40sort_buffer_size	100000
41SHOW SESSION VARIABLES LIKE 'binlog_format';
42Variable_name	Value
43binlog_format	MIXED
44SET STATEMENT sort_buffer_size=150000, binlog_format=row
45FOR SELECT * FROM t1;
46v1	v2
471	2
483	4
49'# Post-STATEMENT variable value'
50SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
51Variable_name	Value
52sort_buffer_size	100000
53SHOW SESSION VARIABLES LIKE 'binlog_format';
54Variable_name	Value
55binlog_format	MIXED
56''
57'#------------------ STATEMENT Test 3 -----------------------#'
58'# set initial variable value, make prepared statement
59SET SESSION binlog_format=row;
60PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
61''
62'# Change variable setting'
63SET SESSION binlog_format=mixed;
64''
65'# Pre-STATEMENT variable value'
66''
67SHOW SESSION VARIABLES LIKE 'binlog_format';
68Variable_name	Value
69binlog_format	MIXED
70''
71EXECUTE stmt1;
72v1	v2
731	2
743	4
75''
76'# Post-STATEMENT variable value'
77SHOW SESSION VARIABLES LIKE 'binlog_format';
78Variable_name	Value
79binlog_format	MIXED
80''
81DEALLOCATE PREPARE stmt1;
82'#------------------ STATEMENT Test 4 -----------------------#'
83'# set initial variable value, make prepared statement
84SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
85''
86'# Pre-STATEMENT variable value'
87SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
88Variable_name	Value
89myisam_sort_buffer_size	500000
90SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
91Variable_name	Value
92myisam_repair_threads	1
93''
94SET STATEMENT myisam_sort_buffer_size=800000,
95myisam_repair_threads=2 FOR OPTIMIZE TABLE t1;
96Table	Op	Msg_type	Msg_text
97test.t1	optimize	status	OK
98''
99'# Post-STATEMENT variable value'
100SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
101Variable_name	Value
102myisam_sort_buffer_size	500000
103SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
104Variable_name	Value
105myisam_repair_threads	1
106''
107'#------------------ STATEMENT Test 5 -----------------------#'
108'# Initialize variables to known setting'
109SET SESSION sort_buffer_size=100000;
110''
111'# Pre-STATEMENT variable value'
112SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
113Variable_name	Value
114sort_buffer_size	100000
115''
116SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2;
117ERROR 42S02: Table 'test.t2' doesn't exist
118''
119'# Post-STATEMENT variable value'
120SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
121Variable_name	Value
122sort_buffer_size	100000
123''
124'#------------------ STATEMENT Test 6 -----------------------#'
125'# Initialize variables to known setting'
126SET SESSION  keep_files_on_create=ON;
127''
128'# Pre-STATEMENT variable value'
129SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
130Variable_name	Value
131keep_files_on_create	ON
132''
133SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1;
134v1	v2
1351	2
1363	4
137''
138'# Post-STATEMENT variable value'
139SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
140Variable_name	Value
141keep_files_on_create	ON
142''
143'#------------------ STATEMENT Test 7 -----------------------#'
144'# Initialize variables to known setting'
145SET SESSION  max_join_size=2222220000000;
146''
147'# Pre-STATEMENT variable value'
148SHOW SESSION VARIABLES LIKE 'max_join_size';
149Variable_name	Value
150max_join_size	2222220000000
151''
152SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1;
153v1	v2
1541	2
1553	4
156''
157'# Post-STATEMENT variable value'
158SHOW SESSION VARIABLES LIKE 'max_join_size';
159Variable_name	Value
160max_join_size	2222220000000
161''
162'#------------------Test 8-----------------------#'
163'# Initialize test variables'
164SET SESSION myisam_sort_buffer_size=500000,
165myisam_repair_threads=1,
166sort_buffer_size = 200000,
167max_join_size=2222220000000,
168keep_files_on_create=ON;
169''
170'#  LONG    '
171SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
172Variable_name	Value
173sort_buffer_size	200000
174SET STATEMENT sort_buffer_size = 100000
175FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
176Variable_name	Value
177sort_buffer_size	100000
178SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
179Variable_name	Value
180sort_buffer_size	200000
181''
182'# MY_BOOL     '
183SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
184Variable_name	Value
185keep_files_on_create	ON
186SET STATEMENT keep_files_on_create=OFF
187FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
188Variable_name	Value
189keep_files_on_create	OFF
190SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
191Variable_name	Value
192keep_files_on_create	ON
193''
194'# INT/LONG    '
195SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
196Variable_name	Value
197myisam_repair_threads	1
198SET STATEMENT myisam_repair_threads=2
199FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
200Variable_name	Value
201myisam_repair_threads	2
202SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
203Variable_name	Value
204myisam_repair_threads	1
205''
206'# ULONGLONG     '
207SHOW SESSION VARIABLES LIKE 'max_join_size';
208Variable_name	Value
209max_join_size	2222220000000
210SET STATEMENT max_join_size=2000000000000
211FOR SHOW SESSION VARIABLES LIKE 'max_join_size';
212Variable_name	Value
213max_join_size	2000000000000
214SHOW SESSION VARIABLES LIKE 'max_join_size';
215Variable_name	Value
216max_join_size	2222220000000
217''
218'#------------------Test 9-----------------------#'
219'# set initial variable values
220SET SESSION myisam_sort_buffer_size=500000,
221myisam_repair_threads=1,
222sort_buffer_size=100000,
223binlog_format=mixed,
224keep_files_on_create=ON,
225max_join_size=2222220000000;
226''
227''
228'# Pre-STATEMENT variable value
229SELECT @@myisam_sort_buffer_size,
230@@myisam_repair_threads,
231@@sort_buffer_size,
232@@binlog_format,
233@@keep_files_on_create,
234@@max_join_size;
235@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
236500000	1	100000	MIXED	1	2222220000000
237''
238''
239CREATE FUNCTION myProc (cost DECIMAL(10,2))
240RETURNS DECIMAL(10,2)
241SQL SECURITY DEFINER
242tax: BEGIN
243DECLARE order_tax DECIMAL(10,2);
244SET order_tax = cost * .05;
245RETURN order_tax;
246END|
247''
248'# During Execution values
249SET STATEMENT myisam_sort_buffer_size=400000,
250myisam_repair_threads=2,
251sort_buffer_size=200000,
252binlog_format=row,
253keep_files_on_create=OFF,
254max_join_size=4444440000000 FOR
255SELECT myProc(123.45);
256myProc(123.45)
2576.17
258''
259'# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
260SELECT @@myisam_sort_buffer_size,
261@@myisam_repair_threads,
262@@sort_buffer_size,
263@@binlog_format,
264@@keep_files_on_create,
265@@max_join_size;
266@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
267500000	1	100000	MIXED	1	2222220000000
268''
269SET STATEMENT myisam_sort_buffer_size=400000,
270myisam_repair_threads=2,
271sort_buffer_size=200000,
272binlog_format=row,
273keep_files_on_create=OFF,
274max_join_size=4444440000000 FOR
275DROP FUNCTION myProc;
276''
277'# Post-STATEMENT No 2 variable value
278SELECT @@myisam_sort_buffer_size,
279@@myisam_repair_threads,
280@@sort_buffer_size,
281@@binlog_format,
282@@keep_files_on_create,
283@@max_join_size;
284@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
285500000	1	100000	MIXED	1	2222220000000
286''
287'#------------------Test 10-----------------------#'
288'# set initial variable values
289SET SESSION myisam_sort_buffer_size=500000,
290myisam_repair_threads=1,
291sort_buffer_size=100000,
292binlog_format=mixed,
293keep_files_on_create=ON,
294max_join_size=2222220000000;
295''
296'# Pre-STATEMENT variable value
297SELECT @@myisam_sort_buffer_size,
298@@myisam_repair_threads,
299@@sort_buffer_size,
300@@binlog_format,
301@@keep_files_on_create,
302@@max_join_size;
303@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
304500000	1	100000	MIXED	1	2222220000000
305''
306''
307SET STATEMENT myisam_sort_buffer_size=400000,
308myisam_repair_threads=2,
309sort_buffer_size=200000,
310binlog_format=row,
311keep_files_on_create=OFF,
312max_join_size=4444440000000 FOR
313PREPARE stmt2
314FROM 'SELECT * FROM t1';
315''
316'Test No 1 Post Value & Test 2 Pre values'
317SELECT @@myisam_sort_buffer_size,
318@@myisam_repair_threads,
319@@sort_buffer_size,
320@@binlog_format,
321@@keep_files_on_create,
322@@max_join_size;
323@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
324500000	1	100000	MIXED	1	2222220000000
325''
326''
327SET STATEMENT myisam_sort_buffer_size=400000,
328myisam_repair_threads=2,
329sort_buffer_size=200000,
330binlog_format=row,
331keep_files_on_create=OFF,
332max_join_size=4444440000000 FOR
333EXECUTE stmt2;
334v1	v2
3351	2
3363	4
337''
338'# Post-STATEMENT No 2
339SELECT @@myisam_sort_buffer_size,
340@@myisam_repair_threads,
341@@sort_buffer_size,
342@@binlog_format,
343@@keep_files_on_create,
344@@max_join_size;
345@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
346500000	1	100000	MIXED	1	2222220000000
347''
348DEALLOCATE PREPARE stmt2;
349''
350'#------------------Test 11-----------------------#'
351'# set initial variable values
352SET SESSION myisam_sort_buffer_size=500000,
353myisam_repair_threads=1,
354sort_buffer_size=100000,
355binlog_format=mixed,
356keep_files_on_create=ON,
357max_join_size=2222220000000;
358''
359''
360'# Pre-STATEMENT variable value
361SELECT @@myisam_sort_buffer_size,
362@@myisam_repair_threads,
363@@sort_buffer_size,
364@@binlog_format,
365@@keep_files_on_create,
366@@max_join_size;
367@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
368500000	1	100000	MIXED	1	2222220000000
369''
370''
371SET STATEMENT myisam_sort_buffer_size=400000,
372myisam_repair_threads=2,
373sort_buffer_size=200000,
374keep_files_on_create=OFF,
375max_join_size=4444440000000 FOR
376PREPARE stmt1 FROM
377'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
378''
379'Test No 1 Post Value & Test 2 Pre values'
380SELECT @@myisam_sort_buffer_size,
381@@myisam_repair_threads,
382@@sort_buffer_size,
383@@binlog_format,
384@@keep_files_on_create,
385@@max_join_size;
386@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
387500000	1	100000	MIXED	1	2222220000000
388''
389''
390SET STATEMENT myisam_sort_buffer_size=400000,
391myisam_repair_threads=2,
392sort_buffer_size=200000,
393keep_files_on_create=OFF,
394max_join_size=4444440000000 FOR
395EXECUTE stmt1;
396v1	v2
3971	2
3983	4
399''
400'# Post-STATEMENT No 2
401SELECT @@myisam_sort_buffer_size,
402@@myisam_repair_threads,
403@@sort_buffer_size,
404@@binlog_format,
405@@keep_files_on_create,
406@@max_join_size;
407@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
408500000	1	100000	MIXED	1	2222220000000
409''
410''
411'#------------------Test 12-----------------------#'
412'# set initial variable values
413SET SESSION myisam_sort_buffer_size=500000,
414myisam_repair_threads=1,
415sort_buffer_size=100000,
416binlog_format=mixed,
417keep_files_on_create=ON,
418max_join_size=2222220000000;
419''
420''
421'# Pre-STATEMENT variable value
422SELECT @@myisam_sort_buffer_size,
423@@myisam_repair_threads,
424@@sort_buffer_size,
425@@binlog_format,
426@@keep_files_on_create,
427@@max_join_size;
428@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
429500000	1	100000	MIXED	1	2222220000000
430''
431''
432SET STATEMENT myisam_sort_buffer_size=400000,
433myisam_repair_threads=2,
434sort_buffer_size=200000,
435binlog_format=row,
436keep_files_on_create=OFF,
437max_join_size=4444440000000 FOR
438CREATE PROCEDURE p1() BEGIN
439SELECT @@myisam_sort_buffer_size,
440@@myisam_repair_threads,
441@@sort_buffer_size,
442@@binlog_format,
443@@keep_files_on_create,
444@@max_join_size;
445END|
446''
447'Test No 1 Post Value & Test 2 Pre values'
448SELECT @@myisam_sort_buffer_size,
449@@myisam_repair_threads,
450@@sort_buffer_size,
451@@binlog_format,
452@@keep_files_on_create,
453@@max_join_size;
454@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
455500000	1	100000	MIXED	1	2222220000000
456''
457''
458SET STATEMENT myisam_sort_buffer_size=400000,
459myisam_repair_threads=2,
460sort_buffer_size=200000,
461binlog_format=row,
462keep_files_on_create=OFF,
463max_join_size=4444440000000 FOR
464CALL p1();
465@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
466400000	2	200000	ROW	0	4444440000000
467''
468'# Post-STATEMENT No 2
469SELECT @@myisam_sort_buffer_size,
470@@myisam_repair_threads,
471@@sort_buffer_size,
472@@binlog_format,
473@@keep_files_on_create,
474@@max_join_size;
475@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
476500000	1	100000	MIXED	1	2222220000000
477''
478''
479'#------------------Test 13-----------------------#'
480'# set initial variable values
481SET SESSION myisam_sort_buffer_size=500000,
482myisam_repair_threads=1,
483sort_buffer_size=100000,
484binlog_format=mixed,
485keep_files_on_create=ON,
486max_join_size=2222220000000;
487''
488''
489CREATE PROCEDURE p2() BEGIN
490SET STATEMENT myisam_sort_buffer_size=400000,
491myisam_repair_threads=3,
492sort_buffer_size=300000,
493binlog_format=mixed,
494keep_files_on_create=OFF,
495max_join_size=3333330000000 FOR
496CALL p1();
497END|
498''
499'# Pre-STATEMENT variable value
500SELECT @@myisam_sort_buffer_size,
501@@myisam_repair_threads,
502@@sort_buffer_size,
503@@binlog_format,
504@@keep_files_on_create,
505@@max_join_size;
506@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
507500000	1	100000	MIXED	1	2222220000000
508''
509''
510SET STATEMENT myisam_sort_buffer_size=400000,
511myisam_repair_threads=2,
512sort_buffer_size=200000,
513binlog_format=row,
514keep_files_on_create=OFF,
515max_join_size=4444440000000 FOR
516CALL p2();
517@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
518400000	3	300000	MIXED	0	3333330000000
519''
520'# Post-STATEMENT
521SELECT @@myisam_sort_buffer_size,
522@@myisam_repair_threads,
523@@sort_buffer_size,
524@@binlog_format,
525@@keep_files_on_create,
526@@max_join_size;
527@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
528500000	1	100000	MIXED	1	2222220000000
529''
530''
531'#------------------Test 14-----------------------#'
532'# set initial variable values
533SET SESSION myisam_sort_buffer_size=500000,
534myisam_repair_threads=1,
535sort_buffer_size=100000,
536binlog_format=mixed,
537keep_files_on_create=ON,
538max_join_size=2222220000000;
539''
540''
541CREATE PROCEDURE p3() BEGIN
542SELECT @@myisam_sort_buffer_size,
543@@myisam_repair_threads,
544@@sort_buffer_size,
545@@binlog_format,
546@@keep_files_on_create,
547@@max_join_size;
548SET STATEMENT myisam_sort_buffer_size=320000,
549myisam_repair_threads=2,
550sort_buffer_size=220022,
551binlog_format=row,
552keep_files_on_create=ON,
553max_join_size=2222220000000 FOR
554CALL p2();
555END|
556''
557'# Pre-STATEMENT variable value
558SELECT @@myisam_sort_buffer_size,
559@@myisam_repair_threads,
560@@sort_buffer_size,
561@@binlog_format,
562@@keep_files_on_create,
563@@max_join_size;
564@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
565500000	1	100000	MIXED	1	2222220000000
566''
567''
568SET STATEMENT myisam_sort_buffer_size=400000,
569myisam_repair_threads=2,
570sort_buffer_size=200000,
571binlog_format=row,
572keep_files_on_create=OFF,
573max_join_size=4444440000000 FOR
574CALL p3();
575@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
576400000	2	200000	ROW	0	4444440000000
577@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
578400000	3	300000	MIXED	0	3333330000000
579''
580'# Post-STATEMENT
581SELECT @@myisam_sort_buffer_size,
582@@myisam_repair_threads,
583@@sort_buffer_size,
584@@binlog_format,
585@@keep_files_on_create,
586@@max_join_size;
587@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
588500000	1	100000	MIXED	1	2222220000000
589''
590''
591''
592''
593'#------------------Test 15-----------------------#'
594'# set initial variable values
595SET SESSION myisam_sort_buffer_size=500000,
596myisam_repair_threads=1,
597sort_buffer_size=100000,
598binlog_format=mixed,
599keep_files_on_create=ON,
600max_join_size=2222220000000;
601''
602''
603CREATE PROCEDURE p4() BEGIN
604SELECT @@myisam_sort_buffer_size,
605@@myisam_repair_threads,
606@@sort_buffer_size,
607@@binlog_format,
608@@keep_files_on_create,
609@@max_join_size;
610SET STATEMENT myisam_sort_buffer_size=320000,
611myisam_repair_threads=2,
612sort_buffer_size=220022,
613binlog_format=row,
614keep_files_on_create=ON,
615max_join_size=2222220000000 FOR
616SELECT @@myisam_sort_buffer_size,
617@@myisam_repair_threads,
618@@sort_buffer_size,
619@@binlog_format,
620@@keep_files_on_create,
621@@max_join_size;
622SET STATEMENT myisam_sort_buffer_size=320000,
623myisam_repair_threads=2,
624sort_buffer_size=220022,
625binlog_format=row,
626keep_files_on_create=ON,
627max_join_size=2222220000000 FOR
628SELECT @@myisam_sort_buffer_size,
629@@myisam_repair_threads,
630@@sort_buffer_size,
631@@binlog_format,
632@@keep_files_on_create,
633@@max_join_size;
634SET STATEMENT myisam_sort_buffer_size=320000,
635myisam_repair_threads=2,
636sort_buffer_size=220022,
637binlog_format=row,
638keep_files_on_create=ON,
639max_join_size=2222220000000 FOR
640SELECT @@myisam_sort_buffer_size,
641@@myisam_repair_threads,
642@@sort_buffer_size,
643@@binlog_format,
644@@keep_files_on_create,
645@@max_join_size;
646END|
647''
648'# Pre-STATEMENT variable value
649SELECT @@myisam_sort_buffer_size,
650@@myisam_repair_threads,
651@@sort_buffer_size,
652@@binlog_format,
653@@keep_files_on_create,
654@@max_join_size;
655@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
656500000	1	100000	MIXED	1	2222220000000
657''
658''
659SET STATEMENT myisam_sort_buffer_size=400000,
660myisam_repair_threads=2,
661sort_buffer_size=200000,
662binlog_format=row,
663keep_files_on_create=OFF,
664max_join_size=4444440000000 FOR
665CALL p4();
666@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
667400000	2	200000	ROW	0	4444440000000
668@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
669320000	2	220022	ROW	1	2222220000000
670@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
671320000	2	220022	ROW	1	2222220000000
672@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
673320000	2	220022	ROW	1	2222220000000
674''
675'# Post-STATEMENT
676SELECT @@myisam_sort_buffer_size,
677@@myisam_repair_threads,
678@@sort_buffer_size,
679@@binlog_format,
680@@keep_files_on_create,
681@@max_join_size;
682@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
683500000	1	100000	MIXED	1	2222220000000
684''
685''
686'#------------------Test 16-----------------------#'
687''
688'# Pre-STATEMENT variable value
689SELECT @@sql_mode;
690@@sql_mode
691ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
692''
693''
694SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1;
695v1	v2
6961	2
6973	4
698Warnings:
699Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
700''
701'# Post-STATEMENT
702SELECT @@sql_mode;
703@@sql_mode
704ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
705''
706''
707'#------------------Test 17-----------------------#'
708'# set initial variable values
709SET SESSION myisam_sort_buffer_size=500000,
710myisam_repair_threads=1,
711sort_buffer_size=100000,
712binlog_format=mixed,
713keep_files_on_create=ON,
714max_join_size=2222220000000;
715''
716'# Pre-STATEMENT variable value
717SELECT @@myisam_sort_buffer_size,
718@@myisam_repair_threads,
719@@sort_buffer_size,
720@@binlog_format,
721@@keep_files_on_create,
722@@max_join_size;
723@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
724500000	1	100000	MIXED	1	2222220000000
725''
726''
727SET STATEMENT myisam_sort_buffer_size=320000,
728myisam_repair_threads=2,
729sort_buffer_size=220022,
730binlog_format=row,
731keep_files_on_create=ON,
732max_join_size=2222220000000
733FOR SET SESSION
734myisam_sort_buffer_size=260000,
735myisam_repair_threads=3,
736sort_buffer_size=230013,
737binlog_format=row,
738keep_files_on_create=ON,
739max_join_size=2323230000000;
740''
741'# Post-STATEMENT
742SELECT @@myisam_sort_buffer_size,
743@@myisam_repair_threads,
744@@sort_buffer_size,
745@@binlog_format,
746@@keep_files_on_create,
747@@max_join_size;
748@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
749500000	1	100000	MIXED	1	2222220000000
750''
751''
752'#------------------Test 18-----------------------#'
753'# set initial variable values
754SET SESSION myisam_sort_buffer_size=500000,
755myisam_repair_threads=1,
756sort_buffer_size=100000,
757binlog_format=mixed,
758keep_files_on_create=ON,
759max_join_size=2222220000000;
760''
761'# Pre-STATEMENT variable value
762SELECT @@myisam_sort_buffer_size,
763@@myisam_repair_threads,
764@@sort_buffer_size,
765@@binlog_format,
766@@keep_files_on_create,
767@@max_join_size;
768@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
769500000	1	100000	MIXED	1	2222220000000
770''
771''
772CREATE PROCEDURE p5() BEGIN
773SELECT @@myisam_sort_buffer_size,
774@@myisam_repair_threads,
775@@sort_buffer_size,
776@@binlog_format,
777@@keep_files_on_create,
778@@max_join_size;
779SET SESSION
780myisam_sort_buffer_size=260000,
781myisam_repair_threads=3,
782sort_buffer_size=230013,
783binlog_format=row,
784keep_files_on_create=ON,
785max_join_size=2323230000000;
786SELECT @@myisam_sort_buffer_size,
787@@myisam_repair_threads,
788@@sort_buffer_size,
789@@binlog_format,
790@@keep_files_on_create,
791@@max_join_size;
792END|
793''
794''
795SET STATEMENT myisam_sort_buffer_size=400000,
796myisam_repair_threads=2,
797sort_buffer_size=200000,
798binlog_format=row,
799keep_files_on_create=OFF,
800max_join_size=4444440000000 FOR
801CALL p5();
802@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
803400000	2	200000	ROW	0	4444440000000
804@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
805260000	3	230013	ROW	1	2323230000000
806''
807'# Post-STATEMENT
808SELECT @@myisam_sort_buffer_size,
809@@myisam_repair_threads,
810@@sort_buffer_size,
811@@binlog_format,
812@@keep_files_on_create,
813@@max_join_size;
814@@myisam_sort_buffer_size	@@myisam_repair_threads	@@sort_buffer_size	@@binlog_format	@@keep_files_on_create	@@max_join_size
815500000	1	100000	MIXED	1	2222220000000
816''
817''
818'#------------------Test 19-----------------------#'
819SET STATEMENT max_error_count=100 FOR;
820ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
821SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2);
822ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT t1 VALUES (1,2)' at line 1
823SET STATEMENT FOR INSERT INTO t1 VALUES (1,2);
824ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1
825SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
826ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1
827SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
828ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1
829SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
830ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'max_error_count=100 FOR INSERT INTO t1 VALUES (1,2)' at line 1
831SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
832ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2)' at line 1
833''
834''
835'#------------------Test 20-----------------------#'
836SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2);
837ERROR HY000: Variable 'connect_timeout' is a GLOBAL variable and should be set with SET GLOBAL
838''
839''
840'#------------------Test 21-----------------------#'
841SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
842@@myisam_sort_buffer_size	@@sort_buffer_size
843500000	100000
844SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000
845FOR SET STATEMENT myisam_sort_buffer_size=200000
846FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
847@@myisam_sort_buffer_size	@@sort_buffer_size
848700000	3000000
849SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
850@@myisam_sort_buffer_size	@@sort_buffer_size
851500000	100000
852''
853''
854'#------------------Test 22-----------------------#'
855CREATE TABLE STATEMENT(a INT);
856DROP TABLE STATEMENT;
857Test for bug 1418049: SET STATEMENT ... FOR <statement> crashes server
858if <statement> needs to commit implicitly and fails
859CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
860SET @old_lock_wait_timeout = @@session.lock_wait_timeout;
861SET SESSION lock_wait_timeout = 1;
862BEGIN;
863INSERT INTO t2 VALUES (5);
864FLUSH TABLES WITH READ LOCK;
865SET STATEMENT max_join_size = 0 FOR DROP TABLE t2;
866ERROR HY000: Lock wait timeout exceeded; try restarting transaction
867UNLOCK TABLES;
868COMMIT;
869SET SESSION lock_wait_timeout = @old_lock_wait_timeout;
870Test for bug 1387951: SET STATEMENT ... FOR <statement> crashes server
871if <statement> is RW in a RO transaction
872SET SESSION transaction_read_only = TRUE;
873SET STATEMENT myisam_repair_threads=0 FOR OPTIMIZE TABLE t0;
874ERROR 25006: Cannot execute statement in a READ ONLY transaction.
875SET SESSION transaction_read_only = FALSE;
876Test for bug 1412423: SET STATEMENT ... FOR <statement> crashes server
877if <statement> needs to re-open a temp table and fails
878CREATE TEMPORARY TABLE t3 (a INT PRIMARY KEY) ENGINE=InnoDB;
879HANDLER t3 OPEN;
880SET STATEMENT max_join_size=1000 FOR SELECT * FROM t3;
881ERROR HY000: Can't reopen table: 't3'
882DROP TABLE t3;
883#
884# Bug 1392375: Crashing repeated execution of SET STATEMENT ... FOR <SELECT FROM view>
885#
886CREATE VIEW t3 AS SELECT 1 AS a;
887PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t3';
888EXECUTE stmt1;
889a
8901
891EXECUTE stmt1;
892a
8931
894DEALLOCATE PREPARE stmt1;
895DROP VIEW t3;
896#
897# Bug 1635927: Memory leak when using per-query variables with subquery temp tables
898#
899CREATE TABLE t3 (row_key INT NOT NULL DEFAULT 0, ref_key BIGINT(20) NOT NULL);
900INSERT INTO t3 (ref_key) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
901set @row_id := 0;
902INSERT INTO t3 (ref_key) SELECT @row_id := @row_id + 11 as ref_key
903FROM t3 a1, t3 a2, t3 a3;
904SET STATEMENT myisam_repair_threads=1 FOR
905SELECT count(1) FROM t3 s
906JOIN (SELECT row_key, ref_key AS ref_key FROM t3 t
907WHERE t.row_key IN (SELECT row_key FROM t3)) AS r;
908count(1)
9091020100
910DROP TABLE t3;
911''
912'# Cleanup'
913DROP TABLE t1, t2;
914DROP PROCEDURE p1;
915DROP PROCEDURE p2;
916DROP PROCEDURE p3;
917DROP PROCEDURE p4;
918DROP PROCEDURE p5;
919SET STATEMENT innodb_tmpdir=@@global.tmpdir FOR SELECT @@innodb_tmpdir;
920@@innodb_tmpdir
921TMPDIR
922SET SESSION innodb_tmpdir = @@global.tmpdir;
923SELECT @@innodb_tmpdir;
924@@innodb_tmpdir
925TMPDIR
926SET STATEMENT sql_mode='' FOR SELECT @@innodb_tmpdir;
927@@innodb_tmpdir
928TMPDIR
929Warnings:
930Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
931SELECT @@innodb_tmpdir;
932@@innodb_tmpdir
933TMPDIR
934SET STATEMENT innodb_tmpdir='TESTDIR' FOR SELECT @@innodb_tmpdir;
935@@innodb_tmpdir
936TESTDIR
937SELECT @@innodb_tmpdir;
938@@innodb_tmpdir
939TMPDIR
940#
941# Bug 1385352: SET STATEMENT does not work after SET GLOBAL / SHOW GLOBAL STATUS
942# and affects the global value
943#
944SHOW GLOBAL STATUS LIKE 'Threads_connected';
945Variable_name	Value
946Threads_connected	1
947SELECT @@GLOBAL.sql_mode;
948@@GLOBAL.sql_mode
949ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
950SELECT @@GLOBAL.auto_increment_offset;
951@@GLOBAL.auto_increment_offset
9521
953SET STATEMENT sql_mode = 'NO_AUTO_CREATE_USER', auto_increment_offset = 123 FOR
954SELECT @@SESSION.sql_mode, @@SESSION.auto_increment_offset;
955@@SESSION.sql_mode	@@SESSION.auto_increment_offset
956NO_AUTO_CREATE_USER	123
957SELECT @@GLOBAL.sql_mode;
958@@GLOBAL.sql_mode
959ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
960SELECT @@GLOBAL.auto_increment_offset;
961@@GLOBAL.auto_increment_offset
9621
963SET @saved_general_log = @@GLOBAL.general_log;
964SET GLOBAL general_log = 0;
965SET STATEMENT sql_mode='NO_AUTO_CREATE_USER', auto_increment_offset=123 FOR
966SELECT @@SESSION.sql_mode, @@SESSION.auto_increment_offset;
967@@SESSION.sql_mode	@@SESSION.auto_increment_offset
968NO_AUTO_CREATE_USER	123
969SELECT @@GLOBAL.sql_mode;
970@@GLOBAL.sql_mode
971ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
972SELECT @@GLOBAL.auto_increment_offset;
973@@GLOBAL.auto_increment_offset
9741
975SET GLOBAL general_log = @saved_general_log;
976