From: Date: April 19 2007 10:59pm Subject: bk commit into 4.1 tree (gshchepa:1.2636) BUG#27704 List-Archive: http://lists.mysql.com/commits/24952 X-Bug: 27704 Message-Id: <20070419205921.0E8C21B359@localhost.localdomain> Below is the list of changes that have just been committed into a local 4.1 repository of uchum. When uchum does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-04-20 01:59:16+05:00, gshchepa@stripped +5 -0 Bug#27704: incorrect comparison of rows with NULL components Support for NULL components was incomplete for row comparison, fixed. Added support for abort_on_null at compare_row() like in 5.x mysql-test/r/row.result@stripped, 2007-04-20 01:58:47+05:00, gshchepa@stripped +86 -1 Test case updated for Bug#27704 (incorrect comparison of rows with NULL components) mysql-test/r/subselect.result@stripped, 2007-04-20 01:58:56+05:00, gshchepa@stripped +2 -2 Test case updated for Bug#27704 (incorrect comparison of rows with NULL components) mysql-test/t/row.test@stripped, 2007-04-20 01:58:33+05:00, gshchepa@stripped +40 -0 Test case updated for Bug#27704 (incorrect comparison of rows with NULL components) sql/item_cmpfunc.cc@stripped, 2007-04-20 01:58:24+05:00, gshchepa@stripped +32 -4 Bug#27704: incorrect comparison of rows with NULL components Support for NULL components was incomplete for row comparison, fixed. Added support for abort_on_null at compare_row() like in 5.x sql/item_cmpfunc.h@stripped, 2007-04-20 01:59:08+05:00, gshchepa@stripped +3 -1 Bug#27704: incorrect comparison of rows with NULL components Added support for abort_on_null at Item_bool_func2 like in 5.x # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: gshchepa # Host: gshchepa.loc # Root: /home/uchum/work/bk-trees/mysql-4.1-opt-27704 --- 1.221/sql/item_cmpfunc.cc 2007-04-11 23:41:11 +05:00 +++ 1.222/sql/item_cmpfunc.cc 2007-04-20 01:58:24 +05:00 @@ -680,17 +680,45 @@ int Arg_comparator::compare_e_int_diff_s int Arg_comparator::compare_row() { int res= 0; + bool was_null= 0; (*a)->bring_value(); (*b)->bring_value(); uint n= (*a)->cols(); for (uint i= 0; inull_value) - return -1; + { + // NULL was compared + switch (owner->functype()) { + case Item_func::NE_FUNC: + break; // NE never aborts on NULL even if abort_on_null is set + case Item_func::LT_FUNC: + case Item_func::LE_FUNC: + case Item_func::GT_FUNC: + case Item_func::GE_FUNC: + return -1; // <, <=, > and >= always fail on NULL + default: // EQ_FUNC + if (owner->abort_on_null) + return -1; // We do not need correct NULL returning + } + was_null= 1; + owner->null_value= 0; + res= 0; // continue comparison (maybe we will meet explicit difference) + } + else if (res) + return res; + } + if (was_null) + { + /* + There was NULL(s) in comparison in some parts, but there was not + explicit difference in other parts, so we have to return NULL + */ + owner->null_value= 1; + return -1; } - return res; + return 0; } int Arg_comparator::compare_e_row() --- 1.122/sql/item_cmpfunc.h 2007-04-04 02:32:15 +05:00 +++ 1.123/sql/item_cmpfunc.h 2007-04-20 01:59:08 +05:00 @@ -206,10 +206,11 @@ class Item_bool_func2 :public Item_int_f protected: Arg_comparator cmp; String tmp_value1,tmp_value2; + bool abort_on_null; public: Item_bool_func2(Item *a,Item *b) - :Item_int_func(a,b), cmp(tmp_arg, tmp_arg+1) {} + :Item_int_func(a,b), cmp(tmp_arg, tmp_arg+1), abort_on_null(FALSE) {} void fix_length_and_dec(); void set_cmp_func() { @@ -222,6 +223,7 @@ public: bool is_null() { return test(args[0]->is_null() || args[1]->is_null()); } bool is_bool_func() { return 1; } CHARSET_INFO *compare_collation() { return cmp.cmp_collation.collation; } + void top_level_item() { abort_on_null=1; } friend class Arg_comparator; }; --- 1.19/mysql-test/r/row.result 2007-04-11 23:41:11 +05:00 +++ 1.20/mysql-test/r/row.result 2007-04-20 01:58:47 +05:00 @@ -53,7 +53,7 @@ SELECT (1,2,3)=(1,NULL,3); NULL SELECT (1,2,3)=(1,NULL,0); (1,2,3)=(1,NULL,0) -NULL +0 SELECT ROW(1,2,3)=ROW(1,2,3); ROW(1,2,3)=ROW(1,2,3) 1 @@ -188,3 +188,88 @@ SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1, ERROR 21000: Operand should contain 1 column(s) SELECT ROW(2,1) IN (ROW(ROW(1,1,3),0),ROW(21,2)); ERROR 21000: Operand should contain 1 column(s) +CREATE TABLE t1(a int, b int, c int); +INSERT INTO t1 VALUES (1, 2, 3), +(NULL, 2, 3 ), (1, NULL, 3 ), (1, 2, NULL), +(NULL, 2, 3+1), (1, NULL, 3+1), (1, 2+1, NULL), +(NULL, 2, 3-1), (1, NULL, 3-1), (1, 2-1, NULL); +SELECT (1,2,3) <> (1, NULL, 3); +(1,2,3) <> (1, NULL, 3) +NULL +SELECT (1,2,3) <> (1+1, NULL, 3); +(1,2,3) <> (1+1, NULL, 3) +1 +SELECT (1,2,3) <> (1, NULL, 3+1); +(1,2,3) <> (1, NULL, 3+1) +1 +SELECT * FROM t1 WHERE (a,b,c) <> (1,2,3); +a b c +NULL 2 4 +1 NULL 4 +1 3 NULL +NULL 2 2 +1 NULL 2 +1 1 NULL +SELECT (1,2,3) < (NULL, 2, 3); +(1,2,3) < (NULL, 2, 3) +NULL +SELECT (1,2,3) < (1, NULL, 3); +(1,2,3) < (1, NULL, 3) +NULL +SELECT (1,2,3) < (1-1, NULL, 3); +(1,2,3) < (1-1, NULL, 3) +0 +SELECT (1,2,3) < (1+1, NULL, 3); +(1,2,3) < (1+1, NULL, 3) +1 +SELECT * FROM t1 WHERE (a,b,c) < (1,2,3); +a b c +1 1 NULL +SELECT (1,2,3) <= (NULL, 2, 3); +(1,2,3) <= (NULL, 2, 3) +NULL +SELECT (1,2,3) <= (1, NULL, 3); +(1,2,3) <= (1, NULL, 3) +NULL +SELECT (1,2,3) <= (1-1, NULL, 3); +(1,2,3) <= (1-1, NULL, 3) +0 +SELECT (1,2,3) <= (1+1, NULL, 3); +(1,2,3) <= (1+1, NULL, 3) +1 +SELECT * FROM t1 WHERE (a,b,c) <= (1,2,3); +a b c +1 2 3 +1 1 NULL +SELECT (1,2,3) > (NULL, 2, 3); +(1,2,3) > (NULL, 2, 3) +NULL +SELECT (1,2,3) > (1, NULL, 3); +(1,2,3) > (1, NULL, 3) +NULL +SELECT (1,2,3) > (1-1, NULL, 3); +(1,2,3) > (1-1, NULL, 3) +1 +SELECT (1,2,3) > (1+1, NULL, 3); +(1,2,3) > (1+1, NULL, 3) +0 +SELECT * FROM t1 WHERE (a,b,c) > (1,2,3); +a b c +1 3 NULL +SELECT (1,2,3) >= (NULL, 2, 3); +(1,2,3) >= (NULL, 2, 3) +NULL +SELECT (1,2,3) >= (1, NULL, 3); +(1,2,3) >= (1, NULL, 3) +NULL +SELECT (1,2,3) >= (1-1, NULL, 3); +(1,2,3) >= (1-1, NULL, 3) +1 +SELECT (1,2,3) >= (1+1, NULL, 3); +(1,2,3) >= (1+1, NULL, 3) +0 +SELECT * FROM t1 WHERE (a,b,c) >= (1,2,3); +a b c +1 2 3 +1 3 NULL +DROP TABLE t1; --- 1.187/mysql-test/r/subselect.result 2007-01-26 06:44:31 +04:00 +++ 1.188/mysql-test/r/subselect.result 2007-04-20 01:58:56 +05:00 @@ -913,7 +913,7 @@ select a, (select a,b,c from t1 where t1 a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a') (select c from t1 where a=t2.a) 1 1 a 2 0 b -NULL NULL NULL +NULL 0 NULL select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2; a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b') (select c from t1 where a=t2.a) 1 0 a @@ -923,7 +923,7 @@ select a, (select a,b,c from t1 where t1 a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c') (select c from t1 where a=t2.a) 1 0 a 2 0 b -NULL NULL NULL +NULL 0 NULL drop table t1,t2; create table t1 (a int, b real, c varchar(10)); insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b'); --- 1.18/mysql-test/t/row.test 2007-04-11 23:41:11 +05:00 +++ 1.19/mysql-test/t/row.test 2007-04-20 01:58:33 +05:00 @@ -108,4 +108,44 @@ SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1, --error 1241 SELECT ROW(2,1) IN (ROW(ROW(1,1,3),0),ROW(21,2)); +# +# Bug#27704: erroneous comparison of rows with NULL components +# +CREATE TABLE t1(a int, b int, c int); +INSERT INTO t1 VALUES (1, 2, 3), + (NULL, 2, 3 ), (1, NULL, 3 ), (1, 2, NULL), + (NULL, 2, 3+1), (1, NULL, 3+1), (1, 2+1, NULL), + (NULL, 2, 3-1), (1, NULL, 3-1), (1, 2-1, NULL); + +SELECT (1,2,3) <> (1, NULL, 3); +SELECT (1,2,3) <> (1+1, NULL, 3); +SELECT (1,2,3) <> (1, NULL, 3+1); +SELECT * FROM t1 WHERE (a,b,c) <> (1,2,3); + +SELECT (1,2,3) < (NULL, 2, 3); +SELECT (1,2,3) < (1, NULL, 3); +SELECT (1,2,3) < (1-1, NULL, 3); +SELECT (1,2,3) < (1+1, NULL, 3); +SELECT * FROM t1 WHERE (a,b,c) < (1,2,3); + +SELECT (1,2,3) <= (NULL, 2, 3); +SELECT (1,2,3) <= (1, NULL, 3); +SELECT (1,2,3) <= (1-1, NULL, 3); +SELECT (1,2,3) <= (1+1, NULL, 3); +SELECT * FROM t1 WHERE (a,b,c) <= (1,2,3); + +SELECT (1,2,3) > (NULL, 2, 3); +SELECT (1,2,3) > (1, NULL, 3); +SELECT (1,2,3) > (1-1, NULL, 3); +SELECT (1,2,3) > (1+1, NULL, 3); +SELECT * FROM t1 WHERE (a,b,c) > (1,2,3); + +SELECT (1,2,3) >= (NULL, 2, 3); +SELECT (1,2,3) >= (1, NULL, 3); +SELECT (1,2,3) >= (1-1, NULL, 3); +SELECT (1,2,3) >= (1+1, NULL, 3); +SELECT * FROM t1 WHERE (a,b,c) >= (1,2,3); + +DROP TABLE t1; + # End of 4.1 tests