1# ========== parameters.1 ========== 2USE INFORMATION_SCHEMA; 3SHOW CREATE TABLE INFORMATION_SCHEMA.PARAMETERS; 4Table Create Table 5PARAMETERS CREATE TEMPORARY TABLE `PARAMETERS` ( 6 `SPECIFIC_CATALOG` varchar(512) NOT NULL, 7 `SPECIFIC_SCHEMA` varchar(64) NOT NULL, 8 `SPECIFIC_NAME` varchar(64) NOT NULL, 9 `ORDINAL_POSITION` int(21) NOT NULL, 10 `PARAMETER_MODE` varchar(5), 11 `PARAMETER_NAME` varchar(64), 12 `DATA_TYPE` varchar(64) NOT NULL, 13 `CHARACTER_MAXIMUM_LENGTH` int(21), 14 `CHARACTER_OCTET_LENGTH` int(21), 15 `NUMERIC_PRECISION` int(21), 16 `NUMERIC_SCALE` int(21), 17 `DATETIME_PRECISION` bigint(21) unsigned, 18 `CHARACTER_SET_NAME` varchar(64), 19 `COLLATION_NAME` varchar(64), 20 `DTD_IDENTIFIER` longtext NOT NULL, 21 `ROUTINE_TYPE` varchar(9) NOT NULL 22) DEFAULT CHARSET=utf8 23SELECT * FROM information_schema.columns 24WHERE table_schema = 'information_schema' 25 AND table_name = 'parameters' 26ORDER BY ordinal_position; 27TABLE_CATALOG def 28TABLE_SCHEMA information_schema 29TABLE_NAME PARAMETERS 30COLUMN_NAME SPECIFIC_CATALOG 31ORDINAL_POSITION 1 32COLUMN_DEFAULT NULL 33IS_NULLABLE NO 34DATA_TYPE varchar 35CHARACTER_MAXIMUM_LENGTH 512 36CHARACTER_OCTET_LENGTH 1536 37NUMERIC_PRECISION NULL 38NUMERIC_SCALE NULL 39DATETIME_PRECISION NULL 40CHARACTER_SET_NAME utf8 41COLLATION_NAME utf8_general_ci 42COLUMN_TYPE varchar(512) 43COLUMN_KEY 44EXTRA 45PRIVILEGES # 46COLUMN_COMMENT 47IS_GENERATED NEVER 48GENERATION_EXPRESSION NULL 49TABLE_CATALOG def 50TABLE_SCHEMA information_schema 51TABLE_NAME PARAMETERS 52COLUMN_NAME SPECIFIC_SCHEMA 53ORDINAL_POSITION 2 54COLUMN_DEFAULT NULL 55IS_NULLABLE NO 56DATA_TYPE varchar 57CHARACTER_MAXIMUM_LENGTH 64 58CHARACTER_OCTET_LENGTH 192 59NUMERIC_PRECISION NULL 60NUMERIC_SCALE NULL 61DATETIME_PRECISION NULL 62CHARACTER_SET_NAME utf8 63COLLATION_NAME utf8_general_ci 64COLUMN_TYPE varchar(64) 65COLUMN_KEY 66EXTRA 67PRIVILEGES # 68COLUMN_COMMENT 69IS_GENERATED NEVER 70GENERATION_EXPRESSION NULL 71TABLE_CATALOG def 72TABLE_SCHEMA information_schema 73TABLE_NAME PARAMETERS 74COLUMN_NAME SPECIFIC_NAME 75ORDINAL_POSITION 3 76COLUMN_DEFAULT NULL 77IS_NULLABLE NO 78DATA_TYPE varchar 79CHARACTER_MAXIMUM_LENGTH 64 80CHARACTER_OCTET_LENGTH 192 81NUMERIC_PRECISION NULL 82NUMERIC_SCALE NULL 83DATETIME_PRECISION NULL 84CHARACTER_SET_NAME utf8 85COLLATION_NAME utf8_general_ci 86COLUMN_TYPE varchar(64) 87COLUMN_KEY 88EXTRA 89PRIVILEGES # 90COLUMN_COMMENT 91IS_GENERATED NEVER 92GENERATION_EXPRESSION NULL 93TABLE_CATALOG def 94TABLE_SCHEMA information_schema 95TABLE_NAME PARAMETERS 96COLUMN_NAME ORDINAL_POSITION 97ORDINAL_POSITION 4 98COLUMN_DEFAULT NULL 99IS_NULLABLE NO 100DATA_TYPE int 101CHARACTER_MAXIMUM_LENGTH NULL 102CHARACTER_OCTET_LENGTH NULL 103NUMERIC_PRECISION 10 104NUMERIC_SCALE 0 105DATETIME_PRECISION NULL 106CHARACTER_SET_NAME NULL 107COLLATION_NAME NULL 108COLUMN_TYPE int(21) 109COLUMN_KEY 110EXTRA 111PRIVILEGES # 112COLUMN_COMMENT 113IS_GENERATED NEVER 114GENERATION_EXPRESSION NULL 115TABLE_CATALOG def 116TABLE_SCHEMA information_schema 117TABLE_NAME PARAMETERS 118COLUMN_NAME PARAMETER_MODE 119ORDINAL_POSITION 5 120COLUMN_DEFAULT NULL 121IS_NULLABLE YES 122DATA_TYPE varchar 123CHARACTER_MAXIMUM_LENGTH 5 124CHARACTER_OCTET_LENGTH 15 125NUMERIC_PRECISION NULL 126NUMERIC_SCALE NULL 127DATETIME_PRECISION NULL 128CHARACTER_SET_NAME utf8 129COLLATION_NAME utf8_general_ci 130COLUMN_TYPE varchar(5) 131COLUMN_KEY 132EXTRA 133PRIVILEGES # 134COLUMN_COMMENT 135IS_GENERATED NEVER 136GENERATION_EXPRESSION NULL 137TABLE_CATALOG def 138TABLE_SCHEMA information_schema 139TABLE_NAME PARAMETERS 140COLUMN_NAME PARAMETER_NAME 141ORDINAL_POSITION 6 142COLUMN_DEFAULT NULL 143IS_NULLABLE YES 144DATA_TYPE varchar 145CHARACTER_MAXIMUM_LENGTH 64 146CHARACTER_OCTET_LENGTH 192 147NUMERIC_PRECISION NULL 148NUMERIC_SCALE NULL 149DATETIME_PRECISION NULL 150CHARACTER_SET_NAME utf8 151COLLATION_NAME utf8_general_ci 152COLUMN_TYPE varchar(64) 153COLUMN_KEY 154EXTRA 155PRIVILEGES # 156COLUMN_COMMENT 157IS_GENERATED NEVER 158GENERATION_EXPRESSION NULL 159TABLE_CATALOG def 160TABLE_SCHEMA information_schema 161TABLE_NAME PARAMETERS 162COLUMN_NAME DATA_TYPE 163ORDINAL_POSITION 7 164COLUMN_DEFAULT NULL 165IS_NULLABLE NO 166DATA_TYPE varchar 167CHARACTER_MAXIMUM_LENGTH 64 168CHARACTER_OCTET_LENGTH 192 169NUMERIC_PRECISION NULL 170NUMERIC_SCALE NULL 171DATETIME_PRECISION NULL 172CHARACTER_SET_NAME utf8 173COLLATION_NAME utf8_general_ci 174COLUMN_TYPE varchar(64) 175COLUMN_KEY 176EXTRA 177PRIVILEGES # 178COLUMN_COMMENT 179IS_GENERATED NEVER 180GENERATION_EXPRESSION NULL 181TABLE_CATALOG def 182TABLE_SCHEMA information_schema 183TABLE_NAME PARAMETERS 184COLUMN_NAME CHARACTER_MAXIMUM_LENGTH 185ORDINAL_POSITION 8 186COLUMN_DEFAULT NULL 187IS_NULLABLE YES 188DATA_TYPE int 189CHARACTER_MAXIMUM_LENGTH NULL 190CHARACTER_OCTET_LENGTH NULL 191NUMERIC_PRECISION 10 192NUMERIC_SCALE 0 193DATETIME_PRECISION NULL 194CHARACTER_SET_NAME NULL 195COLLATION_NAME NULL 196COLUMN_TYPE int(21) 197COLUMN_KEY 198EXTRA 199PRIVILEGES # 200COLUMN_COMMENT 201IS_GENERATED NEVER 202GENERATION_EXPRESSION NULL 203TABLE_CATALOG def 204TABLE_SCHEMA information_schema 205TABLE_NAME PARAMETERS 206COLUMN_NAME CHARACTER_OCTET_LENGTH 207ORDINAL_POSITION 9 208COLUMN_DEFAULT NULL 209IS_NULLABLE YES 210DATA_TYPE int 211CHARACTER_MAXIMUM_LENGTH NULL 212CHARACTER_OCTET_LENGTH NULL 213NUMERIC_PRECISION 10 214NUMERIC_SCALE 0 215DATETIME_PRECISION NULL 216CHARACTER_SET_NAME NULL 217COLLATION_NAME NULL 218COLUMN_TYPE int(21) 219COLUMN_KEY 220EXTRA 221PRIVILEGES # 222COLUMN_COMMENT 223IS_GENERATED NEVER 224GENERATION_EXPRESSION NULL 225TABLE_CATALOG def 226TABLE_SCHEMA information_schema 227TABLE_NAME PARAMETERS 228COLUMN_NAME NUMERIC_PRECISION 229ORDINAL_POSITION 10 230COLUMN_DEFAULT NULL 231IS_NULLABLE YES 232DATA_TYPE int 233CHARACTER_MAXIMUM_LENGTH NULL 234CHARACTER_OCTET_LENGTH NULL 235NUMERIC_PRECISION 10 236NUMERIC_SCALE 0 237DATETIME_PRECISION NULL 238CHARACTER_SET_NAME NULL 239COLLATION_NAME NULL 240COLUMN_TYPE int(21) 241COLUMN_KEY 242EXTRA 243PRIVILEGES # 244COLUMN_COMMENT 245IS_GENERATED NEVER 246GENERATION_EXPRESSION NULL 247TABLE_CATALOG def 248TABLE_SCHEMA information_schema 249TABLE_NAME PARAMETERS 250COLUMN_NAME NUMERIC_SCALE 251ORDINAL_POSITION 11 252COLUMN_DEFAULT NULL 253IS_NULLABLE YES 254DATA_TYPE int 255CHARACTER_MAXIMUM_LENGTH NULL 256CHARACTER_OCTET_LENGTH NULL 257NUMERIC_PRECISION 10 258NUMERIC_SCALE 0 259DATETIME_PRECISION NULL 260CHARACTER_SET_NAME NULL 261COLLATION_NAME NULL 262COLUMN_TYPE int(21) 263COLUMN_KEY 264EXTRA 265PRIVILEGES # 266COLUMN_COMMENT 267IS_GENERATED NEVER 268GENERATION_EXPRESSION NULL 269TABLE_CATALOG def 270TABLE_SCHEMA information_schema 271TABLE_NAME PARAMETERS 272COLUMN_NAME DATETIME_PRECISION 273ORDINAL_POSITION 12 274COLUMN_DEFAULT NULL 275IS_NULLABLE YES 276DATA_TYPE bigint 277CHARACTER_MAXIMUM_LENGTH NULL 278CHARACTER_OCTET_LENGTH NULL 279NUMERIC_PRECISION 20 280NUMERIC_SCALE 0 281DATETIME_PRECISION NULL 282CHARACTER_SET_NAME NULL 283COLLATION_NAME NULL 284COLUMN_TYPE bigint(21) unsigned 285COLUMN_KEY 286EXTRA 287PRIVILEGES # 288COLUMN_COMMENT 289IS_GENERATED NEVER 290GENERATION_EXPRESSION NULL 291TABLE_CATALOG def 292TABLE_SCHEMA information_schema 293TABLE_NAME PARAMETERS 294COLUMN_NAME CHARACTER_SET_NAME 295ORDINAL_POSITION 13 296COLUMN_DEFAULT NULL 297IS_NULLABLE YES 298DATA_TYPE varchar 299CHARACTER_MAXIMUM_LENGTH 64 300CHARACTER_OCTET_LENGTH 192 301NUMERIC_PRECISION NULL 302NUMERIC_SCALE NULL 303DATETIME_PRECISION NULL 304CHARACTER_SET_NAME utf8 305COLLATION_NAME utf8_general_ci 306COLUMN_TYPE varchar(64) 307COLUMN_KEY 308EXTRA 309PRIVILEGES # 310COLUMN_COMMENT 311IS_GENERATED NEVER 312GENERATION_EXPRESSION NULL 313TABLE_CATALOG def 314TABLE_SCHEMA information_schema 315TABLE_NAME PARAMETERS 316COLUMN_NAME COLLATION_NAME 317ORDINAL_POSITION 14 318COLUMN_DEFAULT NULL 319IS_NULLABLE YES 320DATA_TYPE varchar 321CHARACTER_MAXIMUM_LENGTH 64 322CHARACTER_OCTET_LENGTH 192 323NUMERIC_PRECISION NULL 324NUMERIC_SCALE NULL 325DATETIME_PRECISION NULL 326CHARACTER_SET_NAME utf8 327COLLATION_NAME utf8_general_ci 328COLUMN_TYPE varchar(64) 329COLUMN_KEY 330EXTRA 331PRIVILEGES # 332COLUMN_COMMENT 333IS_GENERATED NEVER 334GENERATION_EXPRESSION NULL 335TABLE_CATALOG def 336TABLE_SCHEMA information_schema 337TABLE_NAME PARAMETERS 338COLUMN_NAME DTD_IDENTIFIER 339ORDINAL_POSITION 15 340COLUMN_DEFAULT NULL 341IS_NULLABLE NO 342DATA_TYPE longtext 343CHARACTER_MAXIMUM_LENGTH 4294967295 344CHARACTER_OCTET_LENGTH 4294967295 345NUMERIC_PRECISION NULL 346NUMERIC_SCALE NULL 347DATETIME_PRECISION NULL 348CHARACTER_SET_NAME utf8 349COLLATION_NAME utf8_general_ci 350COLUMN_TYPE longtext 351COLUMN_KEY 352EXTRA 353PRIVILEGES # 354COLUMN_COMMENT 355IS_GENERATED NEVER 356GENERATION_EXPRESSION NULL 357TABLE_CATALOG def 358TABLE_SCHEMA information_schema 359TABLE_NAME PARAMETERS 360COLUMN_NAME ROUTINE_TYPE 361ORDINAL_POSITION 16 362COLUMN_DEFAULT NULL 363IS_NULLABLE NO 364DATA_TYPE varchar 365CHARACTER_MAXIMUM_LENGTH 9 366CHARACTER_OCTET_LENGTH 27 367NUMERIC_PRECISION NULL 368NUMERIC_SCALE NULL 369DATETIME_PRECISION NULL 370CHARACTER_SET_NAME utf8 371COLLATION_NAME utf8_general_ci 372COLUMN_TYPE varchar(9) 373COLUMN_KEY 374EXTRA 375PRIVILEGES # 376COLUMN_COMMENT 377IS_GENERATED NEVER 378GENERATION_EXPRESSION NULL 379DESCRIBE INFORMATION_SCHEMA.PARAMETERS; 380Field Type Null Key Default Extra 381SPECIFIC_CATALOG varchar(512) NO NULL 382SPECIFIC_SCHEMA varchar(64) NO NULL 383SPECIFIC_NAME varchar(64) NO NULL 384ORDINAL_POSITION int(21) NO NULL 385PARAMETER_MODE varchar(5) YES NULL 386PARAMETER_NAME varchar(64) YES NULL 387DATA_TYPE varchar(64) NO NULL 388CHARACTER_MAXIMUM_LENGTH int(21) YES NULL 389CHARACTER_OCTET_LENGTH int(21) YES NULL 390NUMERIC_PRECISION int(21) YES NULL 391NUMERIC_SCALE int(21) YES NULL 392DATETIME_PRECISION bigint(21) unsigned YES NULL 393CHARACTER_SET_NAME varchar(64) YES NULL 394COLLATION_NAME varchar(64) YES NULL 395DTD_IDENTIFIER longtext NO NULL 396ROUTINE_TYPE varchar(9) NO NULL 397# ========== parameters.2 ========== 398DROP DATABASE IF EXISTS i_s_parameters_test; 399CREATE DATABASE i_s_parameters_test; 400USE i_s_parameters_test; 401CREATE FUNCTION test_func1 (s char(20) RETURNS CHAR(50) 402RETURN CONCAT('Hello', ,s,'!'); 403ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RETURNS CHAR(50) 404RETURN CONCAT('Hello', ,s,'!')' at line 1 405SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 406WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1'; 407SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 408# ========== parameters.3 ========== 409DROP DATABASE IF EXISTS i_s_parameters_test; 410CREATE DATABASE i_s_parameters_test; 411USE i_s_parameters_test; 412CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50) 413RETURN CONCAT('Hello, ',s,'!'); 414SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 415WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1'; 416SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 417def i_s_parameters_test test_func1 0 NULL NULL char 50 50 NULL NULL NULL latin1 latin1_swedish_ci char(50) FUNCTION 418def i_s_parameters_test test_func1 1 IN s char 20 20 NULL NULL NULL latin1 latin1_swedish_ci char(20) FUNCTION 419DROP FUNCTION test_func1; 420SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 421WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1'; 422SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 423# ========== parameters.4 ========== 424DROP DATABASE IF EXISTS i_s_parameters_test; 425CREATE DATABASE i_s_parameters_test; 426USE i_s_parameters_test; 427CREATE PROCEDURE testproc (IN param1 INT) 428BEGIN 429SELECT 2+2 as param1; 430END; 431// 432SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 433WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'testproc'; 434SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 435def i_s_parameters_test testproc 1 IN param1 int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE 436# ========== parameters.5 ========== 437DROP DATABASE IF EXISTS i_s_parameters_test; 438CREATE DATABASE i_s_parameters_test; 439USE i_s_parameters_test; 440CREATE PROCEDURE test_proc(INOUT P INT) SET @x=P*2; 441SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 442WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_proc'; 443SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 444def i_s_parameters_test test_proc 1 INOUT P int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE 445# ========== parameters.6 ========== 446DROP DATABASE IF EXISTS i_s_parameters_test; 447CREATE DATABASE i_s_parameters_test; 448USE i_s_parameters_test; 449CREATE PROCEDURE test_proc(OUT p VARCHAR(10)) SET P='test'; 450SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 451WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_proc'; 452SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 453def i_s_parameters_test test_proc 1 OUT p varchar 10 10 NULL NULL NULL latin1 latin1_swedish_ci varchar(10) PROCEDURE 454# ========== parameters.7 ========== 455DROP DATABASE IF EXISTS i_s_parameters_test; 456CREATE DATABASE i_s_parameters_test; 457USE i_s_parameters_test; 458CREATE FUNCTION test_func1 (s char(20), t char(20)) RETURNS CHAR(40) 459RETURN CONCAT(s,t); 460SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 461WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1'; 462SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 463def i_s_parameters_test test_func1 0 NULL NULL char 40 40 NULL NULL NULL latin1 latin1_swedish_ci char(40) FUNCTION 464def i_s_parameters_test test_func1 1 IN s char 20 20 NULL NULL NULL latin1 latin1_swedish_ci char(20) FUNCTION 465def i_s_parameters_test test_func1 2 IN t char 20 20 NULL NULL NULL latin1 latin1_swedish_ci char(20) FUNCTION 466# ========== parameters.8 ========== 467DROP DATABASE IF EXISTS i_s_parameters_test; 468CREATE DATABASE i_s_parameters_test; 469USE i_s_parameters_test; 470CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50) 471RETURN CONCAT('Hello, ',s,'!'); 472SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 473WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1'; 474SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 475def i_s_parameters_test test_func1 0 NULL NULL char 50 50 NULL NULL NULL latin1 latin1_swedish_ci char(50) FUNCTION 476def i_s_parameters_test test_func1 1 IN s char 20 20 NULL NULL NULL latin1 latin1_swedish_ci char(20) FUNCTION 477# ========== parameters.9 ========== 478DROP DATABASE IF EXISTS i_s_parameters_test; 479CREATE DATABASE i_s_parameters_test; 480USE i_s_parameters_test; 481CREATE FUNCTION test_func2 (s int) RETURNS INT RETURN s*2; 482SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 483WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func2'; 484SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 485def i_s_parameters_test test_func2 0 NULL NULL int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION 486def i_s_parameters_test test_func2 1 IN s int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION 487# ========== parameters.10 ========== 488DROP DATABASE IF EXISTS i_s_parameters_test; 489CREATE DATABASE i_s_parameters_test; 490USE i_s_parameters_test; 491CREATE FUNCTION test_func5 (s date) RETURNS TIMESTAMP 492RETURN CURRENT_TIMESTAMP; 493SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 494WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func5'; 495SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 496def i_s_parameters_test test_func5 0 NULL NULL timestamp NULL NULL NULL NULL 0 NULL NULL timestamp FUNCTION 497def i_s_parameters_test test_func5 1 IN s date NULL NULL NULL NULL NULL NULL NULL date FUNCTION 498# ========== parameters.11 ========== 499DROP DATABASE IF EXISTS i_s_parameters_test; 500CREATE DATABASE i_s_parameters_test; 501USE i_s_parameters_test; 502CREATE FUNCTION test_func5 (s date) RETURNS TIMESTAMP 503RETURN CURRENT_TIMESTAMP; 504SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 505WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func5'; 506SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 507def i_s_parameters_test test_func5 0 NULL NULL timestamp NULL NULL NULL NULL 0 NULL NULL timestamp FUNCTION 508def i_s_parameters_test test_func5 1 IN s date NULL NULL NULL NULL NULL NULL NULL date FUNCTION 509ALTER FUNCTION test_func5 COMMENT 'new comment added'; 510SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 511WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func5'; 512SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 513def i_s_parameters_test test_func5 0 NULL NULL timestamp NULL NULL NULL NULL 0 NULL NULL timestamp FUNCTION 514def i_s_parameters_test test_func5 1 IN s date NULL NULL NULL NULL NULL NULL NULL date FUNCTION 515# ========== parameters.12 ========== 516DROP DATABASE IF EXISTS i_s_parameters_test; 517CREATE DATABASE i_s_parameters_test CHARACTER SET utf8; 518USE i_s_parameters_test; 519CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30) 520RETURN CONCAT('XYZ, ' ,s); 521SELECT * FROM INFORMATION_SCHEMA.PARAMETERS 522WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func5'; 523SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE 524def i_s_parameters_test test_func5 0 NULL NULL varchar 30 90 NULL NULL NULL utf8 utf8_general_ci varchar(30) FUNCTION 525def i_s_parameters_test test_func5 1 IN s char 20 60 NULL NULL NULL utf8 utf8_general_ci char(20) FUNCTION 526DROP DATABASE i_s_parameters_test; 527USE test; 528# 529# Start of 10.3 tests 530# 531# 532# MDEV-15416 Crash when reading I_S.PARAMETERS 533# 534CREATE PROCEDURE p1(a0 TYPE OF t1.a, 535a1 TYPE OF test.t1.a, 536b0 ROW TYPE OF t1, 537b1 ROW TYPE OF test.t1, 538c ROW(a INT,b DOUBLE)) 539BEGIN 540END; 541$$ 542SELECT *, '--------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = 'p1'; 543SPECIFIC_CATALOG def 544SPECIFIC_SCHEMA test 545SPECIFIC_NAME p1 546ORDINAL_POSITION 1 547PARAMETER_MODE IN 548PARAMETER_NAME a0 549DATA_TYPE TYPE OF 550CHARACTER_MAXIMUM_LENGTH NULL 551CHARACTER_OCTET_LENGTH NULL 552NUMERIC_PRECISION NULL 553NUMERIC_SCALE NULL 554DATETIME_PRECISION NULL 555CHARACTER_SET_NAME NULL 556COLLATION_NAME NULL 557DTD_IDENTIFIER TYPE OF `t1`.`a` 558ROUTINE_TYPE PROCEDURE 559-------- -------- 560SPECIFIC_CATALOG def 561SPECIFIC_SCHEMA test 562SPECIFIC_NAME p1 563ORDINAL_POSITION 2 564PARAMETER_MODE IN 565PARAMETER_NAME a1 566DATA_TYPE TYPE OF 567CHARACTER_MAXIMUM_LENGTH NULL 568CHARACTER_OCTET_LENGTH NULL 569NUMERIC_PRECISION NULL 570NUMERIC_SCALE NULL 571DATETIME_PRECISION NULL 572CHARACTER_SET_NAME NULL 573COLLATION_NAME NULL 574DTD_IDENTIFIER TYPE OF `test`.`t1`.`a` 575ROUTINE_TYPE PROCEDURE 576-------- -------- 577SPECIFIC_CATALOG def 578SPECIFIC_SCHEMA test 579SPECIFIC_NAME p1 580ORDINAL_POSITION 3 581PARAMETER_MODE IN 582PARAMETER_NAME b0 583DATA_TYPE ROW TYPE OF 584CHARACTER_MAXIMUM_LENGTH NULL 585CHARACTER_OCTET_LENGTH NULL 586NUMERIC_PRECISION NULL 587NUMERIC_SCALE NULL 588DATETIME_PRECISION NULL 589CHARACTER_SET_NAME NULL 590COLLATION_NAME NULL 591DTD_IDENTIFIER ROW TYPE OF `t1` 592ROUTINE_TYPE PROCEDURE 593-------- -------- 594SPECIFIC_CATALOG def 595SPECIFIC_SCHEMA test 596SPECIFIC_NAME p1 597ORDINAL_POSITION 4 598PARAMETER_MODE IN 599PARAMETER_NAME b1 600DATA_TYPE ROW TYPE OF 601CHARACTER_MAXIMUM_LENGTH NULL 602CHARACTER_OCTET_LENGTH NULL 603NUMERIC_PRECISION NULL 604NUMERIC_SCALE NULL 605DATETIME_PRECISION NULL 606CHARACTER_SET_NAME NULL 607COLLATION_NAME NULL 608DTD_IDENTIFIER ROW TYPE OF `test`.`t1` 609ROUTINE_TYPE PROCEDURE 610-------- -------- 611SPECIFIC_CATALOG def 612SPECIFIC_SCHEMA test 613SPECIFIC_NAME p1 614ORDINAL_POSITION 5 615PARAMETER_MODE IN 616PARAMETER_NAME c 617DATA_TYPE ROW 618CHARACTER_MAXIMUM_LENGTH NULL 619CHARACTER_OCTET_LENGTH NULL 620NUMERIC_PRECISION NULL 621NUMERIC_SCALE NULL 622DATETIME_PRECISION NULL 623CHARACTER_SET_NAME NULL 624COLLATION_NAME NULL 625DTD_IDENTIFIER ROW 626ROUTINE_TYPE PROCEDURE 627-------- -------- 628DROP PROCEDURE p1; 629