diff options
author | unknown <igor@rurik.mysql.com> | 2005-08-26 22:25:45 -0700 |
---|---|---|
committer | unknown <igor@rurik.mysql.com> | 2005-08-26 22:25:45 -0700 |
commit | 28920f5987454c9f28ebddcc9a2babbefa155634 (patch) | |
tree | b1603e07e331943d651cbfb5640464674a6f914f | |
parent | f0aeadcd3cf0e5fe13cdbe5e9142693568912b90 (diff) | |
download | mariadb-git-28920f5987454c9f28ebddcc9a2babbefa155634.tar.gz |
func_str.result, null.result:
Corrected results after the fix for bug #12791.
func_test.result, func_test.test:
Added test cases for bug #12791.
item_func.h, item_func.cc:
Fixed bug #12791.
Made LEAST/GREATES fully Oracle compliant.
LEAST/GREATEST did not return NULL if only some
arguments were NULLs. This did not comply with Oracle.
sql/item_func.cc:
Fixed bug #12791.
Made LEAST/GREATES fully Oracle compliant.
LEAST/GREATEST did not return NULL if only some
arguments were NULLs. This did not comply with Oracle.
sql/item_func.h:
Fixed bug #12791.
Made LEAST/GREATES fully Oracle compliant.
LEAST/GREATEST did not return NULL if only some
arguments were NULLs. This did not comply with Oracle.
mysql-test/t/func_test.test:
Added test cases for bug #12791.
mysql-test/r/func_test.result:
Added test cases for bug #12791.
mysql-test/r/null.result:
Corrected results after the fix for bug #12791.
mysql-test/r/func_str.result:
Corrected results after the fix for bug #12791.
-rw-r--r-- | mysql-test/r/func_str.result | 2 | ||||
-rw-r--r-- | mysql-test/r/func_test.result | 13 | ||||
-rw-r--r-- | mysql-test/r/null.result | 8 | ||||
-rw-r--r-- | mysql-test/t/func_test.test | 9 | ||||
-rw-r--r-- | sql/item_func.cc | 48 | ||||
-rw-r--r-- | sql/item_func.h | 1 |
6 files changed, 49 insertions, 32 deletions
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index c7e69ae1d31..577f943ebde 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -287,7 +287,7 @@ lpad('STRING', 20, CONCAT('p','a','d') ) padpadpadpadpaSTRING select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'); LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD') GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD') -HAROLD HARRY +NULL NULL select least(1,2,3) | greatest(16,32,8), least(5,4)*1,greatest(-1.0,1.0)*1,least(3,2,1)*1.0,greatest(1,1.1,1.0),least("10",9),greatest("A","B","0"); least(1,2,3) | greatest(16,32,8) least(5,4)*1 greatest(-1.0,1.0)*1 least(3,2,1)*1.0 greatest(1,1.1,1.0) least("10",9) greatest("A","B","0") 33 4 1.0 1.0 1.1 9 B diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index 32883921e70..d9437f6c515 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -166,7 +166,6 @@ CREATE TABLE t2 ( access_id smallint(6) NOT NULL default '0', name varchar(20 INSERT INTO t2 VALUES (1,'Everyone',2),(2,'Help',3),(3,'Customer Support',1); SELECT f_acc.rank, a1.rank, a2.rank FROM t1 LEFT JOIN t1 f1 ON (f1.access_id=1 AND f1.faq_group_id = t1.faq_group_id) LEFT JOIN t2 a1 ON (a1.access_id = f1.access_id) LEFT JOIN t1 f2 ON (f2.access_id=3 AND f2.faq_group_id = t1.faq_group_id) LEFT JOIN t2 a2 ON (a2.access_id = f2.access_id), t2 f_acc WHERE LEAST(a1.rank,a2.rank) = f_acc.rank; rank rank rank -2 2 NULL DROP TABLE t1,t2; CREATE TABLE t1 (d varchar(6), k int); INSERT INTO t1 VALUES (NULL, 2); @@ -193,3 +192,15 @@ select * from t1 where a not between 1 and 2 and b not between 3 and 4; a b 4 5 drop table t1; +SELECT GREATEST(1,NULL) FROM DUAL; +GREATEST(1,NULL) +NULL +SELECT LEAST('xxx','aaa',NULL,'yyy') FROM DUAL; +LEAST('xxx','aaa',NULL,'yyy') +NULL +SELECT LEAST(1.1,1.2,NULL,1.0) FROM DUAL; +LEAST(1.1,1.2,NULL,1.0) +NULL +SELECT GREATEST(1.5E+2,1.3E+2,NULL) FROM DUAL; +GREATEST(1.5E+2,1.3E+2,NULL) +NULL diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index 4551fed2732..1425b2a7f89 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -236,10 +236,10 @@ t1 CREATE TABLE `t1` ( `c06` varchar(6) character set latin2 default NULL, `c07` varchar(6) character set latin2 default NULL, `c08` varchar(6) character set latin2 default NULL, - `c09` varchar(6) character set latin2 NOT NULL default '', - `c10` varchar(6) character set latin2 NOT NULL default '', - `c11` varchar(6) character set latin2 NOT NULL default '', - `c12` varchar(6) character set latin2 NOT NULL default '', + `c09` varchar(6) character set latin2 default NULL, + `c10` varchar(6) character set latin2 default NULL, + `c11` varchar(6) character set latin2 default NULL, + `c12` varchar(6) character set latin2 default NULL, `c13` varchar(6) character set latin2 default NULL, `c14` char(0) character set latin2 default NULL, `c15` char(0) character set latin2 default NULL, diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index 424c0d1456b..f2ff47704c9 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -118,3 +118,12 @@ insert into t1 values (1,2), (2,3), (3,4), (4,5); select * from t1 where a not between 1 and 2; select * from t1 where a not between 1 and 2 and b not between 3 and 4; drop table t1; + +# +# Test for bug #12791: one of the arguments of LEAST/GREATEST is NULL +# + +SELECT GREATEST(1,NULL) FROM DUAL; +SELECT LEAST('xxx','aaa',NULL,'yyy') FROM DUAL; +SELECT LEAST(1.1,1.2,NULL,1.0) FROM DUAL; +SELECT GREATEST(1.5E+2,1.3E+2,NULL) FROM DUAL; diff --git a/sql/item_func.cc b/sql/item_func.cc index 13a82fa1361..80808c0ac87 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -1950,7 +1950,7 @@ void Item_func_min_max::fix_length_and_dec() int max_int_part=0; decimals=0; max_length=0; - maybe_null=1; + maybe_null=0; cmp_type=args[0]->result_type(); for (uint i=0 ; i < arg_count ; i++) @@ -1958,8 +1958,8 @@ void Item_func_min_max::fix_length_and_dec() set_if_bigger(max_length, args[i]->max_length); set_if_bigger(decimals, args[i]->decimals); set_if_bigger(max_int_part, args[i]->decimal_int_part()); - if (!args[i]->maybe_null) - maybe_null=0; + if (args[i]->maybe_null) + maybe_null=1; cmp_type=item_cmp_type(cmp_type,args[i]->result_type()); } if (cmp_type == STRING_RESULT) @@ -2005,14 +2005,11 @@ String *Item_func_min_max::val_str(String *str) { String *res; LINT_INIT(res); - null_value=1; + null_value= 0; for (uint i=0; i < arg_count ; i++) { - if (null_value) - { + if (i == 0) res=args[i]->val_str(str); - null_value=args[i]->null_value; - } else { String *res2; @@ -2023,7 +2020,11 @@ String *Item_func_min_max::val_str(String *str) if ((cmp_sign < 0 ? cmp : -cmp) < 0) res=res2; } + else + res= 0; } + if ((null_value= args[i]->null_value)) + break; } if (res) // If !NULL res->set_charset(collation.collation); @@ -2043,20 +2044,19 @@ double Item_func_min_max::val_real() { DBUG_ASSERT(fixed == 1); double value=0.0; - null_value=1; + null_value= 0; for (uint i=0; i < arg_count ; i++) { - if (null_value) - { + if (i == 0) value= args[i]->val_real(); - null_value=args[i]->null_value; - } else { double tmp= args[i]->val_real(); if (!args[i]->null_value && (tmp < value ? cmp_sign : -cmp_sign) > 0) value=tmp; } + if ((null_value= args[i]->null_value)) + break; } return value; } @@ -2066,20 +2066,19 @@ longlong Item_func_min_max::val_int() { DBUG_ASSERT(fixed == 1); longlong value=0; - null_value=1; + null_value= 0; for (uint i=0; i < arg_count ; i++) { - if (null_value) - { + if (i == 0) value=args[i]->val_int(); - null_value=args[i]->null_value; - } else { longlong tmp=args[i]->val_int(); if (!args[i]->null_value && (tmp < value ? cmp_sign : -cmp_sign) > 0) value=tmp; } + if ((null_value= args[i]->null_value)) + break; } return value; } @@ -2089,20 +2088,17 @@ my_decimal *Item_func_min_max::val_decimal(my_decimal *dec) { DBUG_ASSERT(fixed == 1); my_decimal tmp_buf, *tmp, *res= NULL; - null_value=1; + null_value= 0; for (uint i=0; i < arg_count ; i++) { - if (null_value) - { + if (i == 0) res= args[i]->val_decimal(dec); - null_value= args[i]->null_value; - } else { tmp= args[i]->val_decimal(&tmp_buf); if (args[i]->null_value) - continue; - if ((my_decimal_cmp(tmp, res) * cmp_sign) < 0) + res= 0; + else if ((my_decimal_cmp(tmp, res) * cmp_sign) < 0) { if (tmp == &tmp_buf) { @@ -2113,6 +2109,8 @@ my_decimal *Item_func_min_max::val_decimal(my_decimal *dec) res= tmp; } } + if ((null_value= args[i]->null_value)) + break; } return res; } diff --git a/sql/item_func.h b/sql/item_func.h index 384cb486f7c..019abb0c072 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -682,7 +682,6 @@ public: my_decimal *val_decimal(my_decimal *); void fix_length_and_dec(); enum Item_result result_type () const { return cmp_type; } - table_map not_null_tables() const { return 0; } }; class Item_func_min :public Item_func_min_max |