[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 ]

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 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 ]

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

Labels

tech_debt

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

Link

This issue relates to ~~MCOL-4701~~ [ ~~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