[MDEV-8529] EXPLAIN INSERT/UPDATE provides no information about views CHECK OPTION enforcing Created: 2015-07-23  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Views
Affects Version/s: 10.1
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

When an INSERT or UPDATE is performed on a view with CHECK OPTION, some check on data must be performed by MariaDB. MariaDB and MySQL documentation does not contain any specific information.

Could EXPLAIN show which tables are read, which indexes are used, etc?

 Comments   
Comment by Sergei Golubchik [ 2015-10-31 ]

It does now, doesn't it? For example, in explain_non_select test (from our mysql-test suite) there is a test for EXPLAIN UPDATE of a view and it shows tables and indexes. Could you clarify what you mean, please?

Comment by Federico Razzoli [ 2015-10-31 ]

Consider this:

CREATE OR REPLACE TABLE t1 (
 
        a INT PRIMARY KEY,
 
        b INT
 
)
 
        ENGINE = InnoDB;
 
 
 
CREATE OR REPLACE TABLE t2 (
 
        a INT PRIMARY KEY,
 
        b INT
 
)
 
        ENGINE = InnoDB;
 
 
 
CREATE OR REPLACE VIEW v AS
 
        SELECT a, b
 
                FROM t1
 
                WHERE t1.b IN (SELECT a FROM t2)
 
        WITH CHECK OPTION;
 
 
 
INSERT INTO t2 (a, b) VALUES
 
        (1, 1),
 
        (2, 2),
 
        (3, 3);
 
 
 
EXPLAIN INSERT INTO v (a, b) VALUES
 
        (1, 1);

I know that MariaDB checks the view's WHERE clause somehow, but I can't get a query plan.

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