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

UPDATE/DELETE through a VIEW over ENGINE=DuckDB fails with DuckDB catalog error

    XMLWordPrintable

Details

    • Can result in unexpected behaviour

    Description

      UPDATE/DELETE statements through an updatable VIEW over a table with ENGINE=DuckDB fail with a confusing DuckDB catalog error, instead of either completing successfully or returning a clear MariaDB-level error. INSERT through the same view works correctly

      Testcase

      INSTALL SONAME 'ha_duckdb';
      CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=DuckDB;
      CREATE VIEW v1 AS SELECT * FROM t1;
      INSERT INTO v1 VALUES (1);         
      SELECT * FROM t1;                  
      UPDATE v1 SET id=2 WHERE id=1;     
      DELETE FROM v1 WHERE id=2;         
      

      CLI Output

      13.1.0-opt>INSTALL SONAME 'ha_duckdb';
      Query OK, 0 rows affected (0.410 sec)
       
      13.1.0-opt>CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=DuckDB;
      Query OK, 0 rows affected (0.014 sec)
       
      13.1.0-opt>CREATE VIEW v1 AS SELECT * FROM t1;
      Query OK, 0 rows affected (0.004 sec)
       
      13.1.0-opt>INSERT INTO v1 VALUES (1);         
      Query OK, 1 row affected (0.014 sec)
       
      13.1.0-opt>SELECT * FROM t1;                  
      +----+
      | id |
      +----+
      |  1 |
      +----+
      1 row in set (0.004 sec)
       
      13.1.0-opt>UPDATE v1 SET id=2 WHERE id=1;     
      ERROR 1296 (HY000): Got error 168 'Catalog Error: Table with name v1 does not exist!
      Did you mean "t1"?
       
      LINE 1: UPDATE v1 SET id=2 WHERE id=1
                     ^' from DuckDB
      13.1.0-opt>DELETE FROM v1 WHERE id=2;         
      ERROR 1296 (HY000): Got error 168 'Catalog Error: Table with name v1 does not exist!
      Did you mean "t1"?
       
      LINE 1: DELETE FROM v1 WHERE id=2
                          ^' from DuckDB
      13.1.0-opt>
      

      Attachments

        Issue Links

          Activity

            People

              drrtuy Roman
              ramesh Ramesh Sivaraman
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 0.5h
                  0.5h

                  Git Integration

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