Hi, Sachin! I suspect is_null() could be wrong here. Please, add a test case where a table has a BEFORE UPDATE trigger and a long UNIQUE over a NOT NULL field. And see how cmp_binary_offset() is used in compare_record(), for example. On Oct 08, Sachin Setiya wrote:
revision-id: 56f2b3d12b6 (mariadb-10.4.11-385-g56f2b3d12b6) parent(s): af834c218a5 author: Sachin Setiya <sachin.setiya@mariadb.com> committer: Sachin Setiya <sachin.setiya@mariadb.com> timestamp: 2020-09-14 20:08:18 +0100 message:
MDEV-23264 Unique blobs allow duplicate values upon UPDATE
Problem:- We are able to insert duplicate value in table because cmp_binary_offset is not able to differentiate between NULL and empty string. So check_duplicate_long_entry_key is never called and we don't check for duplicate. Solution Added a if condition with is_null() on field which can differentiate between NULL and empty string.
diff --git a/sql/handler.cc b/sql/handler.cc index 40dea349272..71e6a904f23 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -6699,8 +6699,13 @@ static int check_duplicate_long_entries_update(TABLE *table, handler *h, uchar * for (uint j= 0; j < key_parts; j++, keypart++) { field= keypart->field; - /* Compare fields if they are different then check for duplicates*/ - if(field->cmp_binary_offset(reclength)) + /* + Compare fields if they are different then check for duplicates + cmp_binary_offset cannot differentiate between null and empty string + So also check for that too + */ + if((field->is_null(0) != field->is_null(reclength)) || + field->cmp_binary_offset(reclength)) { if((error= check_duplicate_long_entry_key(table, table->update_handler, new_rec, i)))
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org