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 ]
            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
            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 ]
            gdorman Gregory Dorman (Inactive) made changes -
            Labels tech_debt
            dleeyh Daniel Lee (Inactive) made changes -
            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.