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

No error thrown when CTE columns updated in updates set clause

    XMLWordPrintable

Details

    • Bug
    • Status: In Testing (View Workflow)
    • Major
    • Resolution: Unresolved
    • 12.3
    • 12.3
    • Optimizer - CTE
    • 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

          Activity

            People

              mariadb-pavithrapandith Pavithra Pandith
              mariadb-pavithrapandith Pavithra Pandith
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.