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#1 */ 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 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# BUG#59405: FIND_IN_SET won't work normaly after upgrade from 5.1 to 5.5 164# 165CREATE TABLE t1(days set('1','2','3','4','5','6','7')); 166INSERT INTO t1 VALUES('1,2,3,4,5,6,7'), (NULL), ('1,2,3,4,5,6,7'); 167 168SELECT * FROM t1 WHERE FIND_IN_SET(DAYOFWEEK(CURRENT_DATE()), days); 169days 1701,2,3,4,5,6,7 1711,2,3,4,5,6,7 172SELECT * FROM t1 WHERE FIND_IN_SET(DAYOFWEEK(CURRENT_DATE()), days) IS UNKNOWN; 173days 174NULL 175SELECT * FROM t1 WHERE FIND_IN_SET(DAYOFWEEK(CURRENT_DATE()), NULL); 176days 177SELECT * FROM t1 WHERE FIND_IN_SET(DAYOFWEEK(CURRENT_DATE()), NULL) IS UNKNOWN; 178days 1791,2,3,4,5,6,7 180NULL 1811,2,3,4,5,6,7 182SELECT * FROM t1 WHERE FIND_IN_SET(7, days); 183days 1841,2,3,4,5,6,7 1851,2,3,4,5,6,7 186SELECT * FROM t1 WHERE FIND_IN_SET(8, days); 187days 188SELECT * FROM t1 WHERE FIND_IN_SET(NULL, days); 189days 190SELECT * FROM t1 WHERE FIND_IN_SET(NULL, days) IS UNKNOWN; 191days 1921,2,3,4,5,6,7 193NULL 1941,2,3,4,5,6,7 195SELECT * FROM t1 WHERE FIND_IN_SET(NULL, NULL); 196days 197SELECT * FROM t1 WHERE FIND_IN_SET(NULL, NULL) IS UNKNOWN; 198days 1991,2,3,4,5,6,7 200NULL 2011,2,3,4,5,6,7 202 203DROP TABLE t1; 204# 205# BUG#12211480: GROUP BY MAKE_SET WITH EMPTY SET VALUES CRASH IN MY_HASH_SORT_SIMPLE 206# 207CREATE TABLE t1 (a INT, b CHAR NOT NULL); 208INSERT INTO t1 VALUES (NULL,'1'),(NULL,'1'); 209SELECT COUNT(*) FROM t1 GROUP BY MAKE_SET(a,b); 210COUNT(*) 2112 212DROP TABLE t1; 213# End of test BUG#12211480 214# 215# Bug#12677197 MAKE_SET() AND MY_EMPTY_STRING BUGS CAUSE CRASHING 216# 217do 218nullif( ( rtrim( make_set((cast(('%S') as unsigned)), 219(point((0xaf),('')))) 220) 221), ('')) 222; 223Warnings: 224Warning 1292 Truncated incorrect INTEGER value: '%S' 225do 226dayofmonth( ( not( trim( trailing( convert((''), binary(4))) 227from( make_set( ('>>'), ('`')))) 228))) 229; 230Warnings: 231Warning 1292 Truncated incorrect INTEGER value: '>>' 232Warning 1292 Incorrect datetime value: '1' 233do quote(make_set((''), (cast(('-2147483649.1') as binary(513))))); 234Warnings: 235Warning 1292 Truncated incorrect INTEGER value: '' 236do 237trim( both(-8388607) 238from( make_set( ( extract( minute_second from 239( str_to_date((rpad(1.0,4,1)), (''))) 240) 241), 242( char((connection_id()) using macce))) 243) 244) 245; 246Warnings: 247Warning 1292 Truncated incorrect date value: '1.01' 248