Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 10.11.19, 11.4.13, 11.8.9, 12.3.3, 13.0.2, 13.1.1
-
None
-
Official Docker images mariadb:10.11.19, 11.4.13, 11.8.9, 12.3.3, 13.0.2, 13.1.1, x86_64, default configuration
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
- relates to
-
MDEV-30309 Assertion failure or unexpected result upon 2nd execution of SP with DELETE RETURNING from view
-
- Open
-