diff options
| -rw-r--r-- | mysql-test/r/func_if.result | 35 | ||||
| -rw-r--r-- | mysql-test/t/func_if.test | 30 |
2 files changed, 65 insertions, 0 deletions
diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result new file mode 100644 index 00000000000..64ee8c67def --- /dev/null +++ b/mysql-test/r/func_if.result @@ -0,0 +1,35 @@ +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 +s +a +A +a +aa +AA +aaa +BBB +s +a +A +a +aa +AA +aaa +BBB +s +A +AA +BBB +a +a +aa +aaa +s +A +AA +a +a +aa +aaa +sum(if(num is null,0.00,num)) +nan diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test new file mode 100644 index 00000000000..c5cc73ecd1f --- /dev/null +++ b/mysql-test/t/func_if.test @@ -0,0 +1,30 @@ +# +# Init section +# +drop table if exists t1; + +# +# Simple IF tests +# + +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 ; + +# +# Test of IF and case-sensitiveness +# +CREATE TABLE t1 (st varchar(255) NOT NULL, u int(11) NOT NULL) TYPE=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; +select if(u=1,st,st) s from t1 order by s; +select if(u=1,binary st,st) s from t1 order by s; +select if(u=1,st,binary st) s from t1 where st like "%a%" order by s; +drop table t1; + +# +# Problem with IF() +# + +create table t1 (num double(12,2)); +insert into t1 values (144.54); +select sum(if(num is null,0.00,num)) from t1; +drop table t1; |