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