summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/row.result99
-rw-r--r--mysql-test/r/subselect.result4
-rw-r--r--mysql-test/t/row.test45
-rw-r--r--sql/item_cmpfunc.cc36
-rw-r--r--sql/item_cmpfunc.h4
5 files changed, 180 insertions, 8 deletions
diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result
index 1c1c4809f36..6169619712b 100644
--- a/mysql-test/r/row.result
+++ b/mysql-test/r/row.result
@@ -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,100 @@ SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1,1,3),0));
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)
+0
+SELECT (1,2,3) = (1, NULL, 3+1);
+(1,2,3) = (1, NULL, 3+1)
+0
+SELECT * FROM t1 WHERE (a,b,c) = (1,2,3);
+a b c
+1 2 3
+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;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index a339a139687..5bb79a53771 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -913,7 +913,7 @@ select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t
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=t2.a) = ROW(a,4,'c'),(select c from t
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');
diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test
index 6c66d45b942..6f845607d8c 100644
--- a/mysql-test/t/row.test
+++ b/mysql-test/t/row.test
@@ -108,4 +108,49 @@ SELECT ROW(2,1) IN (ROW(21,2),ROW(ROW(1,1,3),0));
--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) <> (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
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index ce3096da778..be44d490415 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -680,17 +680,45 @@ int Arg_comparator::compare_e_int_diff_signedness()
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; i<n; i++)
{
- if ((res= comparators[i].compare()))
- return res;
+ res= comparators[i].compare();
if (owner->null_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;
}
- return res;
+ if (was_null)
+ {
+ /*
+ There was NULL(s) in comparison in some parts, but there was no
+ explicit difference in other parts, so we have to return NULL.
+ */
+ owner->null_value= 1;
+ return -1;
+ }
+ return 0;
}
int Arg_comparator::compare_e_row()
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 132e019b4a3..7dbbdc7a63b 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -206,10 +206,11 @@ class Item_bool_func2 :public Item_int_func
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= TRUE; }
friend class Arg_comparator;
};
View Lorry Controller Status