1select interval(55,10,20,30,40,50,60,70,80,90,100),interval(3,1,1+1,1+1+1+1),field("IBM","NCA","ICL","SUN","IBM","DIGITAL"),field("A","B","C"),elt(2,"ONE","TWO","THREE"),interval(0,1,2,3,4),elt(1,1,2,3)|0,elt(1,1.1,1.2,1.3)+0; 2interval(55,10,20,30,40,50,60,70,80,90,100) interval(3,1,1+1,1+1+1+1) field("IBM","NCA","ICL","SUN","IBM","DIGITAL") field("A","B","C") elt(2,"ONE","TWO","THREE") interval(0,1,2,3,4) elt(1,1,2,3)|0 elt(1,1.1,1.2,1.3)+0 35 2 4 0 TWO 0 1 1.1 4explain extended select INTERVAL(55,10,20,30,40,50,60,70,80,90,100),interval(3,1,1+1,1+1+1+1),field("IBM","NCA","ICL","SUN","IBM","DIGITAL"),field("A","B","C"),elt(2,"ONE","TWO","THREE"),interval(0,1,2,3,4),elt(1,1,2,3)|0,elt(1,1.1,1.2,1.3)+0; 5id select_type table type possible_keys key key_len ref rows filtered Extra 61 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 7Warnings: 8Note 1003 select interval(55,10,20,30,40,50,60,70,80,90,100) AS `INTERVAL(55,10,20,30,40,50,60,70,80,90,100)`,interval(3,1,1 + 1,1 + 1 + 1 + 1) AS `interval(3,1,1+1,1+1+1+1)`,field('IBM','NCA','ICL','SUN','IBM','DIGITAL') AS `field("IBM","NCA","ICL","SUN","IBM","DIGITAL")`,field('A','B','C') AS `field("A","B","C")`,elt(2,'ONE','TWO','THREE') AS `elt(2,"ONE","TWO","THREE")`,interval(0,1,2,3,4) AS `interval(0,1,2,3,4)`,elt(1,1,2,3) | 0 AS `elt(1,1,2,3)|0`,elt(1,1.1,1.2,1.3) + 0 AS `elt(1,1.1,1.2,1.3)+0` 9SELECT INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56); 10INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56) 111 12SELECT INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56, 77); 13INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56, 77) 141 15select find_in_set("b","a,b,c"),find_in_set("c","a,b,c"),find_in_set("dd","a,bbb,dd"),find_in_set("bbb","a,bbb,dd"); 16find_in_set("b","a,b,c") find_in_set("c","a,b,c") find_in_set("dd","a,bbb,dd") find_in_set("bbb","a,bbb,dd") 172 3 3 2 18select find_in_set("d","a,b,c"),find_in_set("dd","a,bbb,d"),find_in_set("bb","a,bbb,dd"); 19find_in_set("d","a,b,c") find_in_set("dd","a,bbb,d") find_in_set("bb","a,bbb,dd") 200 0 0 21select make_set(0,'a','b','c'),make_set(-1,'a','b','c'),make_set(1,'a','b','c'),make_set(2,'a','b','c'),make_set(1+2,concat('a','b'),'c'); 22make_set(0,'a','b','c') make_set(-1,'a','b','c') make_set(1,'a','b','c') make_set(2,'a','b','c') make_set(1+2,concat('a','b'),'c') 23 a,b,c a b ab,c 24select make_set(NULL,'a','b','c'),make_set(1|4,'a',NULL,'c'),make_set(1+2,'a',NULL,'c'); 25make_set(NULL,'a','b','c') make_set(1|4,'a',NULL,'c') make_set(1+2,'a',NULL,'c') 26NULL a,c a 27select export_set(9,"Y","N","-",5),export_set(9,"Y","N"),export_set(9,"Y","N",""); 28export_set(9,"Y","N","-",5) export_set(9,"Y","N") export_set(9,"Y","N","") 29Y-N-N-Y-N Y,N,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N YNNYNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN 30select elt(2,1),field(NULL,"a","b","c"); 31elt(2,1) field(NULL,"a","b","c") 32NULL 0 33select field("b","a",NULL),field(1,0,NULL)+0,field(1.0,0.0,NULL)+0.0,field(1.0e1,0.0e1,NULL)+0.0e1; 34field("b","a",NULL) field(1,0,NULL)+0 field(1.0,0.0,NULL)+0.0 field(1.0e1,0.0e1,NULL)+0.0e1 350 0 0.0 0 36select field(NULL,"a",NULL),field(NULL,0,NULL)+0,field(NULL,0.0,NULL)+0.0,field(NULL,0.0e1,NULL)+0.0e1; 37field(NULL,"a",NULL) field(NULL,0,NULL)+0 field(NULL,0.0,NULL)+0.0 field(NULL,0.0e1,NULL)+0.0e1 380 0 0.0 0 39select find_in_set("","a,b,c"),find_in_set("","a,b,c,"),find_in_set("",",a,b,c"); 40find_in_set("","a,b,c") find_in_set("","a,b,c,") find_in_set("",",a,b,c") 410 4 1 42select find_in_set("abc","abc"),find_in_set("ab","abc"),find_in_set("abcd","abc"); 43find_in_set("abc","abc") find_in_set("ab","abc") find_in_set("abcd","abc") 441 0 0 45select interval(null, 1, 10, 100); 46interval(null, 1, 10, 100) 47-1 48drop table if exists t1,t2; 49create table t1 (id int(10) not null unique); 50create table t2 (id int(10) not null primary key, val int(10) not null); 51insert into t1 values (1),(2),(4); 52insert into t2 values (1,1),(2,1),(3,1),(4,2); 53select one.id, elt(two.val,'one','two') from t1 one, t2 two where two.id=one.id; 54id elt(two.val,'one','two') 551 one 562 one 574 two 58select one.id, elt(two.val,'one','two') from t1 one, t2 two where two.id=one.id order by one.id; 59id elt(two.val,'one','two') 601 one 612 one 624 two 63drop table t1,t2; 64select find_in_set(binary 'a',binary 'A,B,C'); 65find_in_set(binary 'a',binary 'A,B,C') 660 67select find_in_set('a',binary 'A,B,C'); 68find_in_set('a',binary 'A,B,C') 690 70select find_in_set(binary 'a', 'A,B,C'); 71find_in_set(binary 'a', 'A,B,C') 720 73select find_in_set('1','3,1,'); 74find_in_set('1','3,1,') 752 76End of 4.1 tests 77SELECT INTERVAL(0.0, NULL); 78INTERVAL(0.0, NULL) 791 80SELECT INTERVAL(0.0, CAST(NULL AS DECIMAL)); 81INTERVAL(0.0, CAST(NULL AS DECIMAL)) 821 83SELECT INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL)); 84INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL)) 851 86SELECT INTERVAL(0.0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); 87INTERVAL(0.0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) 888 89SELECT INTERVAL(0.0, CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), 90CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), 91CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL)); 92INTERVAL(0.0, CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), 93CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), 94CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL)) 958 96SELECT INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), 97CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), 98CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), 99CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL)); 100INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), 101CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), 102CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), 103CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL)) 1048 105End of 5.0 tests 106drop table if exists t1; 107create table t1 (f1 set('test1','test2','test3') character set utf8 default null) 108engine=myisam default charset=latin1; 109insert into t1 values (''),(null),(null),(''),(''),(''); 110select find_in_set(f1,f1) as a from t1,(select find_in_set(f1,f1) as b from t1) a; 111a 1120 113NULL 114NULL 1150 1160 1170 1180 119NULL 120NULL 1210 1220 1230 1240 125NULL 126NULL 1270 1280 1290 1300 131NULL 132NULL 1330 1340 1350 1360 137NULL 138NULL 1390 1400 1410 1420 143NULL 144NULL 1450 1460 1470 148drop table t1; 149CREATE TABLE t1( a SET('a', 'b', 'c') ); 150CREATE TABLE t2( a SET('a', 'b', 'c') ); 151INSERT IGNORE INTO t1 VALUES ('d'); 152Warnings: 153Warning 1265 Data truncated for column 'a' at row 1 154INSERT INTO t2 VALUES (''); 155SELECT CONVERT( a USING latin1 ) FROM t1; 156CONVERT( a USING latin1 ) 157 158SELECT CONVERT( a USING latin1 ) FROM t2; 159CONVERT( a USING latin1 ) 160 161DROP TABLE t1, t2; 162# 163# Start of 5.3 tests 164# 165# 166# MDEV-4512 Valgrind warnings in my_long10_to_str_8bit on INTERVAL and DATE_ADD with incorrect types 167# 168CREATE TABLE t1 (pk INT PRIMARY KEY); 169INSERT INTO t1 VALUES (10),(11); 170SELECT INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) FROM t1; 171INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) 1728 1738 174Warnings: 175Warning 1292 Incorrect datetime value: '10' for column `test`.`t1`.`pk` at row 1 176Warning 1292 Incorrect datetime value: '11' for column `test`.`t1`.`pk` at row 2 177DROP TABLE t1; 178# 179# End of 5.3 tests 180# 181# 182# BUG#59405: FIND_IN_SET won't work normaly after upgrade from 5.1 to 5.5 183# 184CREATE TABLE t1(days set('1','2','3','4','5','6','7')); 185INSERT INTO t1 VALUES('1,2,3,4,5,6,7'), (NULL), ('1,2,3,4,5,6,7'); 186 187SELECT * FROM t1 WHERE FIND_IN_SET(DAYOFWEEK(CURRENT_DATE()), days); 188days 1891,2,3,4,5,6,7 1901,2,3,4,5,6,7 191SELECT * FROM t1 WHERE FIND_IN_SET(DAYOFWEEK(CURRENT_DATE()), days) IS UNKNOWN; 192days 193NULL 194SELECT * FROM t1 WHERE FIND_IN_SET(DAYOFWEEK(CURRENT_DATE()), NULL); 195days 196SELECT * FROM t1 WHERE FIND_IN_SET(DAYOFWEEK(CURRENT_DATE()), NULL) IS UNKNOWN; 197days 1981,2,3,4,5,6,7 199NULL 2001,2,3,4,5,6,7 201SELECT * FROM t1 WHERE FIND_IN_SET(7, days); 202days 2031,2,3,4,5,6,7 2041,2,3,4,5,6,7 205SELECT * FROM t1 WHERE FIND_IN_SET(8, days); 206days 207SELECT * FROM t1 WHERE FIND_IN_SET(NULL, days); 208days 209SELECT * FROM t1 WHERE FIND_IN_SET(NULL, days) IS UNKNOWN; 210days 2111,2,3,4,5,6,7 212NULL 2131,2,3,4,5,6,7 214SELECT * FROM t1 WHERE FIND_IN_SET(NULL, NULL); 215days 216SELECT * FROM t1 WHERE FIND_IN_SET(NULL, NULL) IS UNKNOWN; 217days 2181,2,3,4,5,6,7 219NULL 2201,2,3,4,5,6,7 221 222DROP TABLE t1; 223# 224# MDEV-14596 Crash in INTERVAL(ROW(..),ROW(..)) 225# 226SELECT INTERVAL(ROW(1,1),ROW(1,2)); 227ERROR 21000: Operand should contain 1 column(s) 228SELECT INTERVAL(1,ROW(1,2)); 229ERROR 21000: Operand should contain 1 column(s) 230SELECT INTERVAL(ROW(1,2),1); 231ERROR 21000: Operand should contain 1 column(s) 232