Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
-
None
Description
The following demonstrates the problem:
MariaDB [test]> create view v1 as
|
-> with cte(a) as (select 1 from dual) select * from cte;
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [test]> select * from v1;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.01 sec)
|
|
|
MariaDB [test]> set @view_def = "
|
"> create view v2 as with cte(a) as (select 1 from dual) select * from cte
|
"> ";
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [test]> PREPARE stmt FROM @view_def;
|
Query OK, 0 rows affected (0.00 sec)
|
Statement prepared
|
|
|
MariaDB [test]> EXECUTE stmt;
|
ERROR 1054 (42S22): Unknown column 'cte.a' in 'field list'
|
Note that the definitions of v1 and v2 are identical.
The problem occurs as the With_element::rename_columns_of_derived_unit procedure doesn't allocate the CTE column names in a permanent MEMROOT for prepared statements and stored procedures.
So the problem is more general and doesn't concern views only. The test below doesn't work also.
MariaDB [test]> prepare stmt from "with cte(a) as (select 1) select * from cte";Query OK, 0 rows affected (0.00 sec)Statement preparedMariaDB [test]> execute stmt;ERROR 1054 (42S22): Unknown column 'cte.a' in 'field list'