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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
|
# Order on stored and virtual generated columns with type JSON.
CREATE TABLE t(
j JSON,
stored_gc JSON GENERATED ALWAYS AS (JSON_EXTRACT(j, '$[0]')) STORED,
virtual_gc JSON GENERATED ALWAYS AS (JSON_EXTRACT(j, '$[1]')) VIRTUAL);
INSERT INTO t(j) VALUES
(JSON_ARRAY(1, 2)), (JSON_ARRAY(2, 1)), (JSON_ARRAY(10, 10)), (JSON_ARRAY(5));
SELECT * FROM t ORDER BY stored_gc;
SELECT * FROM t ORDER BY virtual_gc;
DROP TABLE t;
--echo # ----------------------------------------------------------------------
--echo # Test of generated columns that call JSON functions
--echo # ----------------------------------------------------------------------
CREATE TABLE t(id INT, j JSON,
gc INT GENERATED ALWAYS AS (JSON_EXTRACT(j, '$[0]')));
INSERT INTO t(id, j) VALUES (0, '"5"'), (1, '[]'), (2, '[1,2]'), (3, '5');
--error ER_INVALID_JSON_VALUE_FOR_CAST
INSERT INTO t(j) VALUES ('{}');
--error ER_INVALID_JSON_VALUE_FOR_CAST
INSERT INTO t(j) VALUES ('{"a":1}');
--error ER_INVALID_JSON_VALUE_FOR_CAST
INSERT INTO t(j) VALUES ('"abc"');
--error ER_INVALID_JSON_TEXT
INSERT INTO t(j) VALUES ('');
--error ER_INVALID_JSON_TEXT
INSERT INTO t(j) VALUES ('[');
SELECT * FROM t ORDER BY id;
UPDATE t SET j = '[123]';
SELECT * FROM t ORDER BY id;
--error ER_INVALID_JSON_TEXT
UPDATE t SET j = '[';
DROP TABLE t;
CREATE TABLE t(id INT, j JSON,
gc JSON GENERATED ALWAYS AS (JSON_ARRAY(j)));
INSERT INTO t(id, j)
VALUES (1, '1'), (2, '[true, false]'), (3, '{"a":1,"b":2}');
--error ER_INVALID_JSON_TEXT
INSERT INTO t(j) VALUES ('');
--error ER_INVALID_JSON_TEXT
INSERT INTO t(j) VALUES ('[');
SELECT * FROM t ORDER BY id;
UPDATE t SET j = '"abc"';
SELECT * FROM t ORDER BY id;
--error ER_INVALID_JSON_TEXT
UPDATE t SET j = '[';
DROP TABLE t;
# Timestamp values sometimes got printed as base64 strings.
CREATE TABLE t(ts TIMESTAMP, j JSON AS (CAST(ts AS JSON)));
INSERT INTO t(ts) VALUES ('2000-01-01 00:00:00');
SELECT CAST(JSON_ARRAY(ts, j) AS CHAR) FROM t;
DROP TABLE t;
--echo #
--echo # Bug#21491442 VARIANT::FORCED_RETURN() [WITH T = JSON_SCALAR*]:
--echo # ASSERTION `FALSE' FAILED.
--echo #
create table t (a json, b blob,
c int generated always as (1!=a) virtual not null) engine=innodb;
insert into t(a) values('[1]');
insert into t(a) values('[1]');
select a,c from t;
prepare ps1 from 'insert into t(a) values(?)';
set @a='[1]';
execute ps1 using @a;
execute ps1 using @a;
select a,c from t;
drop table t;
create temporary table t (a json, b blob,
c int generated always as (1!=a) virtual not null) engine=innodb;
insert into t(a) values('[1]');
insert into t(a) values('[1]');
select a,c from t;
prepare ps1 from 'insert into t(a) values(?)';
set @a='[1]';
execute ps1 using @a;
execute ps1 using @a;
select a,c from t;
drop table t;
--echo #
--echo # WL#8170: Expression analyzer for GC.
--echo # Queries with GC and JSON_EXTRACT compared to strings should use index
--echo #
create table t1(
f1 json,
gc varchar(20) character set utf8mb4 as
(json_unquote(json_extract(f1,"$"))) stored,
key gc_idx(gc));
insert into t1(f1) values ('"qwe"'),('"rty"'),('"uiop"');
insert into t1(f1) values ('"zxc"'),('"vbn"'),('"mnb"');
insert into t1(f1) select f1 from t1;
insert into t1(f1) select f1 from t1;
insert into t1(f1) values ('"asd"'),('"asdf"'),('"asasas"');
set @save_opt_sw= @@optimizer_switch;
# Innodb has some issues with ICP & GC stuff
set @@optimizer_switch="index_condition_pushdown=off";
set @string_literal='asd';
--sorted_result
select f1 from t1 where gc = "asd";
explain select f1 from t1 where gc = "asd";
--sorted_result
select f1 from t1 where gc = @string_literal;
explain select f1 from t1 where gc = @string_literal;
--sorted_result
select f1 from t1 where json_extract(f1,"$") = "asd";
explain select f1 from t1 where json_extract(f1,"$") = "asd";
--sorted_result
select f1 from t1 where json_extract(f1,"$") = @string_literal;
explain select f1 from t1 where json_extract(f1,"$") = @string_literal;
--sorted_result
select f1 from t1 where "asd" = json_extract(f1,"$");
explain select f1 from t1 where "asd" = json_extract(f1,"$");
--sorted_result
select f1 from t1 where @string_literal = json_extract(f1,"$");
explain select f1 from t1 where @string_literal = json_extract(f1,"$");
set @string_literal = 'z';
--sorted_result
select f1 from t1 where gc > "z";
explain select f1 from t1 where gc > "z";
--sorted_result
select f1 from t1 where gc > @string_literal;
explain select f1 from t1 where gc > @string_literal;
--sorted_result
select f1 from t1 where json_extract(f1,"$") > "z";
explain select f1 from t1 where json_extract(f1,"$") > "z";
--sorted_result
select f1 from t1 where json_extract(f1,"$") > @string_literal;
explain select f1 from t1 where json_extract(f1,"$") > @string_literal;
set @string_lo = 'v';
set @string_hi = 'z';
--sorted_result
select f1 from t1 where gc > "v" and gc < "z";
explain select f1 from t1 where gc > "v" and gc < "z";
--sorted_result
select f1 from t1 where gc > @string_lo and gc < @string_hi;
explain select f1 from t1 where gc > @string_lo and gc < @string_hi;
--sorted_result
select f1 from t1
where json_extract(f1,"$") > "v" and json_extract(f1,"$") < "z";
--sorted_result
select f1 from t1
where json_extract(f1,"$") > @string_lo and json_extract(f1,"$") < @string_hi;
explain
select f1 from t1
where json_extract(f1,"$") > @string_lo and json_extract(f1,"$") < @string_hi;
--sorted_result
select f1 from t1 where gc between "v" and "z";
explain select f1 from t1 where gc between "v" and "z";
--sorted_result
select f1 from t1 where gc between @string_lo and @string_hi;
explain select f1 from t1 where gc between @string_lo and @string_hi;
--sorted_result
select f1 from t1 where json_extract(f1,"$") between "v" and "z";
explain select f1 from t1 where json_extract(f1,"$") between "v" and "z";
--sorted_result
select f1 from t1 where json_extract(f1,"$") between @string_lo and @string_hi;
explain
select f1 from t1 where json_extract(f1,"$") between @string_lo and @string_hi;
set @string_1 = 'asd';
set @string_2 = 'asasas';
set @string_3 = 'asdf';
--sorted_result
select f1 from t1 where gc in ("asd","asasas","asdf");
explain select f1 from t1 where gc in ("asd","asasas","asdf");
--sorted_result
select f1 from t1 where gc in (@string_1, @string_2, @string_3);
explain select f1 from t1 where gc in (@string_1, @string_2, @string_3);
--sorted_result
select f1 from t1 where json_extract(f1,"$") in ("asd","asasas","asdf");
explain select f1 from t1 where json_extract(f1,"$") in ("asd","asasas","asdf");
--sorted_result
select f1 from t1
where json_extract(f1,"$") in (@string_1, @string_2, @string_3);
explain
select f1 from t1
where json_extract(f1,"$") in (@string_1, @string_2, @string_3);
select f1 from t1 where json_unquote(json_extract(f1,"$")) = "asd";
explain select f1 from t1 where json_unquote(json_extract(f1,"$")) = "asd";
select f1 from t1 where json_unquote(json_extract(f1,"$")) = @string_literal;
explain
select f1 from t1 where json_unquote(json_extract(f1,"$")) = @string_literal;
set @@optimizer_switch= @save_opt_sw;
drop table t1;
create table t1(f1 varchar(10), gc varchar(10) as (json_unquote(f1)) stored,
key gc_idx(gc));
insert into t1(f1) values ('"qwe"'),('"rty"'),('"uiop"');
select f1 from t1 where lower(f1)="qwe";
drop table t1;
--echo #
--echo #
--echo # Bug#21054516:QUERY HAVING SQL_BIG_RESULT ON JSON DATA GIVES EXTRA
--echo # ROWS IN OUTPUT
--echo #
CREATE TABLE t1 (
pk integer auto_increment key,
col_varchar_255_utf8_key varchar(255) CHARACTER SET utf8
);
INSERT INTO t1 VALUES (NULL, 'q') , (NULL, 'tgzvsj') ,
(NULL, 'b') , (NULL, 'q') , (NULL, 'up') , (NULL, 'up') ;
ALTER TABLE t1 ADD COLUMN json_varchar255_utf8_key json;
UPDATE t1 SET json_varchar255_utf8_key =
JSON_OBJECT('col_varchar_255_utf8_key', col_varchar_255_utf8_key);
ALTER TABLE t1 MODIFY col_varchar_255_utf8_key VARCHAR(255)
GENERATED ALWAYS AS
(JSON_EXTRACT(json_varchar255_utf8_key,'$.col_varchar_255_utf8_key[0]'))
STORED;
SELECT SQL_BIG_RESULT table1.json_varchar255_utf8_key AS field1, count(*)
FROM t1 AS table1 LEFT JOIN t1 AS table2 ON table1.pk <= table2.pk
GROUP BY field1;
DROP TABLE t1;
--echo #
--echo # Bug#26352119: WHERE JSON_EXTRACT() ON GEN KEY
--echo #
# The index should be used even if the collation is not the default
# collation of the character set.
SET NAMES utf8mb4 COLLATE utf8mb4_ja_0900_as_cs;
CREATE TABLE t1 (j JSON, pk VARCHAR(10) AS (j->>'$.id') STORED PRIMARY KEY);
INSERT INTO t1 (j) VALUES ('{"id":"a"}'), ('{"id":"b"}'), ('{"id":"c"}');
# Analyze to ensure table statistics are up to date before EXPLAIN.
ANALYZE TABLE t1;
EXPLAIN SELECT * FROM t1 WHERE j->>'$.id'='b';
SELECT * FROM t1 WHERE j->>'$.id'='b';
DROP TABLE t1;
SET NAMES DEFAULT;
|
