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

ALTER from POINT to LINESTRING erroneously preserves POINT values

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
    • 10.4.7, 10.5.0
    • Data types, GIS
    • 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

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Rank Ranked higher
          bar Alexander Barkov made changes -
          Description
          After a meangless alter from PONT to LINESTRING, a SELECT query still returns POINT values.

          {code:sql}
          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;
          {code}
          {noformat}
          +-------+------------------------------------------------------------------------------------------+
          | Table | Create Table |
          +-------+------------------------------------------------------------------------------------------+
          | t1 | CREATE TABLE `t1` (
            `a` linestring DEFAULT NULL
          ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
          +-------+------------------------------------------------------------------------------------------+
          {noformat}

          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:

          {code:sql}
          SELECT AsText(a) FROM t1;
          {code}
          {noformat}
          +------------+
          | AsText(a) |
          +------------+
          | POINT(0 0) |
          +------------+
          {noformat}

          Note, inserting a POINT value to a LINESTRING column correctly returns an error:
          {code:sql}
          INSERT INTo t1 VALUES (Point(0,0));
          {code}
          {noformat}
          ERROR 1366 (22007): Incorrect LINESTRING value: 'POINT' for column `test`.`t1`.`a` at row 1
          {noformat}
          		After a meangless alter from PONT to LINESTRING, a SELECT query still returns POINT values.

          {code:sql}
          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;
          {code}
          {noformat}
          +-------+------------------------------------------------------------------------------------------+
          | Table | Create Table |
          +-------+------------------------------------------------------------------------------------------+
          | t1 | CREATE TABLE `t1` (
            `a` linestring DEFAULT NULL
          ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
          +-------+------------------------------------------------------------------------------------------+
          {noformat}

          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:

          {code:sql}
          SELECT AsText(a) FROM t1;
          {code}
          {noformat}
          +------------+
          | AsText(a) |
          +------------+
          | POINT(0 0) |
          +------------+
          {noformat}

          Note, inserting a POINT value to a LINESTRING column correctly returns an error:
          {code:sql}
          INSERT INTo t1 VALUES (Point(0,0));
          {code}
          {noformat}
          ERROR 1366 (22007): Incorrect LINESTRING value: 'POINT' for column `test`.`t1`.`a` at row 1
          {noformat}

          h2. INSERT..SELECT
          The same problem is repeatable in INSERT..SELECT:
          {code:sql}
          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;
          {code}
          {noformat}
          +------------+
          | AsText(a) |
          +------------+
          | POINT(0 0) |
          +------------+
          {noformat}
          Notice, {{t1.a}} is {{LINESTRING}}, but it stores {{POINT}} values.
          bar Alexander Barkov made changes -
          Description After a meangless alter from PONT to LINESTRING, a SELECT query still returns POINT values.

          {code:sql}
          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;
          {code}
          {noformat}
          +-------+------------------------------------------------------------------------------------------+
          | Table | Create Table |
          +-------+------------------------------------------------------------------------------------------+
          | t1 | CREATE TABLE `t1` (
            `a` linestring DEFAULT NULL
          ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
          +-------+------------------------------------------------------------------------------------------+
          {noformat}

          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:

          {code:sql}
          SELECT AsText(a) FROM t1;
          {code}
          {noformat}
          +------------+
          | AsText(a) |
          +------------+
          | POINT(0 0) |
          +------------+
          {noformat}

          Note, inserting a POINT value to a LINESTRING column correctly returns an error:
          {code:sql}
          INSERT INTo t1 VALUES (Point(0,0));
          {code}
          {noformat}
          ERROR 1366 (22007): Incorrect LINESTRING value: 'POINT' for column `test`.`t1`.`a` at row 1
          {noformat}

          h2. INSERT..SELECT
          The same problem is repeatable in INSERT..SELECT:
          {code:sql}
          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;
          {code}
          {noformat}
          +------------+
          | AsText(a) |
          +------------+
          | POINT(0 0) |
          +------------+
          {noformat}
          Notice, {{t1.a}} is {{LINESTRING}}, but it stores {{POINT}} values.
          		h2. ALTER
          After a meangless alter from PONT to LINESTRING, a SELECT query still returns POINT values.

          {code:sql}
          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;
          {code}
          {noformat}
          +-------+------------------------------------------------------------------------------------------+
          | Table | Create Table |
          +-------+------------------------------------------------------------------------------------------+
          | t1 | CREATE TABLE `t1` (
            `a` linestring DEFAULT NULL
          ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
          +-------+------------------------------------------------------------------------------------------+
          {noformat}

          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:

          {code:sql}
          SELECT AsText(a) FROM t1;
          {code}
          {noformat}
          +------------+
          | AsText(a) |
          +------------+
          | POINT(0 0) |
          +------------+
          {noformat}

          Note, inserting a POINT value to a LINESTRING column correctly returns an error:
          {code:sql}
          INSERT INTo t1 VALUES (Point(0,0));
          {code}
          {noformat}
          ERROR 1366 (22007): Incorrect LINESTRING value: 'POINT' for column `test`.`t1`.`a` at row 1
          {noformat}

          h2. INSERT..SELECT
          The same problem is repeatable in INSERT..SELECT:
          {code:sql}
          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;
          {code}
          {noformat}
          +------------+
          | AsText(a) |
          +------------+
          | POINT(0 0) |
          +------------+
          {noformat}
          Notice, {{t1.a}} is {{LINESTRING}}, but it stores {{POINT}} values.
          bar Alexander Barkov made changes -
          Fix Version/s 10.5.0 [ 23709 ]
          Fix Version/s 10.4.7 [ 23720 ]
          Fix Version/s 10.5 [ 23123 ]
          bar Alexander Barkov made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          bar Alexander Barkov made changes -
          Component/s Data types [ 13906 ]
          Component/s GIS [ 10105 ]
          Resolution Fixed [ 1 ]
          Status Confirmed [ 10101 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 97643 ] MariaDB v4 [ 156371 ]

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.