Debian Codegraph @

mysql-test/suite/x/t/admin_create_index_spatial.test

raw
1 ## manipulate of spatial indexes on document
2
3 --source include/xplugin_preamble.inc
4 --source include/xplugin_create_user.inc
5
6 ## Test starts here
7 --let $xtest_file= $MYSQL_TMP_DIR/admin_create_index_spatial.xpl
8 --write_file $xtest_file
9 -->import use_of_index.macro
10 -->quiet
11
12 ## Test data
13
14
15 -->macro Insert %COLL% %VALUE% %ERROR%
16 Mysqlx.Crud.Insert {
17 collection {
18 name: "%COLL%"
19 schema: "xtest"
20 }
21 data_model: DOCUMENT
22 row {
23 field {
24 type: LITERAL
25 literal {
26 type: V_STRING
27 v_string {
28 value: %VALUE%
29 }
30 }
31 }
32 }
33 }
34 -->expecterror %ERROR%
35 -->recvresult
36 -->endmacro
37
38 -->macro Show %COLL%
39 -->echo ### Columns of %COLL%
40 -->stmtsql show columns from xtest.%COLL%
41 -->recvresult
42 -->echo ### Indexes of %COLL%
43 -->stmtsql show index from xtest.%COLL%
44 -->recvresult
45 -->echo ############################
46 -->endmacro
47
48
49 -->title =Create collection spatial1
50 -->stmtadmin create_collection {"schema":"xtest", "name":"spatial1"}
51 -->recvresult
52
53 -->title =Create and drop spatial index
54 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial1", "type":"spatial", "name":"one", "unique":false, "fields":{"field":"$.first", "type":"geojson", "required":true}}
55 -->recvresult
56 -->callmacro Show spatial1
57 -->stmtadmin drop_collection_index {"schema":"xtest", "collection":"spatial1", "name":"one"}
58 -->recvresult
59 -->callmacro Show spatial1
60 -->title =re-create spatial index after drop and check case sensitiveness for geojson term
61 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial1", "type":"spatial", "name":"one", "unique":false, "fields":{"field":"$.first", "type":"GEOJSON", "required":true}}
62 -->recvresult
63 -->title =Check default value of srid and options
64 -->sql
65 SHOW CREATE TABLE xtest.spatial1;
66 -->endsql
67 -->callmacro Show spatial1
68 -->sql
69 ALTER TABLE xtest.spatial1 DROP COLUMN `$ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B`;
70 -->endsql
71 -->callmacro Show spatial1
72 -->title =create_collection_index with by explicitly setting value of srid and options
73 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial1", "type":"spatial", "name":"one", "unique":false, "fields":{"field":"$.first", "type":"geojson", "required":true, "options":1,"srid":4326}}
74 -->recvresult
75 -->callmacro Show spatial1
76 -->stmtadmin drop_collection_index {"schema":"xtest", "collection":"spatial1", "name":"one"}
77 -->recvresult
78
79 -->title =check required status var updated
80 -->sql
81 SHOW STATUS like 'Mysqlx_stmt_create_collection_index%';
82 SHOW STATUS like 'Mysqlx_stmt_drop_collection_index%';
83 SHOW STATUS LIKE 'Mysqlx_stmt_execute_mysqlx';
84 -->endsql
85 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial1", "type":"spatial", "name":"one", "unique":false, "fields":{"field":"$.first", "type":"geojson", "required":true, "options":1,"srid":4326}}
86 -->recvresult
87 -->stmtadmin drop_collection_index {"schema":"xtest", "collection":"spatial1", "name":"one"}
88 -->recvresult
89 -->sql
90 SHOW STATUS like 'Mysqlx_stmt_create_collection_index%';
91 SHOW STATUS like 'Mysqlx_stmt_drop_collection_index%';
92 SHOW STATUS LIKE 'Mysqlx_stmt_execute_mysqlx';
93 -->endsql
94
95 -->title =Run geomtery query on generated column
96 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial1", "type":"spatial", "name":"one", "unique":false, "fields":{"field":"$.first", "type":"geojson", "required":true, "options":1,"srid":4326}}
97 -->recvresult
98 -->callmacro Insert spatial1 '{"_id": "one", "first":{"type": "Point", "coordinates": [10, 11]}}' ER_SUCCESS
99 -->callmacro Insert spatial1 '{"_id": "two", "first":{"type": "LineString", "coordinates": [[0, 0], [0, 10], [10, 10], [10, 0]]}}' ER_SUCCESS
100 -->callmacro Insert spatial1 '{"_id": "three", "first":{"type": "Polygon", "coordinates": [[[30, 10], [40, 40], [20, 40], [10, 20], [30, 10]]]}}' ER_SUCCESS
101 -->callmacro Insert spatial1 '{"_id": "four", "first":{"type": "MultiPoint", "coordinates": [[10, 40], [40, 30], [20, 20], [30, 10]]}}' ER_SUCCESS
102 -->callmacro Insert spatial1 '{"_id": "five", "first":{"type": "MultiLineString", "coordinates": [[[10, 10], [20, 20], [10, 40]], [[40, 40], [30, 30], [40, 20], [30, 10]]]}}' ER_SUCCESS
103 -->callmacro Insert spatial1 '{"_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]]]]}}' ER_SUCCESS
104 -->callmacro Insert spatial1 '{"_id": "seven", "first":{"type": "GeometryCollection", "geometries": [{"type": "GeometryCollection", "geometries": [{"type": "Point", "coordinates": [10, 20]}]}]}}' ER_SUCCESS
105 -->stmtsql SELECT doc,_id,ST_ASGEOJSON(`$ix_gj_r_9BDE81F857CBD7E27CAED548ED36F57CE4307F0B`) FROM xtest.spatial1;
106 -->recvresult
107 -->echo Using of index give an unexpected result and it caused by another bug
108 -->callmacro Show_use_of_index spatial1 MBRContains(st_geomfromgeojson(doc->'$.first',1,4326),st_geomfromtext('point(10 11)',4326))
109
110 -->stmtadmin drop_collection_index {"schema":"xtest", "collection":"spatial1", "name":"one"}
111 -->recvresult
112 -->stmtsql DELETE FROM xtest.spatial1;
113 -->recvresult
114
115 -->title =create_collection_index with nested member path
116 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial1", "type":"spatial", "name":"one", "unique":false, "fields":{"field":"$.b[1].c", "type":"geojson", "required":true, "options":1,"srid":4326}}
117 -->recvresult
118 -->stmtadmin drop_collection_index {"schema":"xtest", "collection":"spatial1", "name":"one"}
119 -->recvresult
120
121 -->title =Create two spatial indexes on the same member and drop one
122 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial1", "type":"spatial", "name":"one", "unique":false, "fields":{"field":"$.first", "type":"geojson", "required":true}}
123 -->recvresult
124 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial1", "type":"spatial", "name":"two", "unique":false, "fields":{"field":"$.first", "type":"geojson", "required":true}}
125 -->recvresult
126 -->callmacro Show spatial1
127
128 -->title =Drop spatial index on $.first
129 -->stmtadmin drop_collection_index {"schema":"xtest", "collection":"spatial1", "name":"one"}
130 -->recvresult
131 -->callmacro Show spatial1
132
133 -->title =Is collection with spatial index still collection?
134 -->stmtadmin list_objects {"schema":"xtest"}
135 -->recvresult
136
137 -->title =Create spatial index with(out) optional type name
138 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial1", "type":"spatial", "name":"three", "unique":false, "fields":{"field":"$.second", "required":true}}
139 -->recvresult
140 -->callmacro Show spatial1
141
142 -->title =Drop collection spatial1
143 -->stmtadmin drop_collection {"schema":"xtest", "name":"spatial1"}
144 -->recvresult
145
146 -->title =Create collection spatial2
147 -->stmtadmin create_collection {"schema":"xtest", "name":"spatial2"}
148 -->recvresult
149
150
151
152 -->title =Try to create spatial index on two memebers
153 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial2", "type":"spatial", "name":"three", "unique":false, "fields":[{"field":"$.first", "type":"geojson", "required":true},{"field":"$.second", "type":"geojson", "required":true}]}
154 -->expecterror ER_TOO_MANY_KEY_PARTS
155 -->recvresult
156
157 -->title =Try to create "regular" index with spatial options
158 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial2", "name":"regular", "unique":false, "fields":{"field":"$.first", "type":"int", "required":true, "options":1, "srid":4326}}
159 -->expecterror ER_X_CMD_ARGUMENT_VALUE
160 -->recvresult
161
162 -->title =Try to create unique spatial index
163 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial2", "type":"spatial", "name":"three", "unique":true, "fields":{"field":"$.first", "type":"geojson", "required":true}}
164 -->expecterror ER_X_CMD_ARGUMENT_VALUE
165 -->recvresult
166
167 -->title =Try to create spatial index on not requiered member
168 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial2", "type":"spatial", "name":"three", "unique":false, "fields":{"field":"$.first", "type":"geojson", "required":false}}
169 -->expecterror ER_X_DOC_REQUIRED_FIELD_MISSING
170 -->recvresult
171
172 -->title =Try to create "regular" index on geometry member
173 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial2", "type":"index", "name":"regular", "unique":false, "fields":{"field":"$.first", "type":"geojson", "required":false}}
174 -->expecterror ER_X_DOC_REQUIRED_FIELD_MISSING
175 -->recvresult
176
177 -->title =Try to create spatial index with negative options
178 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial2", "type":"spatial", "name":"four", "unique":false, "fields":{"field":"$.first", "type":"geojson", "required":true, "options":-1}}
179 -->expecterror ER_X_CMD_ARGUMENT_VALUE
180 -->recvresult
181
182 -->title =Try to create spatial index with negative srid
183 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial2", "type":"spatial", "name":"four", "unique":false, "fields":{"field":"$.first", "type":"geojson", "required":true, "srid":-1}}
184 -->expecterror ER_X_CMD_ARGUMENT_VALUE
185 -->recvresult
186
187 -->title =Try to insert invalid value to collection with spatial index
188 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial2", "type":"spatial", "name":"three", "unique":false, "fields":{"field":"$.first", "type":"geojson", "required":true}}
189 -->recvresult
190 -->callmacro Insert spatial2 '{"_id": "one", "first":30}' ER_INVALID_GEOJSON_UNSPECIFIED
191
192 -->title =Try to insert 'null' value to collection with spatial index
193 -->callmacro Insert spatial2 '{"_id": "two", "first":null}' ER_BAD_NULL_ERROR_NOT_IGNORED
194 -->callmacro Insert spatial2 '{"_id": "two", "second":null}' ER_BAD_NULL_ERROR_NOT_IGNORED
195 -->stmtadmin drop_collection_index {"schema":"xtest", "collection":"spatial2", "name":"three"}
196 -->recvresult
197
198 -->title =Try to create spatial index on missing member
199 -->callmacro Insert spatial2 '{"_id": "one", "second":30}' ER_SUCCESS
200 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"spatial2", "type":"spatial", "name":"four", "unique":false, "fields":{"field":"$.first", "type":"geojson", "required":true}}
201 -->expecterror ER_BAD_NULL_ERROR_NOT_IGNORED
202 -->recvresult
203
204 EOF
205
206 CREATE SCHEMA xtest DEFAULT CHARSET 'utf8mb4';
207
208 --exec $MYSQLXTEST -ux_root --password='' --file=$xtest_file 2>&1
209
210 ## Cleanup
211 --source ../include/xplugin_cleanup.inc