Debian Codegraph @

mysql-test/suite/x/r/admin_create_index_spatial.result

raw
1 CREATE SCHEMA xtest DEFAULT CHARSET 'utf8mb4';
2
3 Create collection spatial1
4 ==========================
5
6 command ok
7
8 Create and drop spatial index
9 =============================
10
11 command ok
12 ### Columns of spatial1
13 Field Type Null Key Default Extra
14 doc json YES null
15 _id varbinary(32) NO PRI null STORED GENERATED
16 _json_schema json YES null VIRTUAL GENERATED
17 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B geometry NO MUL null STORED GENERATED
18 0 rows affected
19 ### Indexes of spatial1
20 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
21 spatial1 0 PRIMARY 1 _id A 0 null null BTREE YES null
22 spatial1 1 one 1 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B A 0 32 null SPATIAL YES null
23 0 rows affected
24 ############################
25
26 command ok
27 ### Columns of spatial1
28 Field Type Null Key Default Extra
29 doc json YES null
30 _id varbinary(32) NO PRI null STORED GENERATED
31 _json_schema json YES null VIRTUAL GENERATED
32 0 rows affected
33 ### Indexes of spatial1
34 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
35 spatial1 0 PRIMARY 1 _id A 0 null null BTREE YES null
36 0 rows affected
37 ############################
38
39 re-create spatial index after drop and check case sensitiveness for geojson term
40 ================================================================================
41
42 command ok
43
44 Check default value of srid and options
45 =======================================
46 Table Create Table
47 spatial1 CREATE TABLE `spatial1` (
48 `doc` json DEFAULT NULL,
49 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
50 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
51 `$ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B` geometry GENERATED ALWAYS AS (st_geomfromgeojson(json_extract(`doc`,_utf8mb4'$.first'),1,4326)) STORED NOT NULL /*!80003 SRID 4326 */,
52 PRIMARY KEY (`_id`),
53 SPATIAL KEY `one` (`$ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B`),
54 CONSTRAINT `$val_strict_BC152A14972B95B2FBEE160AF396AF2E56657864` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
55 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
56 0 rows affected
57 ### Columns of spatial1
58 Field Type Null Key Default Extra
59 doc json YES null
60 _id varbinary(32) NO PRI null STORED GENERATED
61 _json_schema json YES null VIRTUAL GENERATED
62 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B geometry NO MUL null STORED GENERATED
63 0 rows affected
64 ### Indexes of spatial1
65 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
66 spatial1 0 PRIMARY 1 _id A 0 null null BTREE YES null
67 spatial1 1 one 1 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B A 0 32 null SPATIAL YES null
68 0 rows affected
69 ############################
70
71 0 rows affected
72 Records: 0 Duplicates: 0 Warnings: 0
73 ### Columns of spatial1
74 Field Type Null Key Default Extra
75 doc json YES null
76 _id varbinary(32) NO PRI null STORED GENERATED
77 _json_schema json YES null VIRTUAL GENERATED
78 0 rows affected
79 ### Indexes of spatial1
80 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
81 spatial1 0 PRIMARY 1 _id A 0 null null BTREE YES null
82 0 rows affected
83 ############################
84
85 create_collection_index with by explicitly setting value of srid and options
86 =============================================================================
87
88 command ok
89 ### Columns of spatial1
90 Field Type Null Key Default Extra
91 doc json YES null
92 _id varbinary(32) NO PRI null STORED GENERATED
93 _json_schema json YES null VIRTUAL GENERATED
94 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B geometry NO MUL null STORED GENERATED
95 0 rows affected
96 ### Indexes of spatial1
97 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
98 spatial1 0 PRIMARY 1 _id A 0 null null BTREE YES null
99 spatial1 1 one 1 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B A 0 32 null SPATIAL YES null
100 0 rows affected
101 ############################
102
103 command ok
104
105 check required status var updated
106 =================================
107 Variable_name Value
108 Mysqlx_stmt_create_collection_index 3
109 0 rows affected
110 Variable_name Value
111 Mysqlx_stmt_drop_collection_index 2
112 0 rows affected
113 Variable_name Value
114 Mysqlx_stmt_execute_mysqlx 6
115 0 rows affected
116
117 command ok
118
119 command ok
120 Variable_name Value
121 Mysqlx_stmt_create_collection_index 4
122 0 rows affected
123 Variable_name Value
124 Mysqlx_stmt_drop_collection_index 3
125 0 rows affected
126 Variable_name Value
127 Mysqlx_stmt_execute_mysqlx 8
128 0 rows affected
129
130 Run geomtery query on generated column
131 ========================================
132
133 command ok
134
135 1 rows affected
136 Got expected error (code 0)
137
138 1 rows affected
139 Got expected error (code 0)
140
141 1 rows affected
142 Got expected error (code 0)
143
144 1 rows affected
145 Got expected error (code 0)
146
147 1 rows affected
148 Got expected error (code 0)
149
150 1 rows affected
151 Got expected error (code 0)
152
153 1 rows affected
154 Got expected error (code 0)
155 doc _id ST_ASGEOJSON(`$ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B`)
156 {"_id": "five", "first": {"type": "MultiLineString", "coordinates": [[[10, 10], [20, 20], [10, 40]], [[40, 40], [30, 30], [40, 20], [30, 10]]]}} five {"type": "MultiLineString", "coordinates": [[[10.0, 10.0], [20.0, 20.0], [10.0, 40.0]], [[40.0, 40.0], [30.0, 30.0], [40.0, 20.0], [30.0, 10.0]]]}
157 {"_id": "four", "first": {"type": "MultiPoint", "coordinates": [[10, 40], [40, 30], [20, 20], [30, 10]]}} four {"type": "MultiPoint", "coordinates": [[10.0, 40.0], [40.0, 30.0], [20.0, 20.0], [30.0, 10.0]]}
158 {"_id": "one", "first": {"type": "Point", "coordinates": [10, 11]}} one {"type": "Point", "coordinates": [10.0, 11.0]}
159 {"_id": "seven", "first": {"type": "GeometryCollection", "geometries": [{"type": "GeometryCollection", "geometries": [{"type": "Point", "coordinates": [10, 20]}]}]}} seven {"type": "GeometryCollection", "geometries": [{"type": "GeometryCollection", "geometries": [{"type": "Point", "coordinates": [10.0, 20.0]}]}]}
160 {"_id": "six", "first": {"type": "MultiPolygon", "coordinates": [[[[40, 40], [20, 45], [45, 30], [40, 40]]], [[[20, 35], [10, 30], [10, 10], [30, 5], [45, 20], [20, 35]]]]}} six {"type": "MultiPolygon", "coordinates": [[[[40.0, 40.0], [20.0, 45.0], [45.0, 30.0], [40.0, 40.0]]], [[[20.0, 35.0], [10.0, 30.0], [10.0, 10.0], [30.0, 5.0], [45.0, 20.0], [20.0, 35.0]]]]}
161 {"_id": "three", "first": {"type": "Polygon", "coordinates": [[[30, 10], [40, 40], [20, 40], [10, 20], [30, 10]]]}} three {"type": "Polygon", "coordinates": [[[30.0, 10.0], [40.0, 40.0], [20.0, 40.0], [10.0, 20.0], [30.0, 10.0]]]}
162 {"_id": "two", "first": {"type": "LineString", "coordinates": [[0, 0], [0, 10], [10, 10], [10, 0]]}} two {"type": "LineString", "coordinates": [[0.0, 0.0], [0.0, 10.0], [10.0, 10.0], [10.0, 0.0]]}
163 0 rows affected
164 Using of index give an unexpected result and it caused by another bug
165 ### Show use of index for MBRContains(st_geomfromgeojson(doc->'$.first',1,4326),st_geomfromtext('point(10 11)',4326))
166 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
167 1 SIMPLE spatial1 null ALL null null null null 7 100.00 Using where
168 0 rows affected
169 ############################
170
171 command ok
172
173 7 rows affected
174
175 create_collection_index with nested member path
176 ===============================================
177
178 command ok
179
180 command ok
181
182 Create two spatial indexes on the same member and drop one
183 ==========================================================
184
185 command ok
186
187 command ok
188 ### Columns of spatial1
189 Field Type Null Key Default Extra
190 doc json YES null
191 _id varbinary(32) NO PRI null STORED GENERATED
192 _json_schema json YES null VIRTUAL GENERATED
193 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B geometry NO MUL null STORED GENERATED
194 0 rows affected
195 ### Indexes of spatial1
196 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
197 spatial1 0 PRIMARY 1 _id A 0 null null BTREE YES null
198 spatial1 1 one 1 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B A 0 32 null SPATIAL YES null
199 spatial1 1 two 1 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B A 0 32 null SPATIAL YES null
200 0 rows affected
201 ############################
202
203 Drop spatial index on $.first
204 =============================
205
206 command ok
207 ### Columns of spatial1
208 Field Type Null Key Default Extra
209 doc json YES null
210 _id varbinary(32) NO PRI null STORED GENERATED
211 _json_schema json YES null VIRTUAL GENERATED
212 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B geometry NO MUL null STORED GENERATED
213 0 rows affected
214 ### Indexes of spatial1
215 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
216 spatial1 0 PRIMARY 1 _id A 0 null null BTREE YES null
217 spatial1 1 two 1 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B A 0 32 null SPATIAL YES null
218 0 rows affected
219 ############################
220
221 Is collection with spatial index still collection?
222 ==================================================
223 name type
224 spatial1 COLLECTION
225 command ok
226
227 Create spatial index with(out) optional type name
228 =================================================
229
230 command ok
231 ### Columns of spatial1
232 Field Type Null Key Default Extra
233 doc json YES null
234 _id varbinary(32) NO PRI null STORED GENERATED
235 _json_schema json YES null VIRTUAL GENERATED
236 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B geometry NO MUL null STORED GENERATED
237 $ix_gj_r_350BEBA0BA8F7BA4E147A3E652BE04A3B53B4479 geometry NO MUL null STORED GENERATED
238 0 rows affected
239 ### Indexes of spatial1
240 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
241 spatial1 0 PRIMARY 1 _id A 0 null null BTREE YES null
242 spatial1 1 two 1 $ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B A 0 32 null SPATIAL YES null
243 spatial1 1 three 1 $ix_gj_r_350BEBA0BA8F7BA4E147A3E652BE04A3B53B4479 A 0 32 null SPATIAL YES null
244 0 rows affected
245 ############################
246
247 Drop collection spatial1
248 ========================
249
250 command ok
251
252 Create collection spatial2
253 ==========================
254
255 command ok
256
257 Try to create spatial index on two memebers
258 ===========================================
259 Got expected error: Too many key parts specified; max 1 parts allowed (code 1070)
260
261 Try to create "regular" index with spatial options
262 ==================================================
263 Got expected error: Unsupported argument specification for '$.first' (code 5017)
264
265 Try to create unique spatial index
266 ==================================
267 Got expected error: Unique spatial index is not supported (code 5017)
268
269 Try to create spatial index on not requiered member
270 ===================================================
271 Got expected error: GEOJSON index requires 'constraint.required: TRUE (code 5117)
272
273 Try to create "regular" index on geometry member
274 ================================================
275 Got expected error: GEOJSON index requires 'constraint.required: TRUE (code 5117)
276
277 Try to create spatial index with negative options
278 =================================================
279 Got expected error: Invalid value for argument 'fields.options' (code 5017)
280
281 Try to create spatial index with negative srid
282 ==============================================
283 Got expected error: Invalid value for argument 'fields.srid' (code 5017)
284
285 Try to insert invalid value to collection with spatial index
286 ============================================================
287
288 command ok
289 Got expected error: Invalid GeoJSON data provided to function st_geomfromgeojson (code 3072)
290
291 Try to insert 'null' value to collection with spatial index
292 ===========================================================
293 Got expected error: Column '$ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B' cannot be null (code 3673)
294 Got expected error: Column '$ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B' cannot be null (code 3673)
295
296 command ok
297
298 Try to create spatial index on missing member
299 =============================================
300
301 1 rows affected
302 Got expected error (code 0)
303 Got expected error: Column '$ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B' cannot be null (code 3673)
304 Mysqlx.Ok {
305 msg: "bye!"
306 }
307 ok