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

DELETE FROM and UPDATE through a view made invalid by DROP COLUMN modify the base table, while SELECT from the view fails with ERROR 1356

    XMLWordPrintable

Details

    Description

      After a column used by a view is dropped from the base table, SELECT, DELETE v FROM v, DELETE ... RETURNING and INSERT through the view fail with ERROR 1356. UPDATE of a remaining view column and a plain DELETE FROM v succeed and change the rows of t0 that match the view's WHERE.

      CREATE TABLE t0 (a0 INT, a1 INT, a2 INT);
      INSERT INTO t0 VALUES (NULL, 1, 1), (NULL, 0, 1), (5, 1, 1);
      CREATE VIEW v AS SELECT t0.a2, t0.a1 FROM t0 WHERE t0.a0 IS NULL;
      ALTER TABLE t0 DROP COLUMN a1;
      SELECT * FROM v;                 -- ERROR 1356 (HY000): View 'test.v' references invalid table(s) or column(s) ...
      DELETE v FROM v;                 -- ERROR 1356
      DELETE FROM v RETURNING *;       -- ERROR 1356
      INSERT INTO v (a2) VALUES (9);   -- ERROR 1356
      UPDATE v SET a2 = 7;             -- Query OK, 2 rows affected
      SELECT * FROM t0;                -- (NULL, 7), (NULL, 7), (5, 1)
      DELETE FROM v;                   -- Query OK, 2 rows affected
      SELECT * FROM t0;                -- (5, 1)
      

      Expected: the UPDATE and the DELETE FROM v fail with ERROR 1356 and t0 is unchanged, like the other four statements on the same view.

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              kalakala Kala Chang
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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