1create table t1 (a int, b int); 2insert into t1 values (1,2),(4,6),(9,7), 3(1,1),(2,5),(7,8); 4# just VALUES 5values (1,2); 61 2 71 2 8values (1,2), (3,4), (5.6,0); 91 2 101.0 2 113.0 4 125.6 0 13values ("abc", "def"); 14abc def 15abc def 16# UNION that uses VALUES structure(s) 17select 1,2 18union 19values (1,2); 201 2 211 2 22values (1,2) 23union 24select 1,2; 251 2 261 2 27select 1,2 28union 29values (1,2),(3,4),(5,6),(7,8); 301 2 311 2 323 4 335 6 347 8 35select 3,7 36union 37values (1,2),(3,4),(5,6); 383 7 393 7 401 2 413 4 425 6 43select 3,7,4 44union 45values (1,2,5),(4,5,6); 463 7 4 473 7 4 481 2 5 494 5 6 50select 1,2 51union 52values (1,7),(3,6.5); 531 2 541 2.0 551 7.0 563 6.5 57select 1,2 58union 59values (1,2.0),(3,6); 601 2 611 2.0 623 6.0 63select 1.8,2 64union 65values (1,2),(3,6); 661.8 2 671.8 2 681.0 2 693.0 6 70values (1,2.4),(3,6) 71union 72select 2.8,9; 731 2.4 741.0 2.4 753.0 6.0 762.8 9.0 77values (1,2),(3,4),(5,6),(7,8) 78union 79select 5,6; 801 2 811 2 823 4 835 6 847 8 85select "ab","cdf" 86union 87values ("al","zl"),("we","q"); 88ab cdf 89ab cdf 90al zl 91we q 92values ("ab", "cdf") 93union 94select "ab","cdf"; 95ab cdf 96ab cdf 97values (1,2) 98union 99values (1,2),(5,6); 1001 2 1011 2 1025 6 103values (1,2) 104union 105values (3,4),(5,6); 1061 2 1071 2 1083 4 1095 6 110values (1,2) 111union 112values (1,2) 113union values (4,5); 1141 2 1151 2 1164 5 117# UNION ALL that uses VALUES structure 118values (1,2),(3,4) 119union all 120select 5,6; 1211 2 1221 2 1233 4 1245 6 125values (1,2),(3,4) 126union all 127select 1,2; 1281 2 1291 2 1303 4 1311 2 132select 5,6 133union all 134values (1,2),(3,4); 1355 6 1365 6 1371 2 1383 4 139select 1,2 140union all 141values (1,2),(3,4); 1421 2 1431 2 1441 2 1453 4 146values (1,2) 147union all 148values (1,2),(5,6); 1491 2 1501 2 1511 2 1525 6 153values (1,2) 154union all 155values (3,4),(5,6); 1561 2 1571 2 1583 4 1595 6 160values (1,2) 161union all 162values (1,2) 163union all 164values (4,5); 1651 2 1661 2 1671 2 1684 5 169values (1,2) 170union all 171values (1,2) 172union values (1,2); 1731 2 1741 2 175values (1,2) 176union 177values (1,2) 178union all 179values (1,2); 1801 2 1811 2 1821 2 183# EXCEPT that uses VALUES structure(s) 184select 1,2 185except 186values (3,4),(5,6); 1871 2 1881 2 189select 1,2 190except 191values (1,2),(3,4); 1921 2 193values (1,2),(3,4) 194except 195select 5,6; 1961 2 1971 2 1983 4 199values (1,2),(3,4) 200except 201select 1,2; 2021 2 2033 4 204values (1,2),(3,4) 205except 206values (5,6); 2071 2 2081 2 2093 4 210values (1,2),(3,4) 211except 212values (1,2); 2131 2 2143 4 215# INTERSECT that uses VALUES structure(s) 216select 1,2 217intersect 218values (3,4),(5,6); 2191 2 220select 1,2 221intersect 222values (1,2),(3,4); 2231 2 2241 2 225values (1,2),(3,4) 226intersect 227select 5,6; 2281 2 229values (1,2),(3,4) 230intersect 231select 1,2; 2321 2 2331 2 234values (1,2),(3,4) 235intersect 236values (5,6); 2371 2 238values (1,2),(3,4) 239intersect 240values (1,2); 2411 2 2421 2 243# combination of different structures that uses VALUES structures : UNION + EXCEPT 244values (1,2),(3,4) 245except 246select 1,2 247union values (1,2); 2481 2 2491 2 2503 4 251values (1,2),(3,4) 252except 253values (1,2) 254union 255values (1,2); 2561 2 2571 2 2583 4 259values (1,2),(3,4) 260except 261values (1,2) 262union 263values (3,4); 2641 2 2653 4 266values (1,2),(3,4) 267union 268values (1,2) 269except 270values (1,2); 2711 2 2723 4 273# combination of different structures that uses VALUES structures : UNION ALL + EXCEPT 274values (1,2),(3,4) 275except 276select 1,2 277union all 278values (1,2); 2791 2 2801 2 2813 4 282values (1,2),(3,4) 283except 284values (1,2) 285union all 286values (1,2); 2871 2 2881 2 2893 4 290values (1,2),(3,4) 291except 292values (1,2) 293union all 294values (3,4); 2951 2 2963 4 2973 4 298values (1,2),(3,4) 299union all 300values (1,2) 301except 302values (1,2); 3031 2 3043 4 305# combination of different structures that uses VALUES structures : UNION + INTERSECT 306values (1,2),(3,4) 307intersect 308select 1,2 309union 310values (1,2); 3111 2 3121 2 313values (1,2),(3,4) 314intersect 315values (1,2) 316union 317values (1,2); 3181 2 3191 2 320values (1,2),(3,4) 321intersect 322values (1,2) 323union 324values (3,4); 3251 2 3261 2 3273 4 328values (1,2),(3,4) 329union 330values (1,2) 331intersect 332values (1,2); 3331 2 3341 2 3353 4 336# combination of different structures that uses VALUES structures : UNION ALL + INTERSECT 337values (1,2),(3,4) 338intersect 339select 1,2 340union all 341values (1,2); 3421 2 3431 2 3441 2 345values (1,2),(3,4) 346intersect 347values (1,2) 348union all 349values (1,2); 3501 2 3511 2 3521 2 353values (1,2),(3,4) 354intersect 355values (1,2) 356union all 357values (3,4); 3581 2 3591 2 3603 4 361values (1,2),(3,4) 362union all 363values (1,2) 364intersect 365values (1,2); 3661 2 3671 2 3683 4 3691 2 370# combination of different structures that uses VALUES structures : UNION + UNION ALL 371values (1,2),(3,4) 372union all 373select 1,2 374union 375values (1,2); 3761 2 3771 2 3783 4 379values (1,2),(3,4) 380union all 381values (1,2) 382union 383values (1,2); 3841 2 3851 2 3863 4 387values (1,2),(3,4) 388union all 389values (1,2) 390union 391values (3,4); 3921 2 3931 2 3943 4 395values (1,2),(3,4) 396union 397values (1,2) 398union all 399values (1,2); 4001 2 4011 2 4023 4 4031 2 404values (1,2) 405union 406values (1,2) 407union all 408values (1,2); 4091 2 4101 2 4111 2 412# CTE that uses VALUES structure(s) : non-recursive CTE 413with t2 as 414( 415values (1,2),(3,4) 416) 417select * from t2; 4181 2 4191 2 4203 4 421with t2 as 422( 423select 1,2 424union 425values (1,2) 426) 427select * from t2; 4281 2 4291 2 430with t2 as 431( 432select 1,2 433union 434values (1,2),(3,4) 435) 436select * from t2; 4371 2 4381 2 4393 4 440with t2 as 441( 442values (1,2) 443union 444select 1,2 445) 446select * from t2; 4471 2 4481 2 449with t2 as 450( 451values (1,2),(3,4) 452union 453select 1,2 454) 455select * from t2; 4561 2 4571 2 4583 4 459with t2 as 460( 461values (5,6) 462union 463values (1,2),(3,4) 464) 465select * from t2; 4665 6 4675 6 4681 2 4693 4 470with t2 as 471( 472values (1,2) 473union 474values (1,2),(3,4) 475) 476select * from t2; 4771 2 4781 2 4793 4 480with t2 as 481( 482select 1,2 483union all 484values (1,2),(3,4) 485) 486select * from t2; 4871 2 4881 2 4891 2 4903 4 491with t2 as 492( 493values (1,2),(3,4) 494union all 495select 1,2 496) 497select * from t2; 4981 2 4991 2 5003 4 5011 2 502with t2 as 503( 504values (1,2) 505union all 506values (1,2),(3,4) 507) 508select * from t2; 5091 2 5101 2 5111 2 5123 4 513# recursive CTE that uses VALUES structure(s) : singe VALUES structure as anchor 514with recursive t2(a,b) as 515( 516values(1,1) 517union 518select t1.a, t1.b 519from t1,t2 520where t1.a=t2.a 521) 522select * from t2; 523a b 5241 1 5251 2 526with recursive t2(a,b) as 527( 528values(1,1) 529union 530select t1.a+1, t1.b 531from t1,t2 532where t1.a=t2.a 533) 534select * from t2; 535a b 5361 1 5372 2 5382 1 5393 5 540# recursive CTE that uses VALUES structure(s) : several VALUES structures as anchors 541with recursive t2(a,b) as 542( 543values(1,1) 544union 545values (3,4) 546union 547select t2.a+1, t1.b 548from t1,t2 549where t1.a=t2.a 550) 551select * from t2; 552a b 5531 1 5543 4 5552 2 5562 1 5573 5 558# recursive CTE that uses VALUES structure(s) : that uses UNION ALL 559with recursive t2(a,b,st) as 560( 561values(1,1,1) 562union all 563select t2.a, t1.b, t2.st+1 564from t1,t2 565where t1.a=t2.a and st<3 566) 567select * from t2; 568a b st 5691 1 1 5701 2 2 5711 1 2 5721 2 3 5731 2 3 5741 1 3 5751 1 3 576# recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements) 577with recursive fact(n,f) as 578( 579values(1,1) 580union 581select n+1,f*n from fact where n < 10 582) 583select * from fact; 584n f 5851 1 5862 1 5873 2 5884 6 5895 24 5906 120 5917 720 5928 5040 5939 40320 59410 362880 595# Derived table that uses VALUES structure(s) : singe VALUES structure 596select * from (values (1,2),(3,4)) as t2; 5971 2 5981 2 5993 4 600# Derived table that uses VALUES structure(s) : UNION with VALUES structure(s) 601select * from (select 1,2 union values (1,2)) as t2; 6021 2 6031 2 604select * from (select 1,2 union values (1,2),(3,4)) as t2; 6051 2 6061 2 6073 4 608select * from (values (1,2) union select 1,2) as t2; 6091 2 6101 2 611select * from (values (1,2),(3,4) union select 1,2) as t2; 6121 2 6131 2 6143 4 615select * from (values (5,6) union values (1,2),(3,4)) as t2; 6165 6 6175 6 6181 2 6193 4 620select * from (values (1,2) union values (1,2),(3,4)) as t2; 6211 2 6221 2 6233 4 624# Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s) 625select * from (select 1,2 union all values (1,2),(3,4)) as t2; 6261 2 6271 2 6281 2 6293 4 630select * from (values (1,2),(3,4) union all select 1,2) as t2; 6311 2 6321 2 6333 4 6341 2 635select * from (values (1,2) union all values (1,2),(3,4)) as t2; 6361 2 6371 2 6381 2 6393 4 640# CREATE VIEW that uses VALUES structure(s) : singe VALUES structure 641create view v1 as values (1,2),(3,4); 642select * from v1; 6431 2 6441 2 6453 4 646drop view v1; 647# CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s) 648create view v1 as 649select 1,2 650union 651values (1,2); 652select * from v1; 6531 2 6541 2 655drop view v1; 656create view v1 as 657select 1,2 658union 659values (1,2),(3,4); 660select * from v1; 6611 2 6621 2 6633 4 664drop view v1; 665create view v1 as 666values (1,2) 667union 668select 1,2; 669select * from v1; 6701 2 6711 2 672drop view v1; 673create view v1 as 674values (1,2),(3,4) 675union 676select 1,2; 677select * from v1; 6781 2 6791 2 6803 4 681drop view v1; 682create view v1 as 683values (5,6) 684union 685values (1,2),(3,4); 686select * from v1; 6875 6 6885 6 6891 2 6903 4 691drop view v1; 692# CREATE VIEW that uses VALUES structure(s) : UNION ALL with VALUES structure(s) 693create view v1 as 694values (1,2) 695union 696values (1,2),(3,4); 697select * from v1; 6981 2 6991 2 7003 4 701drop view v1; 702create view v1 as 703select 1,2 704union all 705values (1,2),(3,4); 706select * from v1; 7071 2 7081 2 7091 2 7103 4 711drop view v1; 712create view v1 as 713values (1,2),(3,4) 714union all 715select 1,2; 716select * from v1; 7171 2 7181 2 7193 4 7201 2 721drop view v1; 722create view v1 as 723values (1,2) 724union all 725values (1,2),(3,4); 726select * from v1; 7271 2 7281 2 7291 2 7303 4 731drop view v1; 732# IN-subquery with VALUES structure(s) : simple case 733select * from t1 734where a in (values (1)); 735a b 7361 2 7371 1 738select * from t1 739where a in (select * from (values (1)) as tvc_0); 740a b 7411 2 7421 1 743explain extended select * from t1 744where a in (values (1)); 745id select_type table type possible_keys key key_len ref rows filtered Extra 7461 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 7471 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 7483 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 7492 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 750Warnings: 751Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 752explain extended select * from t1 753where a in (select * from (values (1)) as tvc_0); 754id select_type table type possible_keys key key_len ref rows filtered Extra 7551 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 7561 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 7572 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 7583 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 759Warnings: 760Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 761# IN-subquery with VALUES structure(s) : UNION with VALUES on the first place 762select * from t1 763where a in (values (1) union select 2); 764a b 7651 2 7661 1 7672 5 768select * from t1 769where a in (select * from (values (1)) as tvc_0 union 770select 2); 771a b 7721 2 7731 1 7742 5 775explain extended select * from t1 776where a in (values (1) union select 2); 777id select_type table type possible_keys key key_len ref rows filtered Extra 7781 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 7794 DEPENDENT SUBQUERY <derived2> ref key0 key0 4 func 2 100.00 7802 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 7813 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 782NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL 783Warnings: 784Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) 785explain extended select * from t1 786where a in (select * from (values (1)) as tvc_0 union 787select 2); 788id select_type table type possible_keys key key_len ref rows filtered Extra 7891 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 7902 DEPENDENT SUBQUERY <derived3> ref key0 key0 4 func 2 100.00 7913 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 7924 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 793NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 794Warnings: 795Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) 796# IN-subquery with VALUES structure(s) : UNION with VALUES on the second place 797select * from t1 798where a in (select 2 union values (1)); 799a b 8001 2 8011 1 8022 5 803select * from t1 804where a in (select 2 union 805select * from (values (1)) tvc_0); 806a b 8071 2 8081 1 8092 5 810explain extended select * from t1 811where a in (select 2 union values (1)); 812id select_type table type possible_keys key key_len ref rows filtered Extra 8131 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 8142 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 8154 DEPENDENT UNION <derived3> ref key0 key0 4 func 2 100.00 8163 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 817NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 818Warnings: 819Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) 820explain extended select * from t1 821where a in (select 2 union 822select * from (values (1)) tvc_0); 823id select_type table type possible_keys key key_len ref rows filtered Extra 8241 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 8252 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 8263 DEPENDENT UNION <derived4> ref key0 key0 4 func 2 100.00 8274 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 828NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 829Warnings: 830Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) 831# IN-subquery with VALUES structure(s) : UNION ALL 832select * from t1 833where a in (values (1) union all select b from t1); 834a b 8351 2 8361 1 8372 5 8387 8 839select * from t1 840where a in (select * from (values (1)) as tvc_0 union all 841select b from t1); 842a b 8431 2 8441 1 8452 5 8467 8 847explain extended select * from t1 848where a in (values (1) union all select b from t1); 849id select_type table type possible_keys key key_len ref rows filtered Extra 8501 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 8514 DEPENDENT SUBQUERY <derived2> ref key0 key0 4 func 2 100.00 8522 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 8533 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 6 100.00 Using where 854Warnings: 855Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union all /* select#3 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`))) 856explain extended select * from t1 857where a in (select * from (values (1)) as tvc_0 union all 858select b from t1); 859id select_type table type possible_keys key key_len ref rows filtered Extra 8601 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 8612 DEPENDENT SUBQUERY <derived3> ref key0 key0 4 func 2 100.00 8623 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 8634 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 6 100.00 Using where 864Warnings: 865Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union all /* select#4 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`))) 866# NOT IN subquery with VALUES structure(s) : simple case 867select * from t1 868where a not in (values (1),(2)); 869a b 8704 6 8719 7 8727 8 873select * from t1 874where a not in (select * from (values (1),(2)) as tvc_0); 875a b 8764 6 8779 7 8787 8 879explain extended select * from t1 880where a not in (values (1),(2)); 881id select_type table type possible_keys key key_len ref rows filtered Extra 8821 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 8833 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 8842 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 885Warnings: 886Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`)))) 887explain extended select * from t1 888where a not in (select * from (values (1),(2)) as tvc_0); 889id select_type table type possible_keys key key_len ref rows filtered Extra 8901 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 8912 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 8923 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 893Warnings: 894Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1`)))) 895# NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place 896select * from t1 897where a not in (values (1) union select 2); 898a b 8994 6 9009 7 9017 8 902select * from t1 903where a not in (select * from (values (1)) as tvc_0 union 904select 2); 905a b 9064 6 9079 7 9087 8 909explain extended select * from t1 910where a not in (values (1) union select 2); 911id select_type table type possible_keys key key_len ref rows filtered Extra 9121 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 9134 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 9142 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 9153 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 916NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL 917Warnings: 918Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) union /* select#3 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))) 919explain extended select * from t1 920where a not in (select * from (values (1)) as tvc_0 union 921select 2); 922id select_type table type possible_keys key key_len ref rows filtered Extra 9231 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 9242 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where 9253 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 9264 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 927NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 928Warnings: 929Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) union /* select#4 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))) 930# NOT IN subquery with VALUES structure(s) : UNION with VALUES on the second place 931select * from t1 932where a not in (select 2 union values (1)); 933a b 9344 6 9359 7 9367 8 937select * from t1 938where a not in (select 2 union 939select * from (values (1)) as tvc_0); 940a b 9414 6 9429 7 9437 8 944explain extended select * from t1 945where a not in (select 2 union values (1)); 946id select_type table type possible_keys key key_len ref rows filtered Extra 9471 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 9482 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 9494 DEPENDENT UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where 9503 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 951NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 952Warnings: 953Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`)))) 954explain extended select * from t1 955where a not in (select 2 union 956select * from (values (1)) as tvc_0); 957id select_type table type possible_keys key key_len ref rows filtered Extra 9581 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 9592 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 9603 DEPENDENT UNION <derived4> ALL NULL NULL NULL NULL 2 100.00 Using where 9614 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 962NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 963Warnings: 964Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`)))) 965# ANY-subquery with VALUES structure(s) : simple case 966select * from t1 967where a = any (values (1),(2)); 968a b 9691 2 9701 1 9712 5 972select * from t1 973where a = any (select * from (values (1),(2)) as tvc_0); 974a b 9751 2 9761 1 9772 5 978explain extended select * from t1 979where a = any (values (1),(2)); 980id select_type table type possible_keys key key_len ref rows filtered Extra 9811 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 9821 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 9833 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 9842 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 985Warnings: 986Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 987explain extended select * from t1 988where a = any (select * from (values (1),(2)) as tvc_0); 989id select_type table type possible_keys key key_len ref rows filtered Extra 9901 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 9911 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 9922 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 9933 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 994Warnings: 995Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 996# ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place 997select * from t1 998where a = any (values (1) union select 2); 999a b 10001 2 10011 1 10022 5 1003select * from t1 1004where a = any (select * from (values (1)) as tvc_0 union 1005select 2); 1006a b 10071 2 10081 1 10092 5 1010explain extended select * from t1 1011where a = any (values (1) union select 2); 1012id select_type table type possible_keys key key_len ref rows filtered Extra 10131 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 10144 DEPENDENT SUBQUERY <derived2> ref key0 key0 4 func 2 100.00 10152 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 10163 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1017NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL 1018Warnings: 1019Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) 1020explain extended select * from t1 1021where a = any (select * from (values (1)) as tvc_0 union 1022select 2); 1023id select_type table type possible_keys key key_len ref rows filtered Extra 10241 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 10252 DEPENDENT SUBQUERY <derived3> ref key0 key0 4 func 2 100.00 10263 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 10274 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1028NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 1029Warnings: 1030Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) 1031# ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place 1032select * from t1 1033where a = any (select 2 union values (1)); 1034a b 10351 2 10361 1 10372 5 1038select * from t1 1039where a = any (select 2 union 1040select * from (values (1)) as tvc_0); 1041a b 10421 2 10431 1 10442 5 1045explain extended select * from t1 1046where a = any (select 2 union values (1)); 1047id select_type table type possible_keys key key_len ref rows filtered Extra 10481 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 10492 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 10504 DEPENDENT UNION <derived3> ref key0 key0 4 func 2 100.00 10513 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1052NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 1053Warnings: 1054Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) 1055explain extended select * from t1 1056where a = any (select 2 union 1057select * from (values (1)) as tvc_0); 1058id select_type table type possible_keys key key_len ref rows filtered Extra 10591 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 10602 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 10613 DEPENDENT UNION <derived4> ref key0 key0 4 func 2 100.00 10624 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1063NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 1064Warnings: 1065Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) 1066# ALL-subquery with VALUES structure(s) : simple case 1067select * from t1 1068where a = all (values (1)); 1069a b 10701 2 10711 1 1072select * from t1 1073where a = all (select * from (values (1)) as tvc_0); 1074a b 10751 2 10761 1 1077explain extended select * from t1 1078where a = all (values (1)); 1079id select_type table type possible_keys key key_len ref rows filtered Extra 10801 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 10813 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 10822 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1083Warnings: 1084Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`))))) 1085explain extended select * from t1 1086where a = all (select * from (values (1)) as tvc_0); 1087id select_type table type possible_keys key key_len ref rows filtered Extra 10881 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 10892 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where 10903 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1091Warnings: 1092Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`))))) 1093# ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place 1094select * from t1 1095where a = all (values (1) union select 1); 1096a b 10971 2 10981 1 1099select * from t1 1100where a = all (select * from (values (1)) as tvc_0 union 1101select 1); 1102a b 11031 2 11041 1 1105explain extended select * from t1 1106where a = all (values (1) union select 1); 1107id select_type table type possible_keys key key_len ref rows filtered Extra 11081 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 11094 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 11102 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 11113 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1112NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL 1113Warnings: 1114Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`) union /* select#3 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1)))))) 1115explain extended select * from t1 1116where a = all (select * from (values (1)) as tvc_0 union 1117select 1); 1118id select_type table type possible_keys key key_len ref rows filtered Extra 11191 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 11202 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where 11213 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 11224 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1123NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 1124Warnings: 1125Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`) union /* select#4 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1)))))) 1126# ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place 1127select * from t1 1128where a = any (select 1 union values (1)); 1129a b 11301 2 11311 1 1132select * from t1 1133where a = any (select 1 union 1134select * from (values (1)) as tvc_0); 1135a b 11361 2 11371 1 1138explain extended select * from t1 1139where a = any (select 1 union values (1)); 1140id select_type table type possible_keys key key_len ref rows filtered Extra 11411 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 11422 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 11434 DEPENDENT UNION <derived3> ref key0 key0 4 func 2 100.00 11443 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1145NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 1146Warnings: 1147Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) 1148explain extended select * from t1 1149where a = any (select 1 union 1150select * from (values (1)) as tvc_0); 1151id select_type table type possible_keys key key_len ref rows filtered Extra 11521 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 11532 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 11543 DEPENDENT UNION <derived4> ref key0 key0 4 func 2 100.00 11554 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1156NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 1157Warnings: 1158Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) 1159# prepare statement that uses VALUES structure(s): single VALUES structure 1160prepare stmt1 from " 1161values (1,2); 1162"; 1163execute stmt1; 11641 2 11651 2 1166execute stmt1; 11671 2 11681 2 1169deallocate prepare stmt1; 1170# prepare statement that uses VALUES structure(s): UNION with VALUES structure(s) 1171prepare stmt1 from " 1172 select 1,2 1173 union 1174 values (1,2),(3,4); 1175"; 1176execute stmt1; 11771 2 11781 2 11793 4 1180execute stmt1; 11811 2 11821 2 11833 4 1184deallocate prepare stmt1; 1185prepare stmt1 from " 1186 values (1,2),(3,4) 1187 union 1188 select 1,2; 1189"; 1190execute stmt1; 11911 2 11921 2 11933 4 1194execute stmt1; 11951 2 11961 2 11973 4 1198deallocate prepare stmt1; 1199prepare stmt1 from " 1200 select 1,2 1201 union 1202 values (3,4) 1203 union 1204 values (1,2); 1205"; 1206execute stmt1; 12071 2 12081 2 12093 4 1210execute stmt1; 12111 2 12121 2 12133 4 1214deallocate prepare stmt1; 1215prepare stmt1 from " 1216 values (5,6) 1217 union 1218 values (1,2),(3,4); 1219"; 1220execute stmt1; 12215 6 12225 6 12231 2 12243 4 1225execute stmt1; 12265 6 12275 6 12281 2 12293 4 1230deallocate prepare stmt1; 1231# prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s) 1232prepare stmt1 from " 1233 select 1,2 1234 union 1235 values (1,2),(3,4); 1236"; 1237execute stmt1; 12381 2 12391 2 12403 4 1241execute stmt1; 12421 2 12431 2 12443 4 1245deallocate prepare stmt1; 1246prepare stmt1 from " 1247 values (1,2),(3,4) 1248 union all 1249 select 1,2; 1250"; 1251execute stmt1; 12521 2 12531 2 12543 4 12551 2 1256execute stmt1; 12571 2 12581 2 12593 4 12601 2 1261deallocate prepare stmt1; 1262prepare stmt1 from " 1263 select 1,2 1264 union all 1265 values (3,4) 1266 union all 1267 values (1,2); 1268"; 1269execute stmt1; 12701 2 12711 2 12723 4 12731 2 1274execute stmt1; 12751 2 12761 2 12773 4 12781 2 1279deallocate prepare stmt1; 1280prepare stmt1 from " 1281 values (1,2) 1282 union all 1283 values (1,2),(3,4); 1284"; 1285execute stmt1; 12861 2 12871 2 12881 2 12893 4 1290execute stmt1; 12911 2 12921 2 12931 2 12943 4 1295deallocate prepare stmt1; 1296# explain query that uses VALUES structure(s): single VALUES structure 1297explain 1298values (1,2); 1299id select_type table type possible_keys key key_len ref rows Extra 13001 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used 1301explain format=json 1302values (1,2); 1303EXPLAIN 1304{ 1305 "query_block": { 1306 "union_result": { 1307 "table_name": "<unit1>", 1308 "access_type": "ALL", 1309 "query_specifications": [ 1310 { 1311 "query_block": { 1312 "select_id": 1, 1313 "table": { 1314 "message": "No tables used" 1315 } 1316 } 1317 } 1318 ] 1319 } 1320 } 1321} 1322# explain query that uses VALUES structure(s): UNION with VALUES structure(s) 1323explain 1324select 1,2 1325union 1326values (1,2),(3,4); 1327id select_type table type possible_keys key key_len ref rows Extra 13281 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 13292 UNION NULL NULL NULL NULL NULL NULL NULL No tables used 1330NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 1331explain 1332values (1,2),(3,4) 1333union 1334select 1,2; 1335id select_type table type possible_keys key key_len ref rows Extra 13361 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 13372 UNION NULL NULL NULL NULL NULL NULL NULL No tables used 1338NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 1339explain 1340values (5,6) 1341union 1342values (1,2),(3,4); 1343id select_type table type possible_keys key key_len ref rows Extra 13441 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 13452 UNION NULL NULL NULL NULL NULL NULL NULL No tables used 1346NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 1347explain format=json 1348select 1,2 1349union 1350values (1,2),(3,4); 1351EXPLAIN 1352{ 1353 "query_block": { 1354 "union_result": { 1355 "table_name": "<union1,2>", 1356 "access_type": "ALL", 1357 "query_specifications": [ 1358 { 1359 "query_block": { 1360 "select_id": 1, 1361 "table": { 1362 "message": "No tables used" 1363 } 1364 } 1365 }, 1366 { 1367 "query_block": { 1368 "select_id": 2, 1369 "operation": "UNION", 1370 "table": { 1371 "message": "No tables used" 1372 } 1373 } 1374 } 1375 ] 1376 } 1377 } 1378} 1379explain format=json 1380values (1,2),(3,4) 1381union 1382select 1,2; 1383EXPLAIN 1384{ 1385 "query_block": { 1386 "union_result": { 1387 "table_name": "<union1,2>", 1388 "access_type": "ALL", 1389 "query_specifications": [ 1390 { 1391 "query_block": { 1392 "select_id": 1, 1393 "table": { 1394 "message": "No tables used" 1395 } 1396 } 1397 }, 1398 { 1399 "query_block": { 1400 "select_id": 2, 1401 "operation": "UNION", 1402 "table": { 1403 "message": "No tables used" 1404 } 1405 } 1406 } 1407 ] 1408 } 1409 } 1410} 1411explain format=json 1412values (5,6) 1413union 1414values (1,2),(3,4); 1415EXPLAIN 1416{ 1417 "query_block": { 1418 "union_result": { 1419 "table_name": "<union1,2>", 1420 "access_type": "ALL", 1421 "query_specifications": [ 1422 { 1423 "query_block": { 1424 "select_id": 1, 1425 "table": { 1426 "message": "No tables used" 1427 } 1428 } 1429 }, 1430 { 1431 "query_block": { 1432 "select_id": 2, 1433 "operation": "UNION", 1434 "table": { 1435 "message": "No tables used" 1436 } 1437 } 1438 } 1439 ] 1440 } 1441 } 1442} 1443explain 1444select 1,2 1445union 1446values (3,4) 1447union 1448values (1,2); 1449id select_type table type possible_keys key key_len ref rows Extra 14501 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 14512 UNION NULL NULL NULL NULL NULL NULL NULL No tables used 14523 UNION NULL NULL NULL NULL NULL NULL NULL No tables used 1453NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL 1454explain format=json 1455select 1,2 1456union 1457values (3,4) 1458union 1459values (1,2); 1460EXPLAIN 1461{ 1462 "query_block": { 1463 "union_result": { 1464 "table_name": "<union1,2,3>", 1465 "access_type": "ALL", 1466 "query_specifications": [ 1467 { 1468 "query_block": { 1469 "select_id": 1, 1470 "table": { 1471 "message": "No tables used" 1472 } 1473 } 1474 }, 1475 { 1476 "query_block": { 1477 "select_id": 2, 1478 "operation": "UNION", 1479 "table": { 1480 "message": "No tables used" 1481 } 1482 } 1483 }, 1484 { 1485 "query_block": { 1486 "select_id": 3, 1487 "operation": "UNION", 1488 "table": { 1489 "message": "No tables used" 1490 } 1491 } 1492 } 1493 ] 1494 } 1495 } 1496} 1497# explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s) 1498explain 1499select 1,2 1500union 1501values (1,2),(3,4); 1502id select_type table type possible_keys key key_len ref rows Extra 15031 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 15042 UNION NULL NULL NULL NULL NULL NULL NULL No tables used 1505NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 1506explain 1507values (1,2),(3,4) 1508union all 1509select 1,2; 1510id select_type table type possible_keys key key_len ref rows Extra 15111 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 15122 UNION NULL NULL NULL NULL NULL NULL NULL No tables used 1513explain 1514values (1,2) 1515union all 1516values (1,2),(3,4); 1517id select_type table type possible_keys key key_len ref rows Extra 15181 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 15192 UNION NULL NULL NULL NULL NULL NULL NULL No tables used 1520explain format=json 1521values (1,2),(3,4) 1522union all 1523select 1,2; 1524EXPLAIN 1525{ 1526 "query_block": { 1527 "union_result": { 1528 "table_name": "<union1,2>", 1529 "access_type": "ALL", 1530 "query_specifications": [ 1531 { 1532 "query_block": { 1533 "select_id": 1, 1534 "table": { 1535 "message": "No tables used" 1536 } 1537 } 1538 }, 1539 { 1540 "query_block": { 1541 "select_id": 2, 1542 "operation": "UNION", 1543 "table": { 1544 "message": "No tables used" 1545 } 1546 } 1547 } 1548 ] 1549 } 1550 } 1551} 1552explain format=json 1553select 1,2 1554union 1555values (1,2),(3,4); 1556EXPLAIN 1557{ 1558 "query_block": { 1559 "union_result": { 1560 "table_name": "<union1,2>", 1561 "access_type": "ALL", 1562 "query_specifications": [ 1563 { 1564 "query_block": { 1565 "select_id": 1, 1566 "table": { 1567 "message": "No tables used" 1568 } 1569 } 1570 }, 1571 { 1572 "query_block": { 1573 "select_id": 2, 1574 "operation": "UNION", 1575 "table": { 1576 "message": "No tables used" 1577 } 1578 } 1579 } 1580 ] 1581 } 1582 } 1583} 1584explain format=json 1585values (1,2) 1586union all 1587values (1,2),(3,4); 1588EXPLAIN 1589{ 1590 "query_block": { 1591 "union_result": { 1592 "table_name": "<union1,2>", 1593 "access_type": "ALL", 1594 "query_specifications": [ 1595 { 1596 "query_block": { 1597 "select_id": 1, 1598 "table": { 1599 "message": "No tables used" 1600 } 1601 } 1602 }, 1603 { 1604 "query_block": { 1605 "select_id": 2, 1606 "operation": "UNION", 1607 "table": { 1608 "message": "No tables used" 1609 } 1610 } 1611 } 1612 ] 1613 } 1614 } 1615} 1616explain 1617select 1,2 1618union all 1619values (3,4) 1620union all 1621values (1,2); 1622id select_type table type possible_keys key key_len ref rows Extra 16231 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 16242 UNION NULL NULL NULL NULL NULL NULL NULL No tables used 16253 UNION NULL NULL NULL NULL NULL NULL NULL No tables used 1626explain format=json 1627select 1,2 1628union all 1629values (3,4) 1630union all 1631values (1,2); 1632EXPLAIN 1633{ 1634 "query_block": { 1635 "union_result": { 1636 "table_name": "<union1,2,3>", 1637 "access_type": "ALL", 1638 "query_specifications": [ 1639 { 1640 "query_block": { 1641 "select_id": 1, 1642 "table": { 1643 "message": "No tables used" 1644 } 1645 } 1646 }, 1647 { 1648 "query_block": { 1649 "select_id": 2, 1650 "operation": "UNION", 1651 "table": { 1652 "message": "No tables used" 1653 } 1654 } 1655 }, 1656 { 1657 "query_block": { 1658 "select_id": 3, 1659 "operation": "UNION", 1660 "table": { 1661 "message": "No tables used" 1662 } 1663 } 1664 } 1665 ] 1666 } 1667 } 1668} 1669# analyze query that uses VALUES structure(s): single VALUES structure 1670analyze 1671values (1,2); 1672id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 16731 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1674analyze format=json 1675values (1,2); 1676ANALYZE 1677{ 1678 "query_block": { 1679 "union_result": { 1680 "table_name": "<unit1>", 1681 "access_type": "ALL", 1682 "r_loops": 0, 1683 "r_rows": null, 1684 "query_specifications": [ 1685 { 1686 "query_block": { 1687 "select_id": 1, 1688 "table": { 1689 "message": "No tables used" 1690 } 1691 } 1692 } 1693 ] 1694 } 1695 } 1696} 1697# analyze query that uses VALUES structure(s): UNION with VALUES structure(s) 1698analyze 1699select 1,2 1700union 1701values (1,2),(3,4); 1702id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 17031 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 17042 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1705NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL 1706analyze 1707values (1,2),(3,4) 1708union 1709select 1,2; 1710id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 17111 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 17122 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1713NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL 1714analyze 1715values (5,6) 1716union 1717values (1,2),(3,4); 1718id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 17191 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 17202 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1721NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 3.00 NULL NULL 1722analyze format=json 1723select 1,2 1724union 1725values (1,2),(3,4); 1726ANALYZE 1727{ 1728 "query_block": { 1729 "union_result": { 1730 "table_name": "<union1,2>", 1731 "access_type": "ALL", 1732 "r_loops": 1, 1733 "r_rows": 2, 1734 "query_specifications": [ 1735 { 1736 "query_block": { 1737 "select_id": 1, 1738 "table": { 1739 "message": "No tables used" 1740 } 1741 } 1742 }, 1743 { 1744 "query_block": { 1745 "select_id": 2, 1746 "operation": "UNION", 1747 "table": { 1748 "message": "No tables used" 1749 } 1750 } 1751 } 1752 ] 1753 } 1754 } 1755} 1756analyze format=json 1757values (1,2),(3,4) 1758union 1759select 1,2; 1760ANALYZE 1761{ 1762 "query_block": { 1763 "union_result": { 1764 "table_name": "<union1,2>", 1765 "access_type": "ALL", 1766 "r_loops": 1, 1767 "r_rows": 2, 1768 "query_specifications": [ 1769 { 1770 "query_block": { 1771 "select_id": 1, 1772 "table": { 1773 "message": "No tables used" 1774 } 1775 } 1776 }, 1777 { 1778 "query_block": { 1779 "select_id": 2, 1780 "operation": "UNION", 1781 "table": { 1782 "message": "No tables used" 1783 } 1784 } 1785 } 1786 ] 1787 } 1788 } 1789} 1790analyze format=json 1791values (5,6) 1792union 1793values (1,2),(3,4); 1794ANALYZE 1795{ 1796 "query_block": { 1797 "union_result": { 1798 "table_name": "<union1,2>", 1799 "access_type": "ALL", 1800 "r_loops": 1, 1801 "r_rows": 3, 1802 "query_specifications": [ 1803 { 1804 "query_block": { 1805 "select_id": 1, 1806 "table": { 1807 "message": "No tables used" 1808 } 1809 } 1810 }, 1811 { 1812 "query_block": { 1813 "select_id": 2, 1814 "operation": "UNION", 1815 "table": { 1816 "message": "No tables used" 1817 } 1818 } 1819 } 1820 ] 1821 } 1822 } 1823} 1824analyze 1825select 1,2 1826union 1827values (3,4) 1828union 1829values (1,2); 1830id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 18311 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 18322 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 18333 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1834NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL 1835analyze format=json 1836select 1,2 1837union 1838values (3,4) 1839union 1840values (1,2); 1841ANALYZE 1842{ 1843 "query_block": { 1844 "union_result": { 1845 "table_name": "<union1,2,3>", 1846 "access_type": "ALL", 1847 "r_loops": 1, 1848 "r_rows": 2, 1849 "query_specifications": [ 1850 { 1851 "query_block": { 1852 "select_id": 1, 1853 "table": { 1854 "message": "No tables used" 1855 } 1856 } 1857 }, 1858 { 1859 "query_block": { 1860 "select_id": 2, 1861 "operation": "UNION", 1862 "table": { 1863 "message": "No tables used" 1864 } 1865 } 1866 }, 1867 { 1868 "query_block": { 1869 "select_id": 3, 1870 "operation": "UNION", 1871 "table": { 1872 "message": "No tables used" 1873 } 1874 } 1875 } 1876 ] 1877 } 1878 } 1879} 1880# analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s) 1881analyze 1882select 1,2 1883union 1884values (1,2),(3,4); 1885id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 18861 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 18872 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1888NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL 1889analyze 1890values (1,2),(3,4) 1891union all 1892select 1,2; 1893id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 18941 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 18952 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1896analyze 1897values (1,2) 1898union all 1899values (1,2),(3,4); 1900id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 19011 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 19022 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1903analyze format=json 1904values (1,2),(3,4) 1905union all 1906select 1,2; 1907ANALYZE 1908{ 1909 "query_block": { 1910 "union_result": { 1911 "table_name": "<union1,2>", 1912 "access_type": "ALL", 1913 "r_loops": 0, 1914 "r_rows": null, 1915 "query_specifications": [ 1916 { 1917 "query_block": { 1918 "select_id": 1, 1919 "table": { 1920 "message": "No tables used" 1921 } 1922 } 1923 }, 1924 { 1925 "query_block": { 1926 "select_id": 2, 1927 "operation": "UNION", 1928 "table": { 1929 "message": "No tables used" 1930 } 1931 } 1932 } 1933 ] 1934 } 1935 } 1936} 1937analyze format=json 1938select 1,2 1939union 1940values (1,2),(3,4); 1941ANALYZE 1942{ 1943 "query_block": { 1944 "union_result": { 1945 "table_name": "<union1,2>", 1946 "access_type": "ALL", 1947 "r_loops": 1, 1948 "r_rows": 2, 1949 "query_specifications": [ 1950 { 1951 "query_block": { 1952 "select_id": 1, 1953 "table": { 1954 "message": "No tables used" 1955 } 1956 } 1957 }, 1958 { 1959 "query_block": { 1960 "select_id": 2, 1961 "operation": "UNION", 1962 "table": { 1963 "message": "No tables used" 1964 } 1965 } 1966 } 1967 ] 1968 } 1969 } 1970} 1971analyze format=json 1972values (1,2) 1973union all 1974values (1,2),(3,4); 1975ANALYZE 1976{ 1977 "query_block": { 1978 "union_result": { 1979 "table_name": "<union1,2>", 1980 "access_type": "ALL", 1981 "r_loops": 0, 1982 "r_rows": null, 1983 "query_specifications": [ 1984 { 1985 "query_block": { 1986 "select_id": 1, 1987 "table": { 1988 "message": "No tables used" 1989 } 1990 } 1991 }, 1992 { 1993 "query_block": { 1994 "select_id": 2, 1995 "operation": "UNION", 1996 "table": { 1997 "message": "No tables used" 1998 } 1999 } 2000 } 2001 ] 2002 } 2003 } 2004} 2005analyze 2006select 1,2 2007union all 2008values (3,4) 2009union all 2010values (1,2); 2011id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 20121 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 20132 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 20143 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2015analyze format=json 2016select 1,2 2017union all 2018values (3,4) 2019union all 2020values (1,2); 2021ANALYZE 2022{ 2023 "query_block": { 2024 "union_result": { 2025 "table_name": "<union1,2,3>", 2026 "access_type": "ALL", 2027 "r_loops": 0, 2028 "r_rows": null, 2029 "query_specifications": [ 2030 { 2031 "query_block": { 2032 "select_id": 1, 2033 "table": { 2034 "message": "No tables used" 2035 } 2036 } 2037 }, 2038 { 2039 "query_block": { 2040 "select_id": 2, 2041 "operation": "UNION", 2042 "table": { 2043 "message": "No tables used" 2044 } 2045 } 2046 }, 2047 { 2048 "query_block": { 2049 "select_id": 3, 2050 "operation": "UNION", 2051 "table": { 2052 "message": "No tables used" 2053 } 2054 } 2055 } 2056 ] 2057 } 2058 } 2059} 2060# different number of values in TVC 2061values (1,2),(3,4,5); 2062ERROR HY000: The used table value constructor has a different number of values 2063# illegal parameter data types in TVC 2064values (1,point(1,1)),(1,1); 2065ERROR HY000: Illegal parameter data types geometry and int for operation 'TABLE VALUE CONSTRUCTOR' 2066values (1,point(1,1)+1); 2067ERROR HY000: Illegal parameter data types geometry and int for operation '+' 2068# field reference in TVC 2069select * from (values (1), (b), (2)) as new_tvc; 2070ERROR HY000: Field reference 'b' can't be used in table value constructor 2071select * from (values (1), (t1.b), (2)) as new_tvc; 2072ERROR HY000: Field reference 't1.b' can't be used in table value constructor 2073drop table t1; 2074# 2075# MDEV-15940: cursor over TVC 2076# 2077BEGIN NOT ATOMIC 2078DECLARE v INT; 2079DECLARE cur CURSOR FOR VALUES(7); 2080OPEN cur; 2081FETCH cur INTO v; 2082SELECT v; 2083END; 2084| 2085v 20867 2087BEGIN NOT ATOMIC 2088DECLARE v INT DEFAULT 0; 2089FOR a IN (VALUES (7)) DO SET v = v + 1; END FOR; 2090SELECT v; 2091END; 2092| 2093v 20941 2095# 2096# MDEV-16038: empty row in TVC 2097# 2098with t as (values (),()) select 1 from t; 2099ERROR HY000: Row with no elements is not allowed in table value constructor in this context 2100# 2101# MDEV-17017: TVC in derived table 2102# 2103create table t1 (a int); 2104insert into t1 values (9), (3), (2); 2105select * from (values (7), (5), (8), (1), (3), (8), (1)) t; 21067 21077 21085 21098 21101 21113 21128 21131 2114explain select * from (values (7), (5), (8), (1), (3), (8), (1)) t; 2115id select_type table type possible_keys key key_len ref rows Extra 21161 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 21172 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 2118select * from (values (1,11), (7,77), (3,31), (4,42)) t; 21191 11 21201 11 21217 77 21223 31 21234 42 2124explain select * from (values (1,11), (7,77), (3,31), (4,42)) t; 2125id select_type table type possible_keys key key_len ref rows Extra 21261 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 21272 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 2128select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; 21297 21307 21315 21328 21331 21343 2135explain select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; 2136id select_type table type possible_keys key key_len ref rows Extra 21371 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 21382 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 21393 UNION NULL NULL NULL NULL NULL NULL NULL No tables used 2140NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 2141select * from (values (7), (5), (8), (1) union select * from t1) t; 21427 21437 21445 21458 21461 21479 21483 21492 2150explain select * from (values (7), (5), (8), (1) union select * from t1) t; 2151id select_type table type possible_keys key key_len ref rows Extra 21521 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 21532 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 21543 UNION t1 ALL NULL NULL NULL NULL 3 2155NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 2156drop table t1; 2157# 2158# MDEV-16930: expression in the first row of TVC specifying derived table 2159# 2160SELECT 1 + 1, 2, "abc"; 21611 + 1 2 abc 21622 2 abc 2163SELECT * FROM (SELECT 1 + 1, 2, "abc") t; 21641 + 1 2 abc 21652 2 abc 2166WITH cte AS (SELECT 1 + 1, 2, "abc") SELECT * FROM cte; 21671 + 1 2 abc 21682 2 abc 2169SELECT 1 + 1, 2, "abc" UNION SELECT 3+4, 3, "abc"; 21701 + 1 2 abc 21712 2 abc 21727 3 abc 2173CREATE VIEW v1 AS SELECT 1 + 1, 2, "abc"; 2174SELECT * FROM v1; 21751 + 1 2 abc 21762 2 abc 2177DROP VIEW v1; 2178VALUES(1 + 1,2,"abc"); 21791 + 1 2 abc 21802 2 abc 2181SELECT * FROM (VALUES(1 + 1,2,"abc")) t; 21821 + 1 2 abc 21832 2 abc 2184PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; 2185EXECUTE stmt; 21861 + 1 2 abc 21872 2 abc 2188EXECUTE stmt; 21891 + 1 2 abc 21902 2 abc 2191DEALLOCATE PREPARE stmt; 2192# 2193# MDEV-17894: tvc with ORDER BY ... LIMIT 2194# 2195values (5), (7), (1), (3), (4) limit 2; 21965 21975 21987 2199explain extended values (5), (7), (1), (3), (4) limit 2; 2200id select_type table type possible_keys key key_len ref rows filtered Extra 22011 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2202Warnings: 2203Note 1003 values (5),(7),(1),(3),(4) limit 2 2204values (5), (7), (1), (3), (4) limit 2 offset 1; 22055 22067 22071 2208explain extended values (5), (7), (1), (3), (4) limit 2 offset 1; 2209id select_type table type possible_keys key key_len ref rows filtered Extra 22101 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2211Warnings: 2212Note 1003 values (5),(7),(1),(3),(4) limit 1,2 2213values (5), (7), (1), (3), (4) order by 1 limit 2; 22145 22151 22163 2217explain extended values (5), (7), (1), (3), (4) order by 1 limit 2; 2218id select_type table type possible_keys key key_len ref rows filtered Extra 22191 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2220NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort 2221Warnings: 2222Note 1003 values (5),(7),(1),(3),(4) order by 1 limit 2 2223values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; 22245 22253 22264 2227explain extended values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; 2228id select_type table type possible_keys key key_len ref rows filtered Extra 22291 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2230NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort 2231Warnings: 2232Note 1003 values (5),(7),(1),(3),(4) order by 1 limit 1,2 2233values (5), (7), (1), (3), (4) order by 1; 22345 22351 22363 22374 22385 22397 2240explain extended values (5), (7), (1), (3), (4) order by 1; 2241id select_type table type possible_keys key key_len ref rows filtered Extra 22421 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2243NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort 2244Warnings: 2245Note 1003 values (5),(7),(1),(3),(4) order by 1 2246values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2; 22475 90 22484 10 22497 20 22503 50 22511 70 22525 90 2253explain extended values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2; 2254id select_type table type possible_keys key key_len ref rows filtered Extra 22551 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2256NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort 2257Warnings: 2258Note 1003 values (5,90),(7,20),(1,70),(3,50),(4,10) order by 2 2259select 2 union (values (5), (7), (1), (3), (4) limit 2); 22602 22612 22625 22637 2264explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2); 2265id select_type table type possible_keys key key_len ref rows filtered Extra 22661 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22672 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2268NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL 2269Warnings: 2270Note 1003 /* select#1 */ select 2 AS `2` union (values (5),(7),(1),(3),(4) limit 2) 2271select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); 22722 22732 22747 22751 2276explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); 2277id select_type table type possible_keys key key_len ref rows filtered Extra 22781 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22792 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2280NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL 2281Warnings: 2282Note 1003 /* select#1 */ select 2 AS `2` union (values (5),(7),(1),(3),(4) limit 1,2) 2283select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); 22842 22852 22861 22873 2288explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); 2289id select_type table type possible_keys key key_len ref rows filtered Extra 22901 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 22913 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort 22922 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2293NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL 2294Warnings: 2295Note 1003 /* select#1 */ select 2 AS `2` union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) 2296select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); 22972 22982 22993 23004 2301explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); 2302id select_type table type possible_keys key key_len ref rows filtered Extra 23031 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 23043 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort 23052 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2306NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL 2307Warnings: 2308Note 1003 /* select#1 */ select 2 AS `2` union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,2) 2309(values (5), (7), (1), (3), (4) limit 2) union select 2; 23105 23115 23127 23132 2314explain extended (values (5), (7), (1), (3), (4) limit 2) union select 2; 2315id select_type table type possible_keys key key_len ref rows filtered Extra 23161 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 23172 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2318NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL 2319Warnings: 2320Note 1003 (values (5),(7),(1),(3),(4) limit 2) union /* select#2 */ select 2 AS `2` 2321(values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; 23225 23237 23241 23252 2326explain extended (values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; 2327id select_type table type possible_keys key key_len ref rows filtered Extra 23281 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 23292 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2330NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL 2331Warnings: 2332Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union /* select#2 */ select 2 AS `2` 2333(values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; 23345 23351 23363 23372 2338explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; 2339id select_type table type possible_keys key key_len ref rows filtered Extra 23401 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort 23413 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 23422 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2343NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL 2344Warnings: 2345Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) union /* select#2 */ select 2 AS `2` 2346(values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; 23475 23483 23494 23502 2351explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; 2352id select_type table type possible_keys key key_len ref rows filtered Extra 23531 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort 23543 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 23552 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2356NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL 2357Warnings: 2358Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,2) union /* select#2 */ select 2 AS `2` 2359select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); 23603 23613 23623 23634 2364explain extended select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); 2365id select_type table type possible_keys key key_len ref rows filtered Extra 23661 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 23672 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2368Warnings: 2369Note 1003 /* select#1 */ select 3 AS `3` union all (values (5),(7),(1),(3),(4) limit 3,2) 2370(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; 23715 23723 23734 23743 2375explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; 2376id select_type table type possible_keys key key_len ref rows filtered Extra 23771 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 23782 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2379Warnings: 2380Note 1003 (values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS `3` 2381select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); 23823 23833 23841 23853 2386explain extended select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); 2387id select_type table type possible_keys key key_len ref rows filtered Extra 23881 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 23893 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort 23902 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2391NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL 2392Warnings: 2393Note 1003 /* select#1 */ select 3 AS `3` union all (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) 2394(values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; 23955 23961 23973 23983 2399explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; 2400id select_type table type possible_keys key key_len ref rows filtered Extra 24011 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort 24023 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 24032 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2404NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL 2405Warnings: 2406Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) union all /* select#2 */ select 3 AS `3` 2407( values (5), (7), (1), (3), (4) limit 2 offset 1 ) 2408union 2409( values (5), (7), (1), (3), (4) order by 1 limit 2 ); 24105 24117 24121 24133 2414explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) 2415union 2416( values (5), (7), (1), (3), (4) order by 1 limit 2 ); 2417id select_type table type possible_keys key key_len ref rows filtered Extra 24181 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 24193 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort 24202 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2421NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL 2422Warnings: 2423Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) 2424( values (5), (7), (1), (3), (4) limit 2 offset 1 ) 2425union all 2426( values (5), (7), (1), (3), (4) order by 1 limit 2 ); 24275 24287 24291 24301 24313 2432explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) 2433union all 2434( values (5), (7), (1), (3), (4) order by 1 limit 2 ); 2435id select_type table type possible_keys key key_len ref rows filtered Extra 24361 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 24373 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort 24382 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2439NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL 2440Warnings: 2441Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union all (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) 2442(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; 24435 24443 24453 24464 2447explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; 2448id select_type table type possible_keys key key_len ref rows filtered Extra 24491 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 24502 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2451NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort 2452Warnings: 2453Note 1003 (values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS `3` order by 1 2454(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; 24555 24563 24573 24584 24595 2460explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; 2461id select_type table type possible_keys key key_len ref rows filtered Extra 24621 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort 24633 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 24642 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2465NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort 2466Warnings: 2467Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,3) union all /* select#2 */ select 3 AS `3` order by 1 2468(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 2469order by 1 limit 2 offset 1; 24705 24713 24724 2473explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 2474order by 1 limit 2 offset 1; 2475id select_type table type possible_keys key key_len ref rows filtered Extra 24761 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort 24773 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 24782 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2479NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort 2480Warnings: 2481Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,3) union all /* select#2 */ select 3 AS `3` order by 1 limit 1,2 2482values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; 2483ERROR 42S22: Unknown column '3' in 'order clause' 2484prepare stmt from " 2485select 2 union (values (5), (7), (1), (3), (4) limit 2) 2486"; 2487execute stmt; 24882 24892 24905 24917 2492execute stmt; 24932 24942 24955 24967 2497deallocate prepare stmt; 2498prepare stmt from " 2499select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2) 2500"; 2501execute stmt; 25022 25032 25041 25053 2506execute stmt; 25072 25082 25091 25103 2511deallocate prepare stmt; 2512prepare stmt from " 2513select 3 union all (values (5), (7), (1), (3), (4) limit 2) 2514"; 2515execute stmt; 25163 25173 25185 25197 2520execute stmt; 25213 25223 25235 25247 2525deallocate prepare stmt; 2526prepare stmt from " 2527select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2) 2528"; 2529execute stmt; 25303 25313 25321 25333 2534execute stmt; 25353 25363 25371 25383 2539deallocate prepare stmt; 2540prepare stmt from " 2541( values (5), (7), (1), (3), (4) limit 2 offset 1 ) 2542 union 2543( values (5), (7), (1), (3), (4) order by 1 limit 2 ); 2544"; 2545execute stmt; 25465 25477 25481 25493 2550execute stmt; 25515 25527 25531 25543 2555deallocate prepare stmt; 2556prepare stmt from " 2557values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; 2558"; 2559ERROR 42S22: Unknown column '3' in 'order clause' 2560create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2; 2561show create view v1; 2562View Create View character_set_client collation_connection 2563v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (5),(7),(1),(3),(4) order by 1 limit 2 latin1 latin1_swedish_ci 2564select * from v1; 25655 25661 25673 2568drop view v1; 2569create view v1 as 2570( values (5), (7), (1), (3), (4) limit 2 offset 1 ) 2571union 2572( values (5), (7), (1), (3), (4) order by 1 limit 2 ); 2573show create view v1; 2574View Create View character_set_client collation_connection 2575v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (5),(7),(1),(3),(4) limit 1,2) union (values (5),(7),(1),(3),(4) order by 1 limit 2) latin1 latin1_swedish_ci 2576select * from v1; 25775 25787 25791 25803 2581drop view v1; 2582create view v1 as values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; 2583ERROR 42S22: Unknown column '3' in 'order clause' 2584create view v1 as 2585( values (5), (7), (1), (3), (4) limit 2 offset 1 ) 2586union 2587( values (5), (7), (1), (3), (4) order by 2 limit 2 ); 2588ERROR 42S22: Unknown column '2' in 'order clause' 2589# 2590# MDEV-20229: view defined as select using 2591# CTE with named columns defined as TVC 2592# 2593create view v1 as with t(a) as (values (2), (1)) select a from t; 2594show create view v1; 2595View Create View character_set_client collation_connection 2596v1 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 2597select * from v1; 2598a 25992 26001 2601drop view v1; 2602# 2603# MDEV-22560 Crash on a table value constructor with an SP variable 2604# 2605BEGIN NOT ATOMIC 2606DECLARE a INT DEFAULT 0; 2607VALUES (a) UNION SELECT 1; 2608END; 2609$$ 2610a 26110 26121 2613# 2614# MDEV-21995 Server crashes in Item_field::real_type_handler with table value constructor 2615# 2616VALUES (IGNORE); 2617ERROR HY000: 'ignore' is not allowed in this context 2618VALUES (DEFAULT); 2619ERROR HY000: 'default' is not allowed in this context 2620EXECUTE IMMEDIATE 'VALUES (?)' USING IGNORE; 2621ERROR HY000: 'ignore' is not allowed in this context 2622EXECUTE IMMEDIATE 'VALUES (?)' USING DEFAULT; 2623ERROR HY000: 'default' is not allowed in this context 2624# 2625# MDEV-24675: TVC using subqueries 2626# 2627values((select 1)); 2628(select 1) 26291 2630values (2), ((select 1)); 26312 26322 26331 2634values ((select 1)), (2), ((select 3)); 2635(select 1) 26361 26372 26383 2639values ((select 1), 2), (3,4), (5, (select 6)); 2640(select 1) 2 26411 2 26423 4 26435 6 2644create table t1 (a int, b int); 2645insert into t1 values (1,3), (2,3), (3,2), (1,2); 2646values((select max(a) from t1)); 2647(select max(a) from t1) 26483 2649values((select min(b) from t1)); 2650(select min(b) from t1) 26512 2652values ((select max(a) from t1), (select min(b) from t1)); 2653(select max(a) from t1) (select min(b) from t1) 26543 2 2655values((select * from (select max(b) from t1) as t)); 2656(select * from (select max(b) from t1) as t) 26573 2658drop table t1; 2659# 2660# MDEV-24618: TVC contains extra parenthesis for row expressions 2661# in value list 2662# 2663create table t1 (a int, b int); 2664insert into t1 values (1,3), (2,3); 2665insert into t1 values ((5,4)); 2666ERROR 21000: Operand should contain 1 column(s) 2667values ((1,2)); 2668ERROR 21000: Operand should contain 1 column(s) 2669select * from (values ((1,2))) dt; 2670ERROR 21000: Operand should contain 1 column(s) 2671values (1,2); 26721 2 26731 2 2674values ((select min(a), max(b) from t1)); 2675ERROR 21000: Operand should contain 1 column(s) 2676drop table t1; 2677# 2678# MDEV-24840: union of TVCs in IN subquery 2679# 2680create table t1 (a int) engine=myisam; 2681insert into t1 values (3), (7), (1); 2682select a from t1 where a in (values (7) union values (8)); 2683a 26847 2685explain extended select a from t1 where a in (values (7) union values (8)); 2686id select_type table type possible_keys key key_len ref rows filtered Extra 26871 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 26884 DEPENDENT SUBQUERY <derived2> ref key0 key0 4 func 2 100.00 26892 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 26905 DEPENDENT UNION <derived3> ref key0 key0 4 func 2 100.00 26913 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2692NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL 2693Warnings: 2694Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`7` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`8`))) 2695prepare stmt from "select a from t1 where a in (values (7) union values (8))"; 2696execute stmt; 2697a 26987 2699execute stmt; 2700a 27017 2702deallocate prepare stmt; 2703select a from t1 where a not in (values (7) union values (8)); 2704a 27053 27061 2707explain extended select a from t1 where a not in (values (7) union values (8)); 2708id select_type table type possible_keys key key_len ref rows filtered Extra 27091 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 27104 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where 27112 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 27125 DEPENDENT UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where 27133 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2714NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL 2715Warnings: 2716Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`7`) union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`8`)))) 2717select a from t1 where a < all(values (7) union values (8)); 2718a 27193 27201 2721explain extended select a from t1 where a < all(values (7) union values (8)); 2722id select_type table type possible_keys key key_len ref rows filtered Extra 27231 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 27244 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 27252 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 27265 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 27273 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2728NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL 2729Warnings: 2730Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`))) 2731select a from t1 where a >= any(values (7) union values (8)); 2732a 27337 2734explain extended select a from t1 where a >= any(values (7) union values (8)); 2735id select_type table type possible_keys key key_len ref rows filtered Extra 27361 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 27374 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 27382 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 27395 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 27403 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2741NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL 2742Warnings: 2743Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`))) 2744drop table t1; 2745# 2746# MDEV-24934:EXPLAIN for queries based on TVC using subqueries 2747# 2748create table t1 (a int); 2749insert into t1 values (3), (7), (1); 2750values (8), ((select * from t1 where a between 2 and 4)); 27518 27528 27533 2754explain values (8), ((select * from t1 where a between 2 and 4)); 2755id select_type table type possible_keys key key_len ref rows Extra 27561 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 27572 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where 2758values ((select * from t1 where a between 2 and 4)), 2759((select * from t1 where a > 10)); 2760(select * from t1 where a between 2 and 4) 27613 2762NULL 2763explain values ((select * from t1 where a between 2 and 4)), 2764((select * from t1 where a > 10)); 2765id select_type table type possible_keys key key_len ref rows Extra 27661 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 27673 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where 27682 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where 2769values (10,11), ((select * from t1 where a = 7) + 1, 21); 277010 11 277110 11 27728 21 2773explain values (10,11), ((select * from t1 where a = 7) + 1, 21); 2774id select_type table type possible_keys key key_len ref rows Extra 27751 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 27762 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where 2777drop table t1; 2778# 2779# MDEV-24910: TVC containing subquery used as a subselect 2780# 2781create table t1 (a int) engine=myisam; 2782insert into t1 values (3), (7), (1); 2783create table t2 (b int) engine=myisam; 2784insert into t2 values (1), (2); 2785select (values ((select 2))) from t2; 2786(values ((select 2))) 27872 27882 2789explain select (values ((select 2))) from t2; 2790id select_type table type possible_keys key key_len ref rows Extra 27911 PRIMARY t2 ALL NULL NULL NULL NULL 2 27924 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 27932 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 2794Warnings: 2795Note 1249 Select 3 was reduced during optimization 2796prepare stmt from "select (values ((select 2))) from t2"; 2797execute stmt; 2798(values ((select 2))) 27992 28002 2801execute stmt; 2802(values ((select 2))) 28032 28042 2805deallocate prepare stmt; 2806select (values ((select * from t1 where a > 10))) from t2; 2807(values ((select * from t1 where a > 10))) 2808NULL 2809NULL 2810explain select (values ((select * from t1 where a > 10))) from t2; 2811id select_type table type possible_keys key key_len ref rows Extra 28121 PRIMARY t2 ALL NULL NULL NULL NULL 2 28134 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 28142 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 28153 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where 2816prepare stmt from "select (values ((select * from t1 where a > 10))) from t2"; 2817execute stmt; 2818(values ((select * from t1 where a > 10))) 2819NULL 2820NULL 2821execute stmt; 2822(values ((select * from t1 where a > 10))) 2823NULL 2824NULL 2825deallocate prepare stmt; 2826create table t3 (a int); 2827insert into t3 values 2828(3), (7), (7), (1), (3), (9), (7), (9), (8), (7), (8); 2829create view v1 as select count(a) as c from t3 group by a; 2830select 2831(values ((select * from t3 where a in (select * from v1)))); 2832(values ((select * from t3 where a in (select * from v1)))) 28331 2834explain select 2835(values ((select * from t3 where a in (select * from v1)))); 2836id select_type table type possible_keys key key_len ref rows Extra 28371 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 28386 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 28392 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 28403 SUBQUERY t3 ALL NULL NULL NULL NULL 11 28413 SUBQUERY <subquery4> eq_ref distinct_key distinct_key 8 func 1 Using where 28424 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 11 28435 DERIVED t3 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort 2844prepare stmt from "select 2845(values ((select * from t3 where a in (select * from v1))))"; 2846execute stmt; 2847(values ((select * from t3 where a in (select * from v1)))) 28481 2849execute stmt; 2850(values ((select * from t3 where a in (select * from v1)))) 28511 2852deallocate prepare stmt; 2853select 2854(values ((select * from t3 2855where a > 10 and a in (select * from v1)))); 2856(values ((select * from t3 2857where a > 10 and a in (select * from v1)))) 2858NULL 2859explain select 2860(values ((select * from t3 2861where a > 10 and a in (select * from v1)))); 2862id select_type table type possible_keys key key_len ref rows Extra 28631 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 28646 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 28652 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 28663 SUBQUERY t3 ALL NULL NULL NULL NULL 11 Using where 28673 SUBQUERY <subquery4> eq_ref distinct_key distinct_key 8 func 1 Using where 28684 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 11 28695 DERIVED t3 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort 2870prepare stmt from "select 2871(values ((select * from t3 2872where a > 10 and a in (select * from v1))))"; 2873execute stmt; 2874(values ((select * from t3 2875where a > 10 and a in (select * from v1)))) 2876NULL 2877execute stmt; 2878(values ((select * from t3 2879where a > 10 and a in (select * from v1)))) 2880NULL 2881deallocate prepare stmt; 2882drop view v1; 2883drop table t1,t2,t3; 2884# 2885# MDEV-24919: subselect formed by TVC and used in set function 2886# 2887select sum((values(1))); 2888sum((values(1))) 28891 2890# 2891# MDEV-22786: Nested table values constructors 2892# 2893values ((values (2))); 2894(values (2)) 28952 2896values ((values (2)), (5), (select 4)); 2897(values (2)) 5 (select 4) 28982 5 4 2899values ((7), (values (2)), (5), (select 4)); 29007 (values (2)) 5 (select 4) 29017 2 5 4 2902values ((values (2))) union values ((values (3))); 2903(values (2)) 29042 29053 2906values ((values (2))), ((values (3))); 2907(values (2)) 29082 29093 2910values ((values (2))), ((select 4)), ((values (3))); 2911(values (2)) 29122 29134 29143 2915values ((values (4)), (values (5))), ((values (1)), (values (7))); 2916(values (4)) (values (5)) 29174 5 29181 7 2919values ((values (4)), (select 5)), ((select 1), (values (7))); 2920(values (4)) (select 5) 29214 5 29221 7 2923values ((select 2)) union values ((values (3))); 2924(select 2) 29252 29263 2927values ((values (2))) union values((select 3)); 2928(values (2)) 29292 29303 2931values ((values (2))) union all values ((values (2))); 2932(values (2)) 29332 29342 2935values ((values (4)), (values (5))), ((values (1)), (values (7))) 2936union 2937values ((values (4)), (select 5)), ((select 2), (values (8))); 2938(values (4)) (values (5)) 29394 5 29401 7 29412 8 2942values ((values (4)), (values (5))), ((values (1)), (values (7))) 2943union all 2944values ((values (4)), (select 5)), ((select 2), (values (8))); 2945(values (4)) (values (5)) 29464 5 29471 7 29484 5 29492 8 2950values ((values (1) union values (1))); 2951(values (1) union values (1)) 29521 2953values ((values (1) union values (1) union values (1))); 2954(values (1) union values (1) union values (1)) 29551 2956values ((values ((values (4))))); 2957(values ((values (4)))) 29584 2959values ((values ((select 5)))); 2960(values ((select 5))) 29615 2962values ((select (values (4))), (values ((values(5))))); 2963(select (values (4))) (values ((values(5)))) 29644 5 2965values ((select (values (4))), (values ((select 5)))); 2966(select (values (4))) (values ((select 5))) 29674 5 2968values ((select (values (4))), (values ((values(5))))) 2969union 2970values ((select (values (4))), (values ((select 7)))); 2971(select (values (4))) (values ((values(5)))) 29724 5 29734 7 2974values ((values (2))), ((values ((values (4))))); 2975(values (2)) 29762 29774 2978values ((values (2))), ((values ((select 4)))); 2979(values (2)) 29802 29814 2982values ((values (2))), ((values ((values (4))))) 2983union 2984values ((values (8))), ((values ((select 4)))); 2985(values (2)) 29862 29874 29888 2989values ((values (2))), ((values ((values (4))))) 2990union all 2991values ((values (8))), ((values ((select 4)))); 2992(values (2)) 29932 29944 29958 29964 2997select * from (values ((values (2)))) dt; 2998(values (2)) 29992 3000select * from (values ((values (2)), (5), (select 4))) dt; 3001(values (2)) 5 (select 4) 30022 5 4 3003select * from (values ((values (2))) union values ((values (3)))) dt; 3004(values (2)) 30052 30063 3007select * from (values ((values (2))), ((values (3)))) dt; 3008(values (2)) 30092 30103 3011select * from (values ((values (2))), ((values (3)))) dt; 3012(values (2)) 30132 30143 3015select * from (values ((values (2))), ((select 4)), ((values (3)))) dt; 3016(values (2)) 30172 30184 30193 3020create table t1 (a int); 3021insert into t1 values (3), (7), (1); 3022values ((values ((select a from t1 where a=7)))); 3023(values ((select a from t1 where a=7))) 30247 3025values ((values ((select (values(2)) from t1 where a=8)))); 3026(values ((select (values(2)) from t1 where a=8))) 3027NULL 3028values ((values ((select a from t1 where a=7)))) 3029union 3030values ((values ((select (values(2)) from t1 where a=8)))); 3031(values ((select a from t1 where a=7))) 30327 3033NULL 3034values ((values ((select a from t1 where a in ((values (7))))))); 3035(values ((select a from t1 where a in ((values (7)))))) 30367 3037values ((values ((select a from t1 where a in ((values (7), (8))))))); 3038(values ((select a from t1 where a in ((values (7), (8)))))) 30397 3040values ((values 3041((select a from t1 where a in (values (7) union values (8)))))); 3042(values 3043((select a from t1 where a in (values (7) union values (8))))) 30447 3045values ((values ((select (values(2)) from t1 where a=8)))); 3046(values ((select (values(2)) from t1 where a=8))) 3047NULL 3048values ((select (values(2)) from t1 where a<7)); 3049ERROR 21000: Subquery returns more than 1 row 3050select * from (values ((values ((select a from t1 where a=7))))) dt; 3051(values ((select a from t1 where a=7))) 30527 3053select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt; 3054(values ((select (values(2)) from t1 where a=8))) 3055NULL 3056insert into t1(a) values ((values (2))), ((values (3))); 3057select * from t1; 3058a 30593 30607 30611 30622 30633 3064drop table t1; 3065# 3066# MDEV-25484: Derived table using TVC with LIMIT and ORDER BY 3067# 3068create table t1 (a int); 3069insert into t1 values (3), (7), (1); 3070select * from ( (select * from t1 limit 2) order by 1 desc) as dt; 3071a 30723 30737 3074(values (3), (7), (1) limit 2) order by 1 desc; 30753 30767 30773 3078select * from ( (values (3), (7), (1) limit 2) order by 1 desc) as dt; 30793 30803 30817 3082select * from ( select * from t1 order by 1 limit 2 ) as dt; 3083a 30841 30853 3086values (3),(7),(1) order by 1 limit 2; 30873 30881 30893 3090select * from ( values (3),(7),(1) order by 1 limit 2 ) as dt; 30913 30921 30933 3094values (3),(7),(1) union values (2),(4) order by 1 limit 2; 30953 30961 30972 3098select * from (values (3),(7),(1) union values (2),(4) order by 1 limit 2) as dt; 30993 31001 31012 3102drop table t1; 3103# 3104# MDEV-23182: Server crashes in 3105# Item::fix_fields_if_needed / table_value_constr::prepare upon 2nd execution of PS 3106# 3107SET @save_in_predicate_conversion_threshold=@@in_predicate_conversion_threshold; 3108SET in_predicate_conversion_threshold=2; 3109CREATE TABLE t1 (c VARCHAR(10)) DEFAULT CHARSET=utf8; 3110PREPARE stmt FROM "SELECT * FROM t1 WHERE c IN ('10','20')"; 3111EXECUTE stmt; 3112c 3113# Without the patch second execution of the prepared statement 'stmt' 3114# results in crash. 3115EXECUTE stmt; 3116c 3117DEALLOCATE PREPARE stmt; 3118DROP TABLE t1; 3119# Check that the query without conversion doesn't crash server 3120CREATE TABLE t1 (c VARCHAR(10)); 3121PREPARE stmt FROM "SELECT * FROM t1 WHERE c IN ('10','20')"; 3122EXECUTE stmt; 3123c 3124EXECUTE stmt; 3125c 3126DEALLOCATE PREPARE stmt; 3127DROP TABLE t1; 3128# Test case for a row expression in the left part of the IN clause 3129CREATE TABLE t1 (a VARCHAR(3), b VARCHAR(3)) DEFAULT CHARSET=utf8; 3130PREPARE stmt FROM "SELECT * FROM t1 WHERE (a, b) IN (('10', '10'), ('20', '20'))"; 3131EXECUTE stmt; 3132a b 3133EXECUTE stmt; 3134a b 3135DROP TABLE t1; 3136# Check that the query without conversion is handled successfully 3137CREATE TABLE t1 (a VARCHAR(3), b VARCHAR(3)); 3138PREPARE stmt FROM "SELECT * FROM t1 WHERE (a, b) IN (('10', '10'), ('20', '20'))"; 3139EXECUTE stmt; 3140a b 3141EXECUTE stmt; 3142a b 3143DROP TABLE t1; 3144SET @@in_predicate_conversion_threshold = @save_in_predicate_conversion_threshold; 3145End of 10.3 tests 3146# 3147# MDEV-22610 Crash in INSERT INTO t1 (VALUES (DEFAULT) UNION VALUES (DEFAULT)) 3148# 3149VALUES (DEFAULT) UNION VALUES (DEFAULT); 3150ERROR HY000: 'default' is not allowed in this context 3151VALUES (IGNORE) UNION VALUES (IGNORE); 3152ERROR HY000: 'ignore' is not allowed in this context 3153CREATE TABLE t1 (a INT DEFAULT 10); 3154INSERT INTO t1 (VALUES (DEFAULT) UNION VALUES (DEFAULT)); 3155ERROR HY000: 'default' is not allowed in this context 3156INSERT INTO t1 (VALUES (IGNORE) UNION VALUES (IGNORE)); 3157ERROR HY000: 'ignore' is not allowed in this context 3158DROP TABLE t1; 3159# 3160# End of 10.4 tests 3161# 3162