Hi!

Paul> Subject:null safe comparison

Paul> create table null1 (id int, value int); Paul> create table null2 (id int, value int);

Paul> insert into null1 values (1,null); Paul> insert into null2 values (1,null);

Paul> select t1.value<=>t2.value from null1 t1, null2 t2 where t1.id=t2.id Paul> and t1.id=1; Paul> +---------------------+ Paul> | t1.value<=>t2.value | Paul> +---------------------+ Paul> | 0 | Paul> +---------------------+

Paul> select null<=>null; Paul> +-------------+ Paul> | null<=>null | Paul> +-------------+ Paul> | 1 | Paul> +-------------+

While writing a test case for this case to our new test suite, I noticed a bug in my previous patch. Here is a tested correction for this!

Sorry for the wrong patch :(

===== sql/item_cmpfunc.cc 1.10 vs edited ===== *** sql/item_cmpfunc.cc-1.10 Tue Oct 17 23:57:25 2000 --- edited/sql/item_cmpfunc.cc Fri Dec 15 13:29:48 2000 *************** *** 170,184 ****

/* Same as Item_func_eq, but NULL = NULL */

longlong Item_func_equal::val_int() { ! int value=(this->*cmp_func)(); ! if (null_value) { ! null_value=0; ! return (args[0]->null_value && args[1]->null_value) ? 1 : 0; } ! return value == 0; }

--- 170,213 ----

/* Same as Item_func_eq, but NULL = NULL */

+ void Item_func_equal::fix_length_and_dec() + { + Item_bool_func2::fix_length_and_dec(); + result_type=item_cmp_type(args[0]->result_type(),args[1]->result_type()); + maybe_null=0; null_value=0; + } + longlong Item_func_equal::val_int() { ! switch (result_type) { ! case STRING_RESULT: ! { ! String *res1,*res2; ! res1=args[0]->val_str(&tmp_value1); ! res2=args[1]->val_str(&tmp_value2); ! if (!res1 || !res2) ! return test(res1 == res2); ! return (binary ? test(stringcmp(res1,res2) == 0) : ! test(sortcmp(res1,res2) == 0)); ! } ! case REAL_RESULT: { ! double val1=args[0]->val(); ! double val2=args[1]->val(); ! if (args[0]->null_value || args[1]->null_value) ! return test(args[0]->null_value && args[1]->null_value); ! return test(val1 == val2); ! } ! case INT_RESULT: ! { ! longlong val1=args[0]->val_int(); ! longlong val2=args[1]->val_int(); ! if (args[0]->null_value || args[1]->null_value) ! return test(args[0]->null_value && args[1]->null_value); ! return test(val1 == val2); ! } } ! return 0; // Impossible }

===== sql/item_cmpfunc.h 1.7 vs edited ===== *** sql/item_cmpfunc.h-1.7 Wed Aug 30 22:42:20 2000 --- edited/sql/item_cmpfunc.h Fri Dec 15 12:56:52 2000 *************** *** 70,80 ****

class Item_func_equal :public Item_bool_func2 { public: Item_func_equal(Item *a,Item *b) :Item_bool_func2(a,b) { }; longlong val_int(); ! void fix_length_and_dec() ! { Item_bool_func2::fix_length_and_dec() ; maybe_null=0; } enum Functype functype() const { return EQUAL_FUNC; } enum Functype rev_functype() const { return EQUAL_FUNC; } cond_result eq_cmp_result() const { return COND_TRUE; } --- 70,80 ----

class Item_func_equal :public Item_bool_func2 { + Item_result result_type; public: Item_func_equal(Item *a,Item *b) :Item_bool_func2(a,b) { }; longlong val_int(); ! void fix_length_and_dec(); enum Functype functype() const { return EQUAL_FUNC; } enum Functype rev_functype() const { return EQUAL_FUNC; } cond_result eq_cmp_result() const { return COND_TRUE; }

Regards, Monty