summaryrefslogtreecommitdiff
path: root/mysql-test/suite/pbxt/r/func_if.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/pbxt/r/func_if.result')
-rw-r--r--mysql-test/suite/pbxt/r/func_if.result130
1 files changed, 130 insertions, 0 deletions
diff --git a/mysql-test/suite/pbxt/r/func_if.result b/mysql-test/suite/pbxt/r/func_if.result
new file mode 100644
index 00000000000..e1879daf662
--- /dev/null
+++ b/mysql-test/suite/pbxt/r/func_if.result
@@ -0,0 +1,130 @@
+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;