DELIMETER problem in testing, and other testing stuff

[Joseph Lukas]

I am trying to test the command in using a stored function.  The log  
file stops at the DELIMITER every time and will not set however I have  
no issues with running the commands outside the test suite in normal  
MySQL. The content of the test is at the bottom of the e-mail.

I also do realize that the variables I am setting do not really have  
anything to do with the commands in the test.  I am mainly running the  
test in order to check the function with different types of commands.   
I am not sure if I should be doing much larger tests where the  
variables would have an actual impact. I figure this is not what I  
need to test since variable impact will depend on other code not  
involved with mine. So I figured I should simply be testing that they  
are set, returned, and works properly after errors and with various  
commands.

I plan on using many more tests like SET STATEMENT sort_buffer_size =  
100000 FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; to ensure  
the variables are being set and returned.

So far I have tested that the command works with the variable types  
ULONG, ULONGLONG, HA_ROWS, MY_BOOL, and most other system variable  
types.  I have also tested for conditions when the statement after the  
FOR produces an error.  I still need to know what to do about the  
statement issue of adding it to keyword_sp and causing shift/reduce  
conflicts.

A question as well of how do you change the date_format variable, or  
any other date type variable to another setting such as %M/%D/%Y or  
similar?  I have tried various ways and keep getting invalid errors.   
I have tried settings based on command from internet searches and seem  
to keep getting errors.  This is one variable type I need to test is  
one of dateformat, or does dateformat not matter since i believe you  
can change this in the SQL statement itself.


####################################################################
#   No 1 - Check works with FUNCTIONS
#   No 2 - Test with DROP command
####################################################################
--echo '# set initial variable value, make prepared statement
SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
--echo '# Pre-STATEMENT variable value'
SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
DELIMITER :
CREATE FUNCTION myProc (cost DECIMAL(10,2))
    RETURNS DECIMAL(10,2)

    SQL SECURITY DEFINER

    tax: BEGIN
        DECLARE order_tax DECIMAL(10,2);
        SET order_tax = cost * .05;
        RETURN order_tax;
    END:
DELIMITER ;

SET STATEMENT myisam_sort_buffer_size=800000,
               myisam_repair_threads=2 FOR
               SELECT myProc(123.45);

--echo '# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';

SET STATEMENT myisam_sort_buffer_size=800000,
               myisam_repair_threads=2 FOR
               DROP FUNCTION myProc;

--echo '# Post-STATEMENT No 2 variable value
SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';

--echo ''
--echo '# Cleanup'
DROP TABLE t1;