[MDEV-7905] EXPLAIN FORMAT=JSON and correlated vs uncorrelated subqueries Created: 2015-04-04  Updated: 2017-03-01

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1
Fix Version/s: 10.2

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: analyze-stmt, explain_json


 Description   

Current EXPLAIN FORMAT=JSON output doesn't make it clear whether the subquery is correlated or not.

create table t0 (a int);
 
INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
create table t1 (a int, b int);
 
insert into t1 select a,a from t0;

MariaDB produces:

EXPLAIN: {
 
  "query_block": {
 
    "select_id": 1,
 
    "table": {
 
      "update": 1,
 
      "table_name": "t1",
 
      "access_type": "ALL",
 
      "rows": 10,
 
      "attached_condition": "(t1.a < 5)"
 
    },
 
    "subqueries": [
 
      {
 
        "query_block": {
 
          "select_id": 2,
 
          "table": {
 
            "table_name": "t0",
 
            "access_type": "ALL",
 
            "rows": 10,
 
            "filtered": 100,
 
            "attached_condition": "(t0.a < t1.b)"
 
          }
 
        }
 
      }
 
    ]
 
  }
 
}

MySQL produces:

EXPLAIN: {
 
  "query_block": {
 
    "select_id": 1,
 
    "table": {
 
      "update": true,
 
      "table_name": "t1",
 
      "access_type": "ALL",
 
      "rows": 10,
 
      "filtered": 100,
 
      "attached_condition": "(`test`.`t1`.`a` < 5)"
 
    },
 
    "update_value_subqueries": [
 
      {
 
        "dependent": true,
 
        "cacheable": false,
 
        "query_block": {
 
          "select_id": 2,
 
          "table": {
 
            "table_name": "t0",
 
            "access_type": "ALL",
 
            "rows": 10,
 
            "filtered": 100,
 
            "attached_condition": "(`test`.`t0`.`a` < `test`.`t1`.`b`)"
 
          }
 
        }
 
      }
 
    ]
 
  }
 
}

Note the 

        "dependent": true,
 
        "cacheable": false,

these two look weird (does dependent == !cacheable always ?), but not being able to tell between correlated and uncorrelated subquery at all is not a solution.

I think there are two possible options (correlated/non-correlated).



 Comments   
Comment by Sergei Petrunia [ 2015-04-04 ]

sanja, any ideas why the subquery is not using subquery cache in this example?

Comment by Oleksandr Byelkin [ 2015-04-04 ]

Where is the query?

Comment by Sergei Petrunia [ 2015-04-05 ]

The query is 

analyze format=json
 
update t1 set b=(select max(a) from t0 where a< t1.b) where t1.a<5 \G

(ping sanja).

Comment by Oleksandr Byelkin [ 2015-04-08 ]

Because the transformer run only for selects.

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