WL#4057: Test INFORMATION_SCHEMA: PARAMETERS view
Status: Complete — Priority: Medium
Test the functionality for WL#2003 INFORMATION_SCHEMA: PARAMETERS view 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.
NOTE -- It was decided that the tests for this and WL#4058 - I_S.ROUTINES missing columns would be added to the regression suite rather than becoming part of funcs_1, thus some of the previous notes are no longer applicable. Test plan to be placed here Note by mleich: --------------- One part of the testplan must be: Extend the testsuite funcs_1 datadict tests in such a way that the I_S view parameters is checked like the other I_S tables. IMHO this extension does not remove the need to develop additional tests outside of the datadict standard test. Detail: The testsuite funcs_1 has a part ("datadict") which checks in a systematic way some properties of the I_S tables. This part mentions the I_S table parameters - expects (2007-10-10) that the table does not exist - says that if the table starts to exist several auxiliary scripts datadict_tables.inc, datadict_tables_error_<errno>.test, datadict/datadict_master.inc should be changed to include this table in testing. Background: I have to fix for MySQL 5.1 and up BUG#31407 funcs_1: <engine>__datadict tests broken because of wrong expected results. The upmerge of the corresponding changeset to 5.2 showed that the <engine>__datadict is broken, because - the I_S view parameters exists now - several I_S tables have a slightly different definition etc. A fix of these issues on the fly during the upmerge does clear exceed the purpose of this bug fix and would cost 1 - 2 weeks of work. Therefore Georgi Kodinov (Lead of Bug Team 2) agreed that I file a bug report + disable these tests for 5.2. #### START TEST PLAN #### WL#4057: Test INFORMATION_SCHEMA: PARAMETERS view Draft Test Plan -- Will be altered week of 11/26 - Structure: 1.0 Verify PARAMETERS complies with the specifications of WL#2003 (columns, datatypes, etc) 1.0.1 - Only the 15 implemented columns exist and they are the correct data type. Functional tests: -- Common tests (for Proc and Routines) 2.0.0 - Verify main columns populate as expected. 2.0.1Verify NUMERIC columns are not populated for non-numeric return values -- when Parameter is CHAR, no data should be in the NUMERIC columns. 2.0.2 Verify NUMERIC columns are properly populated for numeric return values 2.0.3 Behavior of CHARACTER columns for non-character return values -- Need to research behavior a bit better -- exploratory testing 2.0.4 Boundary testing -- behavior when values are over specified limits -- Long names, Numeric values, etc. 2.0.5 Verify DTD_IDENTIFIER and DATA_TYPE agree (char(10) and char, etc) 2.0.6 ALTER - NOTE: 220.127.116.11 and 18.104.22.168 are specifically related to adding and removing parameters for a stored procedure rather than simply altering them. We want to verify that the ORDINAL position will increment when new parameters are added and to observe the behavior when we delete one from many 22.214.171.124 - Add inputs and verify rows are created - Verify that new parameters' ORD_POS values increment accordingly. 126.96.36.199 - Remove inputs and verify rows are deleted -- create procedure with 2 parameters, then delete parameter one (ORD_POS = 1) to see what happens to the ORD_POS for the parameter that was = 2 2.0.7 DROP - verify rows for procedure / function are removed. -- after testing a given function / proc DROP it then ensure a SELECT from I_S.PARAMETERS returns no data for the given proc. 2.0.8 Character sets -- Use of differing character sets for PROC information. As an example, validating that umlauts can be used. 2.0.9 Naming restrictions -- Use of reserved words for PROC parameters -- CREATE FUNCTION SELECT (or something like that). --Functions 2.1 - RETURN value -- special behavior is noted in the WL entry for this 2.1.1 - RETURN value of procedure has ORDINAL_POS = 0 2.1.2 - RETURN value of procedure has NULL PARM_NAME and PARM_MODE 3.0 SHOW Correspondence -- Verify that the values returned from SHOW correspond to the values returned via SELECT ... FROM I_S PARAMETERS -- Run a SHOW statement as well as SELECT from I_S.PARM 4.0 Security / Access: -- Verify that only those people with appropriate access will be able to view the data contained in I_S.PARAMETERS. -- Create a limited user and attempt to select from the table.
Results of tests to be placed here (including bugs found)
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.