Impressum intersect_all.result   Interaktion und
PortierbarkeitLisp

create table t1 (a int, b int) engine=MyISAM;
create table t2 (c int, d int) engine=MyISAM;
insert into t1 values (1,1),(2,2),(3,3),(2,2);
insert into t2 values (2,2),(2,2),(5,5);
select * from t1 intersect all select * from t2;
a b
2 2
2 2
(select a,b from t1) intersect all (select c,d from t2);
a b
2 2
2 2
select * from ((select a,b from t1) intersect all (select c,d from t2)) t;
a b
2 2
2 2
select * from ((select a from t1) intersect all (select c from t2)) t;
a
2
2
drop tables t1,t2;
create table t1 (a int, b int) engine=MyISAM;
create table t2 (c int, d int) engine=MyISAM;
create table t3 (e int, f int) engine=MyISAM;
insert into t1 values (1,1),(2,2),(3,3),(2,2);
insert into t2 values (2,2),(3,3),(4,4),(2,2);
insert into t3 values (1,1),(2,2),(5,5),(2,2);
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
a b
2 2
2 2
EXPLAIN (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 
2 INTERSECT t2 ALL NULL NULL NULL NULL 4 
3 INTERSECT t3 ALL NULL NULL NULL NULL 4 
NULL INTERSECT RESULT <intersect1,2,3> ALL NULL NULL NULL NULL NULL 
EXPLAIN extended (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 
2 INTERSECT t2 ALL NULL NULL NULL NULL 4 100.00 
3 INTERSECT t3 ALL NULL NULL NULL NULL 4 100.00 
NULL INTERSECT RESULT <intersect1,2,3> ALL NULL NULL NULL NULL NULL NULL 
Warnings:
Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect all (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)
EXPLAIN extended select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 
2 DERIVED t1 ALL NULL NULL NULL NULL 4 100.00 
3 INTERSECT t2 ALL NULL NULL NULL NULL 4 100.00 
4 INTERSECT t3 ALL NULL NULL NULL NULL 4 100.00 
NULL INTERSECT RESULT <intersect2,3,4> ALL NULL NULL NULL NULL NULL NULL 
Warnings:
Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect all (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `a`
EXPLAIN format=json (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
EXPLAIN
{
  "query_block": {
    "union_result": {
      "table_name": "<intersect1,2,3>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "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
                }
              }
            ]
          }
        },
        {
          "query_block": {
            "select_id": 2,
            "operation": "INTERSECT",
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "loops": 1,
                  "rows": 4,
                  "cost": "COST_REPLACED",
                  "filtered": 100
                }
              }
            ]
          }
        },
        {
          "query_block": {
            "select_id": 3,
            "operation": "INTERSECT",
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t3",
                  "access_type": "ALL",
                  "loops": 1,
                  "rows": 4,
                  "cost": "COST_REPLACED",
                  "filtered": 100
                }
              }
            ]
          }
        }
      ]
    }
  }
}
ANALYZE format=json (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "union_result": {
      "table_name": "<intersect1,2,3>",
      "access_type": "ALL",
      "r_loops": 1,
      "r_rows": 2,
      "query_specifications": [
        {
          "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": 100,
                  "r_filtered": 100
                }
              }
            ]
          }
        },
        {
          "query_block": {
            "select_id": 2,
            "operation": "INTERSECT",
            "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
                }
              }
            ]
          }
        },
        {
          "query_block": {
            "select_id": 3,
            "operation": "INTERSECT",
            "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": 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 * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a;
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": "<derived2>",
          "access_type": "ALL",
          "loops": 1,
          "r_loops": 1,
          "rows": 4,
          "r_rows": 2,
          "cost": "REPLACED",
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "filtered": 100,
          "r_total_filtered": 100,
          "r_filtered": 100,
          "materialized": {
            "query_block": {
              "union_result": {
                "table_name": "<intersect2,3,4>",
                "access_type": "ALL",
                "r_loops": 1,
                "r_rows": 2,
                "query_specifications": [
                  {
                    "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": 100,
                            "r_filtered": 100
                          }
                        }
                      ]
                    }
                  },
                  {
                    "query_block": {
                      "select_id": 3,
                      "operation": "INTERSECT",
                      "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
                          }
                        }
                      ]
                    }
                  },
                  {
                    "query_block": {
                      "select_id": 4,
                      "operation": "INTERSECT",
                      "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": 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
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          }
        }
      }
    ]
  }
}
select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a;
a b
2 2
2 2
prepare stmt from "(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);";
execute stmt;
a b
2 2
2 2
execute stmt;
a b
2 2
2 2
prepare stmt from "select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a";
execute stmt;
a b
2 2
2 2
execute stmt;
a b
2 2
2 2
insert into t1 values (2,2),(3,3);
insert into t2 values (2,2),(2,2),(2,2);
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
a b
2 2
2 2
(select a,b from t1) intersect (select c,d from t2) intersect all (select e,f from t3);
a b
2 2
insert into t3 values (2,2);
(select a,b from t1) intersect all (select c,d from t2) intersect (select e,f from t3);
a b
2 2
(select a,b from t1) intersect all (select c,e from t2,t3);
a b
2 2
2 2
2 2
EXPLAIN (select a,b from t1) intersect all (select c,e from t2,t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 
2 INTERSECT t3 ALL NULL NULL NULL NULL 5 
2 INTERSECT t2 ALL NULL NULL NULL NULL 7 Using join buffer (flat, BNL join)
NULL INTERSECT RESULT <intersect1,2> ALL NULL NULL NULL NULL NULL 
EXPLAIN extended (select a,b from t1) intersect all (select c,e from t2,t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 
2 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00 
2 INTERSECT t2 ALL NULL NULL NULL NULL 7 100.00 Using join buffer (flat, BNL join)
NULL INTERSECT RESULT <intersect1,2> ALL NULL NULL NULL NULL NULL NULL 
Warnings:
Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect all (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t3`.`e` AS `e` from `test`.`t2` join `test`.`t3`)
EXPLAIN extended select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 
2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 
3 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00 
3 INTERSECT t2 ALL NULL NULL NULL NULL 7 100.00 Using join buffer (flat, BNL join)
NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL NULL 
Warnings:
Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect all (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t3`.`e` AS `e` from `test`.`t2` join `test`.`t3`)) `a`
EXPLAIN format=json (select a,b from t1) intersect all (select c,e from t2,t3);
EXPLAIN
{
  "query_block": {
    "union_result": {
      "table_name": "<intersect1,2>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "query_block": {
            "select_id": 1,
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t1",
                  "access_type": "ALL",
                  "loops": 1,
                  "rows": 6,
                  "cost": "COST_REPLACED",
                  "filtered": 100
                }
              }
            ]
          }
        },
        {
          "query_block": {
            "select_id": 2,
            "operation": "INTERSECT",
            "cost": "COST_REPLACED",
            "nested_loop": [
              {
                "table": {
                  "table_name": "t3",
                  "access_type": "ALL",
                  "loops": 1,
                  "rows": 5,
                  "cost": "COST_REPLACED",
                  "filtered": 100
                }
              },
              {
                "block-nl-join": {
                  "table": {
                    "table_name": "t2",
                    "access_type": "ALL",
                    "loops": 5,
                    "rows": 7,
                    "cost": "COST_REPLACED",
                    "filtered": 100
                  },
                  "buffer_type": "flat",
                  "buffer_size": "65",
                  "join_type": "BNL"
                }
              }
            ]
          }
        }
      ]
    }
  }
}
ANALYZE format=json (select a,b from t1) intersect all (select c,e from t2,t3);
ANALYZE
{
  "query_optimization": {
    "r_total_time_ms": "REPLACED"
  },
  "query_block": {
    "union_result": {
      "table_name": "<intersect1,2>",
      "access_type": "ALL",
      "r_loops": 1,
      "r_rows": 3,
      "query_specifications": [
        {
          "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": 6,
                  "r_rows": 6,
                  "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
                }
              }
            ]
          }
        },
        {
          "query_block": {
            "select_id": 2,
            "operation": "INTERSECT",
            "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": 5,
                  "r_rows": 5,
                  "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": 5,
                    "r_loops": 1,
                    "rows": 7,
                    "r_rows": 7,
                    "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": "65",
                  "join_type": "BNL",
                  "r_loops": 5,
                  "r_filtered": 100,
                  "r_unpack_time_ms": "REPLACED",
                  "r_other_time_ms": "REPLACED",
                  "r_effective_rows": 7
                }
              }
            ]
          }
        }
      ]
    }
  }
}
ANALYZE format=json select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a;
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": "<derived2>",
          "access_type": "ALL",
          "loops": 1,
          "r_loops": 1,
          "rows": 6,
          "r_rows": 3,
          "cost": "REPLACED",
          "r_table_time_ms": "REPLACED",
          "r_other_time_ms": "REPLACED",
          "filtered": 100,
          "r_total_filtered": 100,
          "r_filtered": 100,
          "materialized": {
            "query_block": {
              "union_result": {
                "table_name": "<intersect2,3>",
                "access_type": "ALL",
                "r_loops": 1,
                "r_rows": 3,
                "query_specifications": [
                  {
                    "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": 6,
                            "r_rows": 6,
                            "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
                          }
                        }
                      ]
                    }
                  },
                  {
                    "query_block": {
                      "select_id": 3,
                      "operation": "INTERSECT",
                      "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": 5,
                            "r_rows": 5,
                            "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": 5,
                              "r_loops": 1,
                              "rows": 7,
                              "r_rows": 7,
                              "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": "65",
                            "join_type": "BNL",
                            "r_loops": 5,
                            "r_filtered": 100,
                            "r_unpack_time_ms": "REPLACED",
                            "r_other_time_ms": "REPLACED",
                            "r_effective_rows": 7
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          }
        }
      }
    ]
  }
}
select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a;
a b
2 2
2 2
2 2
prepare stmt from "(select a,b from t1) intersect all (select c,e from t2,t3);";
execute stmt;
a b
2 2
2 2
2 2
execute stmt;
a b
2 2
2 2
2 2
prepare stmt from "select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a";
execute stmt;
a b
2 2
2 2
2 2
execute stmt;
a b
2 2
2 2
2 2
drop tables t1,t2,t3;
select 1 as a from dual intersect all select 1 from dual;
a
1
(select 1 from dual) intersect all (select 1 from dual);
1
1
(select 1 from dual into @v) intersect all (select 1 from dual);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'into @v) intersect all (select 1 from dual)' at line 1
select 1 from dual ORDER BY 1 intersect all select 1 from dual;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'intersect all select 1 from dual' at line 1
select 1 as a from dual union all select 1 from dual;
a
1
1
create table t1 (a int, b blob, a1 int, b1 blob);
create table t2 (c int, d blob, c1 int, d1 blob);
insert into t1 values (1,"ddd", 1, "sdfrrwwww"),(2, "fgh", 2, "dffggtt"),(2, "fgh", 2, "dffggtt");
insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg"),(2, "fgh", 2, "dffggtt");
(select a,b,b1 from t1) intersect all (select c,d,d1 from t2);
a b b1
2 fgh dffggtt
2 fgh dffggtt
drop tables t1,t2;
create table t1 (a int, b blob) engine=MyISAM;
create table t2 (c int, d blob) engine=MyISAM;
create table t3 (e int, f blob) engine=MyISAM;
insert into t1 values (1,1),(2,2),(3,3),(2,2),(3,3);
insert into t2 values (2,2),(3,3),(4,4),(2,2),(2,2),(2,2);
insert into t3 values (1,1),(2,2),(5,5),(2,2),(5,5);
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
a b
2 2
2 2
select * from ((select a,b from t1) intersect all (select c,d from t2) intersect (select e,f from t3)) a;
a b
2 2
prepare stmt from "(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);";
execute stmt;
a b
2 2
2 2
execute stmt;
a b
2 2
2 2
prepare stmt from "select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a";
execute stmt;
a b
2 2
2 2
execute stmt;
a b
2 2
2 2
create table t4  (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
show create table t4;
Table Create Table
t4 CREATE TABLE `t4` (
  `a` int(11) DEFAULT NULL,
  `b` blob DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop tables t4;
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4);
a b
4 4
2 2
2 2
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4) except all (select 2,2);
a b
4 4
2 2
drop tables t1,t2,t3;
create table t1 (a int, b int);
create table t2 (c int, d int);
create table t3 (e int, f int);
insert into t1 values (1,1),(2,2),(3,3),(2,2),(3,3);
insert into t2 values (2,2),(3,3),(4,4),(2,2),(2,2),(2,2);
insert into t3 values (1,1),(2,2),(5,5),(2,2),(5,5);
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4);
a b
4 4
2 2
2 2
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4) except all (select 2,2);
a b
4 4
2 2
drop tables t1,t2,t3;
#
# INTERSECT precedence
#
create table t1 (a int, b blob) engine=MyISAM;
create table t2 (c int, d blob) engine=MyISAM;
create table t3 (e int, f blob) engine=MyISAM;
insert into t1 values (5,5),(6,6);
insert into t2 values (2,2),(3,3);
insert into t3 values (1,1),(3,3);
(select a,b from t1) union all (select c,d from t2) intersect (select e,f from t3) union all (select 4,4);
a b
5 5
6 6
3 3
4 4
(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4);
a b
5 5
6 6
3 3
4 4
explain extended (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 
5 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 
3 INTERSECT t3 ALL NULL NULL NULL NULL 2 100.00 
NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL NULL 
4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union all /* select#5 */ select `__5`.`c` AS `c`,`__5`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__5` union all (/* select#4 */ select 4 AS `4`,4 AS `4`)
insert into t2 values (3,3);
insert into t3 values (3,3);
(select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (select 4,4);
e f
3 3
3 3
5 5
6 6
4 4
explain extended (select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (select 4,4);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 
2 INTERSECT t2 ALL NULL NULL NULL NULL 3 100.00 
3 UNION t1 ALL NULL NULL NULL NULL 2 100.00 
4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNIT RESULT <unit1,2,3,4> ALL NULL NULL NULL NULL NULL NULL 
Warnings:
Note 1003 (/* select#1 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`) intersect all (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) union all (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union all (/* select#4 */ select 4 AS `4`,4 AS `4`)
(/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (/* select#5 */ select 4 AS `4`,4 AS `4`);
a b
5 5
6 6
3 3
4 4
prepare stmt from "(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4)";
execute stmt;
a b
5 5
6 6
3 3
3 3
4 4
execute stmt;
a b
5 5
6 6
3 3
3 3
4 4
create view v1 as (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4);
select b,a,b+1 from v1;
b a b+1
5 5 6
6 6 7
3 3 4
3 3 4
4 4 5
select b,a,b+1 from v1 where a > 3;
b a b+1
5 5 6
6 6 7
4 4 5
create procedure p1()
select * from v1;
call p1();
a b
5 5
6 6
3 3
3 3
4 4
call p1();
a b
5 5
6 6
3 3
3 3
4 4
drop procedure p1;
create procedure p1()
(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4);
call p1();
a b
5 5
6 6
3 3
3 3
4 4
call p1();
a b
5 5
6 6
3 3
3 3
4 4
drop procedure p1;
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1`) union all select `__6`.`c` AS `c`,`__6`.`d` AS `d` from ((select `t2`.`c` AS `c`,`t2`.`d` AS `d` from `t2`) intersect all (select `t3`.`e` AS `e`,`t3`.`f` AS `f` from `t3`)) `__6` union all (select 4 AS `4`,4 AS `4`) latin1 latin1_swedish_ci
drop view v1;
drop tables t1,t2,t3;
CREATE TABLE t (i INT);
INSERT INTO t VALUES (1),(2);
SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT ALL SELECT 3 );
i
select i from t where
exists ((select 6 as r from dual having t.i <> 6)
intersect all
(select 3 from dual having t.i <> 3));
i
drop table t;
CREATE TABLE t1 (a varchar(32)) ENGINE=MyISAM;
INSERT INTO t1 VALUES
('Jakarta'),('Lisbon'),('Honolulu'),('Lusaka'),('Barcelona'),('Taipei'),
('Brussels'),('Orlando'),('Osaka'),('Quito'),('Lima'),('Tunis'),
('Unalaska'),('Rotterdam'),('Zagreb'),('Ufa'),('Ryazan'),('Xiamen'),
('London'),('Izmir'),('Samara'),('Bern'),('Zhengzhou'),('Vladivostok'),
('Yangon'),('Victoria'),('Warsaw'),('Luanda'),('Leon'),('Bangkok'),
('Wellington'),('Zibo'),('Qiqihar'),('Delhi'),('Hamburg'),('Ottawa'),
('Vaduz');
CREATE TABLE t2 (b varchar(32)) ENGINE=MyISAM;
INSERT INTO t2 VALUES
('Gaza'),('Jeddah'),('Beirut'),('Incheon'),('Tbilisi'),('Izmir'),
('Quito'),('Riga'),('Freetown'),('Zagreb'),('Caracas'),('Orlando'),
('Kingston'),('Turin'),('Xinyang'),('Osaka'),('Albany'),('Geneva'),
('Omsk'),('Kazan'),('Quezon'),('Indore'),('Odessa'),('Xiamen'),
('Winnipeg'),('Yakutsk'),('Nairobi'),('Ufa'),('Helsinki'),('Vilnius'),
('Aden'),('Liverpool'),('Honolulu'),('Frankfurt'),('Glasgow'),
('Vienna'),('Jackson'),('Jakarta'),('Sydney'),('Oslo'),('Novgorod'),
('Norilsk'),('Izhevsk'),('Istanbul'),('Nice');
CREATE TABLE t3 (c varchar(32)) ENGINE=MyISAM;
INSERT INTO t3 VALUES
('Nicosia'),('Istanbul'),('Richmond'),('Stockholm'),('Dublin'),
('Wichita'),('Warsaw'),('Glasgow'),('Winnipeg'),('Irkutsk'),('Quito'),
('Xiamen'),('Berlin'),('Rome'),('Denver'),('Dallas'),('Kabul'),
('Prague'),('Izhevsk'),('Tirana'),('Sofia'),('Detroit'),('Sorbonne');
select count(*) from (
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
INTERSECT
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
) a;
count(*)
14848
select count(*) from (
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
INTERSECT ALL
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
) a;
count(*)
14848
insert into t1 values ('Xiamen');
insert into t2 values ('Xiamen'),('Xiamen');
insert into t3 values ('Xiamen');
select count(*) from (
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
INTERSECT ALL
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
) a;
count(*)
16430
drop table t1,t2,t3;
CREATE TABLE t1 (a varchar(32) not null) ENGINE=MyISAM;
INSERT INTO t1 VALUES
('Jakarta'),('Lisbon'),('Honolulu'),('Lusaka'),('Barcelona'),('Taipei'),
('Brussels'),('Orlando'),('Osaka'),('Quito'),('Lima'),('Tunis'),
('Unalaska'),('Rotterdam'),('Zagreb'),('Ufa'),('Ryazan'),('Xiamen'),
('London'),('Izmir'),('Samara'),('Bern'),('Zhengzhou'),('Vladivostok'),
('Yangon'),('Victoria'),('Warsaw'),('Luanda'),('Leon'),('Bangkok'),
('Wellington'),('Zibo'),('Qiqihar'),('Delhi'),('Hamburg'),('Ottawa'),
('Vaduz'),('Detroit'),('Detroit');
CREATE TABLE t2 (b varchar(32) not null) ENGINE=MyISAM;
INSERT INTO t2 VALUES
('Gaza'),('Jeddah'),('Beirut'),('Incheon'),('Tbilisi'),('Izmir'),
('Quito'),('Riga'),('Freetown'),('Zagreb'),('Caracas'),('Orlando'),
('Kingston'),('Turin'),('Xinyang'),('Osaka'),('Albany'),('Geneva'),
('Omsk'),('Kazan'),('Quezon'),('Indore'),('Odessa'),('Xiamen'),
('Winnipeg'),('Yakutsk'),('Nairobi'),('Ufa'),('Helsinki'),('Vilnius'),
('Aden'),('Liverpool'),('Honolulu'),('Frankfurt'),('Glasgow'),
('Vienna'),('Jackson'),('Jakarta'),('Sydney'),('Oslo'),('Novgorod'),
('Norilsk'),('Izhevsk'),('Istanbul'),('Nice'),('Detroit'),('Detroit');
CREATE TABLE t3 (c varchar(32) not null) ENGINE=MyISAM;
INSERT INTO t3 VALUES
('Nicosia'),('Istanbul'),('Richmond'),('Stockholm'),('Dublin'),
('Wichita'),('Warsaw'),('Glasgow'),('Winnipeg'),('Irkutsk'),('Quito'),
('Xiamen'),('Berlin'),('Rome'),('Denver'),('Dallas'),('Kabul'),
('Prague'),('Izhevsk'),('Tirana'),('Sofia'),('Detroit'),('Sorbonne'),
('Detroit');
select count(*) from (
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
INTERSECT
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
) a;
count(*)
15547
drop table t1,t2,t3;
create table t12(c1 int);
insert into t12 values(1);
insert into t12 values(2);
create table t13(c1 int);
insert into t13 values(1);
insert into t13 values(3);
create table t234(c1 int);
insert into t234 values(2);
insert into t234 values(3);
insert into t234 values(4);
select * from t13 union select * from t234 intersect all select * from t12;
c1
1
3
2
drop table t12,t13,t234;
create table t1 (a int);
insert into t1 values (3), (1), (7), (3), (2), (7), (4);
create table t2 (a int);
insert into t2 values (4), (5), (9), (1), (8), (9), (2), (2);
create table t3 (a int);
insert into t3 values (8), (1), (8), (2), (3), (7), (2);
select * from t1 where a > 4
union all
select * from t2 where a < 5
intersect all
select * from t3 where a < 5;
a
7
7
2
1
2
explain extended
select * from t1 where a > 4
union all
select * from t2 where a < 5
intersect all
select * from t3 where a < 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where
4 UNION <derived2> ALL NULL NULL NULL NULL 7 100.00 
2 DERIVED t2 ALL NULL NULL NULL NULL 8 100.00 Using where
3 INTERSECT t3 ALL NULL NULL NULL NULL 7 100.00 Using where
NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL NULL 
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 union all /* select#4 */ select `__4`.`a` AS `a` from (/* select#2 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where `test`.`t2`.`a` < 5 intersect all /* select#3 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5) `__4`
drop table t1,t2,t3;
#
# MDEV-25158 Segfault on INTERSECT ALL with UNION in Oracle mode
#
create table t3 (x int);
create table u3 (x int);
create table i3 (x int);
explain SELECT * from t3 union select * from u3 intersect all select * from i3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 system NULL NULL NULL NULL 0 Const row not found
4 UNION <derived2> ALL NULL NULL NULL NULL 2 
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3 INTERSECT NULL NULL NULL NULL NULL NULL NULL no matching row in const table
NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL 
NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL 
set sql_mode= 'oracle';
explain SELECT * from t3 union select * from u3 intersect all select * from i3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3 UNION NULL NULL NULL NULL NULL NULL NULL no matching row in const table
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 
4 INTERSECT i3 system NULL NULL NULL NULL 0 Const row not found
NULL INTERSECT RESULT <intersect1,4> ALL NULL NULL NULL NULL NULL 
select x from t3 union select x from u3 intersect select x from i3;
x
SELECT x from t3 union select x from u3 intersect all select x from i3;
x
insert into t3 values (0);
insert into i3 values (0);
Select x from t3 union select x from u3 intersect select x from i3;
x
0
SELECT x FROM t3 UNION SELECT x FROM u3 INTERSECT ALL SELECT x FROM i3;
x
0
create view v as select * from t3 union select * from u3 intersect select * from i3;
select * from v;
x
0
drop tables t3, u3, i3;
drop view v;
# First line of these results is column names, not the result
# (pay attention to "affected rows")
values (1, 2) union all values (1, 2);
1 2
1 2
1 2
affected rows: 2
values (1, 2) union all values (1, 2) union values (4, 3) union all values (4, 3);
1 2
1 2
4 3
4 3
affected rows: 3
values (1, 2) union all values (1, 2) union values (4, 3) union all values (4, 3) union all values (1, 2);
1 2
1 2
4 3
4 3
1 2
affected rows: 4
values (1, 2) union all values (1, 2) union values (4, 3) union all values (4, 3) union all values (1, 2) union values (1, 2);
1 2
1 2
4 3
affected rows: 2
set sql_mode= default;
create table t1 (a int, b int);
create table t2 like t1;
insert t1 values (1, 2), (1, 2), (1, 2), (2, 3), (2, 3), (3, 4), (3, 4);
insert t2 values (1, 2), (1, 2), (2, 3), (2, 3), (2, 3), (2, 3), (4, 5);
select * from t1 intersect all select * from t2;
a b
1 2
2 3
1 2
2 3
select * from t1 intersect all select * from t2 union values (1, 2);
a b
1 2
2 3
select * from t1 intersect all (select * from t2 union values (1, 2));
a b
1 2
2 3
(select * from t1 intersect all select * from t2) union values (1, 2);
a b
1 2
2 3
create view v1 as select * from t1 intersect all select * from t2 union values (1, 2);
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` intersect all select `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2` union values (1,2) latin1 latin1_swedish_ci
select * from v1;
a b
1 2
2 3
create view v2 as select * from t1 union values (1, 2) intersect all select * from t2;
show create view v2;
View Create View character_set_client collation_connection
v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` union select `__5`.`1` AS `1`,`__5`.`2` AS `2` from (values (1,2) intersect all select `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2`) `__5` latin1 latin1_swedish_ci
select * from v2;
a b
1 2
2 3
3 4
set sql_mode= 'oracle';
select * from t1 intersect select * from t2;
a b
1 2
2 3
select * from t1 intersect all select * from t2;
a b
1 2
2 3
1 2
2 3
# Default: first INTERSECT ALL, then UNION
# Oracle: first UNION, then INTERSECT ALL
select * from t1 union values (1, 2) intersect all select * from t2;
a b
1 2
2 3
select * from t1 union (values (1, 2) intersect all select * from t2);
a b
1 2
2 3
3 4
(select * from t1 union values (1, 2)) intersect all select * from t2;
a b
1 2
2 3
select * from t1 intersect all select * from t2 union values (1, 2);
a b
1 2
2 3
select * from t1 intersect all (select * from t2 union values (1, 2));
a b
1 2
2 3
(select * from t1 intersect all select * from t2) union values (1, 2);
a b
1 2
2 3
explain select * from t1 intersect all select * from t2 union values (1, 2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 
2 DERIVED t1 ALL NULL NULL NULL NULL 7 
3 INTERSECT t2 ALL NULL NULL NULL NULL 7 
NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL 
4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL 
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE VIEW "v1" AS select "__5"."a" AS "a","__5"."b" AS "b" from (select "t1"."a" AS "a","t1"."b" AS "b" from "t1" intersect all select "t2"."a" AS "a","t2"."b" AS "b" from "t2") "__5" union values (1,2) latin1 latin1_swedish_ci
select * from v1;
a b
1 2
2 3
show create view v2;
View Create View character_set_client collation_connection
v2 CREATE VIEW "v2" AS select "t1"."a" AS "a","t1"."b" AS "b" from "t1" union select "__5"."1" AS "1","__5"."2" AS "2" from (values (1,2) intersect all select "t2"."a" AS "a","t2"."b" AS "b" from "t2") "__5" latin1 latin1_swedish_ci
select * from v2;
a b
1 2
2 3
3 4
create view v3 as select * from t1 union values (1, 2) intersect all select * from t2;
show create view v3;
View Create View character_set_client collation_connection
v3 CREATE VIEW "v3" AS select "__5"."a" AS "a","__5"."b" AS "b" from (select "t1"."a" AS "a","t1"."b" AS "b" from "t1" union values (1,2)) "__5" intersect all select "t2"."a" AS "a","t2"."b" AS "b" from "t2" latin1 latin1_swedish_ci
select * from v3;
a b
1 2
2 3
drop tables t1, t2;
drop view v1, v2, v3;
create table t3 (x int);
create table u3 (x int);
create table i3 (x int);
create view v4 as SELECT * from t3 union select * from u3 intersect all select * from i3;
select * from v4;
x
drop tables t3, u3, i3;
drop view v4;
set sql_mode= default;
#
# MDEV-37325 Incorrect results for INTERSECT ALL in ORACLE mode
#
create table t1 (a int, b int);
create table t2 like t1;
insert t1 values (1, 2), (1, 2), (1, 2), (1, 2);
insert t2 values (1, 2), (1, 2);
select * from t1 except all select * from t2 intersect all values (1, 2);
a b
1 2
1 2
1 2
explain select * from t1 except all select * from t2 intersect all values (1, 2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 
4 EXCEPT <derived2> ALL NULL NULL NULL NULL 2 
2 DERIVED t2 ALL NULL NULL NULL NULL 2 
3 INTERSECT NULL NULL NULL NULL NULL NULL NULL No tables used
NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL 
NULL EXCEPT RESULT <except1,4> ALL NULL NULL NULL NULL NULL 
Explain select * from (select * from (select * from t1 except all select * from t2) __3 intersect all values (1,2)) __5;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 
2 DERIVED <derived3> ALL NULL NULL NULL NULL 4 
3 DERIVED t1 ALL NULL NULL NULL NULL 4 
4 EXCEPT t2 ALL NULL NULL NULL NULL 2 
NULL EXCEPT RESULT <except3,4> ALL NULL NULL NULL NULL NULL 
5 INTERSECT NULL NULL NULL NULL NULL NULL NULL No tables used
NULL INTERSECT RESULT <intersect2,5> ALL NULL NULL NULL NULL NULL 
create view v1 as select * from t1 except all select * from t2 intersect all values (1, 2);
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` except all select `__5`.`a` AS `a`,`__5`.`b` AS `b` from (select `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2` intersect all values (1,2)) `__5` latin1 latin1_swedish_ci
select * from v1;
a b
1 2
1 2
1 2
drop view v1;
create view v1 as ((select * from t1 except all select * from t2) intersect all values (1, 2));
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__5`.`a` AS `a`,`__5`.`b` AS `b` from (select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` except all select `t2`.`a` AS `a`,`t2`.`b` AS `b` from `t2`) `__5` intersect all values (1,2) latin1 latin1_swedish_ci
select * from v1;
a b
1 2
drop view v1;
explain select * from ((select * from t1 except all select * from t2) intersect all values (1, 2)) v;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived5> ALL NULL NULL NULL NULL 2 
5 DERIVED <derived2> ALL NULL NULL NULL NULL 4 
2 DERIVED t1 ALL NULL NULL NULL NULL 4 
3 EXCEPT t2 ALL NULL NULL NULL NULL 2 
NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL 
4 INTERSECT NULL NULL NULL NULL NULL NULL NULL No tables used
NULL INTERSECT RESULT <intersect5,4> ALL NULL NULL NULL NULL NULL 
# Oracle: first UNION, then INTERSECT ALL
set sql_mode= 'oracle';
create view v2 as select * from t1 except all select * from t2 intersect all values (1, 2);
show create view v2;
View Create View character_set_client collation_connection
v2 CREATE VIEW "v2" AS select "__5"."a" AS "a","__5"."b" AS "b" from (select "t1"."a" AS "a","t1"."b" AS "b" from "t1" except all select "t2"."a" AS "a","t2"."b" AS "b" from "t2") "__5" intersect all values (1,2) latin1 latin1_swedish_ci
select * from v2;
a b
1 2
drop view v2;
EXPLAIN select * from t1 except all select * from t2 intersect all values (1, 2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 
2 DERIVED t1 ALL NULL NULL NULL NULL 4 
3 EXCEPT t2 ALL NULL NULL NULL NULL 2 
NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL 
4 INTERSECT NULL NULL NULL NULL NULL NULL NULL No tables used
NULL INTERSECT RESULT <intersect1,4> ALL NULL NULL NULL NULL NULL 
select * from t2 union all select * from t2;
a b
1 2
1 2
1 2
1 2
select * from t1 except all select * from t2 intersect all values (1, 2) union values (2, 3);
a b
1 2
2 3
select * from t2 union all select * from t2 except all select * from t2 intersect all values (1, 2) union values (2, 3);
a b
1 2
2 3
select * from t1 except all select * from t2 union values (2, 3);
a b
1 2
2 3
# Default: first INTERSECT ALL, then UNION
select * from t1 union values (1, 2) intersect all select * from t2;
a b
1 2
select * from t1 union (values (1, 2) intersect all select * from t2);
a b
1 2
set sql_mode= default;
select * from t1 union values (1, 2) intersect all select * from t2;
a b
1 2
select * from t1 union (values (1, 2) intersect all select * from t2);
a b
1 2
set sql_mode= 'oracle';
create or replace table t2 like t1;
insert t2 values (1, 2), (2, 3);
create table t3 select * from t1 except all select * from t2;
select * from t3;
a b
1 2
1 2
1 2
select * from t3 intersect all select * from t1;
a b
1 2
1 2
1 2
show create table t2;
Table Create Table
t2 CREATE TABLE "t2" (
  "a" int(11) DEFAULT NULL,
  "b" int(11) DEFAULT NULL
)
select * from t2 order by a desc;
a b
2 3
1 2
select * from t1 except all select * from t2;
a b
1 2
1 2
1 2
select * from t1 except all select * from t2 union all values (0, 1);
a b
1 2
1 2
1 2
0 1
select * from t1 except all select * from t2 union all values (0, 1) order by a limit 2;
a b
0 1
1 2
select * from t1 except all select * from t2 intersect all select * from t1;
a b
1 2
1 2
1 2
select * from t1 except all select * from t2 intersect all select * from t1 union all select * from t2;
a b
1 2
1 2
1 2
1 2
2 3
select * from t1 except all select * from t2 intersect all select * from t1 union all select * from t2 order by a desc limit 3;
a b
2 3
1 2
1 2
drop tables t1, t2, t3;
set sql_mode= default;
#
# MDEV-38722 server crash hp_rec_key_cmp
#
# original reproducer (used to crash), returns a single row
SELECT 1 INTERSECT SELECT 1 UNION ALL SELECT 1 EXCEPT ALL SELECT 1;
1
1
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1),(1),(2),(3);
CREATE TABLE t2 (i INT);
INSERT INTO t2 VALUES (1),(2),(2);
CREATE TABLE t3 (i INT);
INSERT INTO t3 VALUES (1),(3);
SELECT i FROM t1 INTERSECT SELECT i FROM t3
UNION ALL SELECT i FROM t2 EXCEPT ALL SELECT i FROM t3 ORDER BY i;
i
1
2
2
(((SELECT i FROM t1) INTERSECT (SELECT i FROM t3)) UNION ALL (SELECT i FROM t2))
EXCEPT ALL (SELECT i FROM t3) ORDER BY i;
i
1
2
2
# trailing UNION ALL after the EXCEPT ALL
SELECT i FROM t1 INTERSECT SELECT i FROM t2 UNION ALL SELECT i FROM t2
EXCEPT ALL SELECT i FROM t3 UNION ALL SELECT i FROM t1 ORDER BY i;
i
1
1
1
2
2
2
2
3
((((SELECT i FROM t1) INTERSECT (SELECT i FROM t2)) UNION ALL (SELECT i FROM t2))
EXCEPT ALL (SELECT i FROM t3)) UNION ALL (SELECT i FROM t1) ORDER BY i;
i
1
1
1
2
2
2
2
3
# a leading INTERSECT ALL keeps the operation extended while the last
# INTERSECT DISTINCT node is the index-release point
SELECT i FROM t1 INTERSECT ALL SELECT i FROM t2 INTERSECT SELECT i FROM t3
UNION ALL SELECT i FROM t2 ORDER BY i;
i
1
1
2
2
(((SELECT i FROM t1) INTERSECT ALL (SELECT i FROM t2)) INTERSECT (SELECT i FROM t3))
UNION ALL (SELECT i FROM t2) ORDER BY i;
i
1
1
2
2
DROP TABLE t1,t2,t3;
# End of 10.6 tests
#
# MDEV-38722 fix merge problem with view/derived
#
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1),(1),(2),(3);
CREATE TABLE t2 (i INT);
INSERT INTO t2 VALUES (1),(2),(2);
CREATE TABLE t3 (i INT);
INSERT INTO t3 VALUES (1),(3);
SELECT * FROM (SELECT i FROM t1 INTERSECT SELECT i FROM t3
UNION ALL SELECT i FROM t2 EXCEPT ALL SELECT i FROM t3) d ORDER BY i;
i
1
2
2
CREATE VIEW v1 AS SELECT i FROM t1 INTERSECT SELECT i FROM t3
UNION ALL SELECT i FROM t2 EXCEPT ALL SELECT i FROM t3;
SELECT * FROM v1 ORDER BY i;
i
1
2
2
DROP VIEW v1;
DROP TABLE t1,t2,t3;
# End of 11.4 tests