ALTER
After a meangless alter from PONT to LINESTRING, a SELECT query still returns POINT values.
CREATE OR REPLACE TABLE t1 (a POINT) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (Point(0,0));
|
ALTER TABLE t1 MODIFY a LINESTRING;
|
SHOW CREATE TABLE t1;
|
+-------+------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`a` linestring DEFAULT NULL
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
+-------+------------------------------------------------------------------------------------------+
|
Notice, it altered the table without any warnings, although the two data types are not really incompatible.
Now if I do a query, it still return points from a column of the LINESTRING data type:
SELECT AsText(a) FROM t1;
|
+------------+
|
| AsText(a) |
|
+------------+
|
| POINT(0 0) |
|
+------------+
|
Note, inserting a POINT value to a LINESTRING column correctly returns an error:
INSERT INTo t1 VALUES (Point(0,0));
|
ERROR 1366 (22007): Incorrect LINESTRING value: 'POINT' for column `test`.`t1`.`a` at row 1
|
INSERT..SELECT
The same problem is repeatable in INSERT..SELECT:
CREATE OR REPLACE TABLE t1 (a LINESTRING);
|
CREATE OR REPLACE TABLE t2 (a POINT);
|
INSERT INTO t2 VALUES (POINT(0,0));
|
INSERT INTO t1 SELECT * FROM t2;
|
SELECT AsText(a) FROM t1;
|
+------------+
|
| AsText(a) |
|
+------------+
|
| POINT(0 0) |
|
+------------+
|
Notice, t1.a is LINESTRING, but it stores POINT values.
|