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