Debian Codegraph @

mysql-test/suite/gis/r/ddl.result

raw
1 # Create a table with three columns:
2 # 1) Normal POINT column without SRID specification
3 # 2) Normal POINT column with SRID 0
4 # 3) Normal POINT column with SRID 4326
5 CREATE TABLE t1 (no_srid POINT DEFAULT NULL,
6 srid_0 POINT SRID 0 DEFAULT NULL,
7 srid_4326 POINT SRID 4326 DEFAULT NULL);
8 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
9 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
10 def test t1 no_srid NULL NULL point
11 def test t1 srid_0 0 point
12 def test t1 srid_4326 WGS 84 4326 point
13 # Insert some data with correct SRID into these columns
14 INSERT INTO t1 (no_srid, srid_0, srid_4326)
15 VALUES (ST_GeomFromText('POINT(0 0)', 0),
16 ST_GeomFromText('POINT(0 0)', 0),
17 ST_GeomFromText('POINT(0 0)', 4326));
18 INSERT INTO t1 (no_srid, srid_0, srid_4326)
19 VALUES (ST_GeomFromText('POINT(1 1)', 4326),
20 ST_GeomFromText('POINT(1 1)', 0),
21 ST_GeomFromText('POINT(1 1)', 4326));
22 # Insert data with wrong SRID, which should fail.
23 INSERT INTO t1 (srid_0) VALUES (ST_GeomFromText('POINT(1 1)', 4326));
24 ERROR HY000: The SRID of the geometry does not match the SRID of the column 'srid_0'. The SRID of the geometry is 4326, but the SRID of the column is 0. Consider changing the SRID of the geometry or the SRID property of the column.
25 INSERT INTO t1 (srid_4326) VALUES (ST_GeomFromText('POINT(1 1)', 0));
26 ERROR HY000: The SRID of the geometry does not match the SRID of the column 'srid_4326'. The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID of the geometry or the SRID property of the column.
27 INSERT INTO t1 (srid_4326) VALUES (POINT(0, 0));
28 ERROR HY000: The SRID of the geometry does not match the SRID of the column 'srid_4326'. The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID of the geometry or the SRID property of the column.
29 # Try to alter the SRID of the columns, which should fail for all columns
30 # now since they already contain data.
31 ALTER TABLE t1 CHANGE COLUMN no_srid no_srid POINT SRID 0 DEFAULT NULL;
32 ERROR HY000: The SRID of the geometry does not match the SRID of the column 'no_srid'. The SRID of the geometry is 4326, but the SRID of the column is 0. Consider changing the SRID of the geometry or the SRID property of the column.
33 ALTER TABLE t1 CHANGE COLUMN srid_0 srid_0 POINT SRID 4326 DEFAULT NULL;
34 ERROR HY000: The SRID of the geometry does not match the SRID of the column 'srid_0'. The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID of the geometry or the SRID property of the column.
35 ALTER TABLE t1 CHANGE COLUMN srid_4326 srid_4326 POINT SRID 0 DEFAULT NULL;
36 ERROR HY000: The SRID of the geometry does not match the SRID of the column 'srid_4326'. The SRID of the geometry is 4326, but the SRID of the column is 0. Consider changing the SRID of the geometry or the SRID property of the column.
37 # Removing the SRID specification from a column should work just fine.
38 ALTER TABLE t1 CHANGE COLUMN srid_0 no_srid_2 POINT DEFAULT NULL;
39 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
40 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
41 def test t1 no_srid NULL NULL point
42 def test t1 no_srid_2 NULL NULL point
43 def test t1 srid_4326 WGS 84 4326 point
44 # Setting the SRID to a non-existing SRID should not work.
45 ALTER TABLE t1 CHANGE COLUMN srid_4326 srid_1 POINT SRID 1 DEFAULT NULL;
46 ERROR SR001: There's no spatial reference system with SRID 1.
47 DROP TABLE t1;
48 # Creating a geometry column with a non-existing SRID should not work
49 CREATE TABLE t1 (col1 POINT SRID 1);
50 ERROR SR001: There's no spatial reference system with SRID 1.
51 # Try to create a table without the SRID property, insert data with
52 # different SRIDs and then add the SRID property (which should not work).
53 CREATE TABLE t1 (col1 POINT);
54 INSERT INTO t1 VALUES (ST_GeomFromText('POINT(1 1)', 4326)),
55 (ST_GeomFromText('POINT(2 2)', 0)),
56 (ST_GeomFromText('POINT(3 3)', 4326)),
57 (ST_GeomFromText('POINT(4 4)', 0)),
58 (ST_GeomFromText('POINT(5 5)', 4326)),
59 (ST_GeomFromText('POINT(6 6)', 0));
60 ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT SRID 4326;
61 ERROR HY000: The SRID of the geometry does not match the SRID of the column 'col1'. The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID of the geometry or the SRID property of the column.
62 DELETE FROM t1 WHERE ST_SRID(col1) = 0;
63 ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT SRID 4326;
64 DROP TABLE t1;
65 # The SRID property on a generated column should work
66 CREATE TABLE t1 (col1 POINT SRID 4326,
67 col2 POINT AS (ST_SRID(col1, 0)) SRID 0);
68 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
69 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
70 def test t1 col1 WGS 84 4326 point
71 def test t1 col2 0 point
72 INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(1 1)', 4326));
73 DROP TABLE t1;
74 # However, if the SRID property on the generated column doesn't match
75 # the SRID of the data, it should fail.
76 CREATE TABLE t1 (col1 POINT SRID 4326,
77 col2 POINT AS (ST_SRID(col1, 0)) SRID 2000);
78 INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(1 1)', 4326));
79 ERROR HY000: The SRID of the geometry does not match the SRID of the column 'col2'. The SRID of the geometry is 0, but the SRID of the column is 2000. Consider changing the SRID of the geometry or the SRID property of the column.
80 DROP TABLE t1;
81 # Creating a column with SRID property on a MyISAM table should only
82 # be supported if the SRID represents a cartesian coordinate system.
83 CREATE TABLE t1 (col1 POINT SRID 0) ENGINE = MyISAM;
84 CREATE TABLE t2 (col1 POINT SRID 2000) ENGINE = MyISAM;
85 CREATE TABLE t3 (col1 POINT SRID 4326) ENGINE = MyISAM;
86 ERROR 42000: The storage engine for the table doesn't support geographic spatial reference systems
87 DROP TABLE t1, t2;
88 # It should not be possible to use the SRID property with any other types
89 # than geometric types.
90 CREATE TABLE t1 (col1 DOUBLE SRID 0);
91 ERROR HY000: Incorrect usage of SRID and non-geometry column
92 CREATE TABLE t1 (col1 BLOB SRID 0);
93 ERROR HY000: Incorrect usage of SRID and non-geometry column
94 CREATE TABLE t1 (col1 VARCHAR(255) SRID 0);
95 ERROR HY000: Incorrect usage of SRID and non-geometry column
96 # Check that any indexes on SRID-less columns are ignored by the
97 # optimizer
98 CREATE TABLE t1 (col1 POINT NOT NULL, SPATIAL INDEX (col1));
99 Warnings:
100 Warning 3674 The spatial index on column 'col1' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.
101 INSERT INTO t1 VALUES (POINT(0, 0)), (POINT(0.5, 0.5)), (POINT(1, 1));
102 # Update table statistics to make the EXPLAIN stable
103 ANALYZE TABLE t1;
104 Table Op Msg_type Msg_text
105 test.t1 analyze status OK
106 # The following query plan should NOT use the index on "col1", since
107 # the column does not have the SRID property defined.
108 EXPLAIN SELECT ST_AsText(col1) FROM t1
109 WHERE MBRContains(col1, ST_GeomFromText("POLYGON((0 0, 0 0.5, 0.5 0.5, 0.5 0, 0 0))"));
110 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
111 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
112 Warnings:
113 Note 1003 /* select#1 */ select st_astext(`test`.`t1`.`col1`) AS `ST_AsText(col1)` from `test`.`t1` where mbrcontains(`test`.`t1`.`col1`,<cache>(st_geomfromtext('POLYGON((0 0, 0 0.5, 0.5 0.5, 0.5 0, 0 0))')))
114 DROP TABLE t1;
115 # Check that we print error message for misuse of SRID with non-geometric
116 # data type instead of "SRID not found"
117 CREATE TABLE t1 (a INTEGER SRID 1);
118 ERROR HY000: Incorrect usage of SRID and non-geometry column
119 CREATE TABLE t1 (a INTEGER);
120 ALTER TABLE t1 MODIFY COLUMN a INTEGER SRID 1;
121 ERROR HY000: Incorrect usage of SRID and non-geometry column
122 DROP TABLE t1;
123 #
124 # Bug#26594499 WL#8592:ALTER TABLE ADD SPATIAL INDEX RETURNS CANNOT GET
125 # GEOMETRY OBJECT ERROR
126 #
127 CREATE TABLE t1 (
128 g1 geometry NOT NULL,
129 g3 geometry SRID 2000 NOT NULL,
130 g4 geometry SRID 4326 NOT NULL
131 );
132 INSERT INTO t1 VALUES (ST_GeomFromText('POINT(0 0)'),
133 ST_GeomFromText('POINT(0 0)', 2000), ST_GeomFromText('POINT(0 0)', 4326));
134 ALTER TABLE t1 add spatial index idx1(g4);
135 ALTER TABLE t1 add spatial index idx3(g3);
136 DROP TABLE t1;
137 CREATE TABLE t1 (col1 POINT SRID 4294967296);
138 ERROR 22003: SRID value is out of range in 'SRID'
139 #
140 # Check that adding, modifying and removing spatial indexes works well
141 # with SRID columns
142 #
143 CREATE TABLE t1 (col_no_srid POINT NOT NULL,
144 col_srid_0 POINT SRID 0 NOT NULL,
145 col_srid_4326 POINT SRID 4326 NOT NULL);
146 INSERT INTO t1 VALUES (
147 ST_GeomFromText("POINT(0 0)", 0),
148 ST_GeomFromText("POINT(0 0)", 0),
149 ST_GeomFromText("POINT(0 0)", 4326));
150 INSERT INTO t1 VALUES (
151 ST_GeomFromText("POINT(1 1)", 4326),
152 ST_GeomFromText("POINT(1 1)", 0),
153 ST_GeomFromText("POINT(1 1)", 4326));
154 ALTER TABLE t1 ADD SPATIAL INDEX idx1 (col_no_srid);
155 Warnings:
156 Warning 3674 The spatial index on column 'col_no_srid' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.
157 ALTER TABLE t1 ADD SPATIAL INDEX idx2 (col_srid_0);
158 ALTER TABLE t1 ADD SPATIAL INDEX idx3 (col_srid_4326);
159 ALTER TABLE t1 DROP INDEX idx2;
160 ALTER TABLE t1 DROP INDEX idx3;
161 ALTER TABLE t1 DROP INDEX idx1;
162 CREATE SPATIAL INDEX idx1 ON t1 (col_srid_4326);
163 CREATE SPATIAL INDEX idx2 ON t1 (col_no_srid);
164 Warnings:
165 Warning 3674 The spatial index on column 'col_no_srid' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.
166 CREATE SPATIAL INDEX idx3 ON t1 (col_srid_0);
167 DROP INDEX idx1 ON t1;
168 DROP INDEX idx2 ON t1;
169 DROP INDEX idx3 ON t1;
170 DROP TABLE t1;
171 CREATE TABLE t1 (col_no_srid POINT NOT NULL,
172 col_srid_0 POINT SRID 0 NOT NULL,
173 col_srid_4326 POINT SRID 4326 NOT NULL,
174 SPATIAL INDEX idx1 (col_no_srid),
175 SPATIAL INDEX idx2 (col_srid_0),
176 SPATIAL INDEX idx3 (col_srid_4326));
177 Warnings:
178 Warning 3674 The spatial index on column 'col_no_srid' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.
179 INSERT INTO t1 VALUES (
180 ST_GeomFromText("POINT(0 0)", 0),
181 ST_GeomFromText("POINT(0 0)", 0),
182 ST_GeomFromText("POINT(0 0)", 4326));
183 INSERT INTO t1 VALUES (
184 ST_GeomFromText("POINT(1 1)", 4326),
185 ST_GeomFromText("POINT(1 1)", 0),
186 ST_GeomFromText("POINT(1 1)", 4326));
187 DROP INDEX idx3 ON t1;
188 DROP INDEX idx2 ON t1;
189 DROP INDEX idx1 ON t1;
190 DROP TABLE t1;
191 #
192 # Altering the SRID specification should not be allowed if there is a
193 # spatial index on the column.
194 #
195 CREATE TABLE t1 (col1 POINT NOT NULL SRID 4326,
196 SPATIAL INDEX idx1 (col1));
197 ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT NOT NULL SRID 0;
198 ERROR HY000: The SRID specification on the column 'col1' cannot be changed because there is a spatial index on the column. Please remove the spatial index before altering the SRID specification.
199 ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT NOT NULL;
200 ERROR HY000: The SRID specification on the column 'col1' cannot be changed because there is a spatial index on the column. Please remove the spatial index before altering the SRID specification.
201 ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT NOT NULL SRID 0, ALGORITHM=INPLACE;
202 ERROR HY000: The SRID specification on the column 'col1' cannot be changed because there is a spatial index on the column. Please remove the spatial index before altering the SRID specification.
203 ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT NOT NULL, ALGORITHM=INPLACE;
204 ERROR HY000: The SRID specification on the column 'col1' cannot be changed because there is a spatial index on the column. Please remove the spatial index before altering the SRID specification.
205 ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT NOT NULL SRID 0, ALGORITHM=COPY;
206 ERROR HY000: The SRID specification on the column 'col1' cannot be changed because there is a spatial index on the column. Please remove the spatial index before altering the SRID specification.
207 ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT NOT NULL, ALGORITHM=COPY;
208 ERROR HY000: The SRID specification on the column 'col1' cannot be changed because there is a spatial index on the column. Please remove the spatial index before altering the SRID specification.
209 ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT NOT NULL SRID 0, ALGORITHM=DEFAULT;
210 ERROR HY000: The SRID specification on the column 'col1' cannot be changed because there is a spatial index on the column. Please remove the spatial index before altering the SRID specification.
211 ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT NOT NULL, ALGORITHM=DEFAULT;
212 ERROR HY000: The SRID specification on the column 'col1' cannot be changed because there is a spatial index on the column. Please remove the spatial index before altering the SRID specification.
213 ALTER TABLE t1 CHANGE COLUMN col1 col2 POINT NOT NULL SRID 0;
214 ERROR HY000: The SRID specification on the column 'col1' cannot be changed because there is a spatial index on the column. Please remove the spatial index before altering the SRID specification.
215 ALTER TABLE t1 CHANGE COLUMN col1 col2 POINT NOT NULL;
216 ERROR HY000: The SRID specification on the column 'col1' cannot be changed because there is a spatial index on the column. Please remove the spatial index before altering the SRID specification.
217 DROP TABLE t1;
218 # Test SRID specification in combination with generated columns and
219 # spatial indexes.
220 CREATE TABLE t1 (
221 col_no_srid POINT NOT NULL,
222 col_srid_0 POINT NOT NULL SRID 0,
223 col_srid_4326 POINT NOT NULL SRID 4326);
224 INSERT INTO t1 (col_no_srid, col_srid_0, col_srid_4326) VALUES (
225 ST_GeomFromText('POINT(0 0)'),
226 ST_GeomFromText('POINT(0 0)', 0),
227 ST_GeomFromText('POINT(0 0)', 4326));
228 INSERT INTO t1 (col_no_srid, col_srid_0, col_srid_4326) VALUES (
229 ST_GeomFromText('POINT(0 0)', 4326),
230 ST_GeomFromText('POINT(0 0)', 0),
231 ST_GeomFromText('POINT(0 0)', 4326));
232 # This should not be allowed since the column contains data with a
233 # different SRID
234 ALTER TABLE t1 CHANGE COLUMN col_srid_4326 col_srid_2000 POINT NOT NULL SRID 2000;
235 ERROR HY000: The SRID of the geometry does not match the SRID of the column 'col_srid_2000'. The SRID of the geometry is 4326, but the SRID of the column is 2000. Consider changing the SRID of the geometry or the SRID property of the column.
236 DELETE FROM t1;
237 # Now it should be allowed since there is no data in the table,
238 ALTER TABLE t1 CHANGE COLUMN col_srid_4326 col_srid_2000 POINT NOT NULL SRID 2000;
239 INSERT INTO t1 (col_no_srid, col_srid_0, col_srid_2000) VALUES (
240 ST_GeomFromText('POINT(0 0)'),
241 ST_GeomFromText('POINT(0 0)', 0),
242 ST_GeomFromText('POINT(0 0)', 2000));
243 INSERT INTO t1 (col_no_srid, col_srid_0, col_srid_2000) VALUES (
244 ST_GeomFromText('POINT(0 0)', 4326),
245 ST_GeomFromText('POINT(0 0)', 0),
246 ST_GeomFromText('POINT(0 0)', 2000));
247 # Add an index on "col_srid_2000"
248 CREATE SPATIAL INDEX idx_2000 ON t1 (col_srid_2000);
249 INSERT INTO t1 (col_no_srid, col_srid_0, col_srid_2000) VALUES (
250 ST_GeomFromText('POINT(0 0)', 4326),
251 ST_GeomFromText('POINT(0 0)', 0),
252 ST_GeomFromText('POINT(0 0)', 2000));
253 # Add an generated column that is based on the indexed column
254 # (col_srid_2000).
255 ALTER TABLE t1 ADD COLUMN (col_srid_4326_generated POINT AS
256 (ST_SRID(col_srid_2000, 4326)) SRID 4326);
257 SELECT
258 ST_AsText(col_no_srid),
259 ST_AsText(col_srid_0),
260 ST_AsText(col_srid_2000),
261 ST_AsText(col_srid_4326_generated),
262 ST_SRID(col_no_srid),
263 ST_SRID(col_srid_0),
264 ST_SRID(col_srid_2000),
265 ST_SRID(col_srid_4326_generated)
266 FROM t1;
267 ST_AsText(col_no_srid) ST_AsText(col_srid_0) ST_AsText(col_srid_2000) ST_AsText(col_srid_4326_generated) ST_SRID(col_no_srid) ST_SRID(col_srid_0) ST_SRID(col_srid_2000) ST_SRID(col_srid_4326_generated)
268 POINT(0 0) POINT(0 0) POINT(0 0) POINT(0 0) 0 0 2000 4326
269 POINT(0 0) POINT(0 0) POINT(0 0) POINT(0 0) 4326 0 2000 4326
270 POINT(0 0) POINT(0 0) POINT(0 0) POINT(0 0) 4326 0 2000 4326
271 # Add a spatial index on the generated column
272 CREATE INDEX idx_4326_generated ON t1 (col_srid_4326_generated);
273 ERROR HY000: 'Spatial index on virtual generated column' is not supported for generated columns.
274 INSERT INTO t1 (col_no_srid, col_srid_0, col_srid_2000) VALUES (
275 ST_GeomFromText('POINT(1 1)', 4326),
276 ST_GeomFromText('POINT(1 1)', 0),
277 ST_GeomFromText('POINT(1 1)', 2000));
278 # This should give an error since there is an index on the column.
279 ALTER TABLE t1 CHANGE COLUMN col_srid_2000 col_srid_2000 POINT NOT NULL SRID 4326;
280 ERROR HY000: The SRID specification on the column 'col_srid_2000' cannot be changed because there is a spatial index on the column. Please remove the spatial index before altering the SRID specification.
281 # Remove the index from col_srid_2000
282 DROP INDEX idx_2000 ON t1;
283 # This should not be allowed since the column contains data with a
284 # different SRID
285 ALTER TABLE t1 CHANGE COLUMN col_srid_2000 col_srid_2000 POINT NOT NULL SRID 4326;
286 ERROR HY000: The SRID of the geometry does not match the SRID of the column 'col_srid_2000'. The SRID of the geometry is 2000, but the SRID of the column is 4326. Consider changing the SRID of the geometry or the SRID property of the column.
287 DELETE FROM t1;
288 # Now it should be allowed since there is no data in the table,
289 ALTER TABLE t1 CHANGE COLUMN col_srid_2000 col_srid_2000 POINT NOT NULL SRID 4326;
290 # Wrong SRID for col_srid_2000, so should not work
291 INSERT INTO t1 (col_no_srid, col_srid_0, col_srid_2000) VALUES (
292 ST_GeomFromText('POINT(1 1)'),
293 ST_GeomFromText('POINT(1 1)', 0),
294 ST_GeomFromText('POINT(1 1)', 2000));
295 ERROR HY000: The SRID of the geometry does not match the SRID of the column 'col_srid_2000'. The SRID of the geometry is 2000, but the SRID of the column is 4326. Consider changing the SRID of the geometry or the SRID property of the column.
296 INSERT INTO t1 (col_no_srid, col_srid_0, col_srid_2000) VALUES (
297 ST_GeomFromText('POINT(1 1)'),
298 ST_GeomFromText('POINT(1 1)', 0),
299 ST_GeomFromText('POINT(1 1)', 4326));
300 ALTER TABLE t1 CHANGE COLUMN col_srid_2000 col_srid_2000 POINT NOT NULL;
301 # This should work just fine, since we have removed the SRID specification
302 # for col_srid_2000
303 INSERT INTO t1 (col_no_srid, col_srid_0, col_srid_2000) VALUES (
304 ST_GeomFromText('POINT(1 1)'),
305 ST_GeomFromText('POINT(1 1)', 0),
306 ST_GeomFromText('POINT(1 1)', 2000));
307 SELECT
308 ST_AsText(col_no_srid),
309 ST_AsText(col_srid_0),
310 ST_AsText(col_srid_2000),
311 ST_AsText(col_srid_4326_generated),
312 ST_SRID(col_no_srid),
313 ST_SRID(col_srid_0),
314 ST_SRID(col_srid_2000),
315 ST_SRID(col_srid_4326_generated)
316 FROM t1;
317 ST_AsText(col_no_srid) ST_AsText(col_srid_0) ST_AsText(col_srid_2000) ST_AsText(col_srid_4326_generated) ST_SRID(col_no_srid) ST_SRID(col_srid_0) ST_SRID(col_srid_2000) ST_SRID(col_srid_4326_generated)
318 POINT(1 1) POINT(1 1) POINT(1 1) POINT(1 1) 0 0 4326 4326
319 POINT(1 1) POINT(1 1) POINT(1 1) POINT(1 1) 0 0 2000 4326
320 CREATE SPATIAL INDEX idx_0 ON t1 (col_srid_0);
321 # Check that all columns have the correct SRID specification.
322 SHOW CREATE TABLE t1;
323 Table Create Table
324 t1 CREATE TABLE `t1` (
325 `col_no_srid` point NOT NULL,
326 `col_srid_0` point NOT NULL /*!80003 SRID 0 */,
327 `col_srid_2000` point NOT NULL,
328 `col_srid_4326_generated` point GENERATED ALWAYS AS (st_srid(`col_srid_2000`,4326)) VIRTUAL /*!80003 SRID 4326 */,
329 SPATIAL KEY `idx_0` (`col_srid_0`)
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
331 ALTER TABLE t1 DROP COLUMN col_srid_4326_generated;
332 DROP TABLE t1;
333 #
334 # Bug#26594499 WL#8592: CANNOT GET GEOMETRY OBJECT ERROR
335 #
336 CREATE TABLE t1 (
337 g1 geometry NOT NULL,
338 g3 geometry SRID 2000 NOT NULL,
339 g4 geometry SRID 4326 NOT NULL
340 );
341 INSERT INTO t1 VALUES (ST_GeomFromText('POINT(0 0)'),
342 ST_GeomFromText('POINT(0 0)', 2000), ST_GeomFromText('POINT(0 0)', 4326));
343 ALTER TABLE t1 add spatial index idx1(g4);
344 ALTER TABLE t1 add spatial index idx3(g3);
345 DROP TABLE t1;
346 #
347 # Bug#27015964 USELESS SPATIAL INDEX CAN BE CREATED WITHOUT WARNING
348 #
349 # Creating a table with a spatial index on a SRID-less column should
350 # raise a warning.
351 CREATE TABLE t1 (
352 g1 POINT NOT NULL
353 , g2 POINT NOT NULL
354 , g3 POINT NOT NULL SRID 0
355 , SPATIAL INDEX idx1 (g1)
356 , SPATIAL INDEX idx2 (g2)
357 , SPATIAL INDEX idx3 (g3));
358 Warnings:
359 Warning 3674 The spatial index on column 'g1' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.
360 Warning 3674 The spatial index on column 'g2' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.
361 DROP INDEX idx1 ON t1;
362 DROP INDEX idx2 ON t1;
363 DROP INDEX idx3 ON t1;
364 # Verify that we get the same warning when using CREATE INDEX syntax.
365 CREATE SPATIAL INDEX idx1 ON t1 (g1);
366 Warnings:
367 Warning 3674 The spatial index on column 'g1' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.
368 CREATE SPATIAL INDEX idx2 ON t1 (g2);
369 Warnings:
370 Warning 3674 The spatial index on column 'g2' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.
371 CREATE SPATIAL INDEX idx3 ON t1 (g3);
372 DROP INDEX idx1 ON t1;
373 DROP INDEX idx2 ON t1;
374 DROP INDEX idx3 ON t1;
375 # Verify that we get the same warning when using ALTER TABLE syntax.
376 ALTER TABLE t1 ADD SPATIAL INDEX idx1 (g1);
377 Warnings:
378 Warning 3674 The spatial index on column 'g1' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.
379 ALTER TABLE t1 ADD SPATIAL INDEX idx2 (g2);
380 Warnings:
381 Warning 3674 The spatial index on column 'g2' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column.
382 ALTER TABLE t1 ADD SPATIAL INDEX idx3 (g3);
383 DROP TABLE t1;
384 #
385 # Bug#27220467 SERVER HANGS WHILE CREATING A NEW SRS WHICH IS USED IN
386 # ANOTHER CLIENT
387 #
388 # Try to create a point column using a SRID that doesn't exist
389 CREATE TABLE t1 (col1 POINT SRID 4326, col2 POINT SRID 1000000000);
390 ERROR SR001: There's no spatial reference system with SRID 1000000000.
391 # Verify that any lock that was taken for SRIDs is released
392 SELECT COUNT(*) AS should_be_zero FROM performance_schema.metadata_locks
393 WHERE OBJECT_TYPE = 'SRID';
394 should_be_zero
395 0
396 # Force the error to happen after all the columns are contextualized.
397 CREATE TABLE t1 (col1 POINT SRID 4326) ENGINE = "foo";
398 ERROR 42000: Unknown storage engine 'foo'
399 SELECT COUNT(*) AS should_be_zero FROM performance_schema.metadata_locks
400 WHERE OBJECT_TYPE = 'SRID';
401 should_be_zero
402 0
403 # Verify the same behavior for ALTER TABLE as well.
404 CREATE TABLE t1 (col1 INT);
405 ALTER TABLE t1 ADD COLUMN col2 POINT SRID 1000000000;
406 ERROR SR001: There's no spatial reference system with SRID 1000000000.
407 SELECT COUNT(*) AS should_be_zero FROM performance_schema.metadata_locks
408 WHERE OBJECT_TYPE = 'SRID';
409 should_be_zero
410 0
411 DROP TABLE t1;