Inserting into a Columnstore table from a view used to work in 1.2 and below. A customer noticed that in 5.x and above that it no longer functions. See this example:
CREATEDATABASE monty;
USE monty;
CREATETABLE foo (id INT, fname VARCHAR(20));
INSERTINTO foo VALUES (1,'Greg');
CREATEVIEW view_foo asSELECT * FROM foo;
SELECT * FROM view_foo;
CREATETABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;
INSERTINTO bar SELECT * FROM view_foo;
– ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
However, if we use the wrapper trick, it does work:
INSERTINTO bar SELECT * FROM (SELECT * FROM view_foo) t;
CREATETABLE t1 (id INT, fname VARCHAR(20)) ENGINE=InnoDB;
INSERTINTO t1 VALUES (1,'Greg');
CREATEVIEW v1 asSELECT * FROM t1;
SELECT * FROM v1;
CREATETABLE t2 (id INT, fname VARCHAR(20)) ENGINE=Columnstore;
INSERTINTO t2 SELECT * FROM (SELECT * FROM v1) t;
SELECT * FROM t2;
+------+-------+
| id | fname |
+------+-------+
| 1 | Greg |
+------+-------+
INSERTINTO t2 SELECT * FROM v1;
ERROR 1178 (42000): The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
Alexander Barkov
added a comment - Confirmed with the following script:
DROP TABLE IF EXISTS t1,t2;
DROP VIEW IF EXISTS v1;
CREATE TABLE t1 (id INT , fname VARCHAR (20)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1, 'Greg' );
CREATE VIEW v1 as SELECT * FROM t1;
SELECT * FROM v1;
CREATE TABLE t2 (id INT , fname VARCHAR (20)) ENGINE=Columnstore;
INSERT INTO t2 SELECT * FROM ( SELECT * FROM v1) t;
SELECT * FROM t2;
+------+-------+
| id | fname |
+------+-------+
| 1 | Greg |
+------+-------+
INSERT INTO t2 SELECT * FROM v1;
ERROR 1178 (42000): The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
CREATETABLE t1 (id INT, fname VARCHAR(20)) ENGINE=InnoDB;
INSERTINTO t1 VALUES (1,'Greg');
CREATEVIEW v1 asSELECT * FROM t1;
SELECT * FROM v1;
CREATETABLE t2 (id INT, fname VARCHAR(20)) ENGINE=Columnstore;
INSERTINTO t2 VALUES (1,'Roman');
UPDATE t2,v1 SET t2.fname=v1.fname WHERE t2.id=v1.id;
ERROR 1178 (42000): The storage engine for the table doesn't support IDB-1011: Update on VIEW is currently not supported.
DELETE t2 FROM t2,v1 WHERE t2.id=v1.id;
ERROR 1178 (42000): The storage engine for the table doesn't support IDB-1011: Delete on VIEW is currently not supported.
Alexander Barkov
added a comment - UPDATE and DELETE also do not work:
DROP TABLE IF EXISTS t1,t2;
DROP VIEW IF EXISTS v1;
CREATE TABLE t1 (id INT , fname VARCHAR (20)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1, 'Greg' );
CREATE VIEW v1 as SELECT * FROM t1;
SELECT * FROM v1;
CREATE TABLE t2 (id INT , fname VARCHAR (20)) ENGINE=Columnstore;
INSERT INTO t2 VALUES (1, 'Roman' );
UPDATE t2,v1 SET t2.fname=v1.fname WHERE t2.id=v1.id;
ERROR 1178 (42000): The storage engine for the table doesn't support IDB-1011: Update on VIEW is currently not supported.
DELETE t2 FROM t2,v1 WHERE t2.id=v1.id;
ERROR 1178 (42000): The storage engine for the table doesn't support IDB-1011: Delete on VIEW is currently not supported.
As Barkov mentioned, UPDATE and DELETE do not work. Do we plan on fixing them as part of this ticket, or they are out of scope?
Daniel Lee (Inactive)
added a comment - build tested: 5.6.1 ( Drone #2295 )
Insert test case worked fine now.
As Barkov mentioned, UPDATE and DELETE do not work. Do we plan on fixing them as part of this ticket, or they are out of scope?
Bug
Major
Confirmed with the following script:
+------+-------+| id | fname |+------+-------+| 1 | Greg |+------+-------+ERROR 1178 (42000): The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.