Debian Codegraph @

mysql-test/suite/json/inc/json_gcol.inc

raw
1 # Order on stored and virtual generated columns with type JSON.
2 CREATE TABLE t(
3 j JSON,
4 stored_gc JSON GENERATED ALWAYS AS (JSON_EXTRACT(j, '$[0]')) STORED,
5 virtual_gc JSON GENERATED ALWAYS AS (JSON_EXTRACT(j, '$[1]')) VIRTUAL);
6 INSERT INTO t(j) VALUES
7 (JSON_ARRAY(1, 2)), (JSON_ARRAY(2, 1)), (JSON_ARRAY(10, 10)), (JSON_ARRAY(5));
8 SELECT * FROM t ORDER BY stored_gc;
9 SELECT * FROM t ORDER BY virtual_gc;
10 DROP TABLE t;
11
12 --echo # ----------------------------------------------------------------------
13 --echo # Test of generated columns that call JSON functions
14 --echo # ----------------------------------------------------------------------
15 CREATE TABLE t(id INT, j JSON,
16 gc INT GENERATED ALWAYS AS (JSON_EXTRACT(j, '$[0]')));
17 INSERT INTO t(id, j) VALUES (0, '"5"'), (1, '[]'), (2, '[1,2]'), (3, '5');
18 --error ER_INVALID_JSON_VALUE_FOR_CAST
19 INSERT INTO t(j) VALUES ('{}');
20 --error ER_INVALID_JSON_VALUE_FOR_CAST
21 INSERT INTO t(j) VALUES ('{"a":1}');
22 --error ER_INVALID_JSON_VALUE_FOR_CAST
23 INSERT INTO t(j) VALUES ('"abc"');
24 --error ER_INVALID_JSON_TEXT
25 INSERT INTO t(j) VALUES ('');
26 --error ER_INVALID_JSON_TEXT
27 INSERT INTO t(j) VALUES ('[');
28 SELECT * FROM t ORDER BY id;
29 UPDATE t SET j = '[123]';
30 SELECT * FROM t ORDER BY id;
31 --error ER_INVALID_JSON_TEXT
32 UPDATE t SET j = '[';
33 DROP TABLE t;
34
35 CREATE TABLE t(id INT, j JSON,
36 gc JSON GENERATED ALWAYS AS (JSON_ARRAY(j)));
37 INSERT INTO t(id, j)
38 VALUES (1, '1'), (2, '[true, false]'), (3, '{"a":1,"b":2}');
39 --error ER_INVALID_JSON_TEXT
40 INSERT INTO t(j) VALUES ('');
41 --error ER_INVALID_JSON_TEXT
42 INSERT INTO t(j) VALUES ('[');
43 SELECT * FROM t ORDER BY id;
44 UPDATE t SET j = '"abc"';
45 SELECT * FROM t ORDER BY id;
46 --error ER_INVALID_JSON_TEXT
47 UPDATE t SET j = '[';
48 DROP TABLE t;
49
50 # Timestamp values sometimes got printed as base64 strings.
51 CREATE TABLE t(ts TIMESTAMP, j JSON AS (CAST(ts AS JSON)));
52 INSERT INTO t(ts) VALUES ('2000-01-01 00:00:00');
53 SELECT CAST(JSON_ARRAY(ts, j) AS CHAR) FROM t;
54 DROP TABLE t;
55
56 --echo #
57 --echo # Bug#21491442 VARIANT::FORCED_RETURN() [WITH T = JSON_SCALAR*]:
58 --echo # ASSERTION `FALSE' FAILED.
59 --echo #
60 create table t (a json, b blob,
61 c int generated always as (1!=a) virtual not null) engine=innodb;
62 insert into t(a) values('[1]');
63 insert into t(a) values('[1]');
64 select a,c from t;
65 prepare ps1 from 'insert into t(a) values(?)';
66 set @a='[1]';
67 execute ps1 using @a;
68 execute ps1 using @a;
69 select a,c from t;
70 drop table t;
71
72 create temporary table t (a json, b blob,
73 c int generated always as (1!=a) virtual not null) engine=innodb;
74 insert into t(a) values('[1]');
75 insert into t(a) values('[1]');
76 select a,c from t;
77 prepare ps1 from 'insert into t(a) values(?)';
78 set @a='[1]';
79 execute ps1 using @a;
80 execute ps1 using @a;
81 select a,c from t;
82
83 drop table t;
84
85 --echo #
86 --echo # WL#8170: Expression analyzer for GC.
87 --echo # Queries with GC and JSON_EXTRACT compared to strings should use index
88 --echo #
89 create table t1(
90 f1 json,
91 gc varchar(20) character set utf8mb4 as
92 (json_unquote(json_extract(f1,"$"))) stored,
93 key gc_idx(gc));
94 insert into t1(f1) values ('"qwe"'),('"rty"'),('"uiop"');
95 insert into t1(f1) values ('"zxc"'),('"vbn"'),('"mnb"');
96 insert into t1(f1) select f1 from t1;
97 insert into t1(f1) select f1 from t1;
98 insert into t1(f1) values ('"asd"'),('"asdf"'),('"asasas"');
99
100 set @save_opt_sw= @@optimizer_switch;
101 # Innodb has some issues with ICP & GC stuff
102 set @@optimizer_switch="index_condition_pushdown=off";
103
104 set @string_literal='asd';
105 --sorted_result
106 select f1 from t1 where gc = "asd";
107 explain select f1 from t1 where gc = "asd";
108 --sorted_result
109 select f1 from t1 where gc = @string_literal;
110 explain select f1 from t1 where gc = @string_literal;
111 --sorted_result
112 select f1 from t1 where json_extract(f1,"$") = "asd";
113 explain select f1 from t1 where json_extract(f1,"$") = "asd";
114 --sorted_result
115 select f1 from t1 where json_extract(f1,"$") = @string_literal;
116 explain select f1 from t1 where json_extract(f1,"$") = @string_literal;
117
118 --sorted_result
119 select f1 from t1 where "asd" = json_extract(f1,"$");
120 explain select f1 from t1 where "asd" = json_extract(f1,"$");
121 --sorted_result
122 select f1 from t1 where @string_literal = json_extract(f1,"$");
123 explain select f1 from t1 where @string_literal = json_extract(f1,"$");
124
125 set @string_literal = 'z';
126
127 --sorted_result
128 select f1 from t1 where gc > "z";
129 explain select f1 from t1 where gc > "z";
130 --sorted_result
131 select f1 from t1 where gc > @string_literal;
132 explain select f1 from t1 where gc > @string_literal;
133 --sorted_result
134 select f1 from t1 where json_extract(f1,"$") > "z";
135 explain select f1 from t1 where json_extract(f1,"$") > "z";
136 --sorted_result
137 select f1 from t1 where json_extract(f1,"$") > @string_literal;
138 explain select f1 from t1 where json_extract(f1,"$") > @string_literal;
139
140 set @string_lo = 'v';
141 set @string_hi = 'z';
142
143 --sorted_result
144 select f1 from t1 where gc > "v" and gc < "z";
145 explain select f1 from t1 where gc > "v" and gc < "z";
146 --sorted_result
147 select f1 from t1 where gc > @string_lo and gc < @string_hi;
148 explain select f1 from t1 where gc > @string_lo and gc < @string_hi;
149 --sorted_result
150 select f1 from t1
151 where json_extract(f1,"$") > "v" and json_extract(f1,"$") < "z";
152 --sorted_result
153 select f1 from t1
154 where json_extract(f1,"$") > @string_lo and json_extract(f1,"$") < @string_hi;
155 explain
156 select f1 from t1
157 where json_extract(f1,"$") > @string_lo and json_extract(f1,"$") < @string_hi;
158
159 --sorted_result
160 select f1 from t1 where gc between "v" and "z";
161 explain select f1 from t1 where gc between "v" and "z";
162 --sorted_result
163 select f1 from t1 where gc between @string_lo and @string_hi;
164 explain select f1 from t1 where gc between @string_lo and @string_hi;
165 --sorted_result
166 select f1 from t1 where json_extract(f1,"$") between "v" and "z";
167 explain select f1 from t1 where json_extract(f1,"$") between "v" and "z";
168 --sorted_result
169 select f1 from t1 where json_extract(f1,"$") between @string_lo and @string_hi;
170 explain
171 select f1 from t1 where json_extract(f1,"$") between @string_lo and @string_hi;
172
173 set @string_1 = 'asd';
174 set @string_2 = 'asasas';
175 set @string_3 = 'asdf';
176
177 --sorted_result
178 select f1 from t1 where gc in ("asd","asasas","asdf");
179 explain select f1 from t1 where gc in ("asd","asasas","asdf");
180 --sorted_result
181 select f1 from t1 where gc in (@string_1, @string_2, @string_3);
182 explain select f1 from t1 where gc in (@string_1, @string_2, @string_3);
183 --sorted_result
184 select f1 from t1 where json_extract(f1,"$") in ("asd","asasas","asdf");
185 explain select f1 from t1 where json_extract(f1,"$") in ("asd","asasas","asdf");
186
187 --sorted_result
188 select f1 from t1
189 where json_extract(f1,"$") in (@string_1, @string_2, @string_3);
190 explain
191 select f1 from t1
192 where json_extract(f1,"$") in (@string_1, @string_2, @string_3);
193
194 select f1 from t1 where json_unquote(json_extract(f1,"$")) = "asd";
195 explain select f1 from t1 where json_unquote(json_extract(f1,"$")) = "asd";
196 select f1 from t1 where json_unquote(json_extract(f1,"$")) = @string_literal;
197 explain
198 select f1 from t1 where json_unquote(json_extract(f1,"$")) = @string_literal;
199
200 set @@optimizer_switch= @save_opt_sw;
201 drop table t1;
202
203 create table t1(f1 varchar(10), gc varchar(10) as (json_unquote(f1)) stored,
204 key gc_idx(gc));
205 insert into t1(f1) values ('"qwe"'),('"rty"'),('"uiop"');
206 select f1 from t1 where lower(f1)="qwe";
207 drop table t1;
208
209
210 --echo #
211 --echo #
212 --echo # Bug#21054516:QUERY HAVING SQL_BIG_RESULT ON JSON DATA GIVES EXTRA
213 --echo # ROWS IN OUTPUT
214 --echo #
215 CREATE TABLE t1 (
216 pk integer auto_increment key,
217 col_varchar_255_utf8_key varchar(255) CHARACTER SET utf8
218 );
219
220 INSERT INTO t1 VALUES (NULL, 'q') , (NULL, 'tgzvsj') ,
221 (NULL, 'b') , (NULL, 'q') , (NULL, 'up') , (NULL, 'up') ;
222
223 ALTER TABLE t1 ADD COLUMN json_varchar255_utf8_key json;
224
225 UPDATE t1 SET json_varchar255_utf8_key =
226 JSON_OBJECT('col_varchar_255_utf8_key', col_varchar_255_utf8_key);
227
228 ALTER TABLE t1 MODIFY col_varchar_255_utf8_key VARCHAR(255)
229 GENERATED ALWAYS AS
230 (JSON_EXTRACT(json_varchar255_utf8_key,'$.col_varchar_255_utf8_key[0]'))
231 STORED;
232
233 SELECT SQL_BIG_RESULT table1.json_varchar255_utf8_key AS field1, count(*)
234 FROM t1 AS table1 LEFT JOIN t1 AS table2 ON table1.pk <= table2.pk
235 GROUP BY field1;
236 DROP TABLE t1;
237
238 --echo #
239 --echo # Bug#26352119: WHERE JSON_EXTRACT() ON GEN KEY
240 --echo #
241
242 # The index should be used even if the collation is not the default
243 # collation of the character set.
244 SET NAMES utf8mb4 COLLATE utf8mb4_ja_0900_as_cs;
245 CREATE TABLE t1 (j JSON, pk VARCHAR(10) AS (j->>'$.id') STORED PRIMARY KEY);
246 INSERT INTO t1 (j) VALUES ('{"id":"a"}'), ('{"id":"b"}'), ('{"id":"c"}');
247
248 # Analyze to ensure table statistics are up to date before EXPLAIN.
249 ANALYZE TABLE t1;
250 EXPLAIN SELECT * FROM t1 WHERE j->>'$.id'='b';
251 SELECT * FROM t1 WHERE j->>'$.id'='b';
252
253 DROP TABLE t1;
254 SET NAMES DEFAULT;