Bug #97630 | SELECT DISTINCT returns wrong result when there is WITH ROLLUP | ||
---|---|---|---|
Submitted: | 14 Nov 2019 9:08 | Modified: | 6 Jan 2020 5:58 |
Reporter: | Hope Lee (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 8.0.16, 8.0.18 | OS: | CentOS |
Assigned to: | CPU Architecture: | Any |
[14 Nov 2019 9:08]
Hope Lee
[14 Nov 2019 9:22]
MySQL Verification Team
Hello Lee, Thank you for the report and test case. regards, Umesh
[27 Dec 2019 7:06]
Hope Lee
--- a/sql/sql_executor.cc +++ b/sql/sql_executor.cc @@ -6711,7 +6711,8 @@ bool QEP_TAB::remove_duplicates() { field_count * sizeof(*field_lengths), MYF(MY_WME)); if (field_lengths == nullptr) DBUG_RETURN(true); - size_t key_length = compute_field_lengths(first_field, field_lengths); + size_t key_length = compute_field_lengths(first_field, field_lengths) + + field_count * sizeof((*first_field)->null_bit); free_io_cache(tbl); // Safety tbl->file->info(HA_STATUS_VARIABLE); @@ -6845,6 +6846,12 @@ static bool remove_dup_with_hash_index(THD *thd, TABLE *table, /* copy fields to key buffer */ const size_t *field_length = field_lengths; for (Field **ptr = first_field; *ptr; ++ptr, ++field_length) { + /* Whether the field is NULL should be considered */ + *key_pos = (*ptr)->is_real_null() ? (*ptr)->null_bit : ~(*ptr)->null_bit; + key_pos += sizeof((*ptr)->null_bit); + + *key_pos = (*ptr)->is_real_null() ? (*ptr)->null_bit : ~(*ptr)->null_bit; + key_pos += 1; if ((*ptr)->sort_key_is_varlen()) { size_t len = (*ptr)->make_sort_key(key_pos + sizeof(uint32), *field_length - sizeof(uint32));
[27 Dec 2019 7:09]
Hope Lee
Sorry, there is one mistake in the above comment. This is the correct one: --- a/sql/sql_executor.cc +++ b/sql/sql_executor.cc @@ -6711,7 +6711,8 @@ bool QEP_TAB::remove_duplicates() { field_count * sizeof(*field_lengths), MYF(MY_WME)); if (field_lengths == nullptr) DBUG_RETURN(true); - size_t key_length = compute_field_lengths(first_field, field_lengths); + size_t key_length = compute_field_lengths(first_field, field_lengths) + + field_count * sizeof((*first_field)->null_bit); free_io_cache(tbl); // Safety tbl->file->info(HA_STATUS_VARIABLE); @@ -6845,6 +6846,12 @@ static bool remove_dup_with_hash_index(THD *thd, TABLE *table, /* copy fields to key buffer */ const size_t *field_length = field_lengths; for (Field **ptr = first_field; *ptr; ++ptr, ++field_length) { + /* Whether the field is NULL should be considered */ + *key_pos = (*ptr)->is_real_null() ? (*ptr)->null_bit : ~(*ptr)->null_bit; + key_pos += sizeof((*ptr)->null_bit); + if ((*ptr)->sort_key_is_varlen()) { size_t len = (*ptr)->make_sort_key(key_pos + sizeof(uint32), *field_length - sizeof(uint32));
[27 Dec 2019 8:14]
MySQL Verification Team
Thank you, but please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html If you have any questions, please contact the MySQL community team (mailto:mysql-community_ww@oracle.com) or by visiting https://dev.mysql.com/community/
[6 Jan 2020 5:58]
Erlend Dahl
Fixed in 8.0.20 under the heading of Bug#27549694 DISTINCT REMOVES ROWS INCORRECTY WHEN "NULLS" ARE PRESENT Note that the contributed code was not used, since the bug had already been fixed.