Debian Codegraph @

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

raw
1 ########### suite/json/t/json_innodb.test #
2 # Tests json functionality that depends on innodb features #
3 # #
4 # This test copies some tests originally in json.test #
5 ######################################################################
6
7 # original test case in the bug report
8 # innodb allows more than three json columns in a table
9 CREATE TABLE D_21104470 (
10 pk int(11) NOT NULL AUTO_INCREMENT,
11 col_int_nokey json NOT NULL,
12 col_int_key int(11) NOT NULL,
13 col_date_key date NOT NULL,
14 col_date_nokey json NOT NULL,
15 col_time_key time NOT NULL,
16 col_time_nokey json NOT NULL,
17 col_datetime_key datetime NOT NULL,
18 col_datetime_nokey json NOT NULL,
19 col_varchar_key varchar(1) NOT NULL,
20 col_varchar_nokey json NOT NULL,
21 PRIMARY KEY (pk),
22 KEY col_int_key (col_int_key),
23 KEY col_date_key (col_date_key),
24 KEY col_time_key (col_time_key),
25 KEY col_datetime_key (col_datetime_key),
26 KEY col_varchar_key (col_varchar_key),
27 KEY col_int_key_2 (col_int_key,col_date_key,col_time_key,col_datetime_key,col_varchar_key)
28 ) AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
29 INSERT INTO D_21104470
30 VALUES
31 (1,'{"int1": "6"}',3,
32 '2007-06-18','{"date": "2007-06-18"}',
33 '00:00:00','{"time": null}',
34 '2002-08-20 22:48:00','{"datetime": "2002-08-20 22:48:00.035785"}',
35 'd','{"varc": "d"}'),
36 (2,'{"int1": "2"}',8,
37 '2002-10-13','{"date": "2002-10-13"}',
38 '00:00:00','{"time": "00:00:00"}',
39 '1900-01-01 00:00:00','{"datetime": "1900-01-01 00:00:00"}',
40 's','{"varc": "s"}'),
41 (3,'{"int1": "4"}',1,
42 '1900-01-01','{"date": "1900-01-01"}',
43 '15:57:25','{"time": "15:57:25.019666"}',
44 '2005-08-15 00:00:00','{"datetime": "2005-08-15 00:00:00"}',
45 'r','{"varc": "r"}'),
46 (4,'{"int1": "8"}',8,
47 '1900-02-03','{"date": null}',
48 '07:05:51','{"time": "07:05:51.006712"}',
49 '1900-01-01 00:00:00','{"datetime": "1900-01-01 00:00:00"}',
50 'm','{"varc": "m"}'),
51 (5,'{"int1": "4"}',8,
52 '2006-03-09','{"date": "2006-03-09"}',
53 '19:22:21','{"time": "19:22:21.057406"}',
54 '2008-05-16 08:09:06','{"datetime": "2008-05-16 08:09:06.002924"}',
55 'b','{"varc": "b"}'),
56 (6,'{"int1": "4"}',5,
57 '2001-06-05','{"date": "2001-06-05"}',
58 '03:53:16','{"time": "03:53:16.001370"}',
59 '2001-01-20 12:47:23','{"datetime": "2001-01-20 12:47:23.022022"}',
60 'x','{"varc": "x"}'),
61 (7,'{"int1": "7"}',7,
62 '2006-05-28','{"date": "2006-05-28"}',
63 '09:16:38','{"time": "09:16:38.034570"}',
64 '2008-07-02 00:00:00','{"datetime": "2008-07-02 00:00:00"}',
65 'g','{"varc": "g"}'),
66 (8,'{"int1": "4"}',5,
67 '2001-04-19','{"date": "2001-04-19"}',
68 '15:37:26','{"time": "15:37:26.028315"}',
69 '1900-01-01 00:00:00','{"datetime": "1900-01-01 00:00:00"}',
70 'p','{"varc": "p"}'),
71 (9,'{"int1": "1"}',1,
72 '1900-01-01','{"date": "1900-01-01"}',
73 '00:00:00','{"time": "00:00:00"}',
74 '2002-12-08 11:34:58','{"datetime": "2002-12-08 11:34:58.001571"}',
75 'q','{"varc": "q"}'),
76 (10,'{"int1": "9"}',6,
77 '2004-08-20','{"date": "2004-08-20"}',
78 '05:03:03','{"time": "05:03:03.047452"}',
79 '1900-01-01 00:00:00','{"datetime": "1900-01-01 00:00:00"}',
80 'w','{"varc": "w"}');
81
82 CREATE TABLE DD_21104470 (
83 pk int(11) NOT NULL AUTO_INCREMENT,
84 col_int_nokey json NOT NULL,
85 col_int_key int(11) NOT NULL,
86 col_date_key date NOT NULL,
87 col_date_nokey json NOT NULL,
88 col_time_key time NOT NULL,
89 col_time_nokey json NOT NULL,
90 col_datetime_key datetime NOT NULL,
91 col_datetime_nokey json NOT NULL,
92 col_varchar_key varchar(1) NOT NULL,
93 col_varchar_nokey json NOT NULL,
94 PRIMARY KEY (pk),
95 KEY col_int_key (col_int_key),
96 KEY col_date_key (col_date_key),
97 KEY col_time_key (col_time_key),
98 KEY col_datetime_key (col_datetime_key),
99 KEY col_varchar_key (col_varchar_key),
100 KEY col_int_key_2 (col_int_key,col_date_key,col_time_key,col_datetime_key,col_varchar_key)
101 ) AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;
102 INSERT INTO DD_21104470
103 VALUES
104 (10,'{"int1": "6"}',3,
105 '2002-12-16','{"date": "2002-12-16"}',
106 '17:54:20','{"time": "17:54:20.050299"}',
107 '2007-04-06 06:20:37','{"datetime": "2007-04-06 06:20:37.035492"}',
108 'i','{"varc": "i"}'),
109 (11,'{"int1": "1"}',1,
110 '2005-08-08','{"date": "2005-08-08"}',
111 '08:53:47','{"time": "08:53:47.064602"}',
112 '2008-03-07 00:00:00','{"datetime": "2008-03-07 00:00:00"}',
113 'x','{"varc": "x"}'),
114 (12,'{"int1": "5"}',2,
115 '2004-11-16','{"date": "2004-11-16"}',
116 '01:40:28','{"time": "01:40:28.015719"}',
117 '2002-01-25 10:15:08','{"datetime": "2002-01-25 10:15:08.014682"}',
118 'l','{"varc": "l"}'),
119 (13,'{"int1": "6"}',7,
120 '1900-02-04','{"date": null}',
121 '16:37:21','{"time": "16:37:21.055310"}',
122 '2001-02-26 23:35:13','{"datetime": "2001-02-26 23:35:13.014897"}',
123 'q','{"varc": "q"}'),
124 (14,'{"int1": "2"}',6,
125 '2006-05-14','{"date": "2006-05-14"}',
126 '01:00:33','{"time": "01:00:33.038177"}',
127 '2001-06-23 13:47:43','{"datetime": "2001-06-23 13:47:43.001775"}',
128 'n','{"varc": "n"}'),
129 (15,'{"int1": "4"}',1,
130 '2005-01-19','{"date": "2005-01-19"}',
131 '03:06:43','{"time": "03:06:43.059217"}',
132 '2007-01-17 05:06:55','{"datetime": "2007-01-17 05:06:55.064405"}',
133 'r','{"varc": "r"}'),
134 (16,'{"int1": "231"}',156,
135 '1900-01-01','{"date": "1900-01-01"}',
136 '00:00:00','{"time": null}',
137 '2001-10-23 00:00:00','{"datetime": "2001-10-23 00:00:00"}',
138 'c','{"varc": "c"}'),
139 (17,'{"int1": "4"}',8,
140 '2004-06-25','{"date": "2004-06-25"}',
141 '16:13:44','{"time": "16:13:44.008978"}',
142 '2003-11-24 04:13:27','{"datetime": "2003-11-24 04:13:27.046820"}',
143 'h','{"varc": "h"}'),
144 (18,'{"int1": "3"}',7,
145 '2009-12-22','{"date": "2009-12-22"}',
146 '14:09:13','{"time": "14:09:13.028533"}',
147 '2007-09-19 14:33:09','{"datetime": "2007-09-19 14:33:09.017132"}',
148 'k','{"varc": "k"}'),
149 (19,'{"int1": "3"}',2,
150 '2004-06-25','{"date": "2004-06-25"}',
151 '10:09:51','{"time": "10:09:51.017335"}',
152 '1900-01-01 00:00:00','{"datetime": "1900-01-01 00:00:00"}',
153 't','{"varc": "t"}');
154
155 CREATE TABLE insert_select_21104470
156 SELECT
157 table3 . col_datetime_nokey AS field1
158 FROM
159 D_21104470 AS table1
160 LEFT JOIN DD_21104470 AS table2
161 LEFT JOIN DD_21104470 AS table3
162 ON table2 . col_int_key < table3 . col_int_key
163 ON table1 . pk = table2 . col_int_key
164 WHERE table1 . col_int_key >= 3
165 AND table1 . col_int_key < ( 3 + 7 )
166 OR table1 . col_int_key >= 3
167 AND table1 . col_int_key < ( 3 + 25 )
168 AND table1 . col_int_key != table2 . pk
169 AND table1 . col_int_key IS NULL
170 AND table1 . pk IS NULL
171 OR table1 . col_int_key > 3
172 GROUP BY field1
173 ORDER BY field1;
174
175 DROP TABLE D_21104470;
176 DROP TABLE DD_21104470;
177 DROP TABLE insert_select_21104470;
178
179 --echo #
180 --echo # Bug#21383497 DBUG_ABORT() IN VAL_JSON
181 --echo #
182 # This test produces indeterminate results in ndb that depend on the order of evaluation
183 # The group identifier may be any of the items that compare equal
184 CREATE TABLE t(a INT PRIMARY KEY);
185 INSERT INTO t VALUES (1), (2), (3), (4), (5);
186 # Different warnings in the hypergraph join optimizer.
187 --disable_warnings
188 SELECT 1 FROM t GROUP BY ST_ASGEOJSON(POINT(1, 1)) WITH ROLLUP;
189 --enable_warnings
190 SELECT JSON_EXTRACT('{"a":1}', '$.a') AS je, COUNT(DISTINCT a)
191 FROM t GROUP BY je WITH ROLLUP;
192 SELECT JSON_EXTRACT(NULL, '$.a') AS je, COUNT(DISTINCT a)
193 FROM t GROUP BY je WITH ROLLUP;
194 SELECT JSON_EXTRACT('{"a":1}', '$.a') AS je, a, COUNT(DISTINCT a)
195 FROM t GROUP BY je, a WITH ROLLUP;
196 SELECT JSON_EXTRACT('{"a":1}', '$.a') AS je, a, COUNT(DISTINCT a)
197 FROM t GROUP BY a, je WITH ROLLUP;
198 DROP TABLE t;
199
200 --echo #
201 --echo # Bug#21096340 VIEW WITH QUERY HAVING SUM ON JSON COLUMN RETURNS INVALID DATA
202 --echo #
203
204 --source include/turn_off_only_full_group_by.inc
205
206 CREATE TABLE D (
207 col_varchar_10_latin1 varchar(10) CHARACTER SET latin1,
208 col_varchar_255_latin1_key varchar(255) CHARACTER SET latin1,
209 col_int_key int,
210 pk integer auto_increment,
211 col_varchar_255_utf8_key varchar(255) CHARACTER SET utf8,
212 col_int int,
213 col_varchar_10_utf8 varchar(10) CHARACTER SET utf8,
214 col_varchar_255_latin1 varchar(255) CHARACTER SET latin1,
215 col_varchar_255_utf8 varchar(255) CHARACTER SET utf8,
216 col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1,
217 col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8,
218 /*Indices*/
219 key (col_varchar_255_latin1_key),
220 key (col_int_key),
221 primary key (pk),
222 key (col_varchar_255_utf8_key),
223 key (col_varchar_10_latin1_key),
224 key (col_varchar_10_utf8_key)
225 );
226
227 INSERT INTO D VALUES
228 ('have', 'say', NULL, NULL, 'who', NULL, 'JBIYP', 'he''s', 'VWJOC', 'your', 'VDMAE') ,
229 ('YQAHN', 'GIKYQ', 3, NULL, 'no', 1933049856,'got', 'icz', 'RMHUR', 'MPCRS', 'so') ,
230 ('had', 'PASVH', 2144141312, NULL, 'look', 808058880, 'czstj', 'z', 'MRRVF', 'UHWEV', 'that') ,
231 ('BSXXI', 'DLIRG', NULL, NULL, 'zstjzfsu', 1562116096, 'is', 'WKDGV', 'stj', 'tjzf', 'with') ,
232 ('JFSGY', 'c', 98828288, NULL, 'n', 269418496, 'LDJWY', 'jzfsu', 'QPXVX', 'zfsunwdg', 'we');
233
234 ALTER TABLE D MODIFY COLUMN col_int JSON;
235
236 # This query is unreliable in ndb. It may return
237 # SUM(col_int) col_int
238 # 4572643328 1933049856 or
239 # 4572643328 NULL
240 SELECT SUM(col_int), col_int FROM D AS table1 WHERE table1.pk = table1.pk;
241
242 CREATE OR REPLACE VIEW view1 AS SELECT SUM(col_int), col_int FROM D AS table1
243 WHERE table1.pk = table1.pk;
244 SELECT * FROM view1;
245
246 SELECT SUM(col_int), col_int FROM D AS table1 WHERE table1.pk = table1.pk;
247
248 SELECT * FROM view1;
249
250 DROP VIEW view1;
251 DROP TABLE D;
252
253 --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
254
255 --echo #
256 --echo # Bug#29669251: !THD->IS_ERROR() IN JOIN_READ_CONST_TABLE()
257 --echo #
258 CREATE TABLE t(a INT);
259 INSERT INTO t VALUES(1),(2);
260 --error ER_INVALID_JSON_TEXT_IN_PARAM
261 WITH
262 cte1 AS (SELECT STD(a) AS a1 FROM t ),
263 cte2 AS (SELECT (JSON_INSERT('$.KEY3', NULL, NULL)) AS a2 FROM t)
264 SELECT cte1.a1 FROM cte1,cte2 WHERE cte1.a1 LIKE cte2.a2;
265 DROP TABLE t;
266
267 --echo #
268 --echo # Bug#30348554: JSON COLUMN INTERACTION WITH CHAR() SEEMS INCONSISTENT
269 --echo #
270 # Used to return true, should return false.
271 SELECT JSON_ARRAY(_utf8mb4'abc') = JSON_ARRAY(_binary'abc');
272 CREATE TABLE t(j JSON);
273 INSERT INTO t VALUES (JSON_ARRAY('aaa'));
274 # Used to be a no-op, should replace the string with a blob.
275 UPDATE t SET j = JSON_REPLACE(j, '$[0]', REPEAT(CHAR(97), 3));
276 SELECT j, JSON_TYPE(j->'$[0]') FROM t;
277 DROP TABLE t;