Details
-
Bug
-
Status: In Testing (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.3
-
None
Description
Bug found while testing MDEV-37220 (Merged into main branch 12.3.0)
CTE's are read only i.e It cannot have their columns updated in updates set clause
Attempting to do so , should throw error
ERROR 1288 (HY000): The target table cte of the UPDATE is not updatable
Issue :
----------
No error thrown when cte columns are updated in update clause (When there is subquery in SET clause )
MariaDB [m]> with cte as (select * from t1 where c < 5) update cte set cte.a =(select a from cte); |
Query OK, 0 rows affected (0.002 sec) |
Rows matched: 1 Changed: 0 Warnings: 0 |
Note:
-------------
For literal values ,it throws error as expected
MariaDB [m]> with cte as (select * from t1 where c < 5) update cte set cte.a =10;
ERROR 1288 (HY000): The target table cte of the UPDATE is not updatable
How to repro:
create table t1(a int ,b int, c int); |
insert into t1 values (1,1,10) , (2,3,1); |
create table t3(a int); |
insert into t3 values (1),(2); |
 |
with cte as (select * from t1 where c < 5) update cte set cte.a =(select a from cte); |
|
Attachments
Issue Links
- is duplicated by
-
MDEV-38259 Assertion `table_share->tmp_table || m_lock_type == 1' failed
-
- Closed
-