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). Structure: -- these tests have been accomplished via the patches for BUG#31509 -- the only modification will be the commented section listing the order of the columns of INFORMATION_SCHEMA.ROUTINES 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 --Procedure 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 validate. # Create Stored Procedure to verify new columns are NULL CREATE PROCEDURE testproc (OUT param1 INT) BEGIN SELECT 2+2 as param1; END; SELECT * FROM INFORMATION_SCHEMA.ROUTINES; 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). --Routines 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, ',s,'!'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES 2.3.2 Verify NUMERIC columns are properly populated for numeric return values # Create Numeric function to verify NUMERIC columns are properly populated CREATE FUNCTION test_func2 (s int) RETURNS INT RETURN s*2; SELECT * FROM INFORMATION_SCHEMA.ROUTINES 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 returned. 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.
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.