Ziele Untersuchung
mit Columbo Integrität von
Datenbanken Interaktion und
Portierbarkeit Ergonomie der
Schnittstellen

Angebot Produkte Projekt Beratung

Mittel Analytik Modellierung Sprachen Algebra Logik Hardware Denken Kreativität

Zusammenhänge Gesellschaft Wirtschaft Branche Firma

Benutzer


products/Sources/formale Sprachen/C/MariaDB/mysql-test/main/ (MariaDB Server Version 8.1-8.4^©) Datei vom 1.9.2026 mit Größe 50 kB

Quelle intersect_all.result Sprache: Lisp

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

Messung V0.5 in Prozent

¤ Dauer der Verarbeitung: 0.32 Sekunden (vorverarbeitet am 2026-10-08) ¤

Wurzel

Suchen

PVS Prover

Isabelle Prover

NIST Cobol Testsuite

Cephes Mathematical Library

Vienna Development Method

Haftungshinweis

Die Informationen auf dieser Webseite wurden nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit, noch Qualität der bereit gestellten Informationen zugesichert.