WL#4058: Test INFORMATION_SCHEMA.ROUTINES: Add missing columns

Status: Complete   —   Priority: Medium

Test WL#2822 INFORMATION_SCHEMA.ROUTINES: Add missing columns.

This is an addition to the regression suite rather than an extension of funcs_1, etc
Test WL#2822 INFORMATION_SCHEMA.ROUTINES: Add missing columns

Include a manual upgrade/downgrade to/from 5.0-6.0
and 5.1-6.0. SystemQA will write/run complete upgrade/
downgrade test, but ServerQA will do an initial check
to ensure there are no obvious crashes/loss of data.
WL#4058:  INFORMATION_SCHEMA.ROUTINES: Add missing columns
Draft Test Plan

-- Will need to modify any existing tests that rely on the ROUTINES table (that
haven't already been updated).

-- these tests have been accomplished via the patches for BUG#31509
-- the only modification will be the commented section listing the order of the
1.1 Verify presence of 7 new columns
1.2 Verify column list complies with high-level spec of WL#2822 (order and datatype)
1.3 Compare previous ROUTINES table to ensure nothing has changed beyond
addition of 7 new columns -- examine previous test / result files that selected
from INFORMATION_SCHEMA.ROUTINES to ensure that only specified columns were
added, none were dropped, etc. (manual test)

Functional tests:
-- these tests will be added to the <engine>__datadict test

2.1  Verify that new columns are NULL when Stored Routine is a Procedure
   -- This can likely be accomplished through looking at an existing Stored
Routine test that looked at I_S.ROUTINES.  Examine a created procedure and
verify that the new columns are NULL.  This would be a quick visual check to
# Create Stored Procedure to verify new columns are NULL
CREATE PROCEDURE testproc (OUT param1 INT)
    SELECT 2+2 as param1;

2.2  Verify that previous columns are populated as before for a Stored Procedure
   -- Largely accomplished through Martin Hansson's changed to stored_proc
tests.  (same as 2.1).  He has patched these tests to account for the new
columns, I have reviewed the patch and seen that the only changes have been the
addition of new column data (via diff between patched and unpatched tests).

2.3 Verify new columns are populated properly:
2.3.1 Verify NUMERIC columns are not populated for non-numeric return values
   Create (or find) a test that creates a procedure with only CHAR parameters --
make sure that the NUMERIC columns do not contain data for this procedure.

# Stored Functions -- verify new columns are populated for functions.
# Create non-numeric function to verify NUMERIC columns are NULL
CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50) RETURN CONCAT('Hello,

2.3.2 Verify NUMERIC columns are properly populated for numeric return values

# Create Numeric function to verify NUMERIC columns are properly populated


2.3.3 Behavior of CHARACTER columns for non-character return values
   -- Need to research behavior a bit better -- exploratory testing
   -- similar to 2.3.2, but the opposite NUMERIC only then examine CHAR columns.
2.4 Boundary testing -- behavior when values are over specified limits
   -- Input of large names, numeric data, etc to see if an expected error is
2.5  Verify DTD_IDENTIFIER and DATA_TYPE agree (char(10) and char, etc)
   -- This is a quick visual check of any existing test that has been modified
to account for the new columns.

--Upgrade / Downgrade tests
** Need to coordinate w/ System QA about having this examined in upgrade /
downgrade testing.

Procedures created in version 5* would not have data for the new columns -- need
to verify how system will behave on upgrade (missing data for new columns)

Downgrade not so likely to be problematic (can just drop extra column data), but
still should examine behavior.