diff options
Diffstat (limited to 'mysql-test/main/func_if.result')
| -rw-r--r-- | mysql-test/main/func_if.result | 250 |
1 files changed, 250 insertions, 0 deletions
diff --git a/mysql-test/main/func_if.result b/mysql-test/main/func_if.result new file mode 100644 index 00000000000..09eb85d9fb6 --- /dev/null +++ b/mysql-test/main/func_if.result @@ -0,0 +1,250 @@ +drop table if exists t1; +select IF(0,"ERROR","this"),IF(1,"is","ERROR"),IF(NULL,"ERROR","a"),IF(1,2,3)|0,IF(1,2.0,3.0)+0 ; +IF(0,"ERROR","this") IF(1,"is","ERROR") IF(NULL,"ERROR","a") IF(1,2,3)|0 IF(1,2.0,3.0)+0 +this is a 2 2.0 +CREATE TABLE t1 (st varchar(255) NOT NULL, u int(11) NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('a',1),('A',1),('aa',1),('AA',1),('a',1),('aaa',0),('BBB',0); +select if(1,st,st) s from t1 order by s; +s +a +A +a +aa +AA +aaa +BBB +select if(u=1,st,st) s from t1 order by s; +s +a +A +a +aa +AA +aaa +BBB +select if(u=1,binary st,st) s from t1 order by s; +s +A +AA +BBB +a +a +aa +aaa +select if(u=1,st,binary st) s from t1 where st like "%a%" order by s; +s +A +AA +a +a +aa +aaa +explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order by s; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using filesort +Warnings: +Note 1003 select if(`test`.`t1`.`u` = 1,`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) AS `s` from `test`.`t1` where `test`.`t1`.`st` like '%a%' order by if(`test`.`t1`.`u` = 1,`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) +select nullif(u, 1) from t1; +nullif(u, 1) +NULL +NULL +NULL +NULL +NULL +0 +0 +explain extended select nullif(u, 1) from t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 +Warnings: +Note 1003 select nullif(`test`.`t1`.`u`,1) AS `nullif(u, 1)` from `test`.`t1` +drop table t1; +select nullif(1,'test'); +nullif(1,'test') +1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'test' +select NULLIF(NULL,NULL), NULLIF(NULL,1), NULLIF(NULL,1.0), NULLIF(NULL,"test"); +NULLIF(NULL,NULL) NULLIF(NULL,1) NULLIF(NULL,1.0) NULLIF(NULL,"test") +NULL NULL NULL NULL +select NULLIF(1,NULL), NULLIF(1.0, NULL), NULLIF("test", NULL); +NULLIF(1,NULL) NULLIF(1.0, NULL) NULLIF("test", NULL) +1 1.0 test +create table t1 (num double(12,2)); +insert into t1 values (144.54); +select sum(if(num is null,0.00,num)) from t1; +sum(if(num is null,0.00,num)) +144.54 +drop table t1; +create table t1 (x int, y int); +insert into t1 values (0,6),(10,16),(20,26),(30,10),(40,46),(50,56); +select min(if(y -x > 5,y,NULL)), max(if(y - x > 5,y,NULL)) from t1; +min(if(y -x > 5,y,NULL)) max(if(y - x > 5,y,NULL)) +6 56 +drop table t1; +create table t1 (a int); +insert t1 values (1),(2); +select if(1>2,a,avg(a)) from t1; +if(1>2,a,avg(a)) +1.5000 +drop table t1; +SELECT NULLIF(5,5) IS NULL, NULLIF(5,5) IS NOT NULL; +NULLIF(5,5) IS NULL NULLIF(5,5) IS NOT NULL +1 0 +CREATE TABLE `t1` ( +`id` int(11) NOT NULL , +`date` int(10) default NULL, +`text` varchar(32) NOT NULL +); +INSERT INTO t1 VALUES (1,1110000000,'Day 1'),(2,1111000000,'Day 2'),(3,1112000000,'Day 3'); +SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord ASC; +id date_ord text +1 05-03-2005 Day 1 +2 16-03-2005 Day 2 +3 28-03-2005 Day 3 +SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord DESC; +id date_ord text +3 28-03-2005 Day 3 +2 16-03-2005 Day 2 +1 05-03-2005 Day 1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(10)); +INSERT INTO t1 VALUES ('aaa'), (NULL), (''), ('bbb'); +SELECT a, NULLIF(a,'') FROM t1; +a NULLIF(a,'') +aaa aaa +NULL NULL + NULL +bbb bbb +SELECT a, NULLIF(a,'') FROM t1 WHERE NULLIF(a,'') IS NULL; +a NULLIF(a,'') +NULL NULL + NULL +DROP TABLE t1; +create table t1 (f1 int, f2 int); +insert into t1 values(1,1),(0,0); +select f1, f2, if(f1, 40.0, 5.00) from t1 group by f1 order by f2; +f1 f2 if(f1, 40.0, 5.00) +0 0 5.00 +1 1 40.00 +drop table t1; +select if(0, 18446744073709551610, 18446744073709551610); +if(0, 18446744073709551610, 18446744073709551610) +18446744073709551610 +CREATE TABLE t1(a DECIMAL(10,3)); +SELECT t1.a, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2,0)))))))))))))))))))))))))))))) + 1 +FROM t1; +a IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((ROUND(t1.a,2)=1), 2, +IF((R +DROP TABLE t1; +CREATE TABLE t1 (c LONGTEXT); +INSERT INTO t1 VALUES(1), (2), (3), (4), ('1234567890123456789'); +SELECT IF(1, CAST(c AS UNSIGNED), 0) FROM t1; +IF(1, CAST(c AS UNSIGNED), 0) +1 +2 +3 +4 +1234567890123456789 +SELECT * FROM (SELECT MAX(IF(1, CAST(c AS UNSIGNED), 0)) FROM t1) AS te; +MAX(IF(1, CAST(c AS UNSIGNED), 0)) +1234567890123456789 +SELECT * FROM (SELECT MAX(IFNULL(CAST(c AS UNSIGNED), 0)) FROM t1) AS te; +MAX(IFNULL(CAST(c AS UNSIGNED), 0)) +1234567890123456789 +DROP TABLE t1; +End of 5.0 tests +# +# Bug#55077: Assertion failed: width > 0 && to != ((void *)0), file .\dtoa.c +# +CREATE TABLE t1 (a LONGBLOB, b DOUBLE); +INSERT INTO t1 VALUES (NULL, 0), (NULL, 1); +SELECT IF(b, (SELECT a FROM t1 LIMIT 1), b) c FROM t1 GROUP BY c; +c +NULL +0 +DROP TABLE t1; +# +# Bug#12532830 +# SIGFPE OR ASSERTION (PRECISION <= ((9 * 9) - 8*2)) && (DEC <= 30) +# +select +sum(distinct(if('a', +(select adddate(elt(convert(9999999999999999999999999999999999999,decimal(64,0)),count(*)), +interval 1 day)) +, .1))) as foo; +foo +0.1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +# +# LP bug#998321 Simple query with IF expression causes an +# assertion abort (see also mysql Bug#12620084) +# +SELECT if(0, (SELECT min('hello')), NULL); +if(0, (SELECT min('hello')), NULL) +NULL +SELECT if(1, (SELECT min('hello')), NULL); +if(1, (SELECT min('hello')), NULL) +hello +SELECT if(0, NULL, (SELECT min('hello'))); +if(0, NULL, (SELECT min('hello'))) +hello +SELECT if(1, NULL, (SELECT min('hello'))); +if(1, NULL, (SELECT min('hello'))) +NULL +End of 5.2 tests +# +# MDEV-8663: IF Statement returns multiple values erroneously +# (or Assertion `!null_value' failed in Item::send(Protocol*, String*) +# +CREATE TABLE `t1` ( +`datas` VARCHAR(25) NOT NULL +) DEFAULT CHARSET=utf8; +INSERT INTO `t1` VALUES ('1,2'), ('2,3'), ('3,4'); +SELECT IF(FIND_IN_SET('1', `datas`), 1.5, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `t1`; +First Second Third +1.5 1 2 +2.0 1 2 +NULL 1 2 +drop table t1; |