1# Initialise 2--disable_warnings 3drop table if exists t1; 4--enable_warnings 5 6select (1,2,3) IN ((3,2,3), (1,2,3), (1,3,3)); 7select row(10,2,3) IN (row(3,2,3), row(1,2,3), row(1,3,3)); 8select row(1,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3)); 9select row(10,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3)); 10select row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a')); 11select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3)); 12select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3)); 13select row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)); 14select row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)); 15select row('b',1.5,3) IN (row('b',NULL,3), row('a',1.5,3), row(1,3,3)); 16select row('b',1.5,3) IN (row('b',NULL,4), row('a',1.5,3), row(1,3,3)); 17select (1,2,(3,4)) IN ((3,2,(3,4)), (1,2,(3,4))); 18-- error 1241 19select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,4)); 20select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL))); 21explain extended select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL))); 22select row(1,2,row(3,null)) IN (row(3,2,row(3,4)), row(1,2,row(4,5))); 23select row(1,2,row(3,null)) IN (row(3,2,row(3,4)), row(1,2,row(3,5))); 24 25 26SELECT (1,2,3)=(0,NULL,3); 27SELECT (1,2,3)=(1,NULL,3); 28SELECT (1,2,3)=(1,NULL,0); 29 30SELECT ROW(1,2,3)=ROW(1,2,3); 31SELECT ROW(2,2,3)=ROW(1+1,2,3); 32SELECT ROW(1,2,3)=ROW(1+1,2,3); 33SELECT ROW(1,2,3)<ROW(1+1,2,3); 34SELECT ROW(1,2,3)>ROW(1+1,2,3); 35SELECT ROW(1,2,3)<=ROW(1+1,2,3); 36SELECT ROW(1,2,3)>=ROW(1+1,2,3); 37SELECT ROW(1,2,3)<>ROW(1+1,2,3); 38SELECT ROW(NULL,2,3)=ROW(NULL,2,3); 39SELECT ROW(NULL,2,3)<=>ROW(NULL,2,3); 40SELECT ROW(1,2,ROW(3,4,5))=ROW(1,2,ROW(3,4,5)); 41SELECT ROW('test',2,3.33)=ROW('test',2,3.33); 42-- error 1241 43SELECT ROW('test',2,3.33)=ROW('test',2,3.33,4); 44SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,33)); 45SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,3)); 46SELECT ROW('test',2,ROW(3,33))=ROW('test',2,ROW(3,NULL)); 47-- error 1241 48SELECT ROW('test',2,ROW(3,33))=ROW('test',2,4); 49 50create table t1 ( a int, b int, c int); 51insert into t1 values (1,2,3), (2,3,1), (3,2,1), (1,2,NULL); 52select * from t1 where ROW(1,2,3)=ROW(a,b,c); 53select * from t1 where ROW(0,2,3)=ROW(a,b,c); 54select * from t1 where ROW(1,2,3)<ROW(a,b,c); 55select ROW(a,2,3) IN(row(1,b,c), row(2,3,1)) from t1; 56select ROW(c,2,3) IN(row(1,b,a), row(2,3,1)) from t1; 57select ROW(a,b,c) IN(row(1,2,3), row(3,2,1)) from t1; 58select ROW(1,2,3) IN(row(a,b,c), row(1,2,3)) from t1; 59drop table t1; 60 61-- error 1241 62select ROW(1,1); 63 64create table t1 (i int); 65-- error 1241 66select 1 from t1 where ROW(1,1); 67-- error 1241 68select count(*) from t1 order by ROW(1,1); 69-- error 1241 70select count(*) from t1 having (1,1) order by i; 71drop table t1; 72 73create table t1 (a int, b int); 74insert into t1 values (1, 4); 75insert into t1 values (10, 40); 76insert into t1 values (1, 4); 77insert into t1 values (10, 43); 78insert into t1 values (1, 4); 79insert into t1 values (10, 41); 80insert into t1 values (1, 4); 81insert into t1 values (10, 43); 82insert into t1 values (1, 4); 83select a, MAX(b), (1, MAX(b)) = (1, 4) from t1 group by a; 84drop table t1; 85SELECT ROW(2,10) <=> ROW(3,4); 86SELECT ROW(NULL,10) <=> ROW(3,NULL); 87 88# 89# Bug #27484: nested row expressions in IN predicate 90# 91 92--error 1241 93SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),ROW(1,1)); 94--error 1241 95SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),ROW(1,1),ROW(1,ROW(2,3))); 96--error 1241 97SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),ROW(1,ROW(2,2,2))); 98--error 1241 99SELECT ROW(1,ROW(2,3,4)) IN (ROW(1,ROW(2,3,4)),ROW(1,ROW(2,2))); 100 101--error 1241 102SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),(SELECT 1,1)); 103--error 1241 104SELECT ROW(1,ROW(2,3)) IN (ROW(1,ROW(2,3)),(SELECT 1,1),ROW(1,ROW(2,4))); 105--error 1241 106SELECT ROW(1,ROW(2,3)) IN ((SELECT 1,1),ROW(1,ROW(2,3))); 107 108--error 1241 109SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1,1,3),0)); 110--error 1241 111SELECT ROW(2,1) IN (ROW(ROW(1,1,3),0),ROW(21,2)); 112 113# 114# Bug#27704: erroneous comparison of rows with NULL components 115# 116CREATE TABLE t1(a int, b int, c int); 117INSERT INTO t1 VALUES (1, 2, 3), 118 (NULL, 2, 3 ), (1, NULL, 3 ), (1, 2, NULL), 119 (NULL, 2, 3+1), (1, NULL, 3+1), (1, 2+1, NULL), 120 (NULL, 2, 3-1), (1, NULL, 3-1), (1, 2-1, NULL); 121 122SELECT (1,2,3) = (1, NULL, 3); 123SELECT (1,2,3) = (1+1, NULL, 3); 124SELECT (1,2,3) = (1, NULL, 3+1); 125SELECT * FROM t1 WHERE (a,b,c) = (1,2,3); 126 127SELECT (1,2,3) <> (1, NULL, 3); 128SELECT (1,2,3) <> (1+1, NULL, 3); 129SELECT (1,2,3) <> (1, NULL, 3+1); 130SELECT * FROM t1 WHERE (a,b,c) <> (1,2,3); 131 132SELECT (1,2,3) < (NULL, 2, 3); 133SELECT (1,2,3) < (1, NULL, 3); 134SELECT (1,2,3) < (1-1, NULL, 3); 135SELECT (1,2,3) < (1+1, NULL, 3); 136SELECT * FROM t1 WHERE (a,b,c) < (1,2,3); 137 138SELECT (1,2,3) <= (NULL, 2, 3); 139SELECT (1,2,3) <= (1, NULL, 3); 140SELECT (1,2,3) <= (1-1, NULL, 3); 141SELECT (1,2,3) <= (1+1, NULL, 3); 142SELECT * FROM t1 WHERE (a,b,c) <= (1,2,3); 143 144SELECT (1,2,3) > (NULL, 2, 3); 145SELECT (1,2,3) > (1, NULL, 3); 146SELECT (1,2,3) > (1-1, NULL, 3); 147SELECT (1,2,3) > (1+1, NULL, 3); 148SELECT * FROM t1 WHERE (a,b,c) > (1,2,3); 149 150SELECT (1,2,3) >= (NULL, 2, 3); 151SELECT (1,2,3) >= (1, NULL, 3); 152SELECT (1,2,3) >= (1-1, NULL, 3); 153SELECT (1,2,3) >= (1+1, NULL, 3); 154SELECT * FROM t1 WHERE (a,b,c) >= (1,2,3); 155 156DROP TABLE t1; 157 158# End of 4.1 tests 159 160# 161# Correct NULL handling in row comporison (BUG#12509) 162# 163SELECT 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` ; 164select row(NULL,1)=(2,0); 165 166# 167# Bug #16081: row equalities are to be used for query optimizations 168# 169 170CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b)); 171INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (1,2), (3,2), (3,3); 172 173EXPLAIN SELECT * FROM t1 WHERE a=3 AND b=2; 174EXPLAIN SELECT * FROM t1 WHERE (a,b)=(3,2); 175SELECT * FROM t1 WHERE a=3 and b=2; 176SELECT * FROM t1 WHERE (a,b)=(3,2); 177 178CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)); 179INSERT INTO t2 VALUES 180 (1,1,2), (3,1,3), (1,2,2), (4,4,2), 181 (1,1,1), (3,1,1), (1,2,1); 182 183EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b; 184EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b); 185SELECT * FROM t1,t2 WHERE t1.a=t2.a and t1.b=t2.b; 186SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b); 187 188EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2; 189EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2); 190--sorted_result 191SELECT * FROM t1,t2 WHERE t1.a=1 and t1.b=t2.b; 192SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2); 193 194EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1); 195SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1); 196 197EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); 198--sorted_result 199SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1); 200 201EXPLAIN SELECT * FROM t2 WHERE a=3 AND b=2; 202EXPLAIN SELECT * FROM t2 WHERE (a,b)=(3,2); 203SELECT * FROM t2 WHERE a=3 and b=2; 204SELECT * FROM t2 WHERE (a,b)=(3,2); 205 206EXPLAIN SELECT * FROM t1,t2 WHERE t2.a=t1.a AND t2.b=2 AND t2.c=1; 207EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1)); 208SELECT * FROM t1,t2 WHERE (t2.a,(t2.b,t2.c))=(t1.a,(2,1)); 209 210EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); 211SELECT * FROM t1,t2 WHERE t2.a=t1.a AND (t2.b,t2.c)=(2,1); 212 213DROP TABLE t1,t2; 214 215# 216# Bug #27154: crash (memory corruption) when using row equalities 217# 218 219CREATE TABLE t1( 220 a int, b int, c int, d int, e int, f int, g int, h int, 221 PRIMARY KEY (a,b,c,d,e,f,g) 222); 223INSERT INTO t1 VALUES (1,2,3,4,5,6,7,99); 224 225SELECT h FROM t1 WHERE (a,b,c,d,e,f,g)=(1,2,3,4,5,6,7); 226 227SET @x:= (SELECT h FROM t1 WHERE (a,b,c,d,e,f,g)=(1,2,3,4,5,6,7)); 228SELECT @x; 229 230DROP TABLE t1; 231 232# 233# Bug #34620: item_row.cc:50: Item_row::illegal_method_call(const char*): 234# Assertion `0' failed 235# 236 237CREATE TABLE t1 (a INT, b INT); 238INSERT INTO t1 VALUES (1,1); 239 240SELECT ROW(a, 1) IN (SELECT SUM(b), 1) FROM t1 GROUP BY a; 241SELECT ROW(a, 1) IN (SELECT SUM(b), 3) FROM t1 GROUP BY a; 242 243DROP TABLE t1; 244 245# 246# Bug#37601 Cast Is Not Done On Row Comparison 247# 248create table t1 (a varchar(200), 249 b int unsigned not null primary key auto_increment) 250default character set 'utf8'; 251 252create table t2 (c varchar(200), 253 d int unsigned not null primary key auto_increment) 254default character set 'latin1'; 255 256insert into t1 (a) values('abc'); 257insert into t2 (c) values('abc'); 258select * from t1,t2 where (a,b) = (c,d); 259 260select host,user from mysql.user where (host,user) = ('localhost','test'); 261drop table t1,t2; 262 263--echo # 264--echo # Bug#52124 memory leaks like a sieve in datetime, timestamp, time, date fields + warnings 265--echo # 266CREATE TABLE t1 (a DATETIME NOT NULL, b TINYINT); 267INSERT INTO t1 VALUES (0, 0),(0, 0); 268--disable_warnings 269SELECT 1 FROM t1 WHERE ROW(a, b) >= 270ROW('1', (SELECT 1 FROM t1 WHERE a > 1234)); 271--enable_warnings 272DROP TABLE t1; 273 274--echo # 275--echo # Bug #54190: Comparison to row subquery produces incorrect result 276--echo # 277 278SELECT ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0); 279SELECT ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0); 280 281CREATE TABLE t1 (i INT); 282INSERT INTO t1 () VALUES (1), (2), (3); 283 284SELECT ROW(1,2) = (SELECT 1,2 FROM t1 WHERE 1 = 0); 285SELECT ROW(1,2) = (SELECT 1,3 FROM t1 WHERE 1 = 0); 286SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0); 287SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0); 288 289DROP TABLE t1; 290 291--echo End of 5.1 tests 292 293--echo # 294--echo # Start of 10.1 tests 295--echo # 296 297--echo # 298--echo # MDEV-8709 Row equality elements do not get propagated 299--echo # 300CREATE TABLE t1 (a INT, b INT); 301INSERT INTO t1 VALUES (10,10),(20,20); 302# Checking that the a>=10 part gets optimized away in both scalar and row notations 303EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND b=10 AND a>=10; 304EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a,b)=(10,10) AND a>=10; 305DROP TABLE t1; 306 307 308--echo # 309--echo # MDEV-9369 IN operator with ( num, NULL ) gives inconsistent result 310--echo # 311SELECT (1,null) NOT IN ((2,2),(3,3)), (1,null) NOT IN ((2,2)), (1,null) NOT IN ((3,3)); 312 313 314--echo # 315--echo # End of 10.1 tests 316--echo # 317 318 319--echo # 320--echo # Start of 10.5 tests 321--echo # 322 323--echo # 324--echo # MDEV-20809 EXTRACT from INET6 value does not produce any warnings 325--echo # 326 327CREATE TABLE t1 (a GEOMETRY); 328--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION 329SELECT EXTRACT(DAY FROM a) FROM t1; 330DROP TABLE t1; 331 332--echo # 333--echo # End of 10.5 tests 334--echo # 335