Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19819

ALTER from POINT to LINESTRING erroneously preserves POINT values

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5
    • Fix Version/s: 10.4.7, 10.5.0
    • Component/s: Data types, GIS
    • Labels:
      None

      Description

      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.

        Attachments

          Activity

            People

            • Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: