- developers - lists.mariadb.org

Re: [Maria-developers] ddec45aa50e: MDEV-16932: ASAN heap-use-after-free in my_charlen_utf8 / my_well_formed_char_length_utf8 on 2nd execution of SP with ALTER trying to add bad CHECK
by Sergei Golubchik 20 May '19

20 May '19

Hi, Oleksandr! On May 20, Oleksandr Byelkin wrote: > Alter table will trigger recompilation of the SP (as I remember all SPs) I've run this test, it complains about duplicate constraint name. > > What about this: > > > > CREATE TABLE t1 (a INT); > > CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); > > --error ER_BAD_FIELD_ERROR > > CALL sp; > > alter table t1 add column b int, add constraint check (b < 10); > > CALL sp; > > > > ? > > second CALL will not regenerate the name of the constraint. > > Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] ddec45aa50e: MDEV-16932: ASAN heap-use-after-free in my_charlen_utf8 / my_well_formed_char_length_utf8 on 2nd execution of SP with ALTER trying to add bad CHECK
by Sergei Golubchik 20 May '19

20 May '19

Hi, Oleksandr! On May 20, Oleksandr Byelkin wrote: > On Mon, May 20, 2019 at 3:11 PM Sergei Golubchik <serg(a)mariadb.org> wrote: > > On May 20, Oleksandr Byelkin wrote: > > > revision-id: ddec45aa50e (mariadb-10.2.24-11-gddec45aa50e) > > > parent(s): 50999738eae > > > author: Oleksandr Byelkin <sanja(a)mariadb.com> > > > committer: Oleksandr Byelkin <sanja(a)mariadb.com> > > > timestamp: 2019-05-14 14:01:15 +0200 > > > message: > > > > > > MDEV-16932: ASAN heap-use-after-free in my_charlen_utf8 / > > > my_well_formed_char_length_utf8 on 2nd execution of SP with ALTER > > > trying to add bad CHECK > > > > > > In case of error the SP can be executed without re-comilation and so > > > will reuse constructed constaint name, so the name should be allocated > > > in the statement memory. > > > > Is that right? You're saying that the "unique constraint name" will be > > generated once and reused? > > > > But on the second SP invocation is might no longer be unique. I'd think > > that `ALTER TABLE t1 ADD CONSTRAINT CHECK (x > 0)` should add a new > > constraint, not "add a new contraint with the name that used to be > > unique when the SP was invoked for the first time". > > With the second call we have 2 possibility: > > 1) first execution failed and so we can reuse the name > 2) first execution succeed and we will re-compile SP or re-prepare PS and > so will generate new name (that is why we mostly had no problems) What about this: CREATE TABLE t1 (a INT); CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); --error ER_BAD_FIELD_ERROR CALL sp; alter table t1 add column b int, add constraint check (b < 10); CALL sp; ? second CALL will not regenerate the name of the constraint. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] ddec45aa50e: MDEV-16932: ASAN heap-use-after-free in my_charlen_utf8 / my_well_formed_char_length_utf8 on 2nd execution of SP with ALTER trying to add bad CHECK
by Sergei Golubchik 20 May '19

20 May '19

Hi, Oleksandr! On May 20, Oleksandr Byelkin wrote: > revision-id: ddec45aa50e (mariadb-10.2.24-11-gddec45aa50e) > parent(s): 50999738eae > author: Oleksandr Byelkin <sanja(a)mariadb.com> > committer: Oleksandr Byelkin <sanja(a)mariadb.com> > timestamp: 2019-05-14 14:01:15 +0200 > message: > > MDEV-16932: ASAN heap-use-after-free in my_charlen_utf8 / > my_well_formed_char_length_utf8 on 2nd execution of SP with ALTER > trying to add bad CHECK > > In case of error the SP can be executed without re-comilation and so > will reuse constructed constaint name, so the name should be allocated > in the statement memory. Is that right? You're saying that the "unique constraint name" will be generated once and reused? But on the second SP invocation is might no longer be unique. I'd think that `ALTER TABLE t1 ADD CONSTRAINT CHECK (x > 0)` should add a new constraint, not "add a new contraint with the name that used to be unique when the SP was invoked for the first time". > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > index 3df877792cc..ecd5de3a09d 100644 > --- a/sql/sql_table.cc > +++ b/sql/sql_table.cc > @@ -4185,9 +4185,15 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, > while ((check= c_it++)) > { > if (!check->name.length) > + { > + Query_arena backup; > + Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); > make_unique_constraint_name(thd, &check->name, > &alter_info->check_constraint_list, > &nr); > + if (arena) > + thd->restore_active_arena(arena, &backup); > + } > { > /* Check that there's no repeating constraint names. */ > List_iterator_fast<Virtual_column_info> Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] [Commits] 66349551bf3: MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY
by Sergey Petrunia 19 May '19

19 May '19

Hi Varun, The patch is mostly fine. Please find some input on the comments / coding style below. Ok to push after it is addressed. On Fri, May 17, 2019 at 01:44:36PM +0530, Varun wrote: > revision-id: 66349551bf3a266fbefaebfe044abd05108723c2 (mariadb-10.3.12-205-g66349551bf3) > parent(s): 3d56adbfac394b2b3ffd22a89fe7c2978ed9a505 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2019-05-17 13:44:05 +0530 > message: > > MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY > > The issue in this case is that we take in account the estimates from quick keys instead of rec_per_key. > The estimates for quick keys are better than rec_per_key only if we have ref(const), so we need to check > that all keyparts in the ref key are of the type ref(const). > > --- > mysql-test/main/order_by.result | 57 +++++++++++++++++++++++++++++++++++++++++ > mysql-test/main/order_by.test | 37 ++++++++++++++++++++++++++ > sql/sql_select.cc | 42 ++++++++++++++++++++---------- > 3 files changed, 122 insertions(+), 14 deletions(-) ... > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index f36a68bc7ae..8cdcf3afc8b 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -9986,31 +9986,35 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, > j->ref.null_rejecting|= (key_part_map)1 << i; > keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; > /* > - Todo: we should remove this check for thd->lex->describe on the next > - line. With SHOW EXPLAIN code, EXPLAIN printout code no longer depends > - on it. However, removing the check caused change in lots of query > - plans! Does the optimizer depend on the contents of > - table_ref->key_copy ? If yes, do we produce incorrect EXPLAINs? > + We don't want to compute heavy expressions in EXPLAIN, an example would > + select * from t1 where t1.key=(select thats very heavy); > + > + (select thats very heavy) => is a constant here > + eg: (select avg(order_cost) from orders) => constant but expensive > */ > if (!keyuse->val->used_tables() && !thd->lex->describe) > { // Compare against constant > - store_key_item tmp(thd, > + store_key_item tmp(thd, > keyinfo->key_part[i].field, > key_buff + maybe_null, > maybe_null ? key_buff : 0, > keyinfo->key_part[i].length, > keyuse->val, > FALSE); > - if (unlikely(thd->is_fatal_error)) > - DBUG_RETURN(TRUE); > - tmp.copy(); > + if (unlikely(thd->is_fatal_error)) > + DBUG_RETURN(TRUE); > + tmp.copy(); > j->ref.const_ref_part_map |= key_part_map(1) << i ; > } > else > - *ref_key++= get_store_key(thd, > - keyuse,join->const_table_map, > - &keyinfo->key_part[i], > - key_buff, maybe_null); > + { > + *ref_key++= get_store_key(thd, > + keyuse,join->const_table_map, > + &keyinfo->key_part[i], > + key_buff, maybe_null); > + if (!keyuse->val->used_tables()) > + j->ref.const_ref_part_map |= key_part_map(1) << i ; > + } > /* > Remember if we are going to use REF_OR_NULL > But only if field _really_ can be null i.e. we force JT_REF > @@ -25256,6 +25260,8 @@ bool JOIN_TAB::save_explain_data(Explain_table_access *eta, > { > if (!(eta->ref_list.append_str(thd->mem_root, "const"))) > return 1; > + if (thd->lex->describe) > + key_ref++; This is very cryptic. Please provide an explanation, something like: create_ref_for_key() handles keypart=const equalities as follows: - non-EXPLAIN execution will copy the "const" to lookup tuple immediately and will not add an element to ref.key_copy - EXPLAIN will put an element into ref.key_copy. Since we've just printed "const" for it, we should skip it here. > } > else > { > @@ -26921,7 +26927,15 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, > */ > if (ref_key >= 0 && ref_key != MAX_KEY && tab->type == JT_REF) > { > - if (table->quick_keys.is_set(ref_key)) > + /* > + For all the parts of the ref key we check if all of them belong > + to the type ref(const). This is done because if all parts of the ref > + key are of type ref(const), then we are sure that the estimates > + provides by quick keys is better than that provide by rec_per_key. The above is hard to read and has typos. Here's a shorter variant: If ref access uses keypart=const for all its key parts, and quick select uses the same # of key parts, then they are equivalent. Reuse #rows estimate from quick select as it is more precise. > + */ > + if (tab->ref.const_ref_part_map == make_prev_keypart_map(tab->ref.key_parts) && > + table->quick_keys.is_set(ref_key) && > + table->quick_key_parts[ref_key] == tab->ref.key_parts) Please fix indetation; One of the lines is too long. > refkey_rows_estimate= table->quick_rows[ref_key]; > else > { BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] FYI: usage of F1-F12 keys in mysql client
by Aleksey Midenkov 17 May '19

17 May '19

By adding something like this in your .inputrc: $if MYSQL "\033OP": "use information_schema; show tables;\n" "\033OQ": "use test;\n" "\033OR": "select * from " "\033OS": "show columns from " "\033[15~": "\033[17~": "\033[18~": "\033[19~": "\033[20~": "\033[21~": "\033[23~": "\033[24~": $endif you may have F1-F12 keys utilized. Different terminals have different codes (.inputrc doesn't support capability names unfortunately), so you'll have to find out your own codes via tack utiity. With strong will and enough patience it is possible to add Ctrl+, Alt+, Shift+ and their combinations as well. This works only with MDEV-17609 patch. -- All the best, Aleksey Midenkov @midenok

1 0

Re: [Maria-developers] MDEV-19284-19285v3
by Eugene Kosov 14 May '19

14 May '19

Hi, Alexander! Patch looks good to me. 13.05.2019, 17:03, "Alexander Barkov" <bar(a)mariadb.com>: > Hi Eugene, > > Please review the next round of the patch. > > Are any changes in tests still needed? > > Thanks! -- Eugene

1 0

Re: [Maria-developers] 7198e7c71fc: MDEV-15458 Segfault in heap_scan() upon UPDATE after ADD SYSTEM VERSIONING
by Sergei Golubchik 13 May '19

13 May '19

Hi, Aleksey! On Dec 23, Aleksey Midenkov wrote: > revision-id: 7198e7c71fc (versioning-1.0.6-82-g7198e7c71fc) > parent(s): 6be155757b7 > author: Aleksey Midenkov <midenok(a)gmail.com> > committer: Aleksey Midenkov <midenok(a)gmail.com> > timestamp: 2018-12-20 13:25:00 +0300 > message: > > MDEV-15458 Segfault in heap_scan() upon UPDATE after ADD SYSTEM VERSIONING > > * HA_EXTRA_REMEMBER_POS, HA_EXTRA_RESTORE_POS for HEAP storage engine > * Versioning tests support As far as I can see, there are two approaches to fixing this bug. And if we go the way of HA_EXTRA_REMEMBER_POS/HA_EXTRA_RESTORE_POS then your fix is good. But I'd still like to understand whether write_row is supposed to disturb the scan. Why should heap_write() overwrite info->current_ptr=pos and info->current_hash_ptr? Inserting a row doesn't happen at the "current position", so I don't quite understand why it modifies current_ptr at all. There is no functionality like "continue the scan from the last inserted row", right? It just doesn't make sense to me. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 2

[Maria-developers] Welcome to GSoC 2019
by Vicențiu Ciorbaru 10 May '19

10 May '19

Hi! As you may have noticed a few days ago, you have received an email from Google that you have been accepted as part of Google Summer of Code. Congratulations on the effort to learn about the program, get involved and submit succesful proposals. The fun part begins now! If you have not done so already, please reach out to your mentor and seek further advice about your project. Each one of your projects can be implemented in multiple ways, some more complex than others. I strongly advise you to create a concrete plan based off of your proposal with your mentor and aim to stick to it. Your mentor can guide you to ensure you have a succesful GSoC. Remember that you are here to learn about Open Source, what it means to be part of an Open Source community, to interact with the community, all while becoming better programmers. Please take advantage of this opportunity and keep your technical discussions public, on Zulip https://mariadb.zulipchat.com, and on our developer mailing list https://launchpad.net/~maria-developers. By keeping your discussions public, you get the benefit of not only having your mentor see your questions but all of the MariaDB community. You never know when someone might lend a hand in case your mentor is not yet online. :) The mantra of our community should be that we are always welcoming to new people. Do not be afraid to ask questions. There are no bad or stupid or silly questions. The only key requirement is that you first make an effort to find the answer yourself and only afterwards seek outside help. If you can show a bit of involvement, the community will show you the same. I recommend that you agree with your mentor on some form of weekly progress reporting, via either a blog-post, an email, or something similar that can be publicly shared. Hope all of you will have a great GSoC! Vicențiu MariaDB Foundation GSoC 2019 Admin

1 0

Re: [Maria-developers] a4be420c920: MDEV-19235 MariaDB Server compiled for 128 Indexes crashes at startup
by Sergei Golubchik 09 May '19

09 May '19

Hi, Vladislav! Thanks. That's pretty much the solution I had in mind too :) A couple of comments on details below. I've only commented on sql_bitmap.h, everything else is fine. On May 08, Vladislav Vaintroub wrote: > revision-id: a4be420c920 (mariadb-10.4.4-25-ga4be420c920) > parent(s): ee4a2fef181 > author: Vladislav Vaintroub <wlad(a)mariadb.com> > committer: Vladislav Vaintroub <wlad(a)mariadb.com> > timestamp: 2019-04-17 17:17:06 +0100 > message: > > MDEV-19235 MariaDB Server compiled for 128 Indexes crashes at startup > > With MAX_INDEXIES=64(default), key_map=Bitmap<64> is just a wrapper around > ulonglong and thus "trivial" (can be bzero-ed, or memcpy-ed, and stays > valid still) > > With MAX_INDEXES=128, key_map = Bitmap<128> is not a "trivial" type > anymore. The implementation uses MY_BITMAP, and MY_BITMAP contains pointers > which make Bitmap invalid, when it is memcpy-ed/bzero-ed. > > The problem in 10.4 is that there are many new key_map members, inside TABLE > or KEY, and those are often memcopied and bzeroed > > The fix makes Bitmap "trivial", by inlining most of MY_BITMAP functionality. > pointers/heap allocations are not used anymore. > diff --git a/sql/sql_bitmap.h b/sql/sql_bitmap.h > index 705a8d169e1..74866336a1a 100644 > --- a/sql/sql_bitmap.h > +++ b/sql/sql_bitmap.h > @@ -25,73 +25,184 @@ > > #include <my_sys.h> > #include <my_bitmap.h> > +#include <my_bit.h> > > template <uint width> class Bitmap > { > - MY_BITMAP map; > +public: > uint32 buffer[(width+31)/32]; Why is it public? > public: > - Bitmap() { init(); } > + Bitmap():buffer() What does that do? > + { > + } > - Bitmap(const Bitmap& from) { *this=from; } > - explicit Bitmap(uint prefix_to_set) { init(prefix_to_set); } > + explicit Bitmap(uint prefix_to_set):buffer() > + { > + set_prefix(prefix_to_set); > + } > - void init() { my_bitmap_init(&map, buffer, width, 0); } > - void init(uint prefix_to_set) { init(); set_prefix(prefix_to_set); } > uint length() const { return width; } > - Bitmap& operator=(const Bitmap& map2) > - { > + void init() > + { > + clear_all(); > + } > + void init(uint prefix_to_set) > + { > init(); > - memcpy(buffer, map2.buffer, sizeof(buffer)); > - return *this; > + set_prefix(prefix_to_set); I wonder whether init() method is still needed. You've removed it from the constructor. And if there's any other code that uses it, I'd rather it'd be using clear_all(). > } > - void set_bit(uint n) { bitmap_set_bit(&map, n); } > + void set_bit(uint n) > + { > + DBUG_ASSERT(n < width); > + ((uchar*)buffer)[n / 8] |= (1 << (n & 7)); > + } > - void clear_bit(uint n) { bitmap_clear_bit(&map, n); } > + void clear_bit(uint n) > + { > + DBUG_ASSERT(n < width); > + ((uchar*)buffer)[n / 8] &= ~(1 << (n & 7)); > + } > - void set_prefix(uint n) { bitmap_set_prefix(&map, n); } > + void set_prefix(uint prefix_size) > + { > + set_if_smaller(prefix_size, width); > + uint prefix_bytes, prefix_bits, d; > + uchar* m = (uchar*)buffer; > + > + if ((prefix_bytes = prefix_size / 8)) > + memset(m, 0xff, prefix_bytes); > + m += prefix_bytes; > + if ((prefix_bits = prefix_size & 7)) > + { > + *(m++) = (1 << prefix_bits) - 1; > + // As the prefix bits are set, lets count this byte too as a prefix byte. > + prefix_bytes++; > + } > + if ((d = (width + 7) / 8 - prefix_bytes)) > + memset(m, 0, d); > + } > - void set_all() { bitmap_set_all(&map); } > + void set_all() > + { > + set_prefix(width); may be better to optimize it? memset(buffer, 0xff, sizeof(buffer) - 1); ((uchar*)buffer)[sizeof(buffer)-1] = last_byte_mask(width); > + } > - void clear_all() { bitmap_clear_all(&map); } > + void clear_all() > + { > + memset(buffer, 0x00, sizeof(buffer)); > + } > - void intersect(Bitmap& map2) { bitmap_intersect(&map, &map2.map); } > + void intersect(Bitmap & map2) > + { > + for (uint i = 0; i < array_elements(buffer); i++) > + buffer[i] &= map2.buffer[i]; > + } > - void intersect(ulonglong map2buff) > - { > - // Use a spearate temporary buffer, as bitmap_init() clears all the bits. > - ulonglong buf2; > - MY_BITMAP map2; > - > - my_bitmap_init(&map2, (uint32 *) &buf2, sizeof(ulonglong) * 8, 0); > - > - // Store the original bits. > - if (sizeof(ulonglong) >= 8) > - { > - int8store(const_cast<uchar *>(static_cast<uchar *> > - (static_cast<void *>(&buf2))), > - map2buff); > - } > - else > - { > - DBUG_ASSERT(sizeof(buffer) >= 4); > - int4store(const_cast<uchar *>(static_cast<uchar *> > - (static_cast<void *>(&buf2))), > - static_cast<uint32>(map2buff)); > - } > - bitmap_intersect(&map, &map2); > - } > + void intersect_and_pad(ulonglong map2buff, bool pad_with_ones) make it private? > + { > + compile_time_assert(sizeof(ulonglong) == 8); > + uint32 tmp[2]; > + int8store(tmp, map2buff); > + if (array_elements(buffer) > 2 && pad_with_ones) > + set_all(); really? I'd expect that if pad_with_ones==true, you simply don't need to do anything to the rest of the buffer. > + > + buffer[0] &= tmp[0]; > + if (array_elements(buffer) > 1) > + buffer[1] &= tmp[1]; > + > + if (array_elements(buffer) > 2 && !pad_with_ones) > + memset((char*)buffer + 8, 0 , sizeof(buffer) - 8); > + } > + void intersect(ulonglong map2buff) > + { > + intersect_and_pad(map2buff, 0); > + } > /* Use highest bit for all bits above sizeof(ulonglong)*8. */ > void intersect_extended(ulonglong map2buff) > { > - intersect(map2buff); > - if (map.n_bits > sizeof(ulonglong) * 8) > - bitmap_set_above(&map, sizeof(ulonglong), > - MY_TEST(map2buff & (1LL << (sizeof(ulonglong) * 8 - 1)))); > + intersect_and_pad(map2buff, (map2buff & (1ULL << 63))); > } > - void subtract(Bitmap& map2) { bitmap_subtract(&map, &map2.map); } > + void subtract(Bitmap & map2) > + { > + for (size_t i = 0; i < array_elements(buffer); i++) > + buffer[i] &= ~(map2.buffer[i]); > + } > - void merge(Bitmap& map2) { bitmap_union(&map, &map2.map); } > + void merge(Bitmap & map2) > + { > + for (size_t i = 0; i < array_elements(buffer); i++) > + buffer[i] |= map2.buffer[i]; > + } > - bool is_set(uint n) const { return bitmap_is_set(&map, n); } > + bool is_set(uint n) const > + { > + DBUG_ASSERT(n < width); > + return ((uchar*)buffer)[n / 8] & (1 << (n & 7)); > + } > - bool is_prefix(uint n) const { return bitmap_is_prefix(&map, n); } > + bool is_prefix(uint prefix_size) const > + { > + uint prefix_mask = last_byte_mask(prefix_size); > + uchar* m = (uchar*)buffer; > + uchar* end_prefix = m + (prefix_size - 1) / 8; > + uchar* end; > + DBUG_ASSERT(prefix_size <= width); > + > + /* Empty prefix is always true */ > + if (!prefix_size) > + return true; > + > + while (m < end_prefix) > + if (*m++ != 0xff) > + return false; > + > + end = ((uchar*)buffer) + (width + 7) / 8 - 1; > + if (m == end) > + return ((*m & last_byte_mask(width)) == prefix_mask); > + > + if (*m != prefix_mask) > + return false; > + > + while (++m < end) > + if (*m != 0) > + return false; > + return ((*m & last_byte_mask(width)) == 0); > + } > - bool is_clear_all() const { return bitmap_is_clear_all(&map); } > + bool is_clear_all() const > + { > + for (size_t i= 0; i < array_elements(buffer); i++) > + if (buffer[i]) > + return false; > + return true; > + } > - bool is_set_all() const { return bitmap_is_set_all(&map); } > + bool is_set_all() const > + { > + if (width == sizeof(buffer) * 8) > + { > + for (size_t i = 0; i < array_elements(buffer); i++) > + if (buffer[i] != 0xFFFFFFFFU) > + return false; > + return true; > + } > + else > + return is_prefix(width); this can also be optimized to not use complex is_prefix(), but I don't think is_set_all() is used as often as set_all(), so it shouldn't matter as much. > + } > - bool is_subset(const Bitmap& map2) const { return bitmap_is_subset(&map, &map2.map); } > + bool is_subset(const Bitmap & map2) const > + { > + for (size_t i= 0; i < array_elements(buffer); i++) > + if (buffer[i] & ~(map2.buffer[i])) > + return false; > + return true; > + } > - bool is_overlapping(const Bitmap& map2) const { return bitmap_is_overlapping(&map, &map2.map); } > + bool is_overlapping(const Bitmap & map2) const > + { > + for (size_t i = 0; i < array_elements(buffer); i++) > + if (buffer[i] & map2.buffer[i]) > + return true; > + return false; > + } > - bool operator==(const Bitmap& map2) const { return bitmap_cmp(&map, &map2.map); } > + bool operator==(const Bitmap & map2) const > + { > + return memcmp(buffer, map2.buffer, sizeof(buffer)) == 0; > + } > - bool operator!=(const Bitmap& map2) const { return !(*this == map2); } > + bool operator!=(const Bitmap & map2) const > + { > + return !(*this == map2); > + } > char *print(char *buf) const > { > char *s=buf; Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 2

Re: [Maria-developers] [Commits] 0ff8b73b60f: MDEV-15777:Support Early NULLs filtering-like restrictions in the range optimizer
by Sergey Petrunia 06 May '19

06 May '19

Hi Varun, Please finally find the review below. The patch is almost there I think, but there are some minor adjustments that still need to be made. > revision-id: 0ff8b73b60fae40556f91d3959f20bf3cf182b28 (mariadb-10.3.0-947-g0ff8b73b60f) > parent(s): 15419a558370aeed9521b498c34d50f20a8d47a5 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2018-05-15 13:53:11 +0530 > message: > > MDEV-15777:Support Early NULLs filtering-like restrictions in the range optimizer > > --- > mysql-test/main/mdev15777.result | 455 +++++++++++++++++++++++++++++++++++++++ > mysql-test/main/mdev15777.test | 138 ++++++++++++ > sql/opt_range.cc | 125 ++++++++++- > sql/opt_range.h | 2 + > sql/sql_select.cc | 22 +- > sql/table.cc | 1 + > sql/table.h | 6 + > 7 files changed, 745 insertions(+), 4 deletions(-) > > diff --git a/mysql-test/main/mdev15777.result b/mysql-test/main/mdev15777.result > new file mode 100644 > index 00000000000..549ac08b91f > --- /dev/null > +++ b/mysql-test/main/mdev15777.result > @@ -0,0 +1,455 @@ > +# Test added to check that null filtering is used by range optimizer > +create table ten(a int); > +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); > +create table one_k(a int); > +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; > +create table one_m(a int); > +insert into one_m select A.a + B.a* 1000 from one_k A, one_k B; The testcases are extremely confusing. * Do you really need a table with one millon rows to demonstrate the issue? * Why use examples with subqueries (which are converted into join anyway)? - if we want to verify that this optimization works together with subquery-to-semi-join conversion, let's start with a join example, and then construct a subquery which is converted to the query. * The testcases produce a lot of EXPLAIN outputs, and there is no explanation provided what one should expect. Please create a testcase: - of reasonable size (I think tables of 10-20K rows should suffice) - start from a join query - Like in other testcases, tables should be named, t1,t2, ... - I assume the query plan should be: range access on the first table (constructed from a NOT NULL predicate), ref access for the second one. - The testcase should have '--echo #' comments explaining what is relevant in the query plan > +delete from one_m where a=0 limit 1; > +create table t1 ( > +id int(10) unsigned NOT NULL AUTO_INCREMENT, > +filler varchar(100), > +subset_id int(11) DEFAULT NULL, > +PRIMARY KEY (id), > +KEY t1_subset_id (subset_id) > +); > +create table t1_subsets ( ... > diff --git a/sql/opt_range.cc b/sql/opt_range.cc > index 8422917065f..ae06b899ac9 100644 > --- a/sql/opt_range.cc > +++ b/sql/opt_range.cc > @@ -155,6 +155,7 @@ class SEL_IMERGE; > #define CLONE_KEY1_MAYBE 1 > #define CLONE_KEY2_MAYBE 2 > #define swap_clone_flag(A) ((A & 1) << 1) | ((A & 2) >> 1) > +#define FT_KEYPART (MAX_FIELDS+10) > > > /* > @@ -2400,6 +2401,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, > { > uint idx; > double scan_time; > + Item *null_rejecting_conds= NULL; > DBUG_ENTER("SQL_SELECT::test_quick_select"); > DBUG_PRINT("enter",("keys_to_use: %lu prev_tables: %lu const_tables: %lu", > (ulong) keys_to_use.to_ulonglong(), (ulong) prev_tables, > @@ -2423,6 +2425,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, > read_time= (double) records + scan_time + 1; // Force to use index > > possible_keys.clear_all(); > + null_rejecting_conds= head->null_rejecting_conds; > > DBUG_PRINT("info",("Time to scan table: %g", read_time)); > > @@ -2431,7 +2434,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, > { > uchar buff[STACK_BUFF_ALLOC]; > MEM_ROOT alloc; > - SEL_TREE *tree= NULL; > + SEL_TREE *tree= NULL, *not_null_cond_tree= NULL; > KEY_PART *key_parts; > KEY *key_info; > PARAM param; > @@ -2540,6 +2543,12 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, > TRP_GROUP_MIN_MAX *group_trp; > double best_read_time= read_time; > Please add a comment saying that as far as range optimizer is concerned, null_rejecting_conds is just an extra condition on this table. (That is, we don't care if it has NOT NULL conditions or something else). > + if (null_rejecting_conds) > + { > + not_null_cond_tree= null_rejecting_conds->get_mm_tree(&param, > + &null_rejecting_conds); > + } > + > if (cond) > { > if ((tree= cond->get_mm_tree(&param, &cond))) > @@ -2558,6 +2567,13 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, > tree= NULL; > } > } > + if (not_null_cond_tree) > + { > + if (!tree) > + tree= not_null_cond_tree; > + else > + tree= tree_and(&param, tree, not_null_cond_tree); This if-else logic is redundant, as it's already present in tree_and(). Can just have this: tree= tree_and(tree, not_null_cond_tree); > + } > > /* > Try to construct a QUICK_GROUP_MIN_MAX_SELECT. > @@ -14643,6 +14659,113 @@ void QUICK_GROUP_MIN_MAX_SELECT::add_keys_and_lengths(String *key_names, > add_key_and_length(key_names, used_lengths, &first); > } > > +inline void add_cond(THD *thd, Item **e1, Item *e2) > +{ > + if (*e1) > + { > + if (!e2) > + return; > + Item *res; > + if ((res= new (thd->mem_root) Item_cond_and(thd, *e1, e2))) > + { > + res->fix_fields(thd, 0); > + res->update_used_tables(); > + *e1= res; > + } > + } > + else > + *e1= e2; > +} > + > +/* > + Create null rejecting conditions for a table, for all the equalites > + present in the WHERE clause of a query. > + > + SYNOPSIS > + make_null_rejecting_conds() > + @param TABLE - Keys of this table will participate in null > + rejecting conditions > + @param keyuse_array - array that has all the equalites of the > + WHERE clasuse > + > + DESCRIPTION > + This function creates null rejecting conditions for a table. These > + conditions are created to do range analysis on them , the conditions > + are of the form tbl.key.keypart IS NOT NULL. > + > + IMPLEMENTATION > + Lookup in the keyuse array to check if it has equalites that belong > + to the given table. If yes then find out if the conditions are null > + rejecting and accordingly create all the condition for the keys of a > + given table and AND them. > + > + > + RETURN > + NOT NULL - Found null rejecting conditions for the given table > + NULL - No null rejecting conditions for the given table > +*/ > + > +void make_null_rejecting_conds(THD *thd, TABLE *table, > + DYNAMIC_ARRAY *keyuse_array, key_map *const_keys) ^ Please fix identation. > +{ > + KEY *keyinfo; > + Item *cond= NULL; > + KEYUSE* keyuse; > + > + /* > + The null rejecting conds added will be on the keypart of a key, so for > + that we need the table to atleast have a key. > + */ please fix typos/wording in the above comment. > + if (!table->s->keys) > + return ; > + if (table->null_rejecting_conds) > + return; > + > + for(uint i=0; i < keyuse_array->elements; i++) > + { This loop does excessive work. Check out how best_access_path() examines the KEYUSE array. Key points - the array is sorted by table, index_nr, key_part_nr. - JOIN_TAB::keyuse points to the first element that refers to the table described by the JOIN_TAB - The array may have "duplicates", especially when multiple equalities are present: KEYUSE(t.keyXpartY = other_table.colX) KEYUSE(t.keyXpartY = other_table.colY) KEYUSE(t.keyXpartY = some_expr) ... Since the elements are sorted by keyXpartY, it is trival to avoid generating multiple "t.keyXpartY IS NOT NULL" for consecutive KEYUSEs. I would go even further: the same "t.keyXpartY" can be part of multiple indexes (and so its KEYUSE objects will not be adjacent). Can we use a table's column bitmap to avoid generating duplicate IS NOT NULL objects? > + keyuse= (KEYUSE*)dynamic_array_ptr(keyuse_array, i); > + if (keyuse->table == table) > + { > + /* > + No null rejecting conds for a hash key orr full-text keys > + */ please fix typos/wording in the above comment. > + if (keyuse->key == MAX_KEY || keyuse->keypart == FT_KEYPART) > + continue; > + keyinfo= keyuse->table->key_info+keyuse->key; > + Field *field= keyinfo->key_part[keyuse->keypart].field; > + > + /* > + No need to add null-rejecting condition if we have a > + keyuse element as > + - table.key.keypart= const > + - (table.key.keypart= tbl.otherfield or table.key.keypart IS NULL) > + - table.key.keypart IS NOT NULLABLE > + */ > + > + if (keyuse->val->const_item() > + || !(keyuse->null_rejecting && field->maybe_null()) Please remove the brackets: !(A && B) -> !A || !B > + || keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL) Please also put the '||' at the end of the lines, like it is done in the rest of the code. > + continue; > + > + Item_field *field_item= new (thd->mem_root)Item_field(thd, field); > + Item* not_null_item= new (thd->mem_root)Item_func_isnotnull(thd, > + field_item); Please add error handling (just return from the function if we failed to allocate any of the above) > + > + /* > + adding the key to const keys as we have the condition > + as key.keypart IS NOT NULL > + */ > + > + const_keys->set_bit(keyuse->key); > + not_null_item->fix_fields(thd, 0); > + not_null_item->update_used_tables(); > + add_cond(thd, &cond, not_null_item); > + } > + } > + table->null_rejecting_conds= cond; > + return; Please remove the above 'return;' as it is redundant and makes one suspect a merge error > +} > + > > #ifndef DBUG_OFF > > diff --git a/sql/opt_range.h b/sql/opt_range.h > index bd85a12d4a1..894d46a892c 100644 > --- a/sql/opt_range.h > +++ b/sql/opt_range.h > @@ -1728,6 +1728,8 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond); > bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond); > #endif > void store_key_image_to_rec(Field *field, uchar *ptr, uint len); > +void make_null_rejecting_conds(THD *thd, TABLE *table, > + DYNAMIC_ARRAY *keyuse_array, key_map *const_keys); Please fix identation. > > extern String null_string; > > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index f658b78c33c..4aceb333340 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -4805,6 +4805,9 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, > add_group_and_distinct_keys(join, s); > > s->table->cond_selectivity= 1.0; > + > + make_null_rejecting_conds(join->thd, s->table, > + keyuse_array, &s->const_keys); > > /* > Perform range analysis if there are keys it could use (1). > @@ -5352,15 +5356,24 @@ add_key_field(JOIN *join, > If the condition has form "tbl.keypart = othertbl.field" and > othertbl.field can be NULL, there will be no matches if othertbl.field > has NULL value. > + > + The field KEY_FIELD::null_rejecting is set to TRUE if we have both > + the left and right hand side of the equality are NULLABLE > + This is NOT what is happening. null_rejecting is set to true, if *either* left or right-hand side of the equality is nullable. Please fix the comment. Please also fix the comment in KEY_FIELD::null_rejecting to reflect that. > We use null_rejecting in add_not_null_conds() to add > 'othertbl.field IS NOT NULL' to tab->select_cond. > + > + We use null_rejecting in make_null_rejecting_conds() to add > + tbl.keypart IS NOT NULL so we can do range analysis on this condition > + Please join the above two sentences together: null_rejecting is used - by add_not_null_conds(), to add "othertbl.field IS NOT NULL" to othertbl's tab->select_cond. (This is called "Early NULLs filtering") - by make_null_rejecting_conds(), to provide range optimizer with additional "tbl.keypart IS NOT NULL" condition. > */ > { > Item *real= (*value)->real_item(); > if (((cond->functype() == Item_func::EQ_FUNC) || > (cond->functype() == Item_func::MULT_EQUAL_FUNC)) && > - (real->type() == Item::FIELD_ITEM) && > + (((real->type() == Item::FIELD_ITEM) && > ((Item_field*)real)->field->maybe_null()) > + ||(field->maybe_null()))) ^Please fix the identation. > (*key_fields)->null_rejecting= true; > else > (*key_fields)->null_rejecting= false; > @@ -9813,7 +9826,10 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, > uint maybe_null= MY_TEST(keyinfo->key_part[i].null_bit); > j->ref.items[i]=keyuse->val; // Save for cond removal > j->ref.cond_guards[i]= keyuse->cond_guard; > - if (keyuse->null_rejecting) > + Item *real= (keyuse->val)->real_item(); > + if (keyuse->null_rejecting && > + (real->type() == Item::FIELD_ITEM) && > + ((Item_field*)real)->field->maybe_null()) ^ Please fix identation > j->ref.null_rejecting|= (key_part_map)1 << i; > keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; > /* > @@ -18538,7 +18554,7 @@ free_tmp_table(THD *thd, TABLE *entry) > DBUG_ASSERT(entry->pos_in_table_list->table == entry); > entry->pos_in_table_list->table= NULL; > } > - > + entry->null_rejecting_conds= NULL; What is this for? As far as I understand we are freeing the TABLE object > free_root(&own_root, MYF(0)); /* the table is allocated in its own root */ > thd_proc_info(thd, save_proc_info); > > diff --git a/sql/table.cc b/sql/table.cc > index a6e445d0d2e..097fee465de 100644 > --- a/sql/table.cc > +++ b/sql/table.cc > @@ -4597,6 +4597,7 @@ void TABLE::init(THD *thd, TABLE_LIST *tl) > created= TRUE; > cond_selectivity= 1.0; > cond_selectivity_sampling_explain= NULL; > + null_rejecting_conds= NULL; > #ifdef HAVE_REPLICATION > /* used in RBR Triggers */ > master_had_triggers= 0; > diff --git a/sql/table.h b/sql/table.h > index 6fd3f219914..50d47899b49 100644 > --- a/sql/table.h > +++ b/sql/table.h > @@ -1356,6 +1356,12 @@ struct TABLE > SplM_opt_info *spl_opt_info; > key_map keys_usable_for_splitting; > > + /* > + Null rejecting conds added for all tables so we can do range analysis > + on these conditions > + */ "for all tables"? Please change the comment to something like: "NULL-rejecting conditions for this table. They are created from eq_ref access, and used by the range optimizer". > + Item* null_rejecting_conds; > + > void init(THD *thd, TABLE_LIST *tl); > bool fill_item_list(List<Item> *item_list) const; > void reset_item_list(List<Item> *item_list, uint skip) const; BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0