Debian Codegraph @

mysql-test/suite/json/r/json_innodb.result

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