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

ALTER TABLE…ADD COLUMN…POINT NOT NULL without DEFAULT should be refused if the table is not empty

    XMLWordPrintable

    Details

    • Sprint:
      10.2.10, 10.2.14

      Description

      The following test is based on the test innodb_gis.alter_spatial_index.

      For MDEV-11369 Instant ADD COLUMN, we want consistent behaviour in ALTER TABLE. No matter if the ALTER TABLE was implemented by ALGORITHM=COPY, non-instant ALGORITHM=INPLACE, or instant ALGORITHM=INPLACE, the result must be the same. The result should also be consistent with INSERT and UPDATE operations. Here it is not the case:

      --source include/have_innodb.inc
      --source include/have_geometry.inc
      CREATE TABLE tab(c1 int PRIMARY KEY) ENGINE=InnoDB;
      INSERT INTO tab SET c1=1;
      ALTER TABLE tab add COLUMN c2 POINT NOT NULL;
      SELECT * FROM tab;
      INSERT INTO tab SET c1=2;
      SELECT * FROM tab;
      DROP TABLE tab;
      

      Currently, the ALTER TABLE (no matter if ALGORITHM=COPY or ALGORITHM=INPLACE is specified) will insert the invalid value c2='' (the empty string). But, the INSERT is returning an error:

      mysqltest: At line 7: query 'INSERT INTO tab SET c1=2' failed: 1364: Field 'c2' doesn't have a default value
      

      The behaviour of ALTER TABLE and INSERT are inconsistent with each other.

      appended by HF:
      I came upon a crash that i think should be fixed along with this

      MariaDB [test]> create table pt(g geometry);
      Query OK, 0 rows affected (0.50 sec)
       
      MariaDB [test]> insert into pt values (st_geometryfromtext("point(1 2)"));
      Query OK, 1 row affected (0.05 sec)
      MariaDB [test]> alter table pt add column g2 geometry default("123123");
      ERROR 2013 (HY000): Lost connection to MySQL server during query
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: