[MDEV-32271] stored procedure invocation not checking underlying table structure alterations Created: 2023-09-27  Updated: 2023-10-12  Resolved: 2023-09-30

Status: Closed
Project: MariaDB Server
Component/s: Prepared Statements, Stored routines
Affects Version/s: 10.4.31
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Rex Johnston Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: reprepare, stored_procedures


 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

 Comments   
Comment by Rex Johnston [ 2023-09-28 ]

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.

Comment by Sergei Golubchik [ 2023-09-30 ]

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.

Comment by Rex Johnston [ 2023-09-30 ]

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

Comment by Sergei Golubchik [ 2023-10-12 ]

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.

Generated at Thu Feb 08 10:30:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.