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

JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1, 10.2, 10.3, 10.4
    • 10.2.25
    • Optimizer
    • None

    Description

      Take this testcase from subselect.test:

      CREATE TABLE t1 ( a INT );
      INSERT INTO t1 VALUES (1),(5);
       
      # t2 must be MyISAM or Aria and contain 1 row
      CREATE TABLE t2 ( b INT ) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (1);
       
      CREATE TABLE t3 ( c INT );
      INSERT INTO t3 VALUES (4),(5);
       
      SET optimizer_switch='subquery_cache=off';
       
      SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1;
      

      and run EXPLAIN FORMAT=JSON for the last query:

      mysql> explain format=json SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1\G
      *************************** 1. row ***************************
      EXPLAIN: {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows": 2,
            "filtered": 100
          },
          "subqueries": [
            {
              "query_block": {
                "select_id": 2,
                "table": {
                  "table_name": "t2",
                  "access_type": "system",
                  "rows": 1,
                  "filtered": 100
                },
                "subqueries": [
                  {
                    "query_block": {
                      "select_id": 3,
                      "table": {
                        "table_name": "t3",
                        "access_type": "ALL",
                        "rows": 2,
                        "filtered": 100,
                        "attached_condition": "1 = t3.c"
                      }
                    }
                  }
                ]
              }
            }
          ]
        }
      }
      

      One can see that the subquery is present but it is not clear where it is attached to?

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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