Quelle  subselect_mat_analyze_json.result   Sprache: Lisp

set @save_optimizer_switch=@@optimizer_switch;
create table t1 (a int);
create table t2 (b int);
insert into t1 values (null), (1), (2), (3);
insert into t2 values (3), (4);
set @@optimizer_switch = "materialization=on,in_to_exists=off,semijoin=off";
explain format=json select * from t1 where a in (select b from t2);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": "COST_REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "loops": 1,
          "rows": 4,
          "cost": "COST_REPLACED",
          "filtered": 100,
          "attached_condition": "<in_optimizer>(t1.a,t1.a in (subquery#2))"
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "query_block": {
            "select_id": 2,
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "rows": 2,
                  "cost": "COST_REPLACED",
                  "filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
# "Complete match" execution strategy
analyze format=json select * from t1 where a in (select b from t2);
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "cost": "REPLACED",
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "loops": 1,
          "r_loops": 1,
          "rows": 4,
          "r_rows": 4,
          "cost": "REPLACED",
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": 25,
          "attached_condition": "<in_optimizer>(t1.a,t1.a in (subquery#2))",
          "r_filtered": 25
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "index_lookup",
          "r_loops": 3,
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "r_loops": 1,
                  "rows": 2,
                  "r_rows": 2,
                  "cost": "REPLACED",
                  "r_table_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_engine_stats": REPLACED,
                  "filtered": 100,
                  "r_total_filtered": 100,
                  "r_filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
# "Partial match" is used due to NOT IN
analyze format=json select * from t1 where a not in (select b from t2);
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "cost": "REPLACED",
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "loops": 1,
          "r_loops": 1,
          "rows": 4,
          "r_rows": 4,
          "cost": "REPLACED",
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": 50,
          "attached_condition": "!<in_optimizer>(t1.a,t1.a in (subquery#2))",
          "r_filtered": 50
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "null-aware index_lookup",
          "r_loops": 4,
          "r_index_lookups": 3,
          "r_partial_matches": 1,
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "r_loops": 1,
                  "rows": 2,
                  "r_rows": 2,
                  "cost": "REPLACED",
                  "r_table_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_engine_stats": REPLACED,
                  "filtered": 100,
                  "r_total_filtered": 100,
                  "r_filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
# Subselect in GROUP BY
analyze format=json select a from t1 group by a in (select b from t2);
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "cost": "REPLACED",
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "filesort": {
      "sort_key": "<in_optimizer>(t1.a,t1.a in (subquery#2))",
      "r_loops": 1,
      "r_total_time_ms": "REPLACED",
      "r_used_priority_queue": false,
      "r_output_rows": 3,
      "r_buffer_size": "REPLACED",
      "r_sort_mode": "sort_key,rowid",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "loops": 1,
              "r_loops": 1,
              "rows": 4,
              "r_rows": 4,
              "cost": "REPLACED",
              "r_table_time_ms": "REPLACED",
              "r_other_time_ms": "REPLACED",
              "r_engine_stats": REPLACED,
              "filtered": 100,
              "r_total_filtered": 100,
              "r_filtered": 100
            }
          }
        ],
        "subqueries": [
          {
            "materialization": {
              "r_strategy": "null-aware index_lookup",
              "r_loops": 4,
              "r_index_lookups": 3,
              "r_partial_matches": 1,
              "query_block": {
                "select_id": 2,
                "cost": "REPLACED",
                "r_loops": 1,
                "r_total_time_ms": "REPLACED",
                "nested_loop": [
                  {
                    "table": {
                      "table_name": "t2",
                      "access_type": "ALL",
                      "loops": 1,
                      "r_loops": 1,
                      "rows": 2,
                      "r_rows": 2,
                      "cost": "REPLACED",
                      "r_table_time_ms": "REPLACED",
                      "r_other_time_ms": "REPLACED",
                      "r_engine_stats": REPLACED,
                      "filtered": 100,
                      "r_total_filtered": 100,
                      "r_filtered": 100
                    }
                  }
                ]
              }
            }
          }
        ]
      }
    }
  }
}
analyze format=json select a from t1 group by a not in (select b from t2);
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "cost": "REPLACED",
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "filesort": {
      "sort_key": "!<in_optimizer>(t1.a,t1.a in (subquery#2))",
      "r_loops": 1,
      "r_total_time_ms": "REPLACED",
      "r_used_priority_queue": false,
      "r_output_rows": 3,
      "r_buffer_size": "REPLACED",
      "r_sort_mode": "sort_key,rowid",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "loops": 1,
              "r_loops": 1,
              "rows": 4,
              "r_rows": 4,
              "cost": "REPLACED",
              "r_table_time_ms": "REPLACED",
              "r_other_time_ms": "REPLACED",
              "r_engine_stats": REPLACED,
              "filtered": 100,
              "r_total_filtered": 100,
              "r_filtered": 100
            }
          }
        ],
        "subqueries": [
          {
            "materialization": {
              "r_strategy": "null-aware index_lookup",
              "r_loops": 4,
              "r_index_lookups": 3,
              "r_partial_matches": 1,
              "query_block": {
                "select_id": 2,
                "cost": "REPLACED",
                "r_loops": 1,
                "r_total_time_ms": "REPLACED",
                "nested_loop": [
                  {
                    "table": {
                      "table_name": "t2",
                      "access_type": "ALL",
                      "loops": 1,
                      "r_loops": 1,
                      "rows": 2,
                      "r_rows": 2,
                      "cost": "REPLACED",
                      "r_table_time_ms": "REPLACED",
                      "r_other_time_ms": "REPLACED",
                      "r_engine_stats": REPLACED,
                      "filtered": 100,
                      "r_total_filtered": 100,
                      "r_filtered": 100
                    }
                  }
                ]
              }
            }
          }
        ]
      }
    }
  }
}
# Subselect in ORDER BY
analyze format=json select a from t1 order by a in (select b from t2);
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "cost": "REPLACED",
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "nested_loop": [
      {
        "read_sorted_file": {
          "r_rows": 4,
          "filesort": {
            "sort_key": "<in_optimizer>(t1.a,t1.a in (subquery#2))",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "r_used_priority_queue": false,
            "r_output_rows": 4,
            "r_buffer_size": "REPLACED",
            "r_sort_mode": "sort_key,addon_fields",
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "loops": 1,
              "r_loops": 1,
              "rows": 4,
              "r_rows": 4,
              "cost": "REPLACED",
              "r_table_time_ms": "REPLACED",
              "r_other_time_ms": "REPLACED",
              "r_engine_stats": REPLACED,
              "filtered": 100,
              "r_total_filtered": 100,
              "r_filtered": 100
            }
          }
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "null-aware index_lookup",
          "r_loops": 4,
          "r_index_lookups": 3,
          "r_partial_matches": 1,
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "r_loops": 1,
                  "rows": 2,
                  "r_rows": 2,
                  "cost": "REPLACED",
                  "r_table_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_engine_stats": REPLACED,
                  "filtered": 100,
                  "r_total_filtered": 100,
                  "r_filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
# Subselect in HAVING
analyze format=json select a from t1 having a not in (select b from t2);
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "cost": "REPLACED",
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "having_condition": "!<in_optimizer>(t1.a,t1.a in (subquery#2))",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "loops": 1,
          "r_loops": 1,
          "rows": 4,
          "r_rows": 4,
          "cost": "REPLACED",
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": 100,
          "r_filtered": 100
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "null-aware index_lookup",
          "r_loops": 4,
          "r_index_lookups": 3,
          "r_partial_matches": 1,
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "r_loops": 1,
                  "rows": 2,
                  "r_rows": 2,
                  "cost": "REPLACED",
                  "r_table_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_engine_stats": REPLACED,
                  "filtered": 100,
                  "r_total_filtered": 100,
                  "r_filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
# Nested IN
analyze format=json select a from t1 where a in (select a from t1 where a in (select b from t2));
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "cost": "REPLACED",
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "loops": 1,
          "r_loops": 1,
          "rows": 4,
          "r_rows": 4,
          "cost": "REPLACED",
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": 25,
          "attached_condition": "<in_optimizer>(t1.a,t1.a in (subquery#2))",
          "r_filtered": 25
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "index_lookup",
          "r_loops": 3,
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t1",
                  "access_type": "ALL",
                  "loops": 1,
                  "r_loops": 1,
                  "rows": 4,
                  "r_rows": 4,
                  "cost": "REPLACED",
                  "r_table_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_engine_stats": REPLACED,
                  "filtered": 100,
                  "r_total_filtered": 25,
                  "attached_condition": "<in_optimizer>(t1.a,t1.a in (subquery#3))",
                  "r_filtered": 25
                }
              }
            ],
            "subqueries": [
              {
                "materialization": {
                  "r_strategy": "index_lookup",
                  "r_loops": 3,
                  "query_block": {
                    "select_id": 3,
                    "cost": "REPLACED",
                    "r_loops": 1,
                    "r_total_time_ms": "REPLACED",
                    "nested_loop": [
                      {
                        "table": {
                          "table_name": "t2",
                          "access_type": "ALL",
                          "loops": 1,
                          "r_loops": 1,
                          "rows": 2,
                          "r_rows": 2,
                          "cost": "REPLACED",
                          "r_table_time_ms": "REPLACED",
                          "r_other_time_ms": "REPLACED",
                          "r_engine_stats": REPLACED,
                          "filtered": 100,
                          "r_total_filtered": 100,
                          "r_filtered": 100
                        }
                      }
                    ]
                  }
                }
              }
            ]
          }
        }
      }
    ]
  }
}
create table t3 (c int);
insert into t3 (c) values (3), (null), (4);
# Subquery in ON-clause of outer join
analyze format=json select a from t1 left join t2 on a not in (select c from t3);
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "cost": "REPLACED",
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "const_condition": "1",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "loops": 1,
          "r_loops": 1,
          "rows": 4,
          "r_rows": 4,
          "cost": "REPLACED",
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": 100,
          "r_filtered": 100
        }
      },
      {
        "block-nl-join": {
          "table": {
            "table_name": "t2",
            "access_type": "ALL",
            "loops": 4,
            "r_loops": 1,
            "rows": 2,
            "r_rows": 2,
            "cost": "REPLACED",
            "r_table_time_ms": "REPLACED",
            "r_other_time_ms": "REPLACED",
            "r_engine_stats": REPLACED,
            "filtered": 100,
            "r_total_filtered": 100,
            "r_filtered": 100
          },
          "buffer_type": "flat",
          "buffer_size": "87",
          "join_type": "BNL",
          "attached_condition": "trigcond(trigcond(!<in_optimizer>(t1.a,t1.a in (subquery#2))))",
          "r_loops": 4,
          "r_filtered": 50,
          "r_unpack_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "r_effective_rows": 2
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "null-aware index_lookup",
          "r_loops": 4,
          "r_index_lookups": 3,
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t3",
                  "access_type": "ALL",
                  "loops": 1,
                  "r_loops": 1,
                  "rows": 3,
                  "r_rows": 3,
                  "cost": "REPLACED",
                  "r_table_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_engine_stats": REPLACED,
                  "filtered": 100,
                  "r_total_filtered": 100,
                  "r_filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
analyze format=json
select (b, b + 1, b + 2) not in
(select count(distinct a), a + 1, a + 2 from t1 group by a + 1, a + 2)
from t2;
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "cost": "REPLACED",
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "loops": 1,
          "r_loops": 1,
          "rows": 2,
          "r_rows": 2,
          "cost": "REPLACED",
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": 100,
          "r_filtered": 100
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "index_lookup;full scan for partial match",
          "r_loops": 2,
          "r_index_lookups": 2,
          "r_partial_matches": 2,
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "read_sorted_file": {
                  "r_rows": 4,
                  "filesort": {
                    "sort_key": "t1.a + 1, t1.a + 2",
                    "r_loops": 1,
                    "r_total_time_ms": "REPLACED",
                    "r_used_priority_queue": false,
                    "r_output_rows": 4,
                    "r_buffer_size": "REPLACED",
                    "r_sort_mode": "sort_key,addon_fields",
                    "table": {
                      "table_name": "t1",
                      "access_type": "ALL",
                      "loops": 1,
                      "r_loops": 1,
                      "rows": 4,
                      "r_rows": 4,
                      "cost": "REPLACED",
                      "r_table_time_ms": "REPLACED",
                      "r_other_time_ms": "REPLACED",
                      "r_engine_stats": REPLACED,
                      "filtered": 100,
                      "r_total_filtered": 100,
                      "r_filtered": 100
                    }
                  }
                }
              }
            ]
          }
        }
      }
    ]
  }
}
drop table t1, t2, t3;
#
# Tables with more than one column
#
create table t1 (a1 char(1), a2 char(1));
insert into t1 values (null, 'b');
create table t2 (b1 char(1), b2 char(2));
insert into t2 values ('a','b'), ('c', 'd'), (null, 'e'), ('f', 'g');
# Force rowid-merge partial matching
set @@optimizer_switch="partial_match_rowid_merge=on,partial_match_table_scan=off";
explain format=json select * from t1 where (a1, a2) not in (select b1, b2 from t2);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "const_condition": "!<in_optimizer>((NULL,'b'),(NULL,'b') in (subquery#2))",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "system",
          "rows": 1,
          "filtered": 100
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "query_block": {
            "select_id": 2,
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "rows": 4,
                  "cost": "COST_REPLACED",
                  "filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
analyze format=json select * from t1 where (a1, a2) not in (select b1, b2 from t2);
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "const_condition": "!<in_optimizer>((NULL,'b'),(NULL,'b') in (subquery#2))",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "system",
          "r_loops": 0,
          "rows": 1,
          "r_rows": null,
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": null,
          "r_filtered": null
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "index_lookup;array merge for partial match",
          "r_loops": 1,
          "r_partial_matches": 1,
          "r_partial_match_buffer_size": "REPLACED",
          "r_partial_match_array_sizes": [4, 3],
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "r_loops": 1,
                  "rows": 4,
                  "r_rows": 4,
                  "cost": "REPLACED",
                  "r_table_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_engine_stats": REPLACED,
                  "filtered": 100,
                  "r_total_filtered": 100,
                  "r_filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
# Force table scan partial matching
set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=on";
analyze format=json select * from t1 where (a1, a2) not in (select b1, b2 from t2);
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "const_condition": "!<in_optimizer>((NULL,'b'),(NULL,'b') in (subquery#2))",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "system",
          "r_loops": 0,
          "rows": 1,
          "r_rows": null,
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": null,
          "r_filtered": null
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "index_lookup;full scan for partial match",
          "r_loops": 1,
          "r_partial_matches": 1,
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "r_loops": 1,
                  "rows": 4,
                  "r_rows": 4,
                  "cost": "REPLACED",
                  "r_table_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_engine_stats": REPLACED,
                  "filtered": 100,
                  "r_total_filtered": 100,
                  "r_filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
# Subquery in SELECT list
explain format=json select t1.*, (a1, a2) in (select * from t2) as in_res from t1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "system",
          "rows": 1,
          "filtered": 100
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "query_block": {
            "select_id": 2,
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "rows": 4,
                  "cost": "COST_REPLACED",
                  "filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
analyze format=json select t1.*, (a1, a2) in (select * from t2) as in_res from t1;
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "system",
          "r_loops": 0,
          "rows": 1,
          "r_rows": null,
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": null,
          "r_filtered": null
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "index_lookup;full scan for partial match",
          "r_loops": 1,
          "r_partial_matches": 1,
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "r_loops": 1,
                  "rows": 4,
                  "r_rows": 4,
                  "cost": "REPLACED",
                  "r_table_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_engine_stats": REPLACED,
                  "filtered": 100,
                  "r_total_filtered": 100,
                  "r_filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
analyze format=json select t1.*, (a1, a2) not in (select * from t2) as in_res from t1;
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "system",
          "r_loops": 0,
          "rows": 1,
          "r_rows": null,
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": null,
          "r_filtered": null
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "index_lookup;full scan for partial match",
          "r_loops": 1,
          "r_partial_matches": 1,
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "r_loops": 1,
                  "rows": 4,
                  "r_rows": 4,
                  "cost": "REPLACED",
                  "r_table_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_engine_stats": REPLACED,
                  "filtered": 100,
                  "r_total_filtered": 100,
                  "r_filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
set @@optimizer_switch="partial_match_rowid_merge=on,partial_match_table_scan=off";
analyze format=json select t1.*, (a1, a2) in (select * from t2) as in_res from t1;
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "system",
          "r_loops": 0,
          "rows": 1,
          "r_rows": null,
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": null,
          "r_filtered": null
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "index_lookup;array merge for partial match",
          "r_loops": 1,
          "r_partial_matches": 1,
          "r_partial_match_buffer_size": "REPLACED",
          "r_partial_match_array_sizes": [4, 3],
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "r_loops": 1,
                  "rows": 4,
                  "r_rows": 4,
                  "cost": "REPLACED",
                  "r_table_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_engine_stats": REPLACED,
                  "filtered": 100,
                  "r_total_filtered": 100,
                  "r_filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
analyze format=json select t1.*, (a1, a2) not in (select * from t2) as in_res from t1;
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": "REPLACED",
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "system",
          "r_loops": 0,
          "rows": 1,
          "r_rows": null,
          "r_engine_stats": REPLACED,
          "filtered": 100,
          "r_total_filtered": null,
          "r_filtered": null
        }
      }
    ],
    "subqueries": [
      {
        "materialization": {
          "r_strategy": "index_lookup;array merge for partial match",
          "r_loops": 1,
          "r_partial_matches": 1,
          "r_partial_match_buffer_size": "REPLACED",
          "r_partial_match_array_sizes": [4, 3],
          "query_block": {
            "select_id": 2,
            "cost": "REPLACED",
            "r_loops": 1,
            "r_total_time_ms": "REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "r_loops": 1,
                  "rows": 4,
                  "r_rows": 4,
                  "cost": "REPLACED",
                  "r_table_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_engine_stats": REPLACED,
                  "filtered": 100,
                  "r_total_filtered": 100,
                  "r_filtered": 100
                }
              }
            ]
          }
        }
      }
    ]
  }
}
drop table t1,t2;
set @@optimizer_switch=@save_optimizer_switch;