1drop table if exists t1; 2select (1,2,3) IN ((3,2,3), (1,2,3), (1,3,3)); 3(1,2,3) IN ((3,2,3), (1,2,3), (1,3,3)) 41 5select row(10,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3)); 6row(10,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3)) 70 8select row(1,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3)); 9row(1,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3)) 101 11select row(10,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3)); 12row(10,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3)) 130 14select row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a')); 15row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a')) 161 17Warnings: 18Warning 1292 Truncated incorrect DECIMAL value: 'a' 19Warning 1292 Truncated incorrect INTEGER value: 'a' 20select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3)); 21row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3)) 221 23Warnings: 24Warning 1292 Truncated incorrect INTEGER value: 'a' 25select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3)); 26row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3)) 271 28select row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)); 29row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)) 301 31select row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)); 32row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)) 330 34select row('b',1.5,3) IN (row('b',NULL,3), row('a',1.5,3), row(1,3,3)); 35row('b',1.5,3) IN (row('b',NULL,3), row('a',1.5,3), row(1,3,3)) 36NULL 37select row('b',1.5,3) IN (row('b',NULL,4), row('a',1.5,3), row(1,3,3)); 38row('b',1.5,3) IN (row('b',NULL,4), row('a',1.5,3), row(1,3,3)) 390 40select (1,2,(3,4)) IN ((3,2,(3,4)), (1,2,(3,4))); 41(1,2,(3,4)) IN ((3,2,(3,4)), (1,2,(3,4))) 421 43select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,4)); 44ERROR 21000: Operand should contain 2 column(s) 45select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL))); 46row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL))) 47NULL 48explain extended select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL))); 49id select_type table partitions type possible_keys key key_len ref rows filtered Extra 501 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 51Warnings: 52Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 53Note 1003 /* select#1 */ select ((1,2,(3,4)) in ((3,2,(3,4)),(1,2,(3,NULL)))) AS `row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL)))` 54select row(1,2,row(3,null)) IN (row(3,2,row(3,4)), row(1,2,row(4,5))); 55row(1,2,row(3,null)) IN (row(3,2,row(3,4)), row(1,2,row(4,5))) 560 57select row(1,2,row(3,null)) IN (row(3,2,row(3,4)), row(1,2,row(3,5))); 58row(1,2,row(3,null)) IN (row(3,2,row(3,4)), row(1,2,row(3,5))) 59NULL 60SELECT (1,2,3)=(0,NULL,3); 61(1,2,3)=(0,NULL,3) 620 63SELECT (1,2,3)=(1,NULL,3); 64(1,2,3)=(1,NULL,3) 65NULL 66SELECT (1,2,3)=(1,NULL,0); 67(1,2,3)=(1,NULL,0) 680 69SELECT ROW(1,2,3)=ROW(1,2,3); 70ROW(1,2,3)=ROW(1,2,3) 711 72SELECT ROW(2,2,3)=ROW(1+1,2,3); 73ROW(2,2,3)=ROW(1+1,2,3) 741 75SELECT ROW(1,2,3)=ROW(1+1,2,3); 76ROW(1,2,3)=ROW(1+1,2,3) 770 78SELECT ROW(1,2,3)<ROW(1+1,2,3); 79ROW(1,2,3)<ROW(1+1,2,3) 801 81SELECT ROW(1,2,3)>ROW(1+1,2,3); 82ROW(1,2,3)>ROW(1+1,2,3) 830 84SELECT ROW(1,2,3)<=ROW(1+1,2,3); 85ROW(1,2,3)<=ROW(1+1,2,3) 861 87SELECT ROW(1,2,3)>=ROW(1+1,2,3); 88ROW(1,2,3)>=ROW(1+1,2,3) 890 90SELECT ROW(1,2,3)<>ROW(1+1,2,3); 91ROW(1,2,3)<>ROW(1+1,2,3) 921 93SELECT ROW(NULL,2,3)=ROW(NULL,2,3); 94ROW(NULL,2,3)=ROW(NULL,2,3) 95NULL 96SELECT ROW(NULL,2,3)<=>ROW(NULL,2,3); 97ROW(NULL,2,3)<=>ROW(NULL,2,3) 981 99SELECT ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5)); 100ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5)) 1011 102SELECT ROW('test',2,3.33)=ROW('test',2,3.33); 103ROW('test',2,3.33)=ROW('test',2,3.33) 1041 105SELECT ROW('test',2,3.33)=ROW('test',2,3.33,4); 106ERROR 21000: Operand should contain 3 column(s) 107SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,33)); 108ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,33)) 1091 110SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,3)); 111ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,3)) 1120 113SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,NULL)); 114ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,NULL)) 115NULL 116SELECT ROW('test',2,ROW(3,33))=ROW('test',2,4); 117ERROR 21000: Operand should contain 2 column(s) 118create table t1 ( a int, b int, c int); 119insert into t1 values (1,2,3), (2,3,1), (3,2,1), (1,2,NULL); 120select * from t1 where ROW(1,2,3)=ROW(a,b,c); 121a b c 1221 2 3 123select * from t1 where ROW(0,2,3)=ROW(a,b,c); 124a b c 125select * from t1 where ROW(1,2,3)<ROW(a,b,c); 126a b c 1272 3 1 1283 2 1 129select ROW(a,2,3) IN(row(1,b,c), row(2,3,1)) from t1; 130ROW(a,2,3) IN(row(1,b,c), row(2,3,1)) 1311 1320 1330 134NULL 135select ROW(c,2,3) IN(row(1,b,a), row(2,3,1)) from t1; 136ROW(c,2,3) IN(row(1,b,a), row(2,3,1)) 1370 1380 1391 1400 141select ROW(a,b,c) IN(row(1,2,3), row(3,2,1)) from t1; 142ROW(a,b,c) IN(row(1,2,3), row(3,2,1)) 1431 1440 1451 146NULL 147select ROW(1,2,3) IN(row(a,b,c), row(1,2,3)) from t1; 148ROW(1,2,3) IN(row(a,b,c), row(1,2,3)) 1491 1501 1511 1521 153drop table t1; 154select ROW(1,1); 155ERROR 21000: Operand should contain 1 column(s) 156create table t1 (i int); 157select 1 from t1 where ROW(1,1); 158ERROR 21000: Operand should contain 1 column(s) 159select count(*) from t1 order by ROW(1,1); 160ERROR 21000: Operand should contain 1 column(s) 161select count(*) from t1 having (1,1) order by i; 162ERROR 21000: Operand should contain 1 column(s) 163drop table t1; 164create table t1 (a int, b int); 165insert into t1 values (1, 4); 166insert into t1 values (10, 40); 167insert into t1 values (1, 4); 168insert into t1 values (10, 43); 169insert into t1 values (1, 4); 170insert into t1 values (10, 41); 171insert into t1 values (1, 4); 172insert into t1 values (10, 43); 173insert into t1 values (1, 4); 174select a, MAX(b), (1, MAX(b)) = (1, 4) from t1 group by a; 175a MAX(b) (1, MAX(b)) = (1, 4) 1761 4 1 17710 43 0 178drop table t1; 179SELECT ROW(2,10) <=> ROW(3,4); 180ROW(2,10) <=> ROW(3,4) 1810 182SELECT ROW(NULL,10) <=> ROW(3,NULL); 183ROW(NULL,10) <=> ROW(3,NULL) 1840 185SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),ROW(1,1)); 186ERROR 21000: Operand should contain 2 column(s) 187SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),ROW(1,1),ROW(1,ROW(2,3))); 188ERROR 21000: Operand should contain 2 column(s) 189SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),ROW(1,ROW(2,2,2))); 190ERROR 21000: Operand should contain 2 column(s) 191SELECT ROW(1,ROW(2,3,4)) IN (ROW(1,ROW(2,3,4)),ROW(1,ROW(2,2))); 192ERROR 21000: Operand should contain 3 column(s) 193SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),(SELECT 1,1)); 194ERROR 21000: Operand should contain 2 column(s) 195SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),(SELECT 1,1),ROW(1,ROW(2,4))); 196ERROR 21000: Operand should contain 2 column(s) 197SELECT ROW(1,ROW(2,3)) IN ((SELECT 1,1),ROW(1,ROW(2,3))); 198ERROR 21000: Operand should contain 2 column(s) 199SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1,1,3),0)); 200ERROR 21000: Operand should contain 1 column(s) 201SELECT ROW(2,1) IN (ROW(ROW(1,1,3),0),ROW(21,2)); 202ERROR 21000: Operand should contain 1 column(s) 203CREATE TABLE t1(a int, b int, c int); 204INSERT INTO t1 VALUES (1, 2, 3), 205(NULL, 2, 3 ), (1, NULL, 3 ), (1, 2, NULL), 206(NULL, 2, 3+1), (1, NULL, 3+1), (1, 2+1, NULL), 207(NULL, 2, 3-1), (1, NULL, 3-1), (1, 2-1, NULL); 208SELECT (1,2,3) = (1, NULL, 3); 209(1,2,3) = (1, NULL, 3) 210NULL 211SELECT (1,2,3) = (1+1, NULL, 3); 212(1,2,3) = (1+1, NULL, 3) 2130 214SELECT (1,2,3) = (1, NULL, 3+1); 215(1,2,3) = (1, NULL, 3+1) 2160 217SELECT * FROM t1 WHERE (a,b,c) = (1,2,3); 218a b c 2191 2 3 220SELECT (1,2,3) <> (1, NULL, 3); 221(1,2,3) <> (1, NULL, 3) 222NULL 223SELECT (1,2,3) <> (1+1, NULL, 3); 224(1,2,3) <> (1+1, NULL, 3) 2251 226SELECT (1,2,3) <> (1, NULL, 3+1); 227(1,2,3) <> (1, NULL, 3+1) 2281 229SELECT * FROM t1 WHERE (a,b,c) <> (1,2,3); 230a b c 231NULL 2 4 2321 NULL 4 2331 3 NULL 234NULL 2 2 2351 NULL 2 2361 1 NULL 237SELECT (1,2,3) < (NULL, 2, 3); 238(1,2,3) < (NULL, 2, 3) 239NULL 240SELECT (1,2,3) < (1, NULL, 3); 241(1,2,3) < (1, NULL, 3) 242NULL 243SELECT (1,2,3) < (1-1, NULL, 3); 244(1,2,3) < (1-1, NULL, 3) 2450 246SELECT (1,2,3) < (1+1, NULL, 3); 247(1,2,3) < (1+1, NULL, 3) 2481 249SELECT * FROM t1 WHERE (a,b,c) < (1,2,3); 250a b c 2511 1 NULL 252SELECT (1,2,3) <= (NULL, 2, 3); 253(1,2,3) <= (NULL, 2, 3) 254NULL 255SELECT (1,2,3) <= (1, NULL, 3); 256(1,2,3) <= (1, NULL, 3) 257NULL 258SELECT (1,2,3) <= (1-1, NULL, 3); 259(1,2,3) <= (1-1, NULL, 3) 2600 261SELECT (1,2,3) <= (1+1, NULL, 3); 262(1,2,3) <= (1+1, NULL, 3) 2631 264SELECT * FROM t1 WHERE (a,b,c) <= (1,2,3); 265a b c 2661 2 3 2671 1 NULL 268SELECT (1,2,3) > (NULL, 2, 3); 269(1,2,3) > (NULL, 2, 3) 270NULL 271SELECT (1,2,3) > (1, NULL, 3); 272(1,2,3) > (1, NULL, 3) 273NULL 274SELECT (1,2,3) > (1-1, NULL, 3); 275(1,2,3) > (1-1, NULL, 3) 2761 277SELECT (1,2,3) > (1+1, NULL, 3); 278(1,2,3) > (1+1, NULL, 3) 2790 280SELECT * FROM t1 WHERE (a,b,c) > (1,2,3); 281a b c 2821 3 NULL 283SELECT (1,2,3) >= (NULL, 2, 3); 284(1,2,3) >= (NULL, 2, 3) 285NULL 286SELECT (1,2,3) >= (1, NULL, 3); 287(1,2,3) >= (1, NULL, 3) 288NULL 289SELECT (1,2,3) >= (1-1, NULL, 3); 290(1,2,3) >= (1-1, NULL, 3) 2911 292SELECT (1,2,3) >= (1+1, NULL, 3); 293(1,2,3) >= (1+1, NULL, 3) 2940 295SELECT * FROM t1 WHERE (a,b,c) >= (1,2,3); 296a b c 2971 2 3 2981 3 NULL 299DROP TABLE t1; 300SELECT ROW(1,1,1) = ROW(1,1,1) as `1`, ROW(1,1,1) = ROW(1,2,1) as `0`, ROW(1,NULL,1) = ROW(2,2,1) as `0`, ROW(1,NULL,1) = ROW(1,2,2) as `0`, ROW(1,NULL,1) = ROW(1,2,1) as `null` ; 3011 0 0 0 null 3021 0 0 0 NULL 303select row(NULL,1)=(2,0); 304row(NULL,1)=(2,0) 3050 306CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b)); 307INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (1,2), (3,2), (3,3); 308EXPLAIN SELECT * FROM t1 WHERE a=3 AND b=2; 309id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3101 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const,const 1 100.00 Using index 311Warnings: 312Note 1003 /* select#1 */ select '3' AS `a`,'2' AS `b` from `test`.`t1` where 1 313EXPLAIN SELECT * FROM t1 WHERE (a,b)=(3,2); 314id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3151 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const,const 1 100.00 Using index 316Warnings: 317Note 1003 /* select#1 */ select '3' AS `a`,'2' AS `b` from `test`.`t1` where 1 318SELECT * FROM t1 WHERE a=3 and b=2; 319a b 3203 2 321SELECT * FROM t1 WHERE (a,b)=(3,2); 322a b 3233 2 324CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)); 325INSERT INTO t2 VALUES 326(1,1,2), (3,1,3), (1,2,2), (4,4,2), 327(1,1,1), (3,1,1), (1,2,1); 328EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; 329id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3301 SIMPLE t1 NULL index PRIMARY PRIMARY 8 NULL 6 100.00 Using index 3311 SIMPLE t2 NULL ref PRIMARY PRIMARY 8 test.t1.a,test.t1.b 1 100.00 Using index 332Warnings: 333Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`)) 334EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b); 335id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3361 SIMPLE t1 NULL index PRIMARY PRIMARY 8 NULL 6 100.00 Using index 3371 SIMPLE t2 NULL ref PRIMARY PRIMARY 8 test.t1.a,test.t1.b 1 100.00 Using index 338Warnings: 339Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`)) 340SELECT * FROM t1,t2 WHERE t1.a=t2.a and t1.b=t2.b; 341a b a b c 3421 1 1 1 1 3431 1 1 1 2 3441 2 1 2 1 3451 2 1 2 2 3463 1 3 1 1 3473 1 3 1 3 348SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b); 349a b a b c 3501 1 1 1 1 3511 1 1 1 2 3521 2 1 2 1 3531 2 1 2 2 3543 1 3 1 1 3553 1 3 1 3 356EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2; 357id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3581 SIMPLE t1 NULL index PRIMARY PRIMARY 8 NULL 6 16.67 Using where; Using index 3591 SIMPLE t2 NULL ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index 360Warnings: 361Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`b` = 2) and (`test`.`t2`.`a` = `test`.`t1`.`a`)) 362EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2); 363id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3641 SIMPLE t1 NULL index PRIMARY PRIMARY 8 NULL 6 16.67 Using where; Using index 3651 SIMPLE t2 NULL ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index 366Warnings: 367Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`b` = 2) and (`test`.`t2`.`a` = `test`.`t1`.`a`)) 368SELECT * FROM t1,t2 WHERE t1.a=1 and t1.b=t2.b; 369a b a b c 3701 1 1 1 1 3711 1 1 1 2 3721 2 1 2 1 3731 2 1 2 2 3741 1 3 1 1 3751 1 3 1 3 376SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2); 377a b a b c 3781 2 1 1 1 3791 2 1 1 2 3801 2 1 2 1 3811 2 1 2 2 3823 2 3 1 1 3833 2 3 1 3 384EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1); 385id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3861 SIMPLE t1 NULL index PRIMARY PRIMARY 8 NULL 6 100.00 Using index 3871 SIMPLE t2 NULL ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using where; Using index 388Warnings: 389Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 390Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = (`test`.`t2`.`b` + 1))) 391SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1); 392a b a b c 3931 2 1 1 1 3941 2 1 1 2 3953 2 3 1 1 3963 2 3 1 3 397EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); 398id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3991 SIMPLE t1 NULL index NULL PRIMARY 8 NULL 6 100.00 Using index 4001 SIMPLE t2 NULL index NULL PRIMARY 12 NULL 7 100.00 Using where; Using index; Using join buffer (Block Nested Loop) 401Warnings: 402Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 403Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where (((`test`.`t1`.`a` - 1) = (`test`.`t2`.`a` - 1)) and (`test`.`t1`.`b` = (`test`.`t2`.`b` + 1))) 404SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); 405a b a b c 4061 2 1 1 1 4071 2 1 1 2 4083 2 3 1 1 4093 2 3 1 3 410EXPLAIN SELECT * FROM t2 WHERE a=3 AND b=2; 411id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4121 SIMPLE t2 NULL ref PRIMARY PRIMARY 8 const,const 1 100.00 Using index 413Warnings: 414Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`b` = 2) and (`test`.`t2`.`a` = 3)) 415EXPLAIN SELECT * FROM t2 WHERE (a,b)=(3,2); 416id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4171 SIMPLE t2 NULL ref PRIMARY PRIMARY 8 const,const 1 100.00 Using index 418Warnings: 419Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`b` = 2) and (`test`.`t2`.`a` = 3)) 420SELECT * FROM t2 WHERE a=3 and b=2; 421a b c 422SELECT * FROM t2 WHERE (a,b)=(3,2); 423a b c 424EXPLAIN SELECT * FROM t1,t2 WHERE t2.a=t1.a AND t2.b=2 AND t2.c=1; 425id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4261 SIMPLE t2 NULL index PRIMARY PRIMARY 12 NULL 7 14.29 Using where; Using index 4271 SIMPLE t1 NULL ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index 428Warnings: 429Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`c` = 1) and (`test`.`t2`.`b` = 2) and (`test`.`t1`.`a` = `test`.`t2`.`a`)) 430EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1)); 431id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4321 SIMPLE t2 NULL index PRIMARY PRIMARY 12 NULL 7 14.29 Using where; Using index 4331 SIMPLE t1 NULL ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index 434Warnings: 435Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 436Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`c` = 1) and (`test`.`t2`.`b` = 2) and (`test`.`t1`.`a` = `test`.`t2`.`a`)) 437SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1)); 438a b a b c 4391 1 1 2 1 4401 2 1 2 1 441EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); 442id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4431 SIMPLE t2 NULL index PRIMARY PRIMARY 12 NULL 7 14.29 Using where; Using index 4441 SIMPLE t1 NULL ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index 445Warnings: 446Warning 1681 'EXTENDED' is deprecated and will be removed in a future release. 447Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`c` = 1) and (`test`.`t2`.`b` = 2) and (`test`.`t1`.`a` = `test`.`t2`.`a`)) 448SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); 449a b a b c 4501 1 1 2 1 4511 2 1 2 1 452DROP TABLE t1,t2; 453CREATE TABLE t1( 454a int, b int, c int, d int, e int, f int, g int, h int, 455PRIMARY KEY (a,b,c,d,e,f,g) 456); 457INSERT INTO t1 VALUES (1,2,3,4,5,6,7,99); 458SELECT h FROM t1 WHERE (a,b,c,d,e,f,g)=(1,2,3,4,5,6,7); 459h 46099 461SET @x:= (SELECT h FROM t1 WHERE (a,b,c,d,e,f,g)=(1,2,3,4,5,6,7)); 462SELECT @x; 463@x 46499 465DROP TABLE t1; 466CREATE TABLE t1 (a INT, b INT); 467INSERT INTO t1 VALUES (1,1); 468SELECT ROW(a, 1) IN (SELECT SUM(b), 1) FROM t1 GROUP BY a; 469ROW(a, 1) IN (SELECT SUM(b), 1) 4701 471SELECT ROW(a, 1) IN (SELECT SUM(b), 3) FROM t1 GROUP BY a; 472ROW(a, 1) IN (SELECT SUM(b), 3) 4730 474DROP TABLE t1; 475create table t1 (a varchar(200), 476b int unsigned not null primary key auto_increment) 477default character set 'utf8'; 478create table t2 (c varchar(200), 479d int unsigned not null primary key auto_increment) 480default character set 'latin1'; 481insert into t1 (a) values('abc'); 482insert into t2 (c) values('abc'); 483select * from t1,t2 where (a,b) = (c,d); 484a b c d 485abc 1 abc 1 486select host,user from mysql.user where (host,user) = ('localhost','test'); 487host user 488drop table t1,t2; 489# 490# Bug#52124 memory leaks like a sieve in datetime, timestamp, time, date fields + warnings 491# 492CREATE TABLE t1 (a DATETIME NOT NULL, b TINYINT); 493INSERT IGNORE INTO t1 VALUES (0, 0),(0, 0); 494Warnings: 495Warning 1264 Out of range value for column 'a' at row 1 496Warning 1264 Out of range value for column 'a' at row 2 497SELECT 1 FROM t1 WHERE ROW(a, b) >= 498ROW('1', (SELECT 1 FROM t1 WHERE a > 1234)); 4991 500DROP TABLE t1; 501# 502# Bug #54190: Comparison to row subquery produces incorrect result 503# 504SELECT ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0); 505ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0) 506NULL 507SELECT ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0); 508ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0) 509NULL 510CREATE TABLE t1 (i INT); 511INSERT INTO t1 () VALUES (1), (2), (3); 512SELECT ROW(1,2) = (SELECT 1,2 FROM t1 WHERE 1 = 0); 513ROW(1,2) = (SELECT 1,2 FROM t1 WHERE 1 = 0) 514NULL 515SELECT ROW(1,2) = (SELECT 1,3 FROM t1 WHERE 1 = 0); 516ROW(1,2) = (SELECT 1,3 FROM t1 WHERE 1 = 0) 517NULL 518SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0); 519i 520SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0); 521i 522DROP TABLE t1; 523End of 5.1 tests 524# 525# Bug#18745214: ASSERTION FAILED: FALSE, FILE SQL_TMP_TABLE.CC OR 526# MALFORMED PACKET 527# 528CREATE TABLE t1 ( a INT ) ENGINE = INNODB; 529INSERT INTO t1 VALUES ( 1 ); 530SELECT row( 1, 1, 1 ) = row( 1, 1 ) FROM t1 GROUP BY a; 531ERROR 21000: Operand should contain 3 column(s) 532EXPLAIN SELECT row( min(a), 1 ) = row( a, 1 ) AS al FROM t1 GROUP BY a; 533id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5341 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort 535Warnings: 536Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`) = `test`.`t1`.`a`) and (1 = 1)) AS `al` from `test`.`t1` group by `test`.`t1`.`a` 537SELECT row( min(a), 1 ) = row( a, 1 ) AS al FROM t1 GROUP BY a; 538al 5391 540EXPLAIN SELECT row( min(a), 1 ) <> row( a, 1 ) AS al FROM t1 GROUP BY a; 541id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5421 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort 543Warnings: 544Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`) <> `test`.`t1`.`a`) or (1 <> 1)) AS `al` from `test`.`t1` group by `test`.`t1`.`a` 545SELECT row( min(a), 1 ) <> row( a, 1 ) AS al FROM t1 GROUP BY a; 546al 5470 548EXPLAIN SELECT row( min(a), 1 ) <=> row( a, 1 ) AS al FROM t1 GROUP BY a; 549id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5501 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort 551Warnings: 552Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`) <=> `test`.`t1`.`a`) and (1 <=> 1)) AS `al` from `test`.`t1` group by `test`.`t1`.`a` 553SELECT row( min(a), 1 ) <=> row( a, 1 ) AS al FROM t1 GROUP BY a; 554al 5551 556EXPLAIN SELECT row( min(a), 1 ) > row( a, 1 ) AS al FROM t1 GROUP BY a; 557id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5581 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort 559Warnings: 560Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`),1) > (`test`.`t1`.`a`,1)) AS `al` from `test`.`t1` group by `test`.`t1`.`a` 561SELECT row( min(a), 1 ) > row( a, 1 ) AS al FROM t1 GROUP BY a; 562al 5630 564EXPLAIN SELECT row( min(a), 1 ) >= row( a, 1 ) AS al FROM t1 GROUP BY a; 565id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5661 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort 567Warnings: 568Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`),1) >= (`test`.`t1`.`a`,1)) AS `al` from `test`.`t1` group by `test`.`t1`.`a` 569SELECT row( min(a), 1 ) >= row( a, 1 ) AS al FROM t1 GROUP BY a; 570al 5711 572EXPLAIN SELECT row( min(a), 1 ) < row( a, 1 ) AS al FROM t1 GROUP BY a; 573id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5741 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort 575Warnings: 576Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`),1) < (`test`.`t1`.`a`,1)) AS `al` from `test`.`t1` group by `test`.`t1`.`a` 577SELECT row( min(a), 1 ) < row( a, 1 ) AS al FROM t1 GROUP BY a; 578al 5790 580EXPLAIN SELECT row( min(a), 1 ) <= row( a, 1 ) AS al FROM t1 GROUP BY a; 581id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5821 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort 583Warnings: 584Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`),1) <= (`test`.`t1`.`a`,1)) AS `al` from `test`.`t1` group by `test`.`t1`.`a` 585SELECT row( min(a), 1 ) <= row( a, 1 ) AS al FROM t1 GROUP BY a; 586al 5871 588EXPLAIN SELECT row( row( min(a), 1 ), 1 ) = row( row( a, 1 ), 1 ) AS al 589FROM t1 GROUP BY a; 590id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5911 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort 592Warnings: 593Note 1003 /* select#1 */ select ((min(`test`.`t1`.`a`) = `test`.`t1`.`a`) and (1 = 1) and (1 = 1)) AS `al` from `test`.`t1` group by `test`.`t1`.`a` 594SELECT row( row( min(a), 1 ), 1 ) = row( row( a, 1 ), 1 ) AS al 595FROM t1 GROUP BY a; 596al 5971 598# Test of manually thrown error. 599SELECT 1 <=>ALL (SELECT 1); 600ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<=>ALL (SELECT 1)' at line 1 601DROP TABLE t1; 602