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

EXPLAIN FORMAT=JSON and correlated vs uncorrelated subqueries

    XMLWordPrintable

    Details

      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).

        Attachments

          Activity

            People

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            psergey Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: