1select 1 union ( select 2 union select 3); 21 31 42 53 6explain extended 7select 1 union ( select 2 union select 3); 8id select_type table type possible_keys key key_len ref rows filtered Extra 91 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 104 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 112 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 123 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 13NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 14NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL NULL 15Warnings: 16Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`2` AS `2` from (/* select#2 */ select 2 AS `2` union /* select#3 */ select 3 AS `3`) `__4` 17select 1 union ( select 1 union select 1); 181 191 20explain extended 21select 1 union ( select 1 union select 1); 22id select_type table type possible_keys key key_len ref rows filtered Extra 231 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 244 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 252 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 263 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 27NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 28NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL NULL 29Warnings: 30Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4` 31select 1 union all ( select 1 union select 1); 321 331 341 35explain extended 36select 1 union all ( select 1 union select 1); 37id select_type table type possible_keys key key_len ref rows filtered Extra 381 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 394 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 402 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 413 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 42NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 43Warnings: 44Note 1003 /* select#1 */ select 1 AS `1` union all /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4` 45select 1 union ( select 1 union all select 1); 461 471 48explain extended 49select 1 union ( select 1 union all select 1); 50id select_type table type possible_keys key key_len ref rows filtered Extra 511 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 524 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 532 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 543 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 55NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL NULL 56Warnings: 57Note 1003 /* select#1 */ select 1 AS `1` union /* select#4 */ select `__4`.`1` AS `1` from (/* select#2 */ select 1 AS `1` union /* select#3 */ select 1 AS `1`) `__4` 58select 1 union select 1 union all select 1; 591 601 611 62explain extended 63select 1 union select 1 union all select 1; 64id select_type table type possible_keys key key_len ref rows filtered Extra 651 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 662 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 673 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 68NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL NULL 69Warnings: 70Note 1003 /* select#1 */ select 1 AS `1` union /* select#2 */ select 1 AS `1` union all /* select#3 */ select 1 AS `1` 71(select 1 as a) union (select 2) order by a; 72a 731 742 75explain extended 76(select 1 as a) union (select 2) order by a; 77id select_type table type possible_keys key key_len ref rows filtered Extra 781 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 792 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 80NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort 81Warnings: 82Note 1003 (/* select#1 */ select 1 AS `a`) union (/* select#2 */ select 2 AS `2`) order by `a` 83/* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`; 84a 851 862 87explain extended 88/* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a`; 89id select_type table type possible_keys key key_len ref rows filtered Extra 901 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 912 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 92NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort 93Warnings: 94Note 1003 /* select#1 */ select 1 AS `a` union /* select#2 */ select 2 AS `2` order by `a` 95select 1 union ( select 1 union (select 1 union (select 1 union select 1))); 961 971 98explain extended all 99select 1 union ( select 1 union (select 1 union (select 1 union select 1))); 100id select_type table type possible_keys key key_len ref rows filtered Extra 1011 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1028 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 1032 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1047 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 1053 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1066 UNION <derived4> ALL NULL NULL NULL NULL 2 100.00 1074 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1085 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 109NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL 110NULL UNION RESULT <union3,6> ALL NULL NULL NULL NULL NULL NULL 111NULL UNION RESULT <union2,7> ALL NULL NULL NULL NULL NULL NULL 112NULL UNION RESULT <union1,8> ALL NULL NULL NULL NULL NULL NULL 113Warnings: 114Note 1003 /* select#1/0 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#8/0 */ select `__8`.`1` AS `1` from (/* select#2/1 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#7/1 */ select `__7`.`1` AS `1` from (/* select#3/2 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#6/2 */ select `__6`.`1` AS `1` from (/* select#4/3 Filter Select: select `1` AS `1` */ select 1 AS `1` union /* select#5/3 */ select 1 AS `1`) `__6`) `__7`) `__8` 115# 116# MDEV-6341: INSERT ... SELECT UNION with parenthesis 117# 118create table t1 (a int, b int); 119insert into t1 (select 1,1 union select 2,2); 120select * from t1 order by 1; 121a b 1221 1 1232 2 124delete from t1; 125insert into t1 select 1,1 union select 2,2; 126select * from t1 order by 1; 127a b 1281 1 1292 2 130drop table t1; 131CREATE OR REPLACE TABLE t1 AS SELECT 1 AS a UNION SELECT 2; 132select * from t1 order by 1; 133a 1341 1352 136drop table t1; 137CREATE OR REPLACE TABLE t1 AS (SELECT 1 AS a UNION SELECT 2); 138select * from t1 order by 1; 139a 1401 1412 142drop table t1; 143CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a); 144show create view v1; 145View Create View character_set_client collation_connection 146v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select 1 AS `a`) latin1 latin1_swedish_ci 147drop view v1; 148CREATE OR REPLACE VIEW v1 AS SELECT 1 AS a UNION SELECT 2; 149show create view v1; 150View Create View character_set_client collation_connection 151v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union select 2 AS `2` latin1 latin1_swedish_ci 152drop view v1; 153CREATE OR REPLACE VIEW v1 AS (SELECT 1 AS a UNION SELECT 2); 154show create view v1; 155View Create View character_set_client collation_connection 156v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a` union select 2 AS `2` latin1 latin1_swedish_ci 157drop view v1; 158# 159# MDEV-10028: Syntax error on ((SELECT ...) UNION (SELECT ...)) 160# 161CREATE TABLE t1 (a INT); 162INSERT INTO t1 VALUES (10); 163INSERT INTO t1 VALUES (20); 164INSERT INTO t1 VALUES (30); 165((SELECT a FROM t1) UNION (SELECT a FROM t1)); 166a 16710 16820 16930 170(SELECT * FROM t1 UNION SELECT * FROM t1); 171a 17210 17320 17430 175((SELECT a FROM t1) LIMIT 1); 176a 17710 178SELECT * FROM (SELECT 1 UNION (SELECT 2 UNION SELECT 3)) t1; 1791 1801 1812 1823 183DROP TABLE t1; 184# 185# test of several levels of ORDER BY / LIMIT 186# 187create table t1 (a int, b int); 188insert into t1 (a,b) values (1, 100), (2, 200), (3,30), (4,4); 189select a,b from t1 order by 1 limit 3; 190a b 1911 100 1922 200 1933 30 194(select a,b from t1 order by 1 limit 3) order by 2 limit 2; 195a b 1963 30 1971 100 198(select 10,1000 union select a,b from t1 order by 1 limit 3) order by 2 limit 2; 19910 1000 2003 30 2011 100 202((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1 limit 1; 203a b 2041 100 205((select a,b from t1 order by 1 limit 3) order by 2 limit 2) order by 1; 206a b 2071 100 2083 30 209drop table t1; 210# 211# MDEV-16359: union with 3 selects in brackets 212# 213select 1 union select 1 union select 1; 2141 2151 216(select 1 union select 1 union select 1); 2171 2181 219((select 1) union (select 1) union (select 1)); 2201 2211 222# 223# MDEV-16357: union in brackets with tail 224# union with tail in brackets 225# 226CREATE TABLE t1 (a int); 227INSERT INTO t1 VALUES(1),(2),(3),(4); 228CREATE TABLE t2 (a int); 229INSERT INTO t2 VALUES (4),(5),(6),(7); 230(SELECT a FROM t1 UNION SELECT a FROM t2) LIMIT 1; 231a 2321 233(SELECT a FROM t1 UNION SELECT a FROM t2) ORDER BY a DESC; 234a 2357 2366 2375 2384 2393 2402 2411 242(SELECT a FROM t1 UNION SELECT a FROM t2 LIMIT 1); 243a 2441 245DROP TABLE t1,t2; 246# 247# MDEV-19324: ((SELECT ...) ORDER BY col ) LIMIT n 248# 249create table t1 (a int); 250insert into t1 values (10),(20),(30); 251select a from t1 order by a desc limit 1; 252a 25330 254explain extended select a from t1 order by a desc limit 1; 255id select_type table type possible_keys key key_len ref rows filtered Extra 2561 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort 257Warnings: 258Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` desc limit 1 259explain format=json select a from t1 order by a desc limit 1; 260EXPLAIN 261{ 262 "query_block": { 263 "select_id": 1, 264 "read_sorted_file": { 265 "filesort": { 266 "sort_key": "t1.a desc", 267 "table": { 268 "table_name": "t1", 269 "access_type": "ALL", 270 "rows": 3, 271 "filtered": 100 272 } 273 } 274 } 275 } 276} 277(select a from t1 order by a desc) limit 1; 278a 27930 280explain extended (select a from t1 order by a desc) limit 1; 281id select_type table type possible_keys key key_len ref rows filtered Extra 2821 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort 283Warnings: 284Note 1003 (select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` desc limit 1) 285explain format=json (select a from t1 order by a desc) limit 1; 286EXPLAIN 287{ 288 "query_block": { 289 "select_id": 1, 290 "read_sorted_file": { 291 "filesort": { 292 "sort_key": "t1.a desc", 293 "table": { 294 "table_name": "t1", 295 "access_type": "ALL", 296 "rows": 3, 297 "filtered": 100 298 } 299 } 300 } 301 } 302} 303(select a from t1 where a=20 union select a from t1) order by a desc limit 1; 304a 30530 306explain extended (select a from t1 where a=20 union select a from t1) order by a desc limit 1; 307id select_type table type possible_keys key key_len ref rows filtered Extra 3081 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 3092 UNION t1 ALL NULL NULL NULL NULL 3 100.00 310NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort 311Warnings: 312Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 20 union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` desc limit 1 313explain format=json (select a from t1 where a=20 union select a from t1) order by a desc limit 1; 314EXPLAIN 315{ 316 "query_block": { 317 "union_result": { 318 "table_name": "<union1,2>", 319 "access_type": "ALL", 320 "query_specifications": [ 321 { 322 "query_block": { 323 "select_id": 1, 324 "table": { 325 "table_name": "t1", 326 "access_type": "ALL", 327 "rows": 3, 328 "filtered": 100, 329 "attached_condition": "t1.a = 20" 330 } 331 } 332 }, 333 { 334 "query_block": { 335 "select_id": 2, 336 "operation": "UNION", 337 "table": { 338 "table_name": "t1", 339 "access_type": "ALL", 340 "rows": 3, 341 "filtered": 100 342 } 343 } 344 } 345 ] 346 } 347 } 348} 349((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; 350a 35130 352explain extended ((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; 353id select_type table type possible_keys key key_len ref rows filtered Extra 3541 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 3552 UNION t1 ALL NULL NULL NULL NULL 3 100.00 356NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort 357Warnings: 358Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 20 union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` desc limit 1 359explain format=json ((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; 360EXPLAIN 361{ 362 "query_block": { 363 "union_result": { 364 "table_name": "<union1,2>", 365 "access_type": "ALL", 366 "query_specifications": [ 367 { 368 "query_block": { 369 "select_id": 1, 370 "table": { 371 "table_name": "t1", 372 "access_type": "ALL", 373 "rows": 3, 374 "filtered": 100, 375 "attached_condition": "t1.a = 20" 376 } 377 } 378 }, 379 { 380 "query_block": { 381 "select_id": 2, 382 "operation": "UNION", 383 "table": { 384 "table_name": "t1", 385 "access_type": "ALL", 386 "rows": 3, 387 "filtered": 100 388 } 389 } 390 } 391 ] 392 } 393 } 394} 395drop table t1; 396# 397# MDEV-19363: ((SELECT ...) ORDER BY col ) LIMIT n UNION ... 398# 399create table t1 (pk int); 400insert into t1 values (5),(4),(1),(2),(3); 401((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4); 402pk 4031 4042 4055 406explain extended ((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4); 407id select_type table type possible_keys key key_len ref rows filtered Extra 4081 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using filesort 4092 UNION t1 ALL NULL NULL NULL NULL 5 100.00 Using where 410NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL 411Warnings: 412Note 1003 (/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` order by `test`.`t1`.`pk` limit 2) union (/* select#2 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where `test`.`t1`.`pk` > 4) 413explain format=json ((select * from t1 order by pk) limit 2) union (select * from t1 where pk > 4); 414EXPLAIN 415{ 416 "query_block": { 417 "union_result": { 418 "table_name": "<union1,2>", 419 "access_type": "ALL", 420 "query_specifications": [ 421 { 422 "query_block": { 423 "select_id": 1, 424 "read_sorted_file": { 425 "filesort": { 426 "sort_key": "t1.pk", 427 "table": { 428 "table_name": "t1", 429 "access_type": "ALL", 430 "rows": 5, 431 "filtered": 100 432 } 433 } 434 } 435 } 436 }, 437 { 438 "query_block": { 439 "select_id": 2, 440 "operation": "UNION", 441 "table": { 442 "table_name": "t1", 443 "access_type": "ALL", 444 "rows": 5, 445 "filtered": 100, 446 "attached_condition": "t1.pk > 4" 447 } 448 } 449 } 450 ] 451 } 452 } 453} 454drop table t1; 455# 456# MDEV-18689: parenthesis around table names and derived tables 457# 458select * from ( mysql.db ); 459Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Delete_history_priv 460% test Y Y Y Y Y Y N Y Y Y Y Y Y Y Y N N Y Y Y 461% test\_% Y Y Y Y Y Y N Y Y Y Y Y Y Y Y N N Y Y Y 462create table t1 (a int); 463insert into t1 values (7), (2), (7); 464select * from (t1); 465a 4667 4672 4687 469select * from ((t1)); 470a 4717 4722 4737 474select * from (t1 t) where t.a > 5; 475a 4767 4777 478select * from ((t1 t)) where t.a > 5; 479a 4807 4817 482select * from ((select a, sum(a) from t1 group by a) t); 483a sum(a) 4842 2 4857 14 486select * from (((select a, sum(a) from t1 group by a) t)); 487a sum(a) 4882 2 4897 14 490update (t1 t) set t.a=t.a+1; 491select * from t1; 492a 4938 4943 4958 496drop table t1; 497# 498# MDEV-19956: query expressions in different contexts 499# 500create table t1 (a int); 501insert into t1 values (3), (7), (1), (2), (4); 502create table t2 (a int, b int); 503insert into t2 values (3,30), (7,70), (1,10), (2,20), (4,40); 504# 1. select 505# 1.1. simple select 506select * from t1; 507a 5083 5097 5101 5112 5124 513(select * from t1); 514a 5153 5167 5171 5182 5194 520((select * from t1)); 521a 5223 5237 5241 5252 5264 527# 1.2. select with tail 528select * from t1 order by a; 529a 5301 5312 5323 5334 5347 535select a from t1 order by a; 536a 5371 5382 5393 5404 5417 542select a from t1 order by 1; 543a 5441 5452 5463 5474 5487 549select * from t1 order by t1.a; 550a 5511 5522 5533 5544 5557 556(select * from t1 order by t1.a); 557a 5581 5592 5603 5614 5627 563((select * from t1 order by t1.a)); 564a 5651 5662 5673 5684 5697 570(select * from t1 order by t1.a limit 2); 571a 5721 5732 574(select a from t1 where a=1) order by 1 desc; 575a 5761 577# 1.2. select with several tails 578(select * from t2 order by a limit 2) order by b desc; 579a b 5802 20 5811 10 582(select * from t2 order by t2.a limit 2) order by b desc; 583a b 5842 20 5851 10 586((select * from t2 order by t2.a limit 2) order by b desc); 587a b 5882 20 5891 10 590(((select * from t2 order by t2.a) limit 2) order by b desc); 591a b 5922 20 5931 10 594# 2. union 595# 2.1 simple union 596select a from t1 union select a from t1; 597a 5983 5997 6001 6012 6024 603select a from t1 union all select a from t1; 604a 6053 6067 6071 6082 6094 6103 6117 6121 6132 6144 615select a from t1 union select b from t2; 616a 6173 6187 6191 6202 6214 62230 62370 62410 62520 62640 627(select a from t1) union (select a from t1); 628a 6293 6307 6311 6322 6334 634(select a from t1) union (select b from t2); 635a 6363 6377 6381 6392 6404 64130 64270 64310 64420 64540 646select a from t1 where a=1 union select a from t1 where a=3; 647a 6481 6493 650(select a from t1 where a=1) union select a from t1 where a=3; 651a 6521 6533 654((select a from t1 where a=1) union select a from t1 where a=3); 655a 6561 6573 658((select a from t1 where a<=3) union (select a from t1 where a=3)); 659a 6603 6611 6622 663select a from t1 where a=1 union (select a from t1 where a=3); 664a 6651 6663 667(select a from t1 where a=1 union (select a from t1 where a=3)); 668a 6691 6703 671((select a from t1 where a=1 union (select a from t1 where a=3))); 672a 6731 6743 675select a from t1 where a=1 676union 677select a from t1 where a=3 678union 679select a from t1 where a=7; 680a 6811 6823 6837 684( select a from t1 where a=1 685union 686select a from t1 where a=3 687union 688select a from t1 where a=7 ); 689a 6901 6913 6927 693(select a from t1 where a=1 order by a) union select a from t1 where a=3; 694a 6951 6963 697(select a from t1 where a!=3 order by a desc) union select a from t1 where a=3; 698a 6997 7001 7012 7024 7033 704((select a from t1 where a=1 order by a) union select a from t1 where a=3); 705a 7061 7073 708(select a from t1 where a!=3 order by a desc) union select a from t1 where a=3; 709a 7107 7111 7122 7134 7143 715( ( select a from t1 where a!=3 order by a desc limit 3) 716union 717select a from t1 where a=3 ); 718a 7197 7204 7212 7223 723( select a from t1 where a <=3 except select a from t1 where a >=3 ) 724union 725select a from t1 where a=7; 726a 7271 7282 7297 730( ( select a from t1 where a <=3 731except 732select a from t1 where a >=3 ) 733union 734select a from t1 where a=7 ); 735a 7361 7372 7387 739( select a from t1 where a <=3 740except 741( select a from t1 where a >=3 742union 743select a from t1 where a=7 ) ); 744a 7451 7462 747( ( select a from t1 where a <=3 ) 748except 749( select a from t1 where a >=3 750union 751select a from t1 where a=7 ) ); 752a 7531 7542 755# 2.2. union with tail 756select a from t1 where a=1 union select a from t1 where a=3 order by a desc; 757a 7583 7591 760(select a from t1 limit 2) union select a from t1 where a=3 order by a desc; 761a 7627 7633 764select a from t1 where a=4 union (select a from t1 where a <=4 limit 2) 765order by a desc; 766a 7674 7683 7691 770select a from t1 where a=4 771union 772(select a from t1 where a <=4 order by a limit 2) 773order by a desc; 774a 7754 7762 7771 778( select a from t1 where a=4 779union 780( select a from t1 where a <=4 order by a limit 2 ) ) 781order by a desc; 782a 7834 7842 7851 786( select a from t1 where a <=3 except select a from t1 where a >=3 ) 787union 788select a from t1 where a=7 order by a desc; 789a 7907 7912 7921 793( select a from t1 where a!=3 order by a desc ) 794union 795select a from t1 where a=3 796order by a desc; 797a 7987 7994 8003 8012 8021 803(select a from t1 where a=1) 804union 805(select a from t1 where a=3) 806order by a desc; 807a 8083 8091 810( select a from t1 where a=1 811union 812select a from t1 where a=3 ) 813order by a desc; 814a 8153 8161 817( ( select a from t1 where a=1 ) 818union 819( select a from t1 where a=3 ) ) 820order by a desc; 821a 8223 8231 824( select a from t1 where a=1 825union 826select a from t1 where a=3 ) 827order by 1 desc; 828a 8293 8301 831((select a from t1 where a=1 union select a from t1 where a=3)) order by 1 desc; 832a 8333 8341 835(((select a from t1 where a=1) union (select a from t1 where a=3))) 836order by 1 desc; 837a 8383 8391 840( (select a from t1 where a=1 ) 841union 842(select a from t1 where a=3) ) 843order by 1 desc; 844a 8453 8461 847# 2.3. complex union 848select a from t1 where a=1 849union 850select a from t1 where a=3 851union 852select a from t1 where a=2 853union 854select a from t1 where a=4; 855a 8561 8573 8582 8594 860( select a from t1 where a=1 861union 862select a from t1 where a=3 863union 864select a from t1 where a=2 ) 865union 866select a from t1 where a=4; 867a 8681 8693 8702 8714 872(select a from t1 where a=1 union select a from t1 where a=3) 873union 874(select a from t1 where a=2 union select a from t1 where a=4); 875a 8761 8773 8782 8794 880(select a from t1 where a=1 union (select a from t1 where a=3)) 881union 882((select a from t1 where a=2) union select a from t1 where a=4); 883a 8841 8853 8862 8874 888( ( select a from t1 where a=1) 889union 890select a from t1 where a=3 ) 891union 892select a from t1 where a=2 893union 894select a from t1 where a=4; 895a 8961 8973 8982 8994 900( ( ( select a from t1 where a=1) 901union 902select a from t1 where a=3 ) 903union 904select a from t1 where a=2 ) 905union 906select a from t1 where a=4; 907a 9081 9093 9102 9114 912select a from t1 where a=1 913union 914select a from t1 where a=3 915union 916select a from t1 where a=2 917union 918(select a from t1 where a=4); 919a 9201 9213 9222 9234 924select a from t1 where a=1 925union 926select a from t1 where a=3 927union 928( select a from t1 where a=2 929union 930( select a from t1 where a=4 ) ); 931a 9321 9333 9342 9354 936select a from t1 where a=1 937union 938( select a from t1 where a=3 939union 940( select a from t1 where a=2 941union 942( select a from t1 where a=4 ) ) ); 943a 9441 9453 9462 9474 948# 2.4. complex union with tail 949( ( select a from t1 where a=1 union select a from t1 where a=3 ) 950order by a desc ) 951union 952( ( select a from t1 where a=2 union select a from t1 where a=4 ) 953order by a desc ); 954a 9553 9561 9574 9582 959( ( select a from t1 where a=1 union select a from t1 where a=3 ) 960order by a desc ) 961union 962( ( select a from t1 where a=2 union select a from t1 where a=4 ) 963order by a desc ) 964order by a; 965a 9661 9672 9683 9694 970( select a from t1 where a=1 971union 972select a from t1 where a=3 973union 974select a from t1 where a=2 order by a desc limit 2 ) 975union 976select a from t1 where a=4 977order by a; 978a 9792 9803 9814 982( select a from t1 where a=1 983union 984select a from t1 where a=3 order by a desc ) 985union 986select a from t1 where a=2 order by a desc limit 2; 987a 9883 9892 990( ( select a from t1 where a >= 2 991union 992select a from t1 where a=1 order by a desc limit 2 ) 993union 994select a from t1 where a=3 order by a limit 2 ) 995union 996select a from t1 where a=1; 997a 9983 9994 10001 1001# 3. TVC 1002# 3.1. simple TVC 1003values (3), (7), (1); 10043 10053 10067 10071 1008(values (3), (7), (1)); 10093 10103 10117 10121 1013((values (3), (7), (1))); 10143 10153 10167 10171 1018# 3.2. simple TVC with tail(s) 1019values (3), (7), (1) order by 1; 10203 10211 10223 10237 1024(values (3), (7), (1)) order by 1; 10253 10261 10273 10287 1029((values (3), (7), (1))) order by 1; 10303 10311 10323 10337 1034(((values (3), (7), (1))) order by 1); 10353 10361 10373 10387 1039(values (3), (7), (1) limit 2) order by 1 desc; 10403 10417 10423 1043((values (3), (7), (1)) order by 1 desc) limit 2; 10443 10457 10463 1047(((values (3), (7), (1)) order by 1 desc) limit 2); 10483 10497 10503 1051# 3.3. union of TVCs 1052values (3), (7), (1) union values (3), (4), (2); 10533 10543 10557 10561 10574 10582 1059values (3), (7), (1) union all values (3), (4), (2); 10603 10613 10627 10631 10643 10654 10662 1067values (3), (7), (1) union values (3), (4), (2); 10683 10693 10707 10711 10724 10732 1074values (3), (7), (1) except values (3), (4), (2); 10753 10767 10771 1078(values (3), (7), (1)) union (values (3), (4), (2)); 10793 10803 10817 10821 10834 10842 1085(values (3), (7), (1)) union (values (3), (4), (2)) union values (5), (7); 10863 10873 10887 10891 10904 10912 10925 1093(values (3), (7), (1)) union (values (3), (4), (2)) union (values (5), (7)); 10943 10953 10967 10971 10984 10992 11005 1101(values (3), (7), (1) union values (3), (4), (2)) union values (5), (7); 11023 11033 11047 11051 11064 11072 11085 1109values (3), (7), (1) union (values (3), (4), (2) union values (5), (7)); 11103 11113 11127 11131 11144 11152 11165 1117(values (3), (7), (1) union ((values (3), (4), (2) union values (5), (7)))); 11183 11193 11207 11211 11224 11232 11245 1125# 3.4. tailed union of TVCs 1126values (3), (7), (1) union values (3), (4), (2) order by 1; 11273 11281 11292 11303 11314 11327 1133(values (3), (7), (1) union values (3), (4), (2)) order by 1; 11343 11351 11362 11373 11384 11397 1140(values (3), (7), (1) union values (3), (4), (2)) order by 1; 11413 11421 11432 11443 11454 11467 1147values (3), (7), (1) union (values (3), (4), (2)) order by 1; 11483 11491 11502 11513 11524 11537 1154(values (3), (7), (1) union values (3), (4), (2)) order by 1; 11553 11561 11572 11583 11594 11607 1161((values (3), (7), (1)) union values (3), (4), (2)) order by 1; 11623 11631 11642 11653 11664 11677 1168# 3.5. union of tailed TVCs 1169(values (3), (7), (1) order by 1 limit 2) 1170union 1171(values (3), (4), (2) order by 1 desc limit 2); 11723 11731 11743 11754 1176((values (3), (7), (1) order by 1) limit 2) 1177union 1178((values (3), (4), (2) order by 1 desc) limit 2); 11793 11801 11813 11824 1183(((values (3), (7), (1)) order by 1) limit 2) 1184union 1185(((values (3), (4), (2)) order by 1 desc) limit 2); 11863 11871 11883 11894 1190# 3.6. tailed union of tailed TVCs 1191(values (3), (7), (1) order by 1 limit 2) 1192union 1193values (3), (4), (2) 1194order by 1; 11953 11961 11972 11983 11994 1200((values (3), (7), (1)) order by 1 limit 2) 1201union 1202((values (3), (4), (2) order by 1 desc) limit 2) 1203order by 1; 12043 12051 12063 12074 1208# 3.7 [tailed] union of [tailed] select and [tailed] TVC 1209(select a from t1 where a <=3 order by 1 limit 2) 1210union 1211(values (3), (4), (2) order by 1 desc limit 2); 1212a 12131 12142 12154 12163 1217((select a from t1 where a <=3) order by 1 limit 2) 1218union 1219(values (3), (4), (2) order by 1 desc limit 2); 1220a 12211 12222 12234 12243 1225(((select a from t1 where a <=3) order by a) limit 2) 1226union 1227(((values (3), (4), (2)) order by 1 desc) limit 2); 1228a 12291 12302 12314 12323 1233( (((select a from t1 where a <=3) order by a) limit 2) 1234union 1235(((values (3), (4), (2)) order by 1 desc) limit 2) ); 1236a 12371 12382 12394 12403 1241(select a from t1 where a <=3 order by 1 limit 2) 1242union 1243(values (3), (4), (2) order by 1 desc limit 2) 1244order by a; 1245a 12461 12472 12483 12494 1250((select a from t1 where a <=3) order by 1 limit 2) 1251union 1252(values (3), (4), (2) order by 1 desc limit 2) 1253order by a; 1254a 12551 12562 12573 12584 1259(((select a from t1 where a <=3) order by a) limit 2) 1260union 1261(((values (3), (4), (2)) order by 1 desc) limit 2) 1262order by a; 1263a 12641 12652 12663 12674 1268(((values (3), (4), (2)) order by 1 desc) limit 2); 12693 12704 12713 1272( (((select a from t1 where a <=3) order by a) limit 2) 1273union 1274(((values (3), (4), (2)) order by 1 desc) limit 2) ) 1275order by a; 1276a 12771 12782 12793 12804 1281(values (3), (4), (2) order by 1 desc limit 2) 1282union 1283(select a from t1 where a <=3 order by 1 limit 2); 12843 12854 12863 12871 12882 1289(values (3), (4), (2) order by 1 desc limit 2) 1290union 1291((select a from t1 where a <=3) order by 1 limit 2); 12923 12934 12943 12951 12962 1297(((values (3), (4), (2)) order by 1 desc) limit 2) 1298union 1299(((select a from t1 where a <=3) order by 1) limit 2); 13003 13014 13023 13031 13042 1305(((values (3), (4), (2)) order by 1 desc) limit 2) 1306union 1307(((select a from t1 where a <=3) order by a) limit 2) 1308order by 1; 13093 13101 13112 13123 13134 1314( select a from t1 where a=1 1315union 1316values (3), (4), (2) order by 1 desc ) 1317union 1318select a from t1 where a=2 order by a desc limit 3; 1319a 13204 13213 13222 13234. CTE 13244.1. simple select with simple CTE 1325with t as (select * from t1 where a <=3) 1326select * from t; 1327a 13283 13291 13302 1331with t as (select * from t1 where a <=3) 1332(select * from t); 1333a 13343 13351 13362 1337with t as (select * from t1 where a <=3) 1338((select * from t)); 1339a 13403 13411 13422 1343with t as ((select * from t1 where a <=3)) 1344select * from t; 1345a 13463 13471 13482 1349with t as (((select * from t1 where a <=3))) 1350select * from t; 1351a 13523 13531 13542 13554.2. tailed select with simple CTE 1356with t as (select * from t1 where a <=3) 1357select * from t order by a; 1358a 13591 13602 13613 1362with t as (select * from t1 where a <=3) 1363(select * from t) order by a; 1364a 13651 13662 13673 1368with t as (select * from t1 where a <=3) 1369(select * from t) order by a desc limit 2; 1370a 13713 13722 13734.3. [tailed] select with tailed CTE 1374with t as (select * from t1 where a >=2 order by a limit 2) 1375select * from t; 1376a 13772 13783 1379with t as (((select * from t1 where a >=2) order by a desc) limit 2) 1380select * from t; 1381a 13827 13834 1384with t as (select * from t1 where a >=2 order by a desc limit 2) 1385select * from t order by a; 1386a 13874 13887 13894.4. [tailed] union with CTE 1390with t as (select * from t1 where a <=3) 1391select a from t1 where a=1 union select a from t where a=3; 1392a 13931 13943 1395with t as (select * from t1 where a <=3) 1396(select a from t) union (select b from t2); 1397a 13983 13991 14002 140130 140270 140310 140420 140540 1406with t as (select * from t1 where a <=3) 1407(select a from t) union (select b as a from t2) order by a desc; 1408a 140970 141040 141130 141220 141310 14143 14152 14161 14174.5. [tailed] union with [tailed] union in CTE 1418with t as (select * from t1 where a < 3 union select * from t1 where a > 3) 1419select a from t1 where a=1 union select a from t where a=7; 1420a 14211 14227 1423with t as 1424( select * from t1 where a < 3 1425union 1426select * from t1 where a > 3 1427order by a desc limit 3 ) 1428select a from t1 where a=4 union select a from t where a=7; 1429a 14304 14317 1432with t as 1433( select * from t1 where a < 3 1434union 1435select * from t1 where a > 3 1436order by a desc limit 3 ) 1437select a from t1 where a=4 union select a from t where a=7 order by a desc; 1438a 14397 14404 1441with t as 1442( (select * from t1 where a < 3) 1443union 1444(select * from t1 where a > 3) 1445order by a desc limit 3 ) 1446select a from t1 where a=4 union select a from t where a=7 order by a desc; 1447a 14487 14494 1450with t as 1451( (select * from t1 where a < 3) 1452union 1453(select * from t1 where a > 3) 1454order by a desc limit 3 ) 1455(select a from t1 where a=4 union select a from t where a=7 order by a desc); 1456a 14577 14584 1459with t as 1460( (select * from t1 where a < 3) 1461union 1462(select * from t1 where a > 3) 1463order by a desc limit 3 ) 1464((select a from t1 where a=4 union select a from t where a=7) order by a desc); 1465a 14667 14674 1468with t as 1469( select * from t1 where a < 3 1470union 1471values (4), (7) 1472order by a desc limit 3 ) 1473select a from t1 where a=4 union select a from t where a=7 order by a desc; 1474a 14757 14764 14774.6. [tailed] union with [tailed] union of TVC in CTE 1478with t(a) as 1479( values (2), (1) 1480union 1481(values (4), (7)) 1482order by 1 desc limit 3 ) 1483select a from t1 where a=4 union select a from t where a=7 order by a desc; 1484a 14857 14864 1487with t(a) as 1488( (values (2), (1)) 1489union 1490(values (4), (7) order by 1 desc) 1491order by 1 desc limit 3 ) 1492select a from t1 where a=1 union select a from t where a=7 order by a desc; 1493a 14947 14951 1496with t(a) as 1497( (values (2), (1)) 1498union 1499(values (4), (7) order by 1 desc) 1500order by 1 limit 3 ) 1501select a from t where a=1 union values (7) order by a desc; 1502a 15037 15041 1505with t(a) as 1506( (values (2), (1)) 1507union 1508(values (4), (7) order by 1 desc ) ) 1509select a from t where a=1 union select 7 order by a desc; 1510a 15117 15121 15134.5. [tailed] union with two CTEs 1514with t as (select * from t1 where a < 3), 1515s as (select * from t1 where a > 3) 1516select a from t where a=1 union select a from s where a=7 order by a desc; 1517a 15187 15191 1520with t as (select * from t1 where a < 3), 1521s as (select * from t1 where a > 3) 1522(select a from t where a=1 union select a from s where a=7 order by a desc); 1523a 15247 15251 1526with t as (select * from t1 where a < 3), 1527s as (select * from t1 where a > 3) 1528(select a from t where a=1 union select a from s where a=7) order by a desc; 1529a 15307 15311 1532with t as (select * from t1 where a < 3), 1533s as (select * from t where a > 3) 1534select a from t where a=1 union select a from s where a=7 order by a desc; 1535a 15361 1537# 5. single-row subquery in expression 1538# 5.1. [tailed] simple select in expression 1539select (a+1) + b as r from t2; 1540r 154134 154278 154312 154423 154545 1546select ((a+1) + b) as r from t2; 1547r 154834 154978 155012 155123 155245 1553select (b + (select 1)) as r from t2; 1554r 155531 155671 155711 155821 155941 1560select (select a from t1 where a <=3 order by a desc limit 1) as r from t2; 1561r 15623 15633 15643 15653 15663 1567select 1568(select a from t1 where a <=3 order by a desc limit 1) as r from t2; 1569r 15703 15713 15723 15733 15743 1575select (select 100) as r from t2; 1576r 1577100 1578100 1579100 1580100 1581100 1582select ((select 100)) as r from t2; 1583r 1584100 1585100 1586100 1587100 1588100 1589select (select 100) + t2.b as r from t2; 1590r 1591130 1592170 1593110 1594120 1595140 1596select ((select 100) + t2.b) as r from t2; 1597r 1598130 1599170 1600110 1601120 1602140 1603# 5.2. [tailed] TVC in expression 1604select (values (200)) as r from t2; 1605r 1606200 1607200 1608200 1609200 1610200 1611select ((values (200))) as r from t2; 1612r 1613200 1614200 1615200 1616200 1617200 1618select (values (200)) + t2.b as r from t2; 1619r 1620230 1621270 1622210 1623220 1624240 1625select ((values (200)) + t2.b) as r from t2; 1626r 1627230 1628270 1629210 1630220 1631240 1632select (values (200), (300) order by 1 desc limit 1) as r from t2; 1633r 1634300 1635300 1636300 1637300 1638300 1639select ((values (200), (300)) order by 1 desc limit 1) as r from t2; 1640r 1641300 1642300 1643300 1644300 1645300 1646select (select * from t1 limit 1) as r from t2; 1647r 16483 16493 16503 16513 16523 1653select (select * from t1 order by a limit 1) as r from t2; 1654r 16551 16561 16571 16581 16591 1660select ((select * from t1 order by a limit 1)) as r from t2; 1661r 16621 16631 16641 16651 16661 1667((select ((select * from t1 order by a limit 1)) as r from t2)); 1668r 16691 16701 16711 16721 16731 1674select (select * from t1 order by a limit 1) + t2.b as r from t2; 1675r 167631 167771 167811 167921 168041 1681# 5.3. [tailed] union in expression 1682select 1683( select a from t1 where a<3 union select a from t1 where a>4 1684order by a desc limit 1 ) as r 1685from t1; 1686r 16877 16887 16897 16907 16917 1692select 1693( (select a from t1 where a<3) union (select a from t1 where a>4) 1694order by a desc limit 1 ) as r 1695from t1; 1696r 16977 16987 16997 17007 17017 1702select 1703( select a from t1 where a<3 union select a from t1 where a>4 1704order by a desc limit 1 ) + t1.a as r 1705from t1; 1706r 170710 170814 17098 17109 171111 1712select 1713t1.a + 1714( select a from t1 where a<3 union select a from t1 where a>4 1715order by a desc limit 1 ) as r 1716from t1; 1717r 171810 171914 17208 17219 172211 1723select 1724( (select a from t1 where a<3 union select a from t1 where a>4 1725order by a desc limit 1 ) + t1.a) as r 1726from t1; 1727r 172810 172914 17308 17319 173211 1733select 1734( ( (select a from t1 where a<3) union (select a from t1 where a>4) 1735order by a desc limit 1 ) + t1.a ) as r 1736from t1; 1737r 173810 173914 17408 17419 174211 1743# 5.4. [tailed] select with simple CTE in expression 1744select 1745( with t as (select * from t1 where a <=3) 1746select a from t limit 1) as r 1747from t2; 1748r 17493 17503 17513 17523 17533 1754select 1755( with t as (select * from t1 where a <=3) 1756select a from t limit 1) + t2.b as r 1757from t2; 1758r 175933 176073 176113 176223 176343 1764select 1765t2.b +( with t as (select * from t1 where a <=3) 1766select a from t limit 1) as r 1767from t2; 1768r 176933 177073 177113 177223 177343 1774select 1775((( with t as (select * from t1 where a <=3) 1776select a from t limit 1) + t2.b)) as r 1777from t2; 1778r 177933 178073 178113 178223 178343 1784select 1785( with t as (select * from t1 where a <=3) 1786select a from t limit 1) + 100 as r 1787from t2; 1788r 1789103 1790103 1791103 1792103 1793103 1794select 1795( with t as (select * from t1 where a <=3) 1796select a from t limit 1) + (select 100) as r 1797from t2; 1798r 1799103 1800103 1801103 1802103 1803103 1804select 1805( with t as (select * from t1 where a <=3) 1806select a from t limit 1) + t2.b + (select 100) as r 1807from t2; 1808r 1809133 1810173 1811113 1812123 1813143 1814select 1815( with t as (select * from t1 where a <=3) 1816select a from t limit 1 ) + (t2.b + (select 100)) as r 1817from t2; 1818r 1819133 1820173 1821113 1822123 1823143 1824select 1825( with t as (select * from t1 where a <=3) 1826select a from t limit 1 ) + t2.b + (values (100)) as r 1827from t2; 1828r 1829133 1830173 1831113 1832123 1833143 1834# 5.5. [tailed] union with simple CTE in expression 1835select 1836( with t as (select * from t1 where a <=3) 1837select a from t union select b from t2 order by a desc limit 1) as r 1838from t2; 1839r 184070 184170 184270 184370 184470 1845select 1846( with t as (select * from t1 where a <=3) 1847(select a from t) union (select b from t2) order by a desc limit 1) as r 1848from t2; 1849r 185070 185170 185270 185370 185470 1855select 1856( with t as (select * from t1 where a <=3) 1857(select a from t) union (select b from t2) order by a desc limit 1) as r 1858from t2; 1859r 186070 186170 186270 186370 186470 1865select 1866( ( with t as (select * from t1 where a <=3) 1867(select a from t) union (select b from t2) order by a desc limit 1) + 1868t2.a ) as r 1869from t2; 1870r 187173 187277 187371 187472 187574 1876# 5.6. [tailed] union with CTE with union in expression 1877select 1878( with t as 1879( select * from t1 where a < 3 1880union 1881select * from t1 where a > 3 1882order by a desc limit 3 ) 1883select a from t1 where a=4 union select a from t where a=7 limit 1) as r 1884from t2; 1885r 18864 18874 18884 18894 18904 1891select 1892( with t as 1893( select * from t1 where a < 3 1894union 1895select * from t1 where a > 3 1896order by a desc limit 3 ) 1897select a from t1 where a=4 union select a from t where a=7 limit 1) + 1898t2. b as r 1899from t2; 1900r 190134 190274 190314 190424 190544 1906# 5.7. [tailed] union of TVCs with CTE with union in expression 1907select 1908( with t(a) as 1909( (values (2), (1)) 1910union 1911(values (4), (7) order by 1 limit 1) 1912order by 1 desc limit 3 ) select * from t limit 1 ) + t2.b as r 1913from t2; 1914r 191534 191674 191714 191824 191944 1920select 1921( with t(a) as 1922( select 2 union select 1 1923union 1924(values (4), (7) order by 1 limit 1) 1925order by 1 limit 3 ) select * from t limit 1 ) + t2.b as r 1926from t2; 1927r 192831 192971 193011 193121 193241 1933# 6. subquery 1934# 6.1. TVC in IN subquery 1935select a from t1 where a in (1,8,7); 1936a 19377 19381 1939select a from t1 where a in (values (1), (8), (7)); 1940a 19417 19421 1943# 6.2. simple select in IN subquery 1944select a from t1 where a in (select a from t2 where a <= 3); 1945a 19463 19471 19482 1949select a from t1 where a in ((select a from t2 where a <= 3)); 1950a 19513 19521 19532 1954# 6.3. union in IN subquery 1955select a from t1 1956where a in (select a from t1 where a<=2 union select a from t2 where b>40); 1957a 19587 19591 19602 1961select a from t1 1962where a in (select a from t1 where a<=2 union (select a from t2 where b>40)); 1963a 19647 19651 19662 1967select a from t1 1968where a in ((select a from t1 where a<=2) union select a from t2 where b>40); 1969a 19707 19711 19722 1973select a from t1 1974where a in ((select a from t1 where a<=2) union (select a from t2 where b>40)); 1975a 19767 19771 19782 1979# 6.4. select with CTE and union in IN subquery 1980with t as (select a from t1 where a<=2) 1981select a from t1 1982where a in ((select a from t) union (select a from t2 where b>40)); 1983a 19847 19851 19862 1987with t as ((select a from t1 where a<=2)) 1988select a from t1 1989where a in ((select a from t) union (select a from t2 where b>40)); 1990a 19917 19921 19932 1994with t as ((select a from t1 where a<=2) order by a desc limit 1) 1995select a from t1 1996where a in ((select a from t) union (select a from t2 where b>40)); 1997a 19987 19992 2000# 6.5. NOT IN subquery 2001select a from t1 where a not in (1,8,7); 2002a 20033 20042 20054 2006select a from t1 where a not in (values (1), (8), (7)); 2007a 20083 20092 20104 2011select a from t1 where a not in (select a from t2 where a <= 3); 2012a 20137 20144 2015select a from t1 where a not in ((select a from t2 where a <= 3)); 2016a 20177 20184 2019select a from t1 2020where a not in (select a from t1 where a<=2 2021union 2022select a from t2 where b>40); 2023a 20243 20254 2026select a from t1 2027where a not in (select a from t1 where a<=2 2028union 2029(select a from t2 where b>40)); 2030a 20313 20324 2033select a from t1 2034where a not in ((select a from t1 where a<=2) 2035union 2036select a from t2 where b>40); 2037a 20383 20394 2040select a from t1 2041where a not in ((select a from t1 where a<=2) 2042union 2043(select a from t2 where b>40)); 2044a 20453 20464 2047with t as ((select a from t1 where a<=2) order by a desc limit 1) 2048select a from t1 2049where a not in ((select a from t) union (select a from t2 where b>40)); 2050a 20513 20521 20534 2054# 6.6. IN subquery in expression 2055select 1 in (select a from t1) as r, b from t2 where b > 30; 2056r b 20571 70 20581 40 2059select (1 in (select a from t1)) as r, b from t2 where b > 30; 2060r b 20611 70 20621 40 2063select 1 in ((select a from t1)) as r, b from t2 where b > 30; 2064r b 20651 70 20661 40 2067select ((1 in ((select a from t1)))) as r, b from t2 where b > 30; 2068r b 20691 70 20701 40 2071select ((1 in ((select a from t1)))) as r, b from t2 where b > 30; 2072r b 20731 70 20741 40 2075select b, if (a in (select a from t1 where a > 3),10,20) as r from t2; 2076b r 207730 20 207870 10 207910 20 208020 20 208140 10 2082select b, if (a in ((select a from t1 where a > 3)),10,20) as r from t2; 2083b r 208430 20 208570 10 208610 20 208720 20 208840 10 2089# 6.7. IN subquery in SF and SP 2090create function f1(x int) returns int 2091return (x in ((select a from t1 where a <= 4))); 2092select b, f1(a) from t2 where b > 20; 2093b f1(a) 209430 1 209570 0 209640 1 2097drop function f1; 2098create function f2(x int) returns int 2099if x in ((select a from t1 where a <= 4)) 2100then return 100; 2101else return 200; 2102end if | 2103select b, f2(a) from t2 where b > 20; 2104b f2(a) 210530 100 210670 200 210740 100 2108drop function f2; 2109# 6.8. EXISTS subquery 2110select exists (select a from t1 where t1.a=t2.a) as r, b from t2 where b > 30; 2111r b 21121 70 21131 40 2114select exists ((select a from t1 where t1.a=t2.a)) as r, b from t2 where b > 30; 2115r b 21161 70 21171 40 2118with s as 2119( (select * from t1 where a <=4 order by 1 desc limit 2) 2120union 2121values (3), (8), (7) ) 2122select * from t2 where exists ((select * from s where s.a=t2.a)); 2123a b 21243 30 21257 70 21264 40 2127with t as ((select a from t1 where a<=2) order by a desc limit 1) 2128select a from t2 2129where not exists ((select a from t where t.a=t2.a) 2130except 2131(select a from t where a>40)); 2132a 21333 21347 21351 21364 2137# 6.9. EXISTS subquery with SF and SP 2138create function f1(x int) returns int 2139return exists (((select * from t1 where x=a and a <= 4))); 2140select b, f1(a) from t2 where b > 20; 2141b f1(a) 214230 1 214370 0 214440 1 2145drop function f1; 2146create function f2(x int) returns int 2147if not exists (((select * from t1 where x=a and a <= 4))) 2148then return 100; 2149else return 200; 2150end if | 2151select b, f2(a) from t2 where b > 20; 2152b f2(a) 215330 200 215470 100 215540 200 2156drop function f2; 2157# 6.10. subquery with ANY 2158select a from t1 where a = any(select a from t2 where a <= 3); 2159a 21603 21611 21622 2163select a from t1 where a = any((select a from t2 where a <= 3)); 2164a 21653 21661 21672 2168select a from t1 2169where a = any (select a from t1 where a<=2 2170union 2171select a from t2 where b>40); 2172a 21737 21741 21752 2176select a from t1 2177where a = any(select a from t1 where a<=2 2178union 2179(select a from t2 where b>40)); 2180a 21817 21821 21832 2184select a from t1 2185where a = any((select a from t1 where a<=2) 2186union 2187select a from t2 where b>40); 2188a 21897 21901 21912 2192select a from t1 2193where a = any((select a from t1 where a<=2) 2194union 2195(select a from t2 where b>40)); 2196a 21977 21981 21992 2200# 7. create table as 2201# 7.1. create table as simple select 2202create table t as select * from t1 where a <=3; 2203select * from t; 2204a 22053 22061 22072 2208drop table t; 2209create table t select * from t1 where a <=3; 2210select * from t; 2211a 22123 22131 22142 2215drop table t; 2216create table t as (select * from t1 where a <=3); 2217select * from t; 2218a 22193 22201 22212 2222drop table t; 2223create table t (select * from t1 where a <=3); 2224select * from t; 2225a 22263 22271 22282 2229drop table t; 2230create table t as ((select * from t1 where a <=3)); 2231select * from t; 2232a 22333 22341 22352 2236drop table t; 2237create table t ((select * from t1 where a <=3)); 2238select * from t; 2239a 22403 22411 22422 2243drop table t; 2244create table t(a decimal(10,2)) as select * from t1 where a <=3; 2245select * from t; 2246a 22473.00 22481.00 22492.00 2250drop table t; 2251create table t(a decimal(10,2)) select * from t1 where a <=3; 2252select * from t; 2253a 22543.00 22551.00 22562.00 2257drop table t; 2258create table t(a decimal(10,2)) as (select * from t1 where a <=3); 2259select * from t; 2260a 22613.00 22621.00 22632.00 2264drop table t; 2265create table t(a decimal(10,2)) (select * from t1 where a <=3); 2266select * from t; 2267a 22683.00 22691.00 22702.00 2271drop table t; 2272create table t(a decimal(10,2)) as ((select * from t1 where a <=3)); 2273select * from t; 2274a 22753.00 22761.00 22772.00 2278drop table t; 2279create table t(a decimal(10,2)) ((select * from t1 where a <=3)); 2280select * from t; 2281a 22823.00 22831.00 22842.00 2285drop table t; 2286create table t(a decimal(10,2), b int) as 2287((select a, a as b from t1 where a <=3)); 2288select * from t; 2289a b 22903.00 3 22911.00 1 22922.00 2 2293drop table t; 2294create table t(a decimal(10,2), b int) 2295((select a, a as b from t1 where a <=3)); 2296select * from t; 2297a b 22983.00 3 22991.00 1 23002.00 2 2301drop table t; 2302# 7.2. create table as tailed select 2303create table t as select * from t1 where a <=3 order by 1; 2304select * from t; 2305a 23061 23072 23083 2309drop table t; 2310create table t select * from t1 where a <=3 order by 1; 2311select * from t; 2312a 23131 23142 23153 2316drop table t; 2317create table t as select * from t1 where a <=3 order by 1 desc limit 2; 2318select * from t; 2319a 23203 23212 2322drop table t; 2323create table t select * from t1 where a <=3 order by 1 desc limit 2; 2324select * from t; 2325a 23263 23272 2328drop table t; 2329create table t as ((select * from t1 where a <=3) order by 1 desc) limit 2; 2330select * from t; 2331a 23323 23332 2334drop table t; 2335create table t ((select * from t1 where a <=3) order by 1 desc) limit 2; 2336select * from t; 2337a 23383 23392 2340drop table t; 2341# 7.3. create table as select wihout from clause 2342create table t as select 10; 2343select * from t; 234410 234510 2346drop table t; 2347create table t select 10; 2348select * from t; 234910 235010 2351drop table t; 2352# 7.4. create table as union of selects wihout from clause 2353create table t as select 10 union select 70; 2354select * from t; 235510 235610 235770 2358drop table t; 2359create table t select 10 union select 70; 2360select * from t; 236110 236210 236370 2364drop table t; 2365# 7.5. create table as TVC 2366create table t as values (7), (3), (8); 2367select * from t; 23687 23697 23703 23718 2372drop table t; 2373create table t values (7), (3), (8); 2374select * from t; 23757 23767 23773 23788 2379drop table t; 2380create table t as (values (7), (3), (8)); 2381select * from t; 23827 23837 23843 23858 2386drop table t; 2387create table t (values (7), (3), (8)); 2388select * from t; 23897 23907 23913 23928 2393drop table t; 2394create table t as ((values (7), (3), (8))); 2395select * from t; 23967 23977 23983 23998 2400drop table t; 2401create table t ((values (7), (3), (8))); 2402select * from t; 24037 24047 24053 24068 2407drop table t; 2408# 7.6. create table as select with CTE 2409create table t as 2410with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2) 2411select * from s; 2412select * from t; 2413a 24143 24152 2416drop table t; 2417create table t 2418with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2) 2419select * from s; 2420select * from t; 2421a 24223 24232 2424drop table t; 2425create table t as 2426with s as 2427( (select * from t1 where a <=4 order by 1 desc limit 2) 2428union 2429values (3), (8), (7) ) 2430select * from s; 2431select * from t; 2432a 24334 24343 24358 24367 2437drop table t; 2438create table t 2439with s as 2440( (select * from t1 where a <=4 order by 1 desc limit 2) 2441union 2442values (3), (8), (7) ) 2443select * from s; 2444select * from t; 2445a 24464 24473 24488 24497 2450drop table t; 2451create table t as 2452with s(a) as (select * from t1 where a <=3 order by 1 desc limit 2) 2453select * from s; 2454select * from t; 2455a 24563 24572 2458drop table t; 2459# 7.7. create table as union with CTE 2460create table t as 2461with s as 2462( (select * from t1 where a <=4 order by 1 desc limit 2) 2463union 2464values (3), (8), (7) ) 2465select * from s where a>=7 union select a from t2 where b<40; 2466select * from t; 2467a 24688 24697 24703 24711 24722 2473drop table t; 2474create table t 2475with s as 2476( (select * from t1 where a <=4 order by 1 desc limit 2) 2477union 2478values (3), (8), (7) ) 2479select * from s where a>=7 union select a from t2 where b<40; 2480select * from t; 2481a 24828 24837 24843 24851 24862 2487drop table t; 2488create table t 2489with s as 2490( (select * from t1 where a <=4 order by 1 desc limit 2) 2491union 2492values (3), (8), (7) ) 2493select * from s where a>=7 union select a from t2 where b<40; 2494select * from t; 2495a 24968 24977 24983 24991 25002 2501drop table t; 2502create table t as 2503with s as 2504( ( (select * from t1 where a <=4 order by 1 desc limit 2) 2505union 2506values (3), (8), (7) ) ) 2507select * from s where a>=7 union select a from t2 where b<40; 2508select * from t; 2509a 25108 25117 25123 25131 25142 2515drop table t; 2516create table t 2517with s as 2518( ( (select * from t1 where a <=4 order by 1 desc limit 2) 2519union 2520values (3), (8), (7) ) ) 2521select * from s where a>=7 union select a from t2 where b<40; 2522select * from t; 2523a 25248 25257 25263 25271 25282 2529drop table t; 2530create table t as 2531with s as 2532( (select * from t1 where a <=4 order by 1 desc limit 2) 2533union 2534values (3), (8), (7) ) 2535select * from s where a>=7 union select a from s where a<4; 2536select * from t; 2537a 25388 25397 25403 2541drop table t; 2542create table t 2543with s as 2544( (select * from t1 where a <=4 order by 1 desc limit 2) 2545union 2546values (3), (8), (7) ) 2547select * from s where a>=7 union select a from s where a<4; 2548select * from t; 2549a 25508 25517 25523 2553drop table t; 2554create table t as 2555with s as 2556( select * from t1 where a <=4 or a=7 ) 2557select * from s where a>=7 union select a from s where a<3; 2558select * from t; 2559a 25607 25611 25622 2563drop table t; 2564create table t 2565with s as 2566(select * from t1 where a <=4 or a=7) 2567select * from s where a>=7 union select a from s where a<3; 2568select * from t; 2569a 25707 25711 25722 2573drop table t; 2574create table t (a int) 2575with s as 2576( select * from t1 where a <=4 or a=7 ) 2577select * from s where a>=7 union select a from s where a<3; 2578select * from t; 2579a 25807 25811 25822 2583drop table t; 2584create table t (a int) 2585with s as 2586(select * from t1 where a <=4 or a=7) 2587select * from s where a>=7 union select a from s where a<3; 2588select * from t; 2589a 25907 25911 25922 2593drop table t; 2594create table t 2595with s as 2596( select * from t1 where a <=4 or a=7 ) 2597select * from s where a>=7 union select a from s where a<3 2598order by a desc limit 2; 2599select * from t; 2600a 26017 26022 2603drop table t; 2604create table t 2605( with s as 2606( select * from t1 where a <=4 or a=7 ) 2607select * from s where a>=7 union select a from s where a<3 2608order by a desc limit 2 ); 2609select * from t; 2610a 26117 26122 2613drop table t; 2614# 8. insert 2615create table t (c int, d int); 2616# 8.1. insert simple select 2617insert into t select * from t2 where a <=3; 2618select * from t; 2619c d 26203 30 26211 10 26222 20 2623delete from t; 2624insert into t(c) select t2.a from t2 where a <=3; 2625select * from t; 2626c d 26273 NULL 26281 NULL 26292 NULL 2630delete from t; 2631insert into t (select * from t2 where a <=3); 2632select * from t; 2633c d 26343 30 26351 10 26362 20 2637delete from t; 2638insert into t(c) (select t2.a from t2 where a <=3); 2639select * from t; 2640c d 26413 NULL 26421 NULL 26432 NULL 2644delete from t; 2645insert into t ((select * from t2 where a <=3)); 2646select * from t; 2647c d 26483 30 26491 10 26502 20 2651delete from t; 2652insert into t(c) ((select t2.a from t2 where a <=3)); 2653select * from t; 2654c d 26553 NULL 26561 NULL 26572 NULL 2658delete from t; 2659drop table t; 2660create table t(c decimal(10,2)); 2661insert into t select * from t1 where a <=3; 2662select * from t; 2663c 26643.00 26651.00 26662.00 2667delete from t; 2668insert into t(c) select * from t1 where a <=3; 2669select * from t; 2670c 26713.00 26721.00 26732.00 2674delete from t; 2675insert into t (select * from t1 where a <=3); 2676select * from t; 2677c 26783.00 26791.00 26802.00 2681delete from t; 2682insert into t(c) (select * from t1 where a <=3); 2683select * from t; 2684c 26853.00 26861.00 26872.00 2688delete from t; 2689insert into t ((select * from t1 where a <=3)); 2690select * from t; 2691c 26923.00 26931.00 26942.00 2695delete from t; 2696insert into t(c) ((select * from t1 where a <=3)); 2697select * from t; 2698c 26993.00 27001.00 27012.00 2702delete from t; 2703drop table t; 2704create table t(a decimal(10,2), b int); 2705insert into t ((select * from t2 where a <=3)); 2706select * from t; 2707a b 27083.00 30 27091.00 10 27102.00 20 2711delete from t; 2712insert into t(a) ((select a from t2 where a <=3)); 2713select * from t; 2714a b 27153.00 NULL 27161.00 NULL 27172.00 NULL 2718delete from t; 2719drop table t; 2720create table t(c int, d int); 2721# 8.2. insert tailed select 2722insert into t select * from t2 where a <=3 order by 1; 2723select * from t; 2724c d 27251 10 27262 20 27273 30 2728delete from t; 2729insert into t(c) select a from t2 where a <=3 order by 1; 2730select * from t; 2731c d 27321 NULL 27332 NULL 27343 NULL 2735delete from t; 2736insert into t select * from t2 where a <=3 order by 1 desc limit 2; 2737select * from t; 2738c d 27393 30 27402 20 2741delete from t; 2742insert into t(c) select a from t2 where a <=3 order by 1 desc limit 2; 2743select * from t; 2744c d 27453 NULL 27462 NULL 2747delete from t; 2748insert into t ((select * from t2 where a <=3) order by 1 desc) limit 2; 2749select * from t; 2750c d 27513 30 27522 20 2753delete from t; 2754insert into t(c) ((select a from t2 where a <=3) order by 1 desc) limit 2; 2755select * from t; 2756c d 27573 NULL 27582 NULL 2759delete from t; 2760# 8.3. insert select without from clause 2761insert into t select 10, 20; 2762select * from t; 2763c d 276410 20 2765delete from t; 2766insert into t(c) select 10; 2767select * from t; 2768c d 276910 NULL 2770delete from t; 2771# 8.4. insert union of selects without from clause 2772insert into t select 10,20 union select 70,80; 2773select * from t; 2774c d 277510 20 277670 80 2777delete from t; 2778insert into t(c) select 10 union select 70; 2779select * from t; 2780c d 278110 NULL 278270 NULL 2783delete from t; 2784# 8.5. insert TVC 2785insert into t values (7,70), (3,30), (8,80); 2786select * from t; 2787c d 27887 70 27893 30 27908 80 2791delete from t; 2792insert into t(c) values (7), (3), (8); 2793select * from t; 2794c d 27957 NULL 27963 NULL 27978 NULL 2798delete from t; 2799insert into t (values (7,70), (3,30), (8,80)); 2800select * from t; 2801c d 28027 70 28033 30 28048 80 2805delete from t; 2806insert into t(c) (values (7), (3), (8)); 2807select * from t; 2808c d 28097 NULL 28103 NULL 28118 NULL 2812delete from t; 2813insert into t ((values (7,70), (3,30), (8,80))); 2814select * from t; 2815c d 28167 70 28173 30 28188 80 2819delete from t; 2820insert into t(c) ((values (7), (3), (8))); 2821select * from t; 2822c d 28237 NULL 28243 NULL 28258 NULL 2826delete from t; 2827# 8.7. insert simple select with CTE 2828insert into t 2829with s(a,b) as (select * from t2 where a <=3 order by 1 desc limit 2) 2830select * from s; 2831select * from t; 2832c d 28333 30 28342 20 2835delete from t; 2836insert into t(c) 2837with s(a) as (select a from t2 where a <=3 order by 1 desc limit 2) 2838select * from s; 2839select * from t; 2840c d 28413 NULL 28422 NULL 2843delete from t; 2844insert into t 2845with s as 2846( (select * from t2 where a <=4 order by 1 desc limit 2) 2847union 2848values (3,30), (8,80), (7,70) ) 2849select * from s; 2850select * from t; 2851c d 28524 40 28533 30 28548 80 28557 70 2856delete from t; 2857insert into t(c) 2858with s as 2859( (select a from t2 where a <=4 order by 1 desc limit 2) 2860union 2861values (3), (8), (7) ) 2862select * from s; 2863select * from t; 2864c d 28654 NULL 28663 NULL 28678 NULL 28687 NULL 2869delete from t; 2870# 8.8. insert into union with CTE 2871insert into t(c) 2872with s as 2873( (select a from t2 where a <=4 order by 1 desc limit 2) 2874union 2875values (3), (8), (7) ) 2876select * from s where a>=7 union select a from t2 where b<40; 2877select * from t; 2878c d 28798 NULL 28807 NULL 28813 NULL 28821 NULL 28832 NULL 2884delete from t; 2885insert into t 2886with s as 2887( (select * from t2 where a <=4 order by 1 desc limit 2) 2888union 2889values (3,30), (8,80), (7,70) ) 2890select * from s where a>=7 union select * from s where a<4; 2891select * from t; 2892c d 28938 80 28947 70 28953 30 2896delete from t; 2897insert into t(c) 2898with s as 2899( (select a from t2 where a <=4 order by 1 desc limit 2) 2900union 2901values (3), (8), (7) ) 2902select * from s where a>=7 union select * from s where a<4; 2903select * from t; 2904c d 29058 NULL 29067 NULL 29073 NULL 2908delete from t; 2909insert into t 2910with s as 2911( select * from t2 where a <=4 or a=7 ) 2912select * from s where a>=7 union select * from s where a<3; 2913select * from t; 2914c d 29157 70 29161 10 29172 20 2918delete from t; 2919insert into t(c) 2920with s as 2921( select a from t2 where a <=4 or a=7 ) 2922select * from s where a>=7 union select * from s where a<3; 2923select * from t; 2924c d 29257 NULL 29261 NULL 29272 NULL 2928delete from t; 2929drop table t; 2930# 9. derived table 2931# 9.1. derived table as [tailed] simple select 2932select * from (select * from t1) as dt; 2933a 29343 29357 29361 29372 29384 2939select * from ((select * from t1)) as dt; 2940a 29413 29427 29431 29442 29454 2946select * from (((select * from t1))) as dt; 2947a 29483 29497 29501 29512 29524 2953select * from (select * from t1 order by a) as dt; 2954a 29553 29567 29571 29582 29594 2960select * from (select a from t1 order by a) as dt; 2961a 29623 29637 29641 29652 29664 2967select * from (select a from t1 order by 1) as dt; 2968a 29693 29707 29711 29722 29734 2974select * from (select a from t1 order by t1.a) as dt; 2975a 29763 29777 29781 29792 29804 2981select * from ((select * from t1 order by t1.a limit 2)) as dt; 2982a 29831 29842 2985select * from ((select * from t2 order by a limit 2) order by b desc) dt; 2986a b 29871 10 29882 20 2989select * from ((select a from t1 where a=1) order by 1 desc) dt; 2990a 29911 2992# 9.2. derived table as select with two tails 2993select * from 2994((select * from t2 order by t2.a limit 2) order by b desc) dt; 2995a b 29961 10 29972 20 2998select * from 2999((select * from t2 order by t2.a limit 2) order by b desc) as dt; 3000a b 30011 10 30022 20 3003select * from 3004(((select * from t2 order by t2.a limit 2) order by b desc )) as dt; 3005a b 30061 10 30072 20 3008select * from 3009(((select * from t2 order by t2.a) limit 2) order by b desc) dt; 3010a b 30111 10 30122 20 3013select * from 3014((select * from t2 order by a limit 2) order by b desc) dt; 3015a b 30161 10 30172 20 3018select * from 3019((select a from t1 where a=1) order by 1 desc) as dt; 3020a 30211 3022select * from 3023((select * from t2 order by t2.a limit 2) order by b desc) as dt; 3024a b 30251 10 30262 20 3027# 9.3. derived table as union 3028select * from (select a from t1 union select a from t1) as dt; 3029a 30303 30317 30321 30332 30344 3035select * from (select a from t1 union all select a from t1) as dt; 3036a 30373 30387 30391 30402 30414 30423 30437 30441 30452 30464 3047select * from (select a from t1 union select b from t2) as dt; 3048a 30493 30507 30511 30522 30534 305430 305570 305610 305720 305840 3059select * from 3060((select a from t1) union (select a from t1)) as dt; 3061a 30623 30637 30641 30652 30664 3067select * from 3068((select a from t1) union (select b from t2)) as dt; 3069a 30703 30717 30721 30732 30744 307530 307670 307710 307820 307940 3080select * from 3081(select a from t1 where a=1 union select a from t1 where a=3) dt; 3082a 30831 30843 3085select * from 3086((select a from t1 where a=1) union select a from t1 where a=3) dt; 3087a 30881 30893 3090select * from 3091(((select a from t1 where a=1) union select a from t1 where a=3)) dt; 3092a 30931 30943 3095select * from 3096(((select a from t1 where a<=3) union (select a from t1 where a=3))) as dt; 3097a 30983 30991 31002 3101select * from 3102(select a from t1 where a=1 union (select a from t1 where a=3)) as dt; 3103a 31041 31053 3106select * from 3107((select a from t1 where a=1 union (select a from t1 where a=3))) as dt; 3108a 31091 31103 3111select * from 3112(((select a from t1 where a=1 union (select a from t1 where a=3)))) as dt; 3113a 31141 31153 3116select * from 3117( select a from t1 where a=1 3118union 3119select a from t1 where a=3 3120union 3121select a from t1 where a=7 ) as dt; 3122a 31231 31243 31257 3126select * from 3127( (select a from t1 where a=1 order by a) 3128union 3129select a from t1 where a=3 ) as dt; 3130a 31311 31323 3133select * from 3134( (select a from t1 where a!=3 order by a desc) 3135union 3136select a from t1 where a=3 ) as dt; 3137a 31387 31391 31402 31414 31423 3143select * from 3144( ( select a from t1 where a <=3 except select a from t1 where a >=3 ) 3145union 3146select a from t1 where a=7 ) as dt; 3147a 31481 31492 31507 3151select * from 3152( ( ( select a from t1 where a <=3 3153except 3154select a from t1 where a >=3 ) 3155union 3156select a from t1 where a=7 ) ) as dt; 3157a 31581 31592 31607 3161select * from 3162( select a from t1 where a=1 3163union 3164select a from t1 where a=3 3165order by a desc) as dt; 3166a 31673 31681 3169select *from 3170( (select a from t1 limit 2) 3171union 3172select a from t1 where a=3 3173order by a desc) as dt; 3174a 31757 31763 3177select * from 3178( select a from t1 where a=4 3179union 3180(select a from t1 where a <=4 limit 2) 3181order by a desc ) as dt; 3182a 31834 31843 31851 3186select * from 3187( ( select a from t1 where a=4 3188union 3189( select a from t1 where a <=4 order by a ) ) 3190order by a desc limit 2 ) as dt; 3191a 31924 31933 3194select * from 3195( ( select a from t1 where a <=3 except select a from t1 where a >=3 ) 3196union 3197select a from t1 where a=7 order by a desc ) as dt; 3198a 31997 32002 32011 3202select * from 3203( ( select a from t1 where a!=3 order by a desc ) 3204union 3205select a from t1 where a=3 3206order by a desc ) as dt; 3207a 32087 32094 32103 32112 32121 3213select * from 3214( (select a from t1 where a=1) 3215union 3216(select a from t1 where a=3) 3217order by a desc ) as dt; 3218a 32193 32201 3221select * from 3222( ( select a from t1 where a=1 3223union 3224select a from t1 where a=3 ) 3225order by a desc ) as dt; 3226a 32273 32281 3229select * from 3230( ( ( select a from t1 where a=1 ) 3231union 3232( select a from t1 where a=3 ) ) 3233order by a desc ) as dt; 3234a 32353 32361 3237select * from 3238( ( select a from t1 where a=1 3239union 3240select a from t1 where a=3 ) 3241order by 1 desc ) as dt; 3242a 32433 32441 3245select * from 3246( ( (select a from t1 where a=1 3247union 3248select a from t1 where a=3) ) order by 1 desc ) as dt; 3249a 32503 32511 3252select * from 3253((((select a from t1 where a=1) union (select a from t1 where a=3))) 3254order by 1 desc ) as dt; 3255a 32563 32571 3258select * from 3259( ( (select a from t1 where a=1 ) 3260union 3261(select a from t1 where a=3) ) 3262order by 1 desc ) as dt; 3263a 32643 32651 3266select * from 3267( select a from t1 where a=1 3268union 3269select a from t1 where a=3 3270union 3271select a from t1 where a=2 3272union 3273select a from t1 where a=4 ) as dt; 3274a 32751 32763 32772 32784 3279select * from 3280( ( select a from t1 where a=1 3281union 3282select a from t1 where a=3 3283union 3284select a from t1 where a=2 ) 3285union 3286select a from t1 where a=4 ) as dt; 3287a 32881 32893 32902 32914 3292select * from 3293( (select a from t1 where a=1 union select a from t1 where a=3) 3294union 3295(select a from t1 where a=2 union select a from t1 where a=4) ) as dt; 3296a 32971 32983 32992 33004 3301select * from 3302( (select a from t1 where a=1 union (select a from t1 where a=3)) 3303union 3304((select a from t1 where a=2) union select a from t1 where a=4) ) as dt; 3305a 33061 33073 33082 33094 3310select * from 3311( ( ( select a from t1 where a=1) 3312union 3313select a from t1 where a=3 ) 3314union 3315select a from t1 where a=2 3316union 3317select a from t1 where a=4 ) as dt; 3318a 33191 33203 33212 33224 3323select * from 3324( ( ( ( select a from t1 where a=1) 3325union 3326select a from t1 where a=3 ) 3327union 3328select a from t1 where a=2 ) 3329union 3330select a from t1 where a=4 ) as dt; 3331a 33321 33333 33342 33354 3336select * from 3337( select a from t1 where a=1 3338union 3339select a from t1 where a=3 3340union 3341select a from t1 where a=2 3342union 3343(select a from t1 where a=4) ) as dt; 3344a 33451 33463 33472 33484 3349select * from 3350( select a from t1 where a=1 3351union 3352select a from t1 where a=3 3353union 3354( select a from t1 where a=2 3355union 3356( select a from t1 where a=4 ) ) ) as dt; 3357a 33581 33593 33602 33614 3362select * from 3363( select a from t1 where a=1 3364union 3365( select a from t1 where a=3 3366union 3367( select a from t1 where a=2 3368union 3369( select a from t1 where a=4 ) ) ) ) as dt; 3370a 33711 33723 33732 33744 3375select * from 3376( ( ( select a from t1 where a=1 union select a from t1 where a=3 ) 3377order by a desc limit 2 ) 3378union 3379( ( select a from t1 where a=2 union select a from t1 where a=4 ) 3380order by a desc limit 1 ) ) as dt; 3381a 33823 33831 33844 3385select * from 3386( ( ( select a from t1 where a=1 union select a from t1 where a=3 ) 3387order by a desc limit 2 ) 3388union 3389( ( select a from t1 where a=2 union select a from t1 where a=4 ) 3390order by a desc limit 2 ) 3391order by a) as dt; 3392a 33931 33942 33953 33964 3397select * from 3398( ( select a from t1 where a=1 3399union 3400select a from t1 where a=3 3401union 3402select a from t1 where a=2 order by a desc limit 2 ) 3403union 3404select a from t1 where a=4 3405order by a limit 3 ) as dt; 3406a 34072 34083 34094 3410select * from 3411( ( select a from t1 where a=1 3412union 3413select a from t1 where a=3 order by a desc limit 2) 3414union 3415select a from t1 where a=2 order by a desc limit 2 ) as dt; 3416a 34173 34182 3419select * from 3420( ( ( select a from t1 where a >= 2 3421union 3422select a from t1 where a=1 order by a desc limit 2 ) 3423union 3424select a from t1 where a=3 order by a limit 2 ) 3425union 3426select a from t1 where a=1 ) as dt; 3427a 34283 34294 34301 3431# 9.3. derived table as [tailed] TVC 3432select * from 3433( values (3), (7), (1) ) as dt; 34343 34353 34367 34371 3438select * from 3439( (values (3), (7), (1)) ) as dt; 34403 34413 34427 34431 3444select * from 3445(((values (3), (7), (1)))) as dt; 34463 34473 34487 34491 3450select * from 3451( values (3), (7), (1) order by 1 limit 2 ) as dt; 34523 34531 34543 3455select * from 3456( (values (3), (7), (1)) order by 1 limit 2 ) as dt; 34573 34581 34593 3460select * from 3461( ((values (3), (7), (1))) order by 1 limit 2 ) as dt; 34623 34631 34643 3465select * from 3466( (((values (3), (7), (1))) order by 1 limit 2) ) as dt; 34673 34681 34693 3470select * from 3471( ( (values (3), (7), (1) limit 2) order by 1 desc) ) as dt; 34723 34733 34747 3475select * from 3476( ((values (3), (7), (1)) order by 1 desc) limit 2 ) as dt; 34773 34787 34793 3480select * from 3481( (((values (3), (7), (1)) order by 1 desc) limit 2) ) as dt; 34823 34837 34843 3485# 9.3. derived table as union of TVCs 3486select * from 3487( values (3), (7), (1) union values (3), (4), (2) ) dt; 34883 34893 34907 34911 34924 34932 3494select * from 3495( values (3), (7), (1) union all values (3), (4), (2) ) as dt; 34963 34973 34987 34991 35003 35014 35022 3503select * from 3504( values (3), (7), (1) union values (3), (4), (2) ) as dt; 35053 35063 35077 35081 35094 35102 3511select * from 3512( values (3), (7), (1) except values (3), (4), (2) ) as dt; 35133 35147 35151 3516select * from 3517( (values (3), (7), (1)) union (values (3), (4), (2)) ) as dt; 35183 35193 35207 35211 35224 35232 3524select * from 3525( (values (3), (7), (1)) 3526union 3527(values (3), (4), (2)) 3528union values (5), (7) ) dt; 35293 35303 35317 35321 35334 35342 35355 3536select * from 3537( (values (3), (7), (1)) 3538union 3539(values (3), (4), (2)) 3540union 3541(values (5), (7)) ) as dt; 35423 35433 35447 35451 35464 35472 35485 3549select * from 3550( (values (3), (7), (1) 3551union 3552values (3), (4), (2)) 3553union 3554values (5), (7) ) as dt; 35553 35563 35577 35581 35594 35602 35615 3562select * from 3563( values (3), (7), (1) 3564union (values (3), (4), (2) 3565union 3566values (5), (7)) ) as dt; 35673 35683 35697 35701 35714 35722 35735 3574select * from 3575( (values (3), (7), (1) 3576union 3577((values (3), (4), (2) 3578union values (5), (7)))) ) dt; 35793 35803 35817 35821 35834 35842 35855 3586select * from 3587( values (3), (7), (1) 3588union 3589values (3), (4), (2) 3590order by 1 ) as dt; 35913 35921 35932 35943 35954 35967 3597select * from 3598( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt; 35993 36001 36012 36023 36034 36047 3605select * from 3606( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt; 36073 36081 36092 36103 36114 36127 3613select * from 3614( values (3), (7), (1) union (values (3), (4), (2)) order by 1 ) as dt; 36153 36161 36172 36183 36194 36207 3621select * from 3622( (values (3), (7), (1) union values (3), (4), (2)) order by 1 ) as dt; 36233 36241 36252 36263 36274 36287 3629select * from 3630( ((values (3), (7), (1)) union values (3), (4), (2)) order by 1 ) as dt; 36313 36321 36332 36343 36354 36367 3637select * from 3638( (values (3), (7), (1) order by 1 limit 2) 3639union 3640(values (3), (4), (2) order by 1 desc limit 2) ) as dt; 36413 36421 36433 36444 3645select * from 3646( ((values (3), (7), (1) order by 1) limit 2) 3647union 3648((values (3), (4), (2) order by 1 desc) limit 2) ) as dt; 36493 36501 36513 36524 3653select * from 3654( (((values (3), (7), (1)) order by 1) limit 2) 3655union 3656(((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt; 36573 36581 36593 36604 3661select * from 3662( (values (3), (7), (1) order by 1 limit 2) 3663union 3664values (3), (4), (2) 3665order by 1 limit 3 ) as dt; 36663 36671 36682 36693 3670select * from 3671( ((values (3), (7), (1)) order by 1 limit 2) 3672union 3673((values (3), (4), (2) order by 1 desc) limit 2) 3674order by 1 limit 3 ) as dt; 36753 36761 36773 36784 3679select * from 3680( (select a from t1 where a <=3 order by 1 limit 2) 3681union 3682(values (3), (4), (2) order by 1 desc limit 2) ) dt; 3683a 36841 36852 36864 36873 3688select * from 3689( ((select a from t1 where a <=3) order by 1 limit 2) 3690union 3691(values (3), (4), (2) order by 1 desc limit 2) ) as dt; 3692a 36931 36942 36954 36963 3697select * from 3698( (((select a from t1 where a <=3) order by a) limit 2) 3699union 3700(((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt; 3701a 37021 37032 37044 37053 3706select * from 3707( ( (((select a from t1 where a <=3) order by a) limit 2) 3708union 3709(((values (3), (4), (2)) order by 1 desc) limit 2) ) ) dt; 3710a 37111 37122 37134 37143 3715select * from 3716( (select a from t1 where a <=3 order by 1 limit 2) 3717union 3718(values (3), (4), (2) order by 1 desc limit 2) 3719order by a ) as dt; 3720a 37211 37222 37233 37244 3725select * from 3726( ((select a from t1 where a <=3) order by 1 limit 2) 3727union 3728(values (3), (4), (2) order by 1 desc limit 2) 3729order by a ) as dt; 3730a 37311 37322 37333 37344 3735select * from 3736( (((select a from t1 where a <=3) order by a) limit 2) 3737union 3738(((values (3), (4), (2)) order by 1 desc) limit 2) 3739order by a ) as dt; 3740a 37411 37422 37433 37444 3745select * from 3746( (((values (3), (4), (2)) order by 1 desc) limit 2) ) as dt; 37473 37484 37493 3750select * from 3751( ( (((select a from t1 where a <=3) order by a) limit 2) 3752union 3753(((values (3), (4), (2)) order by 1 desc) limit 2) ) 3754order by a ) as dt; 3755a 37561 37572 37583 37594 3760select * from 3761( (values (3), (4), (2) order by 1 desc limit 2) 3762union 3763(select a from t1 where a <=3 order by 1 limit 2) ) as dt; 37643 37654 37663 37671 37682 3769select * from 3770( (values (3), (4), (2) order by 1 desc limit 2) 3771union 3772((select a from t1 where a <=3) order by 1 limit 2) ) as dt; 37733 37744 37753 37761 37772 3778select * from 3779( (((values (3), (4), (2)) order by 1 desc) limit 2) 3780union 3781(((select a from t1 where a <=3) order by 1) limit 2) ) as dt; 37823 37834 37843 37851 37862 3787select * from 3788( (((values (3), (4), (2)) order by 1 desc) limit 2) 3789union 3790(((select a from t1 where a <=3) order by a) limit 2) 3791order by 1 ) as dt; 37923 37931 37942 37953 37964 3797select * from 3798( ( select a from t1 where a=1 3799union 3800values (3), (4), (2) order by 1 desc ) 3801union 3802select a from t1 where a=2 order by a desc limit 3 ) as dt; 3803a 38044 38053 38062 3807# 9.4. derived table as [tailed] simple select with CTE 3808select * from 3809( with t as (select * from t1 where a <=3) 3810select * from t ) as dt; 3811a 38123 38131 38142 3815select * from 3816( with t as (select * from t1 where a <=3) 3817(select * from t) ) as dt; 3818a 38193 38201 38212 3822select * from 3823( with t as (select * from t1 where a <=3) 3824((select * from t)) ) as dt; 3825a 38263 38271 38282 3829select * from 3830( with t as ((select * from t1 where a <=3)) 3831select * from t ) as dt; 3832a 38333 38341 38352 3836select * from 3837( with t as (((select * from t1 where a <=3))) 3838select * from t ) as dt; 3839a 38403 38411 38422 3843select * from 3844( with t as (select * from t1 where a <=3) 3845select * from t order by a ) as dt; 3846a 38473 38481 38492 3850select * from 3851( with t as (select * from t1 where a <=3) 3852(select * from t) order by a ) as dt; 3853a 38543 38551 38562 3857select * from 3858( with t as (select * from t1 where a <=3) 3859(select * from t) order by a desc limit 2 ) as dt; 3860a 38613 38622 3863select * from 3864( with t as (select * from t1 where a >=2 order by a limit 2) 3865select * from t ) as dt; 3866a 38672 38683 3869select * from 3870( with t as (((select * from t1 where a >=2) order by a desc) limit 2) 3871select * from t ) as dt; 3872a 38737 38744 3875select * from 3876( with t as (select * from t1 where a >=2 order by a desc limit 2) 3877select * from t order by a ) as dt; 3878a 38797 38804 3881# 9.5. derived table as tailed union with CTE 3882select * from 3883( with t as (select * from t1 where a <=3) 3884select a from t1 where a=1 union select a from t where a=3 ) as dt; 3885a 38861 38873 3888select * from 3889( with t as (select * from t1 where a <=3) 3890(select a from t) union (select b from t2) ) as dt; 3891a 38923 38931 38942 389530 389670 389710 389820 389940 3900select * from 3901( with t as (select * from t1 where a <=3) 3902(select a from t) union (select b as a from t2) order by a desc ) as dt; 3903a 390470 390540 390630 390720 390810 39093 39102 39111 3912select * from 3913( with t as (select * from t1 where a < 3 union select * from t1 where a > 3) 3914select a from t1 where a=1 union select a from t where a=7 ) as dt; 3915a 39161 39177 3918select * from 3919( with t as 3920( select * from t1 where a < 3 3921union 3922select * from t1 where a > 3 3923order by a desc limit 3 ) 3924select a from t1 where a=4 union select a from t where a=7 ) as dt; 3925a 39264 39277 3928select * from 3929( with t as 3930( select * from t1 where a < 3 3931union 3932select * from t1 where a > 3 3933order by a desc limit 3 ) 3934select a from t1 where a=4 3935union 3936select a from t where a=7 3937order by a desc ) as dt; 3938a 39397 39404 3941select * from 3942( with t as 3943( (select * from t1 where a < 3) 3944union 3945(select * from t1 where a > 3) 3946order by a desc limit 3 ) 3947select a from t1 where a=4 3948union select a from t where a=7 3949order by a desc ) dt; 3950a 39517 39524 3953select * from 3954( with t as 3955( (select * from t1 where a < 3) 3956union 3957(select * from t1 where a > 3) 3958order by a desc limit 3 ) 3959(select a from t1 where a=4 3960union 3961select a from t where a=7 3962order by a desc) ) as dt; 3963a 39647 39654 3966select * from 3967( with t as 3968( (select * from t1 where a < 3) 3969union 3970(select * from t1 where a > 3) 3971order by a desc limit 3 ) 3972((select a from t1 where a=4 3973union 3974select a from t where a=7) order by a desc) ) as dt; 3975a 39767 39774 3978select * from 3979( with t as 3980( select * from t1 where a < 3 3981union 3982values (4), (7) 3983order by a desc limit 3 ) 3984select a from t1 where a=4 3985union 3986select a from t where a=7 3987order by a desc ) dt; 3988a 39897 39904 3991select * from 3992( with t(a) as 3993( values (2), (1) 3994union 3995(values (4), (7)) 3996order by 1 desc limit 3 ) 3997select a from t1 where a=4 3998union select a from t where a=7 3999order by a desc ) as dt; 4000a 40017 40024 4003select * from 4004( with t(a) as 4005( (values (2), (1)) 4006union 4007(values (4), (7) order by 1 desc) 4008order by 1 desc limit 3 ) 4009select a from t1 where a=1 4010union 4011select a from t where a=7 order by a desc ) as dt; 4012a 40137 40141 4015select * from 4016( with t(a) as 4017( (values (2), (1)) 4018union 4019(values (4), (7) order by 1 desc) 4020order by 1 limit 3 ) 4021select a from t where a=1 union values (7) order by a desc ) as dt; 4022a 40237 40241 4025select * from 4026( with t(a) as 4027( (values (2), (1)) 4028union 4029(values (4), (7) order by 1 desc ) ) 4030select a from t where a=1 union select 7 order by a desc ) as dt; 4031a 40327 40331 4034select * from 4035( with t as (select * from t1 where a < 3), 4036s as (select * from t1 where a > 3) 4037select a from t where a=1 4038union select a from s where a=7 4039order by a desc ) dt; 4040a 40417 40421 4043select * from 4044( with t as (select * from t1 where a < 3), 4045s as (select * from t1 where a > 3) 4046(select a from t where a=1 4047union 4048select a from s where a=7 order by a desc) ) dt; 4049a 40507 40511 4052select * from 4053( with t as (select * from t1 where a < 3), 4054s as (select * from t1 where a > 3) 4055(select a from t where a=1 4056union 4057select a from s where a=7) 4058order by a desc ) dt; 4059a 40607 40611 406210. view 406310.1. view as simple select 4064create view v1 as 4065select * from t1; 4066show create view v1; 4067View Create View character_set_client collation_connection 4068v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci 4069select * from v1; 4070a 40713 40727 40731 40742 40754 4076drop view v1; 4077create view v1 as 4078select 2*a as c from t1; 4079show create view v1; 4080View Create View character_set_client collation_connection 4081v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 2 * `t1`.`a` AS `c` from `t1` latin1 latin1_swedish_ci 4082select * from v1; 4083c 40846 408514 40862 40874 40888 4089drop view v1; 4090create view v1(c) as 4091select 2*a from t1; 4092show create view v1; 4093View Create View character_set_client collation_connection 4094v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 2 * `t1`.`a` AS `c` from `t1` latin1 latin1_swedish_ci 4095select * from v1; 4096c 40976 409814 40992 41004 41018 4102drop view v1; 4103create view v1 as 4104((select * from t1)); 4105show create view v1; 4106View Create View character_set_client collation_connection 4107v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1`) latin1 latin1_swedish_ci 4108select * from v1; 4109a 41103 41117 41121 41132 41144 4115drop view v1; 411610.2. view as tailed simple select 4117create view v1 as 4118select * from t1 order by a; 4119show create view v1; 4120View Create View character_set_client collation_connection 4121v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` order by `t1`.`a` latin1 latin1_swedish_ci 4122select * from v1; 4123a 41241 41252 41263 41274 41287 4129drop view v1; 4130create view v1 as 4131(select * from t2 order by a limit 2) order by b desc; 4132show create view v1; 4133View Create View character_set_client collation_connection 4134v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__3`.`a` AS `a`,`__3`.`b` AS `b` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`a` limit 2) `__3` order by `__3`.`b` desc latin1 latin1_swedish_ci 4135select * from v1; 4136a b 41372 20 41381 10 4139drop view v1; 414010.3. view as union 4141create view v1 as 4142select a from t1 union select b from t2; 4143show create view v1; 4144View Create View character_set_client collation_connection 4145v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` union select `t2`.`b` AS `b` from `t2` latin1 latin1_swedish_ci 4146select * from v1; 4147a 41483 41497 41501 41512 41524 415330 415470 415510 415620 415740 4158drop view v1; 4159create view v1 as 4160(select a from t1) union (select b from t2); 4161show create view v1; 4162View Create View character_set_client collation_connection 4163v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1`) union (select `t2`.`b` AS `b` from `t2`) latin1 latin1_swedish_ci 4164select * from v1; 4165a 41663 41677 41681 41692 41704 417130 417270 417310 417420 417540 4176drop view v1; 4177create view v1 as 4178(select a from t1 where a=1) union select a from t1 where a=3; 4179show create view v1; 4180View Create View character_set_client collation_connection 4181v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1) union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 latin1 latin1_swedish_ci 4182select * from v1; 4183a 41841 41853 4186drop view v1; 4187create view v1 as 4188((select a from t1 where a<=3) union (select a from t1 where a=3)); 4189show create view v1; 4190View Create View character_set_client collation_connection 4191v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3) latin1 latin1_swedish_ci 4192select * from v1; 4193a 41943 41951 41962 4197drop view v1; 4198create view v1 as 4199select a from t1 where a=1 4200union 4201select a from t1 where a=3 4202union 4203select a from t1 where a=7; 4204show create view v1; 4205View Create View character_set_client collation_connection 4206v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 7 latin1 latin1_swedish_ci 4207select * from v1; 4208a 42091 42103 42117 4212drop view v1; 4213create view v1 as 4214( ( select a from t1 where a!=3 order by a desc limit 3) 4215union 4216select a from t1 where a=3 ); 4217show create view v1; 4218View Create View character_set_client collation_connection 4219v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <> 3 order by `t1`.`a` desc limit 3) union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 latin1 latin1_swedish_ci 4220select * from v1; 4221a 42227 42234 42242 42253 4226drop view v1; 4227create view v1 as 4228( select a from t1 where a <=3 except select a from t1 where a >=3 ) 4229union 4230select a from t1 where a=7; 4231show create view v1; 4232View Create View character_set_client collation_connection 4233v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__5`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` <= 3 except select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` >= 3) `__5` union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 7 latin1 latin1_swedish_ci 4234select * from v1; 4235a 42361 42372 42387 4239drop view v1; 4240create view v1 as 4241(select a from t1 limit 2) union select a from t1 where a=3 order by a desc; 4242show create view v1; 4243View Create View character_set_client collation_connection 4244v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` limit 2) union select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 3 order by `a` desc latin1 latin1_swedish_ci 4245select * from v1; 4246a 42477 42483 4249drop view v1; 4250create view v1 as 4251select a from t1 where a=1 4252union 4253( select a from t1 where a=3 4254union 4255( select a from t1 where a=2 4256union 4257( select a from t1 where a=4 ) ) ); 4258show create view v1; 4259View Create View character_set_client collation_connection 4260v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union select `__7`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 3 union select `__6`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 union (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4)) `__6`) `__7` latin1 latin1_swedish_ci 4261select * from v1; 4262a 42631 42643 42652 42664 4267drop view v1; 4268create view v1 as 4269( ( select a from t1 where a >= 2 4270union 4271select a from t1 where a=1 order by a desc limit 2 ) 4272union 4273select a from t1 where a=3 order by a limit 2 ) 4274union 4275select a from t1 where a=1; 4276show create view v1; 4277View Create View character_set_client collation_connection 4278v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__7`.`a` AS `a` from (select `__5`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` >= 2 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 order by `a` desc limit 2) `__5` union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 3 order by `a` limit 2) `__7` union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 latin1 latin1_swedish_ci 4279select * from v1; 4280a 42813 42824 42831 4284drop view v1; 428510.4. view as [tailed] TVC 4286create view v1 as 4287values (3), (7), (1); 4288show create view v1; 4289View Create View character_set_client collation_connection 4290v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (3),(7),(1) latin1 latin1_swedish_ci 4291select * from v1; 42923 42933 42947 42951 4296drop view v1; 4297create view v1 as 4298(((values (3), (7), (1))) order by 1); 4299show create view v1; 4300View Create View character_set_client collation_connection 4301v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (3),(7),(1) order by 1) latin1 latin1_swedish_ci 4302select * from v1; 43033 43041 43053 43067 4307drop view v1; 430810.5. view as [tailed] union of TVCs 4309create view v1 as 4310values (3), (7), (1) union values (3), (4), (2); 4311show create view v1; 4312View Create View character_set_client collation_connection 4313v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (3),(7),(1) union values (3),(4),(2) latin1 latin1_swedish_ci 4314select * from v1; 43153 43163 43177 43181 43194 43202 4321drop view v1; 4322create view v1 as 4323(values (3), (7), (1) union values (3), (4), (2)) order by 1; 4324show create view v1; 4325View Create View character_set_client collation_connection 4326v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (3),(7),(1) union values (3),(4),(2) order by 1 latin1 latin1_swedish_ci 4327select * from v1; 43283 43291 43302 43313 43324 43337 4334drop view v1; 4335create view v1 as 4336(values (3), (7), (1) order by 1 limit 2) 4337union 4338(values (3), (4), (2) order by 1 desc limit 2); 4339show create view v1; 4340View Create View character_set_client collation_connection 4341v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (3),(7),(1) order by 1 limit 2) union (values (3),(4),(2) order by 1 desc limit 2) latin1 latin1_swedish_ci 4342select * from v1; 43433 43441 43453 43464 4347drop view v1; 4348create view v1 as 4349(values (3), (7), (1) order by 1 limit 2) 4350union 4351values (3), (4), (2) 4352order by 1; 4353show create view v1; 4354View Create View character_set_client collation_connection 4355v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (3),(7),(1) order by 1 limit 2) union values (3),(4),(2) order by 1 latin1 latin1_swedish_ci 4356select * from v1; 43573 43581 43592 43603 43614 4362drop view v1; 436310.6. view as [tailed] union of [tailed] select and tailed TVC 4364create view v1 as 4365( (((select a from t1 where a <=3) order by a) limit 2) 4366union 4367(((values (3), (4), (2)) order by 1 desc) limit 2) ) 4368order by a; 4369show create view v1; 4370View Create View character_set_client collation_connection 4371v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3 order by `t1`.`a` limit 2) union (values (3),(4),(2) order by 1 desc limit 2) order by `a` latin1 latin1_swedish_ci 4372select * from v1; 4373a 43741 43752 43763 43774 4378drop view v1; 4379create view v1 as 4380( select a from t1 where a=1 4381union 4382values (3), (4), (2) order by 1 desc ) 4383union 4384select a from t1 where a=2 order by a desc limit 3; 4385show create view v1; 4386View Create View character_set_client collation_connection 4387v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `__5`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union values (3),(4),(2) order by 1 desc) `__5` union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 order by `a` desc limit 3 latin1 latin1_swedish_ci 4388select * from v1; 4389a 43904 43913 43922 4393drop view v1; 439410.7. view as select with CTE 4395create view v1 as 4396with t as (select * from t1 where a <=3) 4397select * from t; 4398show create view v1; 4399View Create View character_set_client collation_connection 4400v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` <= 3)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci 4401select * from v1; 4402a 44033 44041 44052 4406drop view v1; 4407create view v1 as 4408with t as 4409( select * from t1 where a < 3 4410union 4411select * from t1 where a > 3 4412order by a desc limit 3 ) 4413select a from t1 where a=4 union select a from t where a=7; 4414show create view v1; 4415View Create View character_set_client collation_connection 4416v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3 order by `a` desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 latin1 latin1_swedish_ci 4417select * from v1; 4418a 44194 44207 4421drop view v1; 442210.8. view as union with CTE 4423create view v1 as 4424with t as 4425( (select * from t1 where a < 3) 4426union 4427(select * from t1 where a > 3) 4428order by a desc limit 3 ) 4429(select a from t1 where a=4 union select a from t where a=7 order by a desc); 4430show create view v1; 4431View Create View character_set_client collation_connection 4432v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) union (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3) order by `a` desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci 4433select * from v1; 4434a 44357 44364 4437drop view v1; 4438create view v1 as 4439with t as 4440( (select * from t1 where a < 3) 4441union 4442(select * from t1 where a > 3) 4443order by a desc limit 3 ) 4444(select a from t where a=4 union select a from t where a=7 order by a desc); 4445show create view v1; 4446View Create View character_set_client collation_connection 4447v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) union (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3) order by `a` desc limit 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci 4448select * from v1; 4449a 44507 44514 4452drop view v1; 4453create view v1 as 4454with t(a) as (values (2), (1)) select a from t; 4455show create view v1; 4456View Create View character_set_client collation_connection 4457v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as (values (2),(1))select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci 4458select * from v1; 4459a 44602 44611 4462drop view v1; 4463create view v1 as 4464with t(a) as 4465( values (2), (1) 4466union 4467(values (4), (7)) 4468order by 1 desc limit 3 ) 4469select a from t1 where a=4 union select a from t where a=7 order by a desc; 4470show create view v1; 4471View Create View character_set_client collation_connection 4472v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci 4473select * from v1; 4474a 44757 44764 4477drop view v1; 4478create view v1 as 4479with t(a) as 4480( (values (2), (1)) 4481union 4482(values (4), (7) order by 1 desc) 4483order by 1 desc limit 3 ) 4484select a from t1 where a=1 union select a from t where a=7 order by a desc; 4485show create view v1; 4486View Create View character_set_client collation_connection 4487v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci 4488select * from v1; 4489a 44907 44911 4492drop view v1; 4493create view v1 as 4494with t as (select * from t1 where a < 3), 4495s as (select * from t1 where a > 3) 4496select a from t where a=1 union select a from s where a=7 order by a desc; 4497show create view v1; 4498View Create View character_set_client collation_connection 4499v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci 4500select * from v1; 4501a 45027 45031 4504drop view v1; 4505create view v1 as 4506with t as (select * from t1 where a < 3), 4507s as (select * from t where a > 3) 4508select a from t where a=1 union select a from s where a=7 order by a desc; 4509show create view v1; 4510View Create View character_set_client collation_connection 4511v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from `t` where `t`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci 4512select * from v1; 4513a 45141 4515drop view v1; 4516drop table t1,t2; 4517# End of 10.4 tests 4518