Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.2.5
    • 5.6.1
    • None
    • 2021-7

    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:

      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.

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

      INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
      


      – OK: 1 row affected

      Attachments

        Issue Links

          Activity

            toddstoffel Todd Stoffel (Inactive) created issue -
            toddstoffel Todd Stoffel (Inactive) made changes -
            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}
            toddstoffel Todd Stoffel (Inactive) made changes -
            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}
            toddstoffel Todd Stoffel (Inactive) made changes -
            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}
            toddstoffel Todd Stoffel (Inactive) made changes -
            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}
            toddstoffel Todd Stoffel (Inactive) made changes -
            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}
            toddstoffel Todd Stoffel (Inactive) made changes -
            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}
            toddstoffel Todd Stoffel (Inactive) made changes -
            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}
            gdorman Gregory Dorman (Inactive) made changes -
            Sprint 2021-7 [ 514 ]
            gdorman Gregory Dorman (Inactive) made changes -
            Rank Ranked higher
            drrtuy Roman made changes -
            Affects Version/s 5.6.1 [ 25031 ]
            gdorman Gregory Dorman (Inactive) made changes -
            Assignee Gregory Dorman [ gdorman ] Alexander Barkov [ bar ]
            gdorman Gregory Dorman (Inactive) made changes -
            Affects Version/s 5.6.1 [ 25031 ]

            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.
            

            bar 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.

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

            gdorman Gregory Dorman (Inactive) added a comment - Not new feature. Technical debt. Huge escalation by one of biggest customers. Needs fixing asap.
            gdorman Gregory Dorman (Inactive) made changes -
            Affects Version/s 1.2.5 [ 23613 ]
            Affects Version/s 5.5.2 [ 25601 ]
            gdorman Gregory Dorman (Inactive) made changes -
            Rank Ranked higher

            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.
            

            bar 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.
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Gagan Goel [ tntnatbry ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            tntnatbry Gagan Goel (Inactive) made changes -
            Status In Review [ 10002 ] In Testing [ 10301 ]
            tntnatbry Gagan Goel (Inactive) made changes -
            Assignee Gagan Goel [ tntnatbry ] Daniel Lee [ dleeyh ]

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

            tntnatbry Gagan Goel (Inactive) added a comment - For QA: Queries to confirm the issue and verify the fix works are in the issue description.
            gdorman Gregory Dorman (Inactive) made changes -
            Labels tech_debt

            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?

            dleeyh 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?
            dleeyh Daniel Lee (Inactive) made changes -

            UPDATE and DELETE issues are being tracked on MCOL-4701

            dleeyh Daniel Lee (Inactive) added a comment - UPDATE and DELETE issues are being tracked on MCOL-4701
            dleeyh Daniel Lee (Inactive) made changes -
            Resolution Fixed [ 1 ]
            Status In Testing [ 10301 ] Closed [ 6 ]
            gdorman Gregory Dorman (Inactive) made changes -
            gdorman Gregory Dorman (Inactive) made changes -
            bar Alexander Barkov made changes -
            toddstoffel Todd Stoffel (Inactive) made changes -
            Rank Ranked lower
            gdorman Gregory Dorman (Inactive) made changes -

            People

              dleeyh Daniel Lee (Inactive)
              toddstoffel Todd Stoffel (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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