1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
|
--echo #
--echo # MDEV-15416 Crash when reading I_S.PARAMETERS
--echo #
--echo # Create in sql_mode=ORACLE, display in sql_mode=ORACLE and sql_mode=DEFAULT
SET sql_mode=ORACLE;
DELIMITER $$;
CREATE PROCEDURE p1(a0 t1.a%TYPE,
a1 test.t1.a%TYPE,
b0 t1%ROWTYPE,
b1 test.t1%ROWTYPE,
d ROW(a INT,b DOUBLE))
AS
BEGIN
NULL;
END;
$$
DELIMITER ;$$
--vertical_results
SET sql_mode=ORACLE;
SELECT *, '--------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='p1';
SET sql_mode=DEFAULT;
SELECT *, '--------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='p1';
--horizontal_results
DROP PROCEDURE p1;
SET sql_mode=ORACLE;
DELIMITER $$;
CREATE FUNCTION f1(a0 t1.a%TYPE,
a1 test.t1.a%TYPE,
b0 t1%ROWTYPE,
b1 test.t1%ROWTYPE,
d ROW(a INT,b DOUBLE))
RETURN INT
AS
BEGIN
RETURN 0;
END;
$$
DELIMITER ;$$
--vertical_results
SET sql_mode=ORACLE;
SELECT *, '--------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='f1';
SET sql_mode=DEFAULT;
SELECT *, '--------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='f1';
--horizontal_results
DROP FUNCTION f1;
--echo # Create in sql_mode=DEFAULT, display in sql_mode=DEFAULT and sql_mode=ORACLE
SET sql_mode=DEFAULT;
DELIMITER $$;
CREATE PROCEDURE p1(a0 TYPE OF t1.a,
a1 TYPE OF test.t1.a,
b0 ROW TYPE OF t1,
b1 ROW TYPE OF test.t1,
d ROW(a INT,b DOUBLE))
BEGIN
END;
$$
DELIMITER ;$$
--vertical_results
SET sql_mode=DEFAULT;
SELECT *, '--------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='p1';
SET sql_mode=ORACLE;
SELECT *, '--------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='p1';
--horizontal_results
DROP PROCEDURE p1;
SET sql_mode=DEFAULT;
DELIMITER $$;
CREATE FUNCTION f1(a0 TYPE OF t1.a,
a1 TYPE OF test.t1.a,
b0 ROW TYPE OF t1,
b1 ROW TYPE OF test.t1,
d ROW(a INT,b DOUBLE))
RETURNS INT
BEGIN
RETURN 0;
END;
$$
DELIMITER ;$$
--vertical_results
SET sql_mode=DEFAULT;
SELECT *, '--------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='f1';
SET sql_mode=ORACLE;
SELECT *, '--------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='f1';
--horizontal_results
DROP FUNCTION f1;
--echo #
--echo # MDEV 18092 Query with the table I_S.PARAMETERS stop working
--echo # after a package is created
--echo #
SET sql_mode=ORACLE;
CREATE DATABASE db1_mdev18092;
USE db1_mdev18092;
DELIMITER $$;
CREATE PROCEDURE p1(a INT)
AS BEGIN
NULL;
END;
$$
CREATE OR REPLACE PACKAGE employee_tools AS
FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2);
PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2));
PROCEDURE raiseSalaryStd(eid INT);
PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2));
END;
$$
DELIMITER ;$$
--vertical_results
SELECT *, '---------------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA='db1_mdev18092';
--horizontal_results
DROP DATABASE db1_mdev18092;
|
