1--source include/have_innodb.inc
2--source include/count_sessions.inc
3--echo '# SET STATEMENT ..... FOR ....  TEST'
4############################ STATEMENT_SET #############################
5#                                                                      #
6# Testing working functionality of SET STATEMENT                       #
7#                                                                      #
8#                                                                      #
9# There is important documentation within                              #
10#                                                                      #
11#                                                                      #
12# Author: Joe Lukas                                                    #
13# Creation:                                                            #
14# 2009-08-02 Implement this test as part of                            #
15#                   WL#681 Per query variable settings                 #
16#                                                                      #
17########################################################################
18
19--disable_warnings
20DROP TABLE IF EXISTS t1;
21DROP FUNCTION IF EXISTS myProc;
22DROP PROCEDURE IF EXISTS p1;
23DROP PROCEDURE IF EXISTS p2;
24DROP PROCEDURE IF EXISTS p3;
25DROP PROCEDURE IF EXISTS p4;
26DROP PROCEDURE IF EXISTS p5;
27DROP TABLE IF EXISTS STATEMENT;
28--enable_warnings
29####################################################################
30#Set up current database
31####################################################################
32--echo '# Setup database'
33CREATE TABLE t1 (v1 INT, v2 INT) ENGINE=MyISAM;
34INSERT INTO t1 VALUES (1,2);
35INSERT INTO t1 VALUES (3,4);
36--echo ''
37--echo '#------------------ STATEMENT Test 1 -----------------------#'
38####################################################################
39#   Checks with variable value type ulong                          #
40####################################################################
41--echo '# Initialize variables to known setting'
42SET SESSION sort_buffer_size=100000;
43--echo ''
44--echo '# Pre-STATEMENT variable value'
45SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
46SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1;
47--echo ''
48--echo '# Post-STATEMENT variable value'
49SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
50--echo ''
51--echo '#------------------ STATEMENT Test 2 -----------------------#'
52####################################################################
53#   Checks for multiple set values inside STATEMENT ... FOR        #
54####################################################################
55--echo '# Initialize variables to known setting'
56SET SESSION binlog_format=mixed;
57SET SESSION sort_buffer_size=100000;
58--echo '# Pre-STATEMENT variable value'
59SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
60SHOW SESSION VARIABLES LIKE 'binlog_format';
61SET STATEMENT sort_buffer_size=150000, binlog_format=row
62 FOR SELECT * FROM t1;
63--echo '# Post-STATEMENT variable value'
64SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
65SHOW SESSION VARIABLES LIKE 'binlog_format';
66
67--echo ''
68--echo '#------------------ STATEMENT Test 3 -----------------------#'
69####################################################################
70#   Check current variable value is stored in using stored         #
71#   statements.                                                    #
72####################################################################
73--echo '# set initial variable value, make prepared statement
74SET SESSION binlog_format=row;
75PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
76--echo ''
77--echo '# Change variable setting'
78SET SESSION binlog_format=mixed;
79--echo ''
80--echo '# Pre-STATEMENT variable value'
81--echo ''
82SHOW SESSION VARIABLES LIKE 'binlog_format';
83--echo ''
84EXECUTE stmt1;
85--echo ''
86--echo '# Post-STATEMENT variable value'
87SHOW SESSION VARIABLES LIKE 'binlog_format';
88
89--echo ''
90DEALLOCATE PREPARE stmt1;
91--echo '#------------------ STATEMENT Test 4 -----------------------#'
92####################################################################
93#   Check works with OPTIMIZE TABLE command                        #
94#   Checks works with a variable value of type INT                 #
95#   Checks works with variable type ULONGLONG                      #
96####################################################################
97--echo '# set initial variable value, make prepared statement
98SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
99--echo ''
100--echo '# Pre-STATEMENT variable value'
101SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
102SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
103--echo ''
104SET STATEMENT myisam_sort_buffer_size=800000,
105              myisam_repair_threads=2 FOR OPTIMIZE TABLE t1;
106--echo ''
107--echo '# Post-STATEMENT variable value'
108SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
109SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
110
111--echo ''
112--echo '#------------------ STATEMENT Test 5 -----------------------#'
113####################################################################
114#   Checks if variable reset after error in statement after FOR    #
115####################################################################
116--echo '# Initialize variables to known setting'
117SET SESSION sort_buffer_size=100000;
118--echo ''
119--echo '# Pre-STATEMENT variable value'
120SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
121--echo ''
122--error ER_NO_SUCH_TABLE
123SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2;
124--echo ''
125--echo '# Post-STATEMENT variable value'
126SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
127
128--echo ''
129--echo '#------------------ STATEMENT Test 6 -----------------------#'
130####################################################################
131#   Checks works with variable type MY_BOOL                        #
132####################################################################
133--echo '# Initialize variables to known setting'
134SET SESSION  keep_files_on_create=ON;
135--echo ''
136--echo '# Pre-STATEMENT variable value'
137SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
138--echo ''
139SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1;
140--echo ''
141--echo '# Post-STATEMENT variable value'
142SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
143
144--echo ''
145--echo '#------------------ STATEMENT Test 7 -----------------------#'
146####################################################################
147#   Checks works with variable type HA_ROWS                        #
148####################################################################
149--echo '# Initialize variables to known setting'
150SET SESSION  max_join_size=2222220000000;
151--echo ''
152--echo '# Pre-STATEMENT variable value'
153SHOW SESSION VARIABLES LIKE 'max_join_size';
154--echo ''
155SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1;
156--echo ''
157--echo '# Post-STATEMENT variable value'
158SHOW SESSION VARIABLES LIKE 'max_join_size';
159
160--echo ''
161--echo '#------------------Test 8-----------------------#'
162####################################################################
163#   Ensure variable of each type is set to proper value during     #
164#   statement after FOR execution                                  #
165####################################################################
166--echo '# Initialize test variables'
167SET SESSION myisam_sort_buffer_size=500000,
168            myisam_repair_threads=1,
169            sort_buffer_size = 200000,
170            max_join_size=2222220000000,
171            keep_files_on_create=ON;
172
173--echo ''
174--echo '#  LONG    '
175SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
176SET STATEMENT sort_buffer_size = 100000
177              FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
178SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
179--echo ''
180--echo '# MY_BOOL     '
181SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
182SET STATEMENT keep_files_on_create=OFF
183              FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
184SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
185
186--echo ''
187--echo '# INT/LONG    '
188SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
189SET STATEMENT myisam_repair_threads=2
190              FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
191SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
192--echo ''
193--echo '# ULONGLONG     '
194SHOW SESSION VARIABLES LIKE 'max_join_size';
195SET STATEMENT max_join_size=2000000000000
196              FOR SHOW SESSION VARIABLES LIKE 'max_join_size';
197SHOW SESSION VARIABLES LIKE 'max_join_size';
198
199--echo ''
200--echo '#------------------Test 9-----------------------#'
201####################################################################
202#   No 1 - Check works with CREATE ... BEGIN ... END  command      #
203#          Display variables during execution                      #
204#   No 2 - Test with DROP command                                  #
205####################################################################
206--echo '# set initial variable values
207SET SESSION myisam_sort_buffer_size=500000,
208            myisam_repair_threads=1,
209            sort_buffer_size=100000,
210            binlog_format=mixed,
211            keep_files_on_create=ON,
212            max_join_size=2222220000000;
213--echo ''
214--echo ''
215--echo '# Pre-STATEMENT variable value
216SELECT @@myisam_sort_buffer_size,
217       @@myisam_repair_threads,
218       @@sort_buffer_size,
219       @@binlog_format,
220       @@keep_files_on_create,
221       @@max_join_size;
222--echo ''
223--echo ''
224DELIMITER |;
225CREATE FUNCTION myProc (cost DECIMAL(10,2))
226   RETURNS DECIMAL(10,2)
227
228   SQL SECURITY DEFINER
229
230   tax: BEGIN
231       DECLARE order_tax DECIMAL(10,2);
232       SET order_tax = cost * .05;
233       RETURN order_tax;
234   END|
235DELIMITER ;|
236--echo ''
237--echo '# During Execution values
238SET STATEMENT myisam_sort_buffer_size=400000,
239              myisam_repair_threads=2,
240              sort_buffer_size=200000,
241              binlog_format=row,
242              keep_files_on_create=OFF,
243              max_join_size=4444440000000 FOR
244              SELECT myProc(123.45);
245--echo ''
246--echo '# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
247SELECT @@myisam_sort_buffer_size,
248       @@myisam_repair_threads,
249       @@sort_buffer_size,
250       @@binlog_format,
251       @@keep_files_on_create,
252       @@max_join_size;
253--echo ''
254SET STATEMENT myisam_sort_buffer_size=400000,
255              myisam_repair_threads=2,
256              sort_buffer_size=200000,
257              binlog_format=row,
258              keep_files_on_create=OFF,
259              max_join_size=4444440000000 FOR
260              DROP FUNCTION myProc;
261--echo ''
262--echo '# Post-STATEMENT No 2 variable value
263SELECT @@myisam_sort_buffer_size,
264       @@myisam_repair_threads,
265       @@sort_buffer_size,
266       @@binlog_format,
267       @@keep_files_on_create,
268       @@max_join_size;
269
270--echo ''
271--echo '#------------------Test 10-----------------------#'
272####################################################################
273#   No 1 - Check with PREPARE statement                            #
274#         with STATEMENT inside with same variable as outside      #
275#   No 2 - Check with EXECUTE statement                            #
276####################################################################
277--echo '# set initial variable values
278SET SESSION myisam_sort_buffer_size=500000,
279            myisam_repair_threads=1,
280            sort_buffer_size=100000,
281            binlog_format=mixed,
282            keep_files_on_create=ON,
283            max_join_size=2222220000000;
284--echo ''
285--echo '# Pre-STATEMENT variable value
286SELECT @@myisam_sort_buffer_size,
287       @@myisam_repair_threads,
288       @@sort_buffer_size,
289       @@binlog_format,
290       @@keep_files_on_create,
291       @@max_join_size;
292--echo ''
293--echo ''
294SET STATEMENT myisam_sort_buffer_size=400000,
295              myisam_repair_threads=2,
296              sort_buffer_size=200000,
297              binlog_format=row,
298              keep_files_on_create=OFF,
299              max_join_size=4444440000000 FOR
300              PREPARE stmt2
301              FROM 'SELECT * FROM t1';
302--echo ''
303--echo 'Test No 1 Post Value & Test 2 Pre values'
304SELECT @@myisam_sort_buffer_size,
305       @@myisam_repair_threads,
306       @@sort_buffer_size,
307       @@binlog_format,
308       @@keep_files_on_create,
309       @@max_join_size;
310--echo ''
311--echo ''
312SET STATEMENT myisam_sort_buffer_size=400000,
313              myisam_repair_threads=2,
314              sort_buffer_size=200000,
315              binlog_format=row,
316              keep_files_on_create=OFF,
317              max_join_size=4444440000000 FOR
318              EXECUTE stmt2;
319--echo ''
320--echo '# Post-STATEMENT No 2
321SELECT @@myisam_sort_buffer_size,
322       @@myisam_repair_threads,
323       @@sort_buffer_size,
324       @@binlog_format,
325       @@keep_files_on_create,
326       @@max_join_size;
327--echo ''
328DEALLOCATE PREPARE stmt2;
329--echo ''
330--echo '#------------------Test 11-----------------------#'
331####################################################################
332#   No 1 - Check with PREPARE statement                            #
333#          check with different variable on inside PREPARE         #
334#   No 2 - Check with EXECUTE statement                            #
335####################################################################
336--echo '# set initial variable values
337SET SESSION myisam_sort_buffer_size=500000,
338            myisam_repair_threads=1,
339            sort_buffer_size=100000,
340            binlog_format=mixed,
341            keep_files_on_create=ON,
342            max_join_size=2222220000000;
343--echo ''
344--echo ''
345--echo '# Pre-STATEMENT variable value
346SELECT @@myisam_sort_buffer_size,
347       @@myisam_repair_threads,
348       @@sort_buffer_size,
349       @@binlog_format,
350       @@keep_files_on_create,
351       @@max_join_size;
352--echo ''
353--echo ''
354SET STATEMENT myisam_sort_buffer_size=400000,
355              myisam_repair_threads=2,
356              sort_buffer_size=200000,
357              keep_files_on_create=OFF,
358              max_join_size=4444440000000 FOR
359              PREPARE stmt1 FROM
360             'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
361--echo ''
362--echo 'Test No 1 Post Value & Test 2 Pre values'
363SELECT @@myisam_sort_buffer_size,
364       @@myisam_repair_threads,
365       @@sort_buffer_size,
366       @@binlog_format,
367       @@keep_files_on_create,
368       @@max_join_size;
369--echo ''
370--echo ''
371SET STATEMENT myisam_sort_buffer_size=400000,
372              myisam_repair_threads=2,
373              sort_buffer_size=200000,
374              keep_files_on_create=OFF,
375              max_join_size=4444440000000 FOR
376              EXECUTE stmt1;
377--echo ''
378--echo '# Post-STATEMENT No 2
379SELECT @@myisam_sort_buffer_size,
380       @@myisam_repair_threads,
381       @@sort_buffer_size,
382       @@binlog_format,
383       @@keep_files_on_create,
384       @@max_join_size;
385--echo ''
386--echo ''
387--echo '#------------------Test 12-----------------------#'
388####################################################################
389#   No 1 - Check with PROCEDURE     (show variables in procedure)  #
390#   No 2 - Check with CALL statement show variables in PROCEDURE   #
391####################################################################
392--echo '# set initial variable values
393SET SESSION myisam_sort_buffer_size=500000,
394            myisam_repair_threads=1,
395            sort_buffer_size=100000,
396            binlog_format=mixed,
397            keep_files_on_create=ON,
398            max_join_size=2222220000000;
399--echo ''
400--echo ''
401--echo '# Pre-STATEMENT variable value
402SELECT @@myisam_sort_buffer_size,
403       @@myisam_repair_threads,
404       @@sort_buffer_size,
405       @@binlog_format,
406       @@keep_files_on_create,
407       @@max_join_size;
408--echo ''
409--echo ''
410DELIMITER |;
411SET STATEMENT myisam_sort_buffer_size=400000,
412              myisam_repair_threads=2,
413              sort_buffer_size=200000,
414              binlog_format=row,
415              keep_files_on_create=OFF,
416              max_join_size=4444440000000 FOR
417              CREATE PROCEDURE p1() BEGIN
418              SELECT @@myisam_sort_buffer_size,
419                     @@myisam_repair_threads,
420                     @@sort_buffer_size,
421                     @@binlog_format,
422                     @@keep_files_on_create,
423                     @@max_join_size;
424              END|
425DELIMITER ;|
426--echo ''
427--echo 'Test No 1 Post Value & Test 2 Pre values'
428SELECT @@myisam_sort_buffer_size,
429       @@myisam_repair_threads,
430       @@sort_buffer_size,
431       @@binlog_format,
432       @@keep_files_on_create,
433       @@max_join_size;
434--echo ''
435--echo ''
436SET STATEMENT myisam_sort_buffer_size=400000,
437              myisam_repair_threads=2,
438              sort_buffer_size=200000,
439              binlog_format=row,
440              keep_files_on_create=OFF,
441              max_join_size=4444440000000 FOR
442              CALL p1();
443--echo ''
444--echo '# Post-STATEMENT No 2
445SELECT @@myisam_sort_buffer_size,
446       @@myisam_repair_threads,
447       @@sort_buffer_size,
448       @@binlog_format,
449       @@keep_files_on_create,
450       @@max_join_size;
451--echo ''
452--echo ''
453
454--echo '#------------------Test 13-----------------------#'
455####################################################################
456#   Check PROCEDURE containing SET STATEMENT FOR                   #
457#      p1() from test 12 will be used to display variables         #
458####################################################################
459--echo '# set initial variable values
460SET SESSION myisam_sort_buffer_size=500000,
461            myisam_repair_threads=1,
462            sort_buffer_size=100000,
463            binlog_format=mixed,
464            keep_files_on_create=ON,
465            max_join_size=2222220000000;
466--echo ''
467--echo ''
468DELIMITER |;
469CREATE PROCEDURE p2() BEGIN
470       SET STATEMENT myisam_sort_buffer_size=400000,
471              myisam_repair_threads=3,
472              sort_buffer_size=300000,
473              binlog_format=mixed,
474              keep_files_on_create=OFF,
475              max_join_size=3333330000000 FOR
476              CALL p1();
477              END|
478DELIMITER ;|
479--echo ''
480--echo '# Pre-STATEMENT variable value
481SELECT @@myisam_sort_buffer_size,
482       @@myisam_repair_threads,
483       @@sort_buffer_size,
484       @@binlog_format,
485       @@keep_files_on_create,
486       @@max_join_size;
487--echo ''
488--echo ''
489SET STATEMENT myisam_sort_buffer_size=400000,
490              myisam_repair_threads=2,
491              sort_buffer_size=200000,
492              binlog_format=row,
493              keep_files_on_create=OFF,
494              max_join_size=4444440000000 FOR
495              CALL p2();
496--echo ''
497--echo '# Post-STATEMENT
498SELECT @@myisam_sort_buffer_size,
499       @@myisam_repair_threads,
500       @@sort_buffer_size,
501       @@binlog_format,
502       @@keep_files_on_create,
503       @@max_join_size;
504--echo ''
505--echo ''
506--echo '#------------------Test 14-----------------------#'
507####################################################################
508#   Check PROCEDURE containing compound SET STATEMENT FOR          #
509#      p2() will be used as compounding statement from test 13     #
510####################################################################
511--echo '# set initial variable values
512SET SESSION myisam_sort_buffer_size=500000,
513            myisam_repair_threads=1,
514            sort_buffer_size=100000,
515            binlog_format=mixed,
516            keep_files_on_create=ON,
517            max_join_size=2222220000000;
518--echo ''
519--echo ''
520DELIMITER |;
521CREATE PROCEDURE p3() BEGIN
522       SELECT @@myisam_sort_buffer_size,
523              @@myisam_repair_threads,
524              @@sort_buffer_size,
525              @@binlog_format,
526              @@keep_files_on_create,
527              @@max_join_size;
528       SET STATEMENT myisam_sort_buffer_size=320000,
529              myisam_repair_threads=2,
530              sort_buffer_size=220022,
531              binlog_format=row,
532              keep_files_on_create=ON,
533              max_join_size=2222220000000 FOR
534              CALL p2();
535              END|
536DELIMITER ;|
537--echo ''
538--echo '# Pre-STATEMENT variable value
539SELECT @@myisam_sort_buffer_size,
540       @@myisam_repair_threads,
541       @@sort_buffer_size,
542       @@binlog_format,
543       @@keep_files_on_create,
544       @@max_join_size;
545--echo ''
546--echo ''
547SET STATEMENT myisam_sort_buffer_size=400000,
548              myisam_repair_threads=2,
549              sort_buffer_size=200000,
550              binlog_format=row,
551              keep_files_on_create=OFF,
552              max_join_size=4444440000000 FOR
553              CALL p3();
554--echo ''
555--echo '# Post-STATEMENT
556SELECT @@myisam_sort_buffer_size,
557       @@myisam_repair_threads,
558       @@sort_buffer_size,
559       @@binlog_format,
560       @@keep_files_on_create,
561       @@max_join_size;
562--echo ''
563--echo ''
564
565       --echo ''
566--echo ''
567--echo '#------------------Test 15-----------------------#'
568####################################################################
569#   Check PROCEDURE containing compound SET STATEMENT FOR          #
570#      call multiple SET STATEMENT .. FOR showing SELECT           #
571####################################################################
572--echo '# set initial variable values
573SET SESSION myisam_sort_buffer_size=500000,
574            myisam_repair_threads=1,
575            sort_buffer_size=100000,
576            binlog_format=mixed,
577            keep_files_on_create=ON,
578            max_join_size=2222220000000;
579--echo ''
580--echo ''
581DELIMITER |;
582CREATE PROCEDURE p4() BEGIN
583       SELECT @@myisam_sort_buffer_size,
584              @@myisam_repair_threads,
585              @@sort_buffer_size,
586              @@binlog_format,
587              @@keep_files_on_create,
588              @@max_join_size;
589       SET STATEMENT myisam_sort_buffer_size=320000,
590              myisam_repair_threads=2,
591              sort_buffer_size=220022,
592              binlog_format=row,
593              keep_files_on_create=ON,
594              max_join_size=2222220000000 FOR
595              SELECT @@myisam_sort_buffer_size,
596              @@myisam_repair_threads,
597              @@sort_buffer_size,
598              @@binlog_format,
599              @@keep_files_on_create,
600              @@max_join_size;
601       SET STATEMENT myisam_sort_buffer_size=320000,
602              myisam_repair_threads=2,
603              sort_buffer_size=220022,
604              binlog_format=row,
605              keep_files_on_create=ON,
606              max_join_size=2222220000000 FOR
607              SELECT @@myisam_sort_buffer_size,
608              @@myisam_repair_threads,
609              @@sort_buffer_size,
610              @@binlog_format,
611              @@keep_files_on_create,
612              @@max_join_size;
613       SET STATEMENT myisam_sort_buffer_size=320000,
614              myisam_repair_threads=2,
615              sort_buffer_size=220022,
616              binlog_format=row,
617              keep_files_on_create=ON,
618              max_join_size=2222220000000 FOR
619              SELECT @@myisam_sort_buffer_size,
620              @@myisam_repair_threads,
621              @@sort_buffer_size,
622              @@binlog_format,
623              @@keep_files_on_create,
624              @@max_join_size;
625              END|
626DELIMITER ;|
627--echo ''
628--echo '# Pre-STATEMENT variable value
629SELECT @@myisam_sort_buffer_size,
630       @@myisam_repair_threads,
631       @@sort_buffer_size,
632       @@binlog_format,
633       @@keep_files_on_create,
634       @@max_join_size;
635--echo ''
636--echo ''
637SET STATEMENT myisam_sort_buffer_size=400000,
638              myisam_repair_threads=2,
639              sort_buffer_size=200000,
640              binlog_format=row,
641              keep_files_on_create=OFF,
642              max_join_size=4444440000000 FOR
643              CALL p4();
644--echo ''
645--echo '# Post-STATEMENT
646SELECT @@myisam_sort_buffer_size,
647       @@myisam_repair_threads,
648       @@sort_buffer_size,
649       @@binlog_format,
650       @@keep_files_on_create,
651       @@max_join_size;
652
653--echo ''
654--echo ''
655--echo '#------------------Test 16-----------------------#'
656####################################################################
657#   Test Effect on parsing                                         #
658####################################################################
659--echo ''
660--echo '# Pre-STATEMENT variable value
661SELECT @@sql_mode;
662--echo ''
663--echo ''
664SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1;
665
666--echo ''
667--echo '# Post-STATEMENT
668SELECT @@sql_mode;
669--echo ''
670--echo ''
671--echo '#------------------Test 17-----------------------#'
672####################################################################
673#   Test effect of SET STATEMENT FOR with SET SESSION modifying    #
674#   the same variables as the SET STATEMENT                        #
675####################################################################
676--echo '# set initial variable values
677SET SESSION myisam_sort_buffer_size=500000,
678            myisam_repair_threads=1,
679            sort_buffer_size=100000,
680            binlog_format=mixed,
681            keep_files_on_create=ON,
682            max_join_size=2222220000000;
683--echo ''
684--echo '# Pre-STATEMENT variable value
685SELECT @@myisam_sort_buffer_size,
686       @@myisam_repair_threads,
687       @@sort_buffer_size,
688       @@binlog_format,
689       @@keep_files_on_create,
690       @@max_join_size;
691--echo ''
692--echo ''
693SET STATEMENT myisam_sort_buffer_size=320000,
694           myisam_repair_threads=2,
695           sort_buffer_size=220022,
696           binlog_format=row,
697           keep_files_on_create=ON,
698           max_join_size=2222220000000
699           FOR SET SESSION
700           myisam_sort_buffer_size=260000,
701           myisam_repair_threads=3,
702           sort_buffer_size=230013,
703           binlog_format=row,
704           keep_files_on_create=ON,
705           max_join_size=2323230000000;
706
707--echo ''
708--echo '# Post-STATEMENT
709SELECT @@myisam_sort_buffer_size,
710       @@myisam_repair_threads,
711       @@sort_buffer_size,
712       @@binlog_format,
713       @@keep_files_on_create,
714       @@max_join_size;
715
716--echo ''
717--echo ''
718--echo '#------------------Test 18-----------------------#'
719####################################################################
720#   Test effect of SET SESSION inside a stored procedure with      #
721#   with a SET STATEMENT on outside variables                      #
722####################################################################
723--echo '# set initial variable values
724SET SESSION myisam_sort_buffer_size=500000,
725            myisam_repair_threads=1,
726            sort_buffer_size=100000,
727            binlog_format=mixed,
728            keep_files_on_create=ON,
729            max_join_size=2222220000000;
730--echo ''
731--echo '# Pre-STATEMENT variable value
732SELECT @@myisam_sort_buffer_size,
733       @@myisam_repair_threads,
734       @@sort_buffer_size,
735       @@binlog_format,
736       @@keep_files_on_create,
737       @@max_join_size;
738--echo ''
739--echo ''
740DELIMITER |;
741CREATE PROCEDURE p5() BEGIN
742	  SELECT @@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		SET SESSION
749           myisam_sort_buffer_size=260000,
750           myisam_repair_threads=3,
751           sort_buffer_size=230013,
752           binlog_format=row,
753           keep_files_on_create=ON,
754           max_join_size=2323230000000;
755      SELECT @@myisam_sort_buffer_size,
756             @@myisam_repair_threads,
757             @@sort_buffer_size,
758             @@binlog_format,
759             @@keep_files_on_create,
760             @@max_join_size;
761        END|
762DELIMITER ;|
763--echo ''
764--echo ''
765SET STATEMENT myisam_sort_buffer_size=400000,
766              myisam_repair_threads=2,
767              sort_buffer_size=200000,
768              binlog_format=row,
769              keep_files_on_create=OFF,
770              max_join_size=4444440000000 FOR
771              CALL p5();
772
773--echo ''
774--echo '# Post-STATEMENT
775SELECT @@myisam_sort_buffer_size,
776       @@myisam_repair_threads,
777       @@sort_buffer_size,
778       @@binlog_format,
779       @@keep_files_on_create,
780       @@max_join_size;
781
782--echo ''
783--echo ''
784--echo '#------------------Test 19-----------------------#'
785#Test for bad syntax
786--error ER_PARSE_ERROR
787SET STATEMENT max_error_count=100 FOR;
788--error ER_PARSE_ERROR
789SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2);
790--error ER_PARSE_ERROR
791SET STATEMENT FOR INSERT INTO t1 VALUES (1,2);
792--error ER_PARSE_ERROR
793SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
794--error ER_PARSE_ERROR
795SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
796--error ER_UNKNOWN_SYSTEM_VARIABLE
797SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
798--error ER_PARSE_ERROR
799SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
800
801--echo ''
802--echo ''
803--echo '#------------------Test 20-----------------------#'
804#Test for global-only variables
805--error ER_GLOBAL_VARIABLE
806SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2);
807
808--echo ''
809--echo ''
810--echo '#------------------Test 21-----------------------#'
811#Test for recursion
812SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
813SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000
814  FOR SET STATEMENT myisam_sort_buffer_size=200000
815    FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
816SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
817
818--echo ''
819--echo ''
820--echo '#------------------Test 22-----------------------#'
821#Test for STATEMENT keyword
822CREATE TABLE STATEMENT(a INT);
823DROP TABLE STATEMENT;
824
825--echo Test for bug 1418049: SET STATEMENT ... FOR <statement> crashes server
826--echo if <statement> needs to commit implicitly and fails
827
828CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
829
830SET @old_lock_wait_timeout = @@session.lock_wait_timeout;
831SET SESSION lock_wait_timeout = 1;
832
833BEGIN;
834INSERT INTO t2 VALUES (5);
835
836--connect(con1,localhost,root,,)
837--connection con1
838FLUSH TABLES WITH READ LOCK;
839
840--connection default
841--error ER_LOCK_WAIT_TIMEOUT
842SET STATEMENT max_join_size = 0 FOR DROP TABLE t2;
843
844--connection con1
845UNLOCK TABLES;
846--disconnect con1
847--connection default
848COMMIT;
849SET SESSION lock_wait_timeout = @old_lock_wait_timeout;
850
851--echo Test for bug 1387951: SET STATEMENT ... FOR <statement> crashes server
852--echo if <statement> is RW in a RO transaction
853SET @old_tx_read_only = @@session.tx_read_only;
854SET SESSION tx_read_only = TRUE;
855--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
856SET STATEMENT myisam_repair_threads=0 FOR OPTIMIZE TABLE t0;
857SET SESSION tx_read_only = @old_tx_read_only;
858
859--echo Test for bug 1412423: SET STATEMENT ... FOR <statement> crashes server
860--echo if <statement> needs to re-open a temp table and fails
861CREATE TEMPORARY TABLE t3 (a INT PRIMARY KEY) ENGINE=InnoDB;
862HANDLER t3 OPEN;
863--error ER_CANT_REOPEN_TABLE
864SET STATEMENT max_join_size=1000 FOR SELECT * FROM t3;
865DROP TABLE t3;
866
867--echo #
868--echo # Bug 1392375: Crashing repeated execution of SET STATEMENT ... FOR <SELECT FROM view>
869--echo #
870
871CREATE VIEW t3 AS SELECT 1 AS a;
872PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t3';
873EXECUTE stmt1;
874EXECUTE stmt1;
875
876DEALLOCATE PREPARE stmt1;
877DROP VIEW t3;
878
879--echo #
880--echo # Bug 1635927: Memory leak when using per-query variables with subquery temp tables
881--echo #
882
883CREATE TABLE t3 (row_key INT NOT NULL DEFAULT 0, ref_key BIGINT(20) NOT NULL);
884
885INSERT INTO t3 (ref_key) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
886set @row_id := 0;
887INSERT INTO t3 (ref_key) SELECT @row_id := @row_id + 11 as ref_key
888       FROM t3 a1, t3 a2, t3 a3;
889
890SET STATEMENT myisam_repair_threads=1 FOR
891SELECT count(1) FROM t3 s
892       JOIN (SELECT row_key, ref_key AS ref_key FROM t3 t
893       WHERE t.row_key IN (SELECT row_key FROM t3)) AS r;
894
895DROP TABLE t3;
896
897--echo ''
898--echo '# Cleanup'
899DROP TABLE t1, t2;
900DROP PROCEDURE p1;
901DROP PROCEDURE p2;
902DROP PROCEDURE p3;
903DROP PROCEDURE p4;
904DROP PROCEDURE p5;
905
906####################################################################
907#   lp:1626519 memory allocation for PLUGIN_VAR_MEMALLOC           #
908####################################################################
909
910let $MYSQL_TMP_DIR= `select @@tmpdir`;
911--replace_result $MYSQL_TMP_DIR TMPDIR
912--eval SET STATEMENT innodb_tmpdir=@@global.tmpdir FOR SELECT @@innodb_tmpdir
913SET SESSION innodb_tmpdir = @@global.tmpdir;
914--replace_result $MYSQL_TMP_DIR TMPDIR
915SELECT @@innodb_tmpdir;
916--replace_result $MYSQL_TMP_DIR TMPDIR
917SET STATEMENT sql_mode='' FOR SELECT @@innodb_tmpdir;
918--replace_result $MYSQL_TMP_DIR TMPDIR
919SELECT @@innodb_tmpdir;
920--replace_result $MYSQL_TEST_DIR TESTDIR
921--eval SET STATEMENT innodb_tmpdir='$MYSQL_TEST_DIR' FOR SELECT @@innodb_tmpdir
922--replace_result $MYSQL_TMP_DIR TMPDIR
923SELECT @@innodb_tmpdir;
924
925--echo #
926--echo # Bug 1385352: SET STATEMENT does not work after SET GLOBAL / SHOW GLOBAL STATUS
927--echo # and affects the global value
928--echo #
929
930--source include/wait_until_count_sessions.inc
931
932SHOW GLOBAL STATUS LIKE 'Threads_connected';
933
934SELECT @@GLOBAL.sql_mode;
935SELECT @@GLOBAL.auto_increment_offset;
936SET STATEMENT sql_mode = 'NO_AUTO_CREATE_USER', auto_increment_offset = 123 FOR
937    SELECT @@SESSION.sql_mode, @@SESSION.auto_increment_offset;
938SELECT @@GLOBAL.sql_mode;
939SELECT @@GLOBAL.auto_increment_offset;
940
941SET @saved_general_log = @@GLOBAL.general_log;
942SET GLOBAL general_log = 0;
943
944SET STATEMENT sql_mode='NO_AUTO_CREATE_USER', auto_increment_offset=123 FOR
945    SELECT @@SESSION.sql_mode, @@SESSION.auto_increment_offset;
946SELECT @@GLOBAL.sql_mode;
947SELECT @@GLOBAL.auto_increment_offset;
948
949SET GLOBAL general_log = @saved_general_log;
950