Debian Codegraph @

mysql-test/suite/json/t/type_json.test

raw
1 --echo #
2 --echo # Start of 10.5 tests
3 --echo #
4
5
6 --echo #
7 --echo # MDEV-27361 Hybrid functions with JSON arguments do not send format metadata
8 --echo #
9
10 # Mix all columns in the given table tbl to each other and calculate expr.
11
12 DELIMITER $$;
13 CREATE PROCEDURE p1(tbl TEXT, expr TEXT)
14 BEGIN
15 DECLARE t1cols INT DEFAULT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
16 WHERE TABLE_NAME=tbl AND TABLE_SCHEMA='test');
17 FOR a IN 0..(t1cols-1) DO
18 FOR b IN 0..(t1cols-1) DO
19 BEGIN
20 DECLARE query TEXT DEFAULT CONCAT('SELECT ', expr, ' FROM ', tbl);
21 SET query= REPLACE(query, 'olt1', a);
22 SET query= REPLACE(query, 'olt2', b);
23 EXECUTE IMMEDIATE query;
24 END;
25 END FOR;
26 END FOR;
27 END;
28 $$
29 DELIMITER ;$$
30
31
32 # Mix all columns in table t1 to all columns in table t2 and calculate expr
33
34 DELIMITER $$;
35 CREATE PROCEDURE p2(expr TEXT)
36 BEGIN
37 DECLARE t1cols INT DEFAULT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
38 WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test');
39 DECLARE t2cols INT DEFAULT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
40 WHERE TABLE_NAME='t2' AND TABLE_SCHEMA='test');
41 FOR a IN 0..(t1cols-1) DO
42 FOR b IN 0..(t2cols-1) DO
43 BEGIN
44 DECLARE query TEXT DEFAULT CONCAT('SELECT ', expr, ' FROM t1, t2');
45 SET query= REPLACE(query, 'olt1', a);
46 SET query= REPLACE(query, 'olt2', b);
47 EXECUTE IMMEDIATE query;
48 END;
49 END FOR;
50 END FOR;
51 END;
52 $$
53 DELIMITER ;$$
54
55
56
57 CREATE TABLE t1
58 (
59 c0 CHAR(30) DEFAULT '1' CHECK(JSON_VALID(c0)),
60 c1 VARCHAR(30) DEFAULT '1' CHECK(JSON_VALID(c1)),
61 c2 TINYTEXT DEFAULT '1' CHECK(JSON_VALID(c2)),
62 c3 TEXT DEFAULT '1' CHECK(JSON_VALID(c3)),
63 c4 MEDIUMTEXT DEFAULT '1' CHECK(JSON_VALID(c4)),
64 c5 LONGTEXT DEFAULT '1' CHECK(JSON_VALID(c5)),
65 # The below data types do not have any special JSON properties.
66 # Just checking CHECK(JSON_VALID()) does not give any unexpected surprises.
67 c6 INT DEFAULT '1' CHECK(JSON_VALID(c6)),
68 c7 ENUM('1') DEFAULT '1' CHECK(JSON_VALID(c7))
69 );
70 INSERT INTO t1 VALUES ();
71
72
73 CREATE TABLE t1c
74 (
75 c0 CHAR(30) DEFAULT '1' CHECK(JSON_VALID(c0)),
76 c1 VARCHAR(30) COMPRESSED DEFAULT '1' CHECK(JSON_VALID(c1)),
77 c2 TINYTEXT COMPRESSED DEFAULT '1' CHECK(JSON_VALID(c2)),
78 c3 TEXT COMPRESSED DEFAULT '1' CHECK(JSON_VALID(c3)),
79 c4 MEDIUMTEXT COMPRESSED DEFAULT '1' CHECK(JSON_VALID(c4)),
80 c5 LONGTEXT COMPRESSED DEFAULT '1' CHECK(JSON_VALID(c5)),
81 # The below data types do not have any special JSON properties.
82 # Just checking CHECK(JSON_VALID()) does not give any unexpected surprises.
83 c6 INT DEFAULT '1' CHECK(JSON_VALID(c6)),
84 c7 ENUM('1') DEFAULT '1' CHECK(JSON_VALID(c7))
85 );
86 INSERT INTO t1c VALUES ();
87
88
89
90 CREATE TABLE t2
91 (
92 c0 CHAR(30) DEFAULT '1',
93 c1 VARCHAR(30) DEFAULT '1',
94 c2 TINYTEXT DEFAULT '1',
95 c3 TEXT DEFAULT '1',
96 c4 MEDIUMTEXT DEFAULT '1',
97 c5 LONGTEXT DEFAULT '1',
98 c6 INT DEFAULT 1,
99 c7 DOUBLE DEFAULT 1,
100 c8 DECIMAL(10,2) DEFAULT 1.23,
101 c9 TIME DEFAULT '10:20:30',
102 c10 DATE DEFAULT '2001-01-01',
103 c11 DATETIME DEFAULT '2001-01-01 10:20:30',
104 c12 ENUM('1','2') DEFAULT '1'
105 );
106 INSERT INTO t2 VALUES ();
107
108
109 --disable_ps_protocol
110 --enable_metadata
111 CALL p1('t1', 'COALESCE(colt1, colt2)');
112 CALL p1('t1', 'LEAST(colt1, colt2)');
113 CALL p1('t1', 'colt1+colt2');
114 --disable_metadata
115 --disable_ps_protocol
116
117
118 --disable_ps_protocol
119 --enable_metadata
120 CALL p1('t1c', 'COALESCE(colt1, colt2)');
121 CALL p1('t1c', 'LEAST(colt1, colt2)');
122 CALL p1('t1c', 'colt1+colt2');
123 --disable_metadata
124 --disable_ps_protocol
125
126
127 --disable_ps_protocol
128 --enable_metadata
129 CALL p2('COALESCE(t1.colt1, t2.colt2)');
130 CALL p2('LEAST(t1.colt1, t2.colt2)');
131 CALL p2('t1.colt1+t2.colt2');
132 --disable_metadata
133 --disable_ps_protocol
134
135
136 DROP PROCEDURE p1;
137 DROP PROCEDURE p2;
138
139 DROP TABLE t1, t1c, t2;
140
141 --echo #
142 --echo # MDEV-30646 View created via JSON_ARRAYAGG returns incorrect json object
143 --echo #
144
145 CREATE VIEW v1 AS SELECT JSON_OBJECT('plugin','unix_socket') as v1_json;
146 SELECT JSON_ARRAYAGG(v1_json) FROM v1;
147 DROP VIEW v1;
148
149
150 --echo #
151 --echo # End of 10.5 tests
152 --echo #