1 2# 3# Testing if SET and similar functions 4# 5 6select 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; 7explain 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; 8# Test 8 and 9 values (Bug #1561) 9SELECT INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56); 10SELECT INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56, 77); 11 12select 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"); 13select find_in_set("d","a,b,c"),find_in_set("dd","a,bbb,d"),find_in_set("bb","a,bbb,dd"); 14select 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'); 15select make_set(NULL,'a','b','c'),make_set(1|4,'a',NULL,'c'),make_set(1+2,'a',NULL,'c'); 16select export_set(9,"Y","N","-",5),export_set(9,"Y","N"),export_set(9,"Y","N",""); 17 18# 19# Wrong usage of functions 20# 21select elt(2,1),field(NULL,"a","b","c"); 22select 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; 23select field(NULL,"a",NULL),field(NULL,0,NULL)+0,field(NULL,0.0,NULL)+0.0,field(NULL,0.0e1,NULL)+0.0e1; 24select find_in_set("","a,b,c"),find_in_set("","a,b,c,"),find_in_set("",",a,b,c"); 25select find_in_set("abc","abc"),find_in_set("ab","abc"),find_in_set("abcd","abc"); 26select interval(null, 1, 10, 100); 27 28# 29# test for a bug with elt() 30# 31 32--disable_warnings 33drop table if exists t1,t2; 34--enable_warnings 35 36create table t1 (id int(10) not null unique); 37create table t2 (id int(10) not null primary key, val int(10) not null); 38insert into t1 values (1),(2),(4); 39insert into t2 values (1,1),(2,1),(3,1),(4,2); 40 41select one.id, elt(two.val,'one','two') from t1 one, t2 two where two.id=one.id; 42select one.id, elt(two.val,'one','two') from t1 one, t2 two where two.id=one.id order by one.id; 43drop table t1,t2; 44 45# 46# Bug4340: find_in_set is case insensitive even on binary operators 47# 48 49select find_in_set(binary 'a',binary 'A,B,C'); 50select find_in_set('a',binary 'A,B,C'); 51select find_in_set(binary 'a', 'A,B,C'); 52 53# 54# Bug5513:FIND_IN_SET fails if set ends with a comma 55# 56select find_in_set('1','3,1,'); 57 58--echo End of 4.1 tests 59 60# 61# Bug #32560: crash with interval function and count(*) 62# 63SELECT INTERVAL(0.0, NULL); 64SELECT INTERVAL(0.0, CAST(NULL AS DECIMAL)); 65SELECT INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL)); 66SELECT INTERVAL(0.0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); 67SELECT INTERVAL(0.0, CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), 68 CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), 69 CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL), CAST(NULL AS DECIMAL)); 70SELECT INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), 71 CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), 72 CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), 73 CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL)); 74 75--echo End of 5.0 tests 76 77# 78# Bug#44367 valgrind warnings with find_in_set() functions 79# 80--disable_warnings 81drop table if exists t1; 82--enable_warnings 83create table t1 (f1 set('test1','test2','test3') character set utf8 default null) 84default charset=latin1; 85insert into t1 values (''),(null),(null),(''),(''),(''); 86select find_in_set(f1,f1) as a from t1,(select find_in_set(f1,f1) as b from t1) a; 87drop table t1; 88# 89# Bug#45168: assertion with convert() and empty set value 90# 91CREATE TABLE t1( a SET('a', 'b', 'c') ); 92CREATE TABLE t2( a SET('a', 'b', 'c') ); 93 94INSERT IGNORE INTO t1 VALUES ('d'); 95INSERT INTO t2 VALUES (''); 96 97SELECT CONVERT( a USING latin1 ) FROM t1; 98SELECT CONVERT( a USING latin1 ) FROM t2; 99 100DROP TABLE t1, t2; 101 102--echo # 103--echo # BUG#59405: FIND_IN_SET won't work normaly after upgrade from 5.1 to 5.5 104--echo # 105 106CREATE TABLE t1(days set('1','2','3','4','5','6','7')); 107INSERT INTO t1 VALUES('1,2,3,4,5,6,7'), (NULL), ('1,2,3,4,5,6,7'); 108 109--echo 110SELECT * FROM t1 WHERE FIND_IN_SET(DAYOFWEEK(CURRENT_DATE()), days); 111SELECT * FROM t1 WHERE FIND_IN_SET(DAYOFWEEK(CURRENT_DATE()), days) IS UNKNOWN; 112SELECT * FROM t1 WHERE FIND_IN_SET(DAYOFWEEK(CURRENT_DATE()), NULL); 113SELECT * FROM t1 WHERE FIND_IN_SET(DAYOFWEEK(CURRENT_DATE()), NULL) IS UNKNOWN; 114SELECT * FROM t1 WHERE FIND_IN_SET(7, days); 115SELECT * FROM t1 WHERE FIND_IN_SET(8, days); 116SELECT * FROM t1 WHERE FIND_IN_SET(NULL, days); 117SELECT * FROM t1 WHERE FIND_IN_SET(NULL, days) IS UNKNOWN; 118SELECT * FROM t1 WHERE FIND_IN_SET(NULL, NULL); 119SELECT * FROM t1 WHERE FIND_IN_SET(NULL, NULL) IS UNKNOWN; 120 121--echo 122DROP TABLE t1; 123 124--echo # 125--echo # BUG#12211480: GROUP BY MAKE_SET WITH EMPTY SET VALUES CRASH IN MY_HASH_SORT_SIMPLE 126--echo # 127 128CREATE TABLE t1 (a INT, b CHAR NOT NULL); 129INSERT INTO t1 VALUES (NULL,'1'),(NULL,'1'); 130SELECT COUNT(*) FROM t1 GROUP BY MAKE_SET(a,b); 131 132DROP TABLE t1; 133 134--echo # End of test BUG#12211480 135 136--echo # 137--echo # Bug#12677197 MAKE_SET() AND MY_EMPTY_STRING BUGS CAUSE CRASHING 138--echo # 139 140do 141nullif( ( rtrim( make_set((cast(('%S') as unsigned)), 142 (point((0xaf),('')))) 143 ) 144 ), ('')) 145; 146 147do 148dayofmonth( ( not( trim( trailing( convert((''), binary(4))) 149 from( make_set( ('>>'), ('`')))) 150 ))) 151; 152 153do quote(make_set((''), (cast(('-2147483649.1') as binary(513))))); 154 155do 156trim( both(-8388607) 157 from( make_set( ( extract( minute_second from 158 ( str_to_date((rpad(1.0,4,1)), (''))) 159 ) 160 ), 161 ( char((connection_id()) using macce))) 162 ) 163 ) 164; 165 166--echo # Bug#21547779 Assertion failed: select_lex->leaf_table_count == 0 167 168CREATE TABLE t(a INTEGER) engine=innodb; 169SELECT 1 IN (SELECT MAKE_SET(-1, 1, (SELECT 1 FROM t)) FROM t); 170DROP TABLE t; 171