Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32271

stored procedure invocation not checking underlying table structure alterations

Details

    Description

      create table t1 (a int, b int);
      create procedure sp() with cte as (select * from t1) select * from cte;
      call sp();
      alter table t1 add column c int;
      call sp();
      

      The second sp() call should probably fail with ER_WITH_COL_WRONG_LIST

      Attachments

        Activity

          Johnston Rex Johnston added a comment - - edited

          additionally prepared statements can too suffer from this

          prepare st from "with cte (c1,c2) as
            (select a as col1, b as col2 from t1
            union select a as col3, b as col4 from t2),
          cte2 (c3, c4) as
            (select a as col5, b as col6 from t1
            union select a as col7, b as col8 from t2)
          select * from cte where c1=1 union select * from cte2 where c3=3";
           
          execute st;
          alter table t1 add column c int;
          execute st;
          

          provokes no error.

          Johnston Rex Johnston added a comment - - edited additionally prepared statements can too suffer from this prepare st from "with cte (c1,c2) as (select a as col1, b as col2 from t1 union select a as col3, b as col4 from t2), cte2 (c3, c4) as (select a as col5, b as col6 from t1 union select a as col7, b as col8 from t2) select * from cte where c1=1 union select * from cte2 where c3=3" ;   execute st; alter table t1 add column c int ; execute st; provokes no error.

          why would the second routine invocation or execute fail? you added a column the routine definition is still vaild. if you'd remove a column, it'd fail.

          serg Sergei Golubchik added a comment - why would the second routine invocation or execute fail? you added a column the routine definition is still vaild. if you'd remove a column, it'd fail.
          Johnston Rex Johnston added a comment -

          then we should at least be consistent.

          MariaDB [test]> prepare st from "with cte (c1,c2) as (select * from t1) select * from cte";
          Query OK, 0 rows affected (0.005 sec)
          Statement prepared
           
          MariaDB [test]> execute st;
          +------+------+
          | c1   | c2   |
          +------+------+
          |    1 |    1 |
          |    1 |    2 |
          |    1 |    3 |
          |    2 |    1 |
          |    2 |    2 |
          +------+------+
          5 rows in set (0.006 sec)
           
          MariaDB [test]> execute st;
          +------+------+
          | c1   | c2   |
          +------+------+
          |    1 |    1 |
          |    1 |    2 |
          |    1 |    3 |
          |    2 |    1 |
          |    2 |    2 |
          +------+------+
          5 rows in set (0.005 sec)
           
          MariaDB [test]> alter table t1 add column c int;
          Query OK, 0 rows affected (0.037 sec)
          Records: 0  Duplicates: 0  Warnings: 0
           
          MariaDB [test]> execute st;
          ERROR 4002 (HY000): WITH column list and SELECT field list have different column counts
          

          Johnston Rex Johnston added a comment - then we should at least be consistent. MariaDB [test]> prepare st from "with cte (c1,c2) as (select * from t1) select * from cte"; Query OK, 0 rows affected (0.005 sec) Statement prepared   MariaDB [test]> execute st; +------+------+ | c1 | c2 | +------+------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 2 | +------+------+ 5 rows in set (0.006 sec)   MariaDB [test]> execute st; +------+------+ | c1 | c2 | +------+------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 2 | +------+------+ 5 rows in set (0.005 sec)   MariaDB [test]> alter table t1 add column c int; Query OK, 0 rows affected (0.037 sec) Records: 0 Duplicates: 0 Warnings: 0   MariaDB [test]> execute st; ERROR 4002 (HY000): WITH column list and SELECT field list have different column counts

          here is detects the ALTER TABLE and reprepares the statement using your original query text. that fails, because the query is no longer valid — WITH column list and SELECT field list have different column counts, indeed. I don't see this being the case in any other of your examples.

          serg Sergei Golubchik added a comment - here is detects the ALTER TABLE and reprepares the statement using your original query text. that fails, because the query is no longer valid — WITH column list and SELECT field list have different column counts, indeed. I don't see this being the case in any other of your examples.

          People

            Unassigned Unassigned
            Johnston Rex Johnston
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.