[MCOL-4687] Insert from view regression - Jira

Todd Stoffel (Inactive) created issue - 2021-04-20 19:33

Todd Stoffel (Inactive) made changes - 2021-04-20 19:35

Field	Original Value	New Value
Description	CREATE DATABASE monty; USE monty; CREATE TABLE foo (id INT, fname VARCHAR(20)); INSERT INTO foo VALUES (1,'Greg'); CREATE VIEW view_foo as SELECT * FROM foo; SELECT * FROM view_foo; CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore; INSERT INTO bar SELECT * FROM view_foo; -- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported. INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t; -- OK: 1 row affected	{noformat} CREATE DATABASE monty; USE monty; CREATE TABLE foo (id INT, fname VARCHAR(20)); INSERT INTO foo VALUES (1,'Greg'); CREATE VIEW view_foo as SELECT * FROM foo; SELECT * FROM view_foo; CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore; INSERT INTO bar SELECT * FROM view_foo; {noformat} {color:red} -- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported. {color} {noformat} INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t; {noformat} {color:green} -- OK: 1 row affected {color}

Todd Stoffel (Inactive) made changes - 2021-04-20 19:36

Description

{noformat}
CREATE DATABASE monty;

USE monty;

CREATE TABLE foo (id INT, fname VARCHAR(20));

INSERT INTO foo VALUES (1,'Greg');

CREATE VIEW view_foo as SELECT * FROM foo;

SELECT * FROM view_foo;

CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;

INSERT INTO bar SELECT * FROM view_foo;
{noformat}

{color:red}
-- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
{color}

{noformat}
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
{noformat}

{color:green}
-- OK: 1 row affected
{color}

{code:sql}
CREATE DATABASE monty;

USE monty;

CREATE TABLE foo (id INT, fname VARCHAR(20));

INSERT INTO foo VALUES (1,'Greg');

CREATE VIEW view_foo as SELECT * FROM foo;

SELECT * FROM view_foo;

CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;

INSERT INTO bar SELECT * FROM view_foo;
{code}

{color:red}
-- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
{color}

{code:sql}
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
{code}

{color:green}
-- OK: 1 row affected
{color}

Todd Stoffel (Inactive) made changes - 2021-04-20 19:36

Description

{code:sql}
CREATE DATABASE monty;

USE monty;

CREATE TABLE foo (id INT, fname VARCHAR(20));

INSERT INTO foo VALUES (1,'Greg');

CREATE VIEW view_foo as SELECT * FROM foo;

SELECT * FROM view_foo;

CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;

INSERT INTO bar SELECT * FROM view_foo;
{code}

{color:red}
-- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
{color}

{code:sql}
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
{code}

{color:green}
-- OK: 1 row affected
{color}

{code:sql}
CREATE DATABASE monty;

USE monty;

CREATE TABLE foo (id INT, fname VARCHAR(20));

INSERT INTO foo VALUES (1,'Greg');

CREATE VIEW view_foo as SELECT * FROM foo;

SELECT * FROM view_foo;

CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;

INSERT INTO bar SELECT * FROM view_foo;
{code}
{color:red}
-- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
{color}

{code:sql}
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
{code}
{color:green}
-- OK: 1 row affected
{color}

Todd Stoffel (Inactive) made changes - 2021-04-20 19:37

Description

{code:sql}
CREATE DATABASE monty;

USE monty;

CREATE TABLE foo (id INT, fname VARCHAR(20));

INSERT INTO foo VALUES (1,'Greg');

CREATE VIEW view_foo as SELECT * FROM foo;

SELECT * FROM view_foo;

CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;

INSERT INTO bar SELECT * FROM view_foo;
{code}
{color:red}
-- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
{color}

{code:sql}
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
{code}
{color:green}
-- OK: 1 row affected
{color}

{code:sql}
CREATE DATABASE monty;
USE monty;
CREATE TABLE foo (id INT, fname VARCHAR(20));
INSERT INTO foo VALUES (1,'Greg');
CREATE VIEW view_foo as SELECT * FROM foo;
SELECT * FROM view_foo;
CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;
INSERT INTO bar SELECT * FROM view_foo;
{code}
{color:red}
-- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
{color}

{code:sql}
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
{code}
{color:green}
-- OK: 1 row affected
{color}

Todd Stoffel (Inactive) made changes - 2021-04-20 19:37

Description

{code:sql}
CREATE DATABASE monty;
USE monty;
CREATE TABLE foo (id INT, fname VARCHAR(20));
INSERT INTO foo VALUES (1,'Greg');
CREATE VIEW view_foo as SELECT * FROM foo;
SELECT * FROM view_foo;
CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;
INSERT INTO bar SELECT * FROM view_foo;
{code}
{color:red}
-- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
{color}

{code:sql}
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
{code}
{color:green}
-- OK: 1 row affected
{color}

{code:sql}
CREATE DATABASE monty;
USE monty;
CREATE TABLE foo (id INT, fname VARCHAR(20));
INSERT INTO foo VALUES (1,'Greg');
CREATE VIEW view_foo as SELECT * FROM foo;
SELECT * FROM view_foo;
CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;
INSERT INTO bar SELECT * FROM view_foo;
{code}
{color:red}
-- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
{color}
{code:sql}
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
{code}
{color:green}
-- OK: 1 row affected
{color}

Todd Stoffel (Inactive) made changes - 2021-04-20 19:38

Description

{code:sql}
CREATE DATABASE monty;
USE monty;
CREATE TABLE foo (id INT, fname VARCHAR(20));
INSERT INTO foo VALUES (1,'Greg');
CREATE VIEW view_foo as SELECT * FROM foo;
SELECT * FROM view_foo;
CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;
INSERT INTO bar SELECT * FROM view_foo;
{code}
{color:red}
-- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
{color}
{code:sql}
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
{code}
{color:green}
-- OK: 1 row affected
{color}

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:

{code:sql}
CREATE DATABASE monty;
USE monty;
CREATE TABLE foo (id INT, fname VARCHAR(20));
INSERT INTO foo VALUES (1,'Greg');
CREATE VIEW view_foo as SELECT * FROM foo;
SELECT * FROM view_foo;
CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;
INSERT INTO bar SELECT * FROM view_foo;
{code}
{color:red}
-- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
{color}
{code:sql}
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
{code}
{color:green}
-- OK: 1 row affected
{color}

Todd Stoffel (Inactive) made changes - 2021-04-20 19:39

Description

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:

{code:sql}
CREATE DATABASE monty;
USE monty;
CREATE TABLE foo (id INT, fname VARCHAR(20));
INSERT INTO foo VALUES (1,'Greg');
CREATE VIEW view_foo as SELECT * FROM foo;
SELECT * FROM view_foo;
CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;
INSERT INTO bar SELECT * FROM view_foo;
{code}
{color:red}
-- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
{color}
{code:sql}
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
{code}
{color:green}
-- OK: 1 row affected
{color}

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:

{code:sql}
CREATE DATABASE monty;
USE monty;
CREATE TABLE foo (id INT, fname VARCHAR(20));
INSERT INTO foo VALUES (1,'Greg');
CREATE VIEW view_foo as SELECT * FROM foo;
SELECT * FROM view_foo;
CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;
INSERT INTO bar SELECT * FROM view_foo;
{code}
{color:red}
-- ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
{color}

However, if we use the wrapper trick, it does work:

{code:sql}
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
{code}
{color:green}
-- OK: 1 row affected
{color}

Gregory Dorman (Inactive) made changes - 2021-04-21 13:07

Sprint

2021-7 [ 514 ]

Gregory Dorman (Inactive) made changes - 2021-04-21 13:07

Rank

Ranked higher

Roman made changes - 2021-04-21 17:14

Affects Version/s

5.6.1 [ 25031 ]

Gregory Dorman (Inactive) made changes - 2021-04-21 17:16

Assignee

Gregory Dorman [ gdorman ]

Alexander Barkov [ bar ]

Gregory Dorman (Inactive) made changes - 2021-04-21 17:17

Affects Version/s

5.6.1 [ 25031 ]

Alexander Barkov added a comment - 2021-04-23 05:29

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.

Alexander Barkov added a comment - 2021-04-23 05:29 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.

Gregory Dorman (Inactive) added a comment - 2021-04-23 12:25

Not new feature. Technical debt. Huge escalation by one of biggest customers. Needs fixing asap.

Gregory Dorman (Inactive) added a comment - 2021-04-23 12:25 Not new feature. Technical debt. Huge escalation by one of biggest customers. Needs fixing asap.

Gregory Dorman (Inactive) made changes - 2021-04-23 17:00

Affects Version/s		1.2.5 [ 23613 ]
Affects Version/s	5.5.2 [ 25601 ]

Gregory Dorman (Inactive) made changes - 2021-04-27 18:31

Rank

Ranked higher

Alexander Barkov added a comment - 2021-04-28 09:05

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.

Alexander Barkov added a comment - 2021-04-28 09:05 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.

Alexander Barkov made changes - 2021-04-30 12:40

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2021-04-30 12:41

Assignee	Alexander Barkov [ bar ]	Gagan Goel [ tntnatbry ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Gagan Goel (Inactive) made changes - 2021-04-30 13:53

Status

In Review [ 10002 ]

In Testing [ 10301 ]

Gagan Goel (Inactive) made changes - 2021-04-30 13:53

Assignee

Gagan Goel [ tntnatbry ]

Daniel Lee [ dleeyh ]

Gagan Goel (Inactive) added a comment - 2021-04-30 13:55

For QA: Queries to confirm the issue and verify the fix works are in the issue description.

Gagan Goel (Inactive) added a comment - 2021-04-30 13:55 For QA: Queries to confirm the issue and verify the fix works are in the issue description.

Gregory Dorman (Inactive) made changes - 2021-04-30 15:25

Labels

tech_debt

Daniel Lee (Inactive) added a comment - 2021-04-30 15:29

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?

Daniel Lee (Inactive) added a comment - 2021-04-30 15:29 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?

Daniel Lee (Inactive) made changes - 2021-04-30 15:41

Link

This issue relates to ~~MCOL-4701~~ [ ~~MCOL-4701~~ ]

Daniel Lee (Inactive) added a comment - 2021-04-30 15:44

UPDATE and DELETE issues are being tracked on ~~MCOL-4701~~

Daniel Lee (Inactive) added a comment - 2021-04-30 15:44 UPDATE and DELETE issues are being tracked on MCOL-4701

Daniel Lee (Inactive) made changes - 2021-04-30 15:44

Resolution		Fixed [ 1 ]
Status	In Testing [ 10301 ]	Closed [ 6 ]

Gregory Dorman (Inactive) made changes - 2021-04-30 15:51

Link

This issue split to MCOL-4702 [ MCOL-4702 ]

Gregory Dorman (Inactive) made changes - 2021-04-30 15:55

Link

This issue relates to ~~MCOL-4701~~ [ ~~MCOL-4701~~ ]

Alexander Barkov made changes - 2021-04-30 17:22

Link

This issue relates to ~~MCOL-4701~~ [ ~~MCOL-4701~~ ]

Todd Stoffel (Inactive) made changes - 2021-05-03 16:37

Rank

Ranked lower

Gregory Dorman (Inactive) made changes - 2021-05-17 16:14

Link

This issue split to ~~MCOL-4704~~ [ ~~MCOL-4704~~ ]

MariaDB ColumnStore

Insert from view regression

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration