[MDEV-19714] JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON Created: 2019-06-07  Updated: 2019-06-08  Resolved: 2019-06-08

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.2.25

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: 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?

 Comments   
Comment by Sergei Petrunia [ 2019-06-07 ]

outer_ref_cond is printed started from 10.2, so this should be fixed in 10.2+.

Comment by Sergei Petrunia [ 2019-06-07 ]

It is not entirely clear to me why it was necessary to have JOIN::pseudo_bits_cond in addition to JOIN::outer_ref_cond. But if the condition is there, it should be printed.

Generated at Thu Feb 08 08:53:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.