1set @@join_buffer_size=256*1024;
2create table t1 (a int, b int) engine=MyISAM;
3create table t2 (c int, d int) engine=MyISAM;
4insert into t1 values (1,1),(2,2);
5insert into t2 values (2,2),(3,3);
6(select a,b from t1) except (select c,d from t2);
7a	b
81	1
9EXPLAIN (select a,b from t1) except (select c,d from t2);
10id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
111	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2
122	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2
13NULL	EXCEPT RESULT	<except1,2>	ALL	NULL	NULL	NULL	NULL	NULL
14EXPLAIN extended (select a,b from t1) except (select c,d from t2);
15id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
161	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00
172	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2	100.00
18NULL	EXCEPT RESULT	<except1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL
19Warnings:
20Note	1003	(/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) except (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`)
21EXPLAIN extended select * from ((select a,b from t1) except (select c,d from t2)) a;
22id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
231	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00
242	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00
253	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2	100.00
26NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL
27Warnings:
28Note	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`) except (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`)) `a`
29EXPLAIN format=json (select a,b from t1) except (select c,d from t2);
30EXPLAIN
31{
32  "query_block": {
33    "union_result": {
34      "table_name": "<except1,2>",
35      "access_type": "ALL",
36      "query_specifications": [
37        {
38          "query_block": {
39            "select_id": 1,
40            "table": {
41              "table_name": "t1",
42              "access_type": "ALL",
43              "rows": 2,
44              "filtered": 100
45            }
46          }
47        },
48        {
49          "query_block": {
50            "select_id": 2,
51            "operation": "EXCEPT",
52            "table": {
53              "table_name": "t2",
54              "access_type": "ALL",
55              "rows": 2,
56              "filtered": 100
57            }
58          }
59        }
60      ]
61    }
62  }
63}
64ANALYZE format=json (select a,b from t1) except (select c,d from t2);
65ANALYZE
66{
67  "query_block": {
68    "union_result": {
69      "table_name": "<except1,2>",
70      "access_type": "ALL",
71      "r_loops": 1,
72      "r_rows": 1,
73      "query_specifications": [
74        {
75          "query_block": {
76            "select_id": 1,
77            "r_loops": 1,
78            "r_total_time_ms": "REPLACED",
79            "table": {
80              "table_name": "t1",
81              "access_type": "ALL",
82              "r_loops": 1,
83              "rows": 2,
84              "r_rows": 2,
85              "r_table_time_ms": "REPLACED",
86              "r_other_time_ms": "REPLACED",
87              "filtered": 100,
88              "r_filtered": 100
89            }
90          }
91        },
92        {
93          "query_block": {
94            "select_id": 2,
95            "operation": "EXCEPT",
96            "r_loops": 1,
97            "r_total_time_ms": "REPLACED",
98            "table": {
99              "table_name": "t2",
100              "access_type": "ALL",
101              "r_loops": 1,
102              "rows": 2,
103              "r_rows": 2,
104              "r_table_time_ms": "REPLACED",
105              "r_other_time_ms": "REPLACED",
106              "filtered": 100,
107              "r_filtered": 100
108            }
109          }
110        }
111      ]
112    }
113  }
114}
115ANALYZE format=json select * from ((select a,b from t1) except (select c,d from t2)) a;
116ANALYZE
117{
118  "query_block": {
119    "select_id": 1,
120    "r_loops": 1,
121    "r_total_time_ms": "REPLACED",
122    "table": {
123      "table_name": "<derived2>",
124      "access_type": "ALL",
125      "r_loops": 1,
126      "rows": 2,
127      "r_rows": 1,
128      "r_table_time_ms": "REPLACED",
129      "r_other_time_ms": "REPLACED",
130      "filtered": 100,
131      "r_filtered": 100,
132      "materialized": {
133        "query_block": {
134          "union_result": {
135            "table_name": "<except2,3>",
136            "access_type": "ALL",
137            "r_loops": 1,
138            "r_rows": 1,
139            "query_specifications": [
140              {
141                "query_block": {
142                  "select_id": 2,
143                  "r_loops": 1,
144                  "r_total_time_ms": "REPLACED",
145                  "table": {
146                    "table_name": "t1",
147                    "access_type": "ALL",
148                    "r_loops": 1,
149                    "rows": 2,
150                    "r_rows": 2,
151                    "r_table_time_ms": "REPLACED",
152                    "r_other_time_ms": "REPLACED",
153                    "filtered": 100,
154                    "r_filtered": 100
155                  }
156                }
157              },
158              {
159                "query_block": {
160                  "select_id": 3,
161                  "operation": "EXCEPT",
162                  "r_loops": 1,
163                  "r_total_time_ms": "REPLACED",
164                  "table": {
165                    "table_name": "t2",
166                    "access_type": "ALL",
167                    "r_loops": 1,
168                    "rows": 2,
169                    "r_rows": 2,
170                    "r_table_time_ms": "REPLACED",
171                    "r_other_time_ms": "REPLACED",
172                    "filtered": 100,
173                    "r_filtered": 100
174                  }
175                }
176              }
177            ]
178          }
179        }
180      }
181    }
182  }
183}
184select * from ((select a,b from t1) except (select c,d from t2)) a;
185a	b
1861	1
187prepare stmt from "(select a,b from t1) except (select c,d from t2)";
188execute stmt;
189a	b
1901	1
191execute stmt;
192a	b
1931	1
194prepare stmt from "select * from ((select a,b from t1) except (select c,d from t2)) a";
195execute stmt;
196a	b
1971	1
198execute stmt;
199a	b
2001	1
201drop tables t1,t2;
202create table t1 (a int, b int) engine=MyISAM;
203create table t2 (c int, d int) engine=MyISAM;
204create table t3 (e int, f int) engine=MyISAM;
205create table t4 (g int, h int) engine=MyISAM;
206insert into t1 values (1,1),(2,2);
207insert into t2 values (2,2),(3,3);
208insert into t3 values (4,4),(5,5);
209insert into t4 values (4,4),(7,7);
210(select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
211a	b	e	f
2121	1	4	4
2131	1	5	5
2142	2	5	5
215EXPLAIN (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
216id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2171	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2
2181	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
2192	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2
2202	EXCEPT	t4	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
221NULL	EXCEPT RESULT	<except1,2>	ALL	NULL	NULL	NULL	NULL	NULL
222EXPLAIN (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
223id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2241	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2
2251	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
2262	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2
2272	EXCEPT	t4	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
228NULL	EXCEPT RESULT	<except1,2>	ALL	NULL	NULL	NULL	NULL	NULL
229EXPLAIN extended select * from ((select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)) a;
230id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
2311	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	100.00
2322	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00
2332	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
2343	EXCEPT	t2	ALL	NULL	NULL	NULL	NULL	2	100.00
2353	EXCEPT	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
236NULL	EXCEPT RESULT	<except2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL
237Warnings:
238Note	1003	/* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b`,`a`.`e` AS `e`,`a`.`f` AS `f` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` join `test`.`t3`) except (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t4`.`g` AS `g`,`test`.`t4`.`h` AS `h` from `test`.`t2` join `test`.`t4`)) `a`
239EXPLAIN format=json (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
240EXPLAIN
241{
242  "query_block": {
243    "union_result": {
244      "table_name": "<except1,2>",
245      "access_type": "ALL",
246      "query_specifications": [
247        {
248          "query_block": {
249            "select_id": 1,
250            "table": {
251              "table_name": "t1",
252              "access_type": "ALL",
253              "rows": 2,
254              "filtered": 100
255            },
256            "block-nl-join": {
257              "table": {
258                "table_name": "t3",
259                "access_type": "ALL",
260                "rows": 2,
261                "filtered": 100
262              },
263              "buffer_type": "flat",
264              "buffer_size": "119",
265              "join_type": "BNL"
266            }
267          }
268        },
269        {
270          "query_block": {
271            "select_id": 2,
272            "operation": "EXCEPT",
273            "table": {
274              "table_name": "t2",
275              "access_type": "ALL",
276              "rows": 2,
277              "filtered": 100
278            },
279            "block-nl-join": {
280              "table": {
281                "table_name": "t4",
282                "access_type": "ALL",
283                "rows": 2,
284                "filtered": 100
285              },
286              "buffer_type": "flat",
287              "buffer_size": "119",
288              "join_type": "BNL"
289            }
290          }
291        }
292      ]
293    }
294  }
295}
296ANALYZE format=json (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
297ANALYZE
298{
299  "query_block": {
300    "union_result": {
301      "table_name": "<except1,2>",
302      "access_type": "ALL",
303      "r_loops": 1,
304      "r_rows": 3,
305      "query_specifications": [
306        {
307          "query_block": {
308            "select_id": 1,
309            "r_loops": 1,
310            "r_total_time_ms": "REPLACED",
311            "table": {
312              "table_name": "t1",
313              "access_type": "ALL",
314              "r_loops": 1,
315              "rows": 2,
316              "r_rows": 2,
317              "r_table_time_ms": "REPLACED",
318              "r_other_time_ms": "REPLACED",
319              "filtered": 100,
320              "r_filtered": 100
321            },
322            "block-nl-join": {
323              "table": {
324                "table_name": "t3",
325                "access_type": "ALL",
326                "r_loops": 1,
327                "rows": 2,
328                "r_rows": 2,
329                "r_table_time_ms": "REPLACED",
330                "r_other_time_ms": "REPLACED",
331                "filtered": 100,
332                "r_filtered": 100
333              },
334              "buffer_type": "flat",
335              "buffer_size": "119",
336              "join_type": "BNL",
337              "r_filtered": 100
338            }
339          }
340        },
341        {
342          "query_block": {
343            "select_id": 2,
344            "operation": "EXCEPT",
345            "r_loops": 1,
346            "r_total_time_ms": "REPLACED",
347            "table": {
348              "table_name": "t2",
349              "access_type": "ALL",
350              "r_loops": 1,
351              "rows": 2,
352              "r_rows": 2,
353              "r_table_time_ms": "REPLACED",
354              "r_other_time_ms": "REPLACED",
355              "filtered": 100,
356              "r_filtered": 100
357            },
358            "block-nl-join": {
359              "table": {
360                "table_name": "t4",
361                "access_type": "ALL",
362                "r_loops": 1,
363                "rows": 2,
364                "r_rows": 2,
365                "r_table_time_ms": "REPLACED",
366                "r_other_time_ms": "REPLACED",
367                "filtered": 100,
368                "r_filtered": 100
369              },
370              "buffer_type": "flat",
371              "buffer_size": "119",
372              "join_type": "BNL",
373              "r_filtered": 100
374            }
375          }
376        }
377      ]
378    }
379  }
380}
381ANALYZE format=json select * from ((select a,b,e,f from t1,t3) except
382(select c,d,g,h from t2,t4)) a;
383ANALYZE
384{
385  "query_block": {
386    "select_id": 1,
387    "r_loops": 1,
388    "r_total_time_ms": "REPLACED",
389    "table": {
390      "table_name": "<derived2>",
391      "access_type": "ALL",
392      "r_loops": 1,
393      "rows": 4,
394      "r_rows": 3,
395      "r_table_time_ms": "REPLACED",
396      "r_other_time_ms": "REPLACED",
397      "filtered": 100,
398      "r_filtered": 100,
399      "materialized": {
400        "query_block": {
401          "union_result": {
402            "table_name": "<except2,3>",
403            "access_type": "ALL",
404            "r_loops": 1,
405            "r_rows": 3,
406            "query_specifications": [
407              {
408                "query_block": {
409                  "select_id": 2,
410                  "r_loops": 1,
411                  "r_total_time_ms": "REPLACED",
412                  "table": {
413                    "table_name": "t1",
414                    "access_type": "ALL",
415                    "r_loops": 1,
416                    "rows": 2,
417                    "r_rows": 2,
418                    "r_table_time_ms": "REPLACED",
419                    "r_other_time_ms": "REPLACED",
420                    "filtered": 100,
421                    "r_filtered": 100
422                  },
423                  "block-nl-join": {
424                    "table": {
425                      "table_name": "t3",
426                      "access_type": "ALL",
427                      "r_loops": 1,
428                      "rows": 2,
429                      "r_rows": 2,
430                      "r_table_time_ms": "REPLACED",
431                      "r_other_time_ms": "REPLACED",
432                      "filtered": 100,
433                      "r_filtered": 100
434                    },
435                    "buffer_type": "flat",
436                    "buffer_size": "119",
437                    "join_type": "BNL",
438                    "r_filtered": 100
439                  }
440                }
441              },
442              {
443                "query_block": {
444                  "select_id": 3,
445                  "operation": "EXCEPT",
446                  "r_loops": 1,
447                  "r_total_time_ms": "REPLACED",
448                  "table": {
449                    "table_name": "t2",
450                    "access_type": "ALL",
451                    "r_loops": 1,
452                    "rows": 2,
453                    "r_rows": 2,
454                    "r_table_time_ms": "REPLACED",
455                    "r_other_time_ms": "REPLACED",
456                    "filtered": 100,
457                    "r_filtered": 100
458                  },
459                  "block-nl-join": {
460                    "table": {
461                      "table_name": "t4",
462                      "access_type": "ALL",
463                      "r_loops": 1,
464                      "rows": 2,
465                      "r_rows": 2,
466                      "r_table_time_ms": "REPLACED",
467                      "r_other_time_ms": "REPLACED",
468                      "filtered": 100,
469                      "r_filtered": 100
470                    },
471                    "buffer_type": "flat",
472                    "buffer_size": "119",
473                    "join_type": "BNL",
474                    "r_filtered": 100
475                  }
476                }
477              }
478            ]
479          }
480        }
481      }
482    }
483  }
484}
485select * from ((select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)) a;
486a	b	e	f
4871	1	4	4
4881	1	5	5
4892	2	5	5
490prepare stmt from "(select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)";
491execute stmt;
492a	b	e	f
4931	1	4	4
4941	1	5	5
4952	2	5	5
496execute stmt;
497a	b	e	f
4981	1	4	4
4991	1	5	5
5002	2	5	5
501prepare stmt from "select * from ((select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)) a";
502execute stmt;
503a	b	e	f
5041	1	4	4
5051	1	5	5
5062	2	5	5
507execute stmt;
508a	b	e	f
5091	1	4	4
5101	1	5	5
5112	2	5	5
512drop tables t1,t2,t3,t4;
513select 1 as a from dual except select 1 from dual;
514a
515(select 1 from dual) except (select 1 from dual);
5161
517(select 1 from dual into @v) except (select 1 from dual);
518ERROR 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) except (select 1 from dual)' at line 1
519select 1 from dual ORDER BY 1 except select 1 from dual;
520ERROR 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 'except select 1 from dual' at line 1
521select 1 as a from dual union all select 1 from dual;
522a
5231
5241
525create table t1 (a int, b blob, a1 int, b1 blob) engine=MyISAM;
526create table t2 (c int, d blob, c1 int, d1 blob) engine=MyISAM;
527insert into t1 values (1,"ddd", 1, "sdfrrwwww"),(2, "fgh", 2, "dffggtt");
528insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg");
529(select a,b,b1 from t1) except (select c,d,d1 from t2);
530a	b	b1
5311	ddd	sdfrrwwww
532create table t3 (select a,b,b1 from t1) except (select c,d,d1 from t2);
533show create table t3;
534Table	Create Table
535t3	CREATE TABLE `t3` (
536  `a` int(11) DEFAULT NULL,
537  `b` blob DEFAULT NULL,
538  `b1` blob DEFAULT NULL
539) ENGINE=MyISAM DEFAULT CHARSET=latin1
540drop tables t1,t2,t3;
541#
542# MDEV-13723: Server crashes in ha_heap::find_unique_row or
543# Assertion `0' failed in st_select_lex_unit::optimize with INTERSECT
544#
545CREATE TABLE t (i INT);
546INSERT INTO t VALUES (1),(2);
547SELECT * FROM t WHERE i != ANY ( SELECT 3 EXCEPT SELECT 3 );
548i
549drop table t;
550# End of 10.3 tests
551