- developers - lists.mariadb.org

Re: [Maria-developers] Mdev-14586 Assertion `0' failed in retrieve_auto_increment upon attempt to drop PK
by Sergei Golubchik 19 Jan '18

19 Jan '18

Hi, Sachin! I agree with the patch, I think it's correct. Good tests too. I wasn't able to understand the bug from the commit comment though, see below. Ok to push (preferrably with the improved commit message). On Jan 19, Sachin Setiya wrote: > Hi serg , please review the latest patch > > commit d9641478bb8f6045039a3d942f2925ada334907e > Author: Sachin Setiya <sachin.setiya(a)maridb.com> > Date: Fri Jan 19 19:33:45 2018 +0530 > > MDEV-14586 Assertion `0' failed in retrieve_auto_increment ... > > Problem:- > If we create table using myisam/aria then this crashes the server. > CREATE TABLE t1(a bit(1), b int auto_increment , index(a,b)); > insert into t1 values(1,1); > Or this query > CREATE TABLE t1 (b BIT(1), pk INTEGER AUTO_INCREMENT PRIMARY KEY); > ALTER TABLE t1 ADD INDEX(b,pk); > INSERT INTO t1 VALUES (1,b'1'); > ALTER TABLE t1 DROP PRIMARY KEY; > > Reason:- > The reason for this is > 1st- Since bit field is stored in record null bits itself , so > find_ref_key in open_binary_frm will wrongly set share->next_number_key_offset > and share->next_number_keypart (both set to 0). I would say that find_ref_key() finds what key an auto_increment field belongs to by comparing key_part->offset and field->ptr. But BIT fields might have zero length in the record, so a key might have many key parts with the same offset. That is, comparing offsets cannot uniquely identify the correct key part. > 2nd- Since next_number_key_offset is zero it myisam/aria will think that > auto_increment is in first part of key. > 3nd- myisam/aria will call retrieve_auto_key which will see first key_part > field as a bit field and call assert(0) > > Solution:- Many key parts might have the same offset, but BIT fields do not support auto_increment. So, we can skip all key parts over BIT fields, and then comparing offsets will be unambiguous. > Although the bit field is stored in record null_byte but in key file(.MYI) > this does not hold true. Bit(1-7) will take one byte of memory. So the correct > approach will be to never call retrieve_auto_increment in the case when > bit field is in the starting of index. So I have changed the find_ref_key > function to take account that if first N fields is Bit field then simply go > to next field which is not Bit field. > Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] [Commits] 60d1803ec64: MDEV-6707: Wrong result (extra row) with group by, multi-part key
by Sergey Petrunia 18 Jan '18

18 Jan '18

On Thu, Jan 11, 2018 at 02:35:55AM +0530, Varun wrote: > revision-id: 60d1803ec645b6d388aac7125ac6381ecee4d548 (mariadb-5.5.56-115-g60d1803ec64) > parent(s): 9c9cf556a15af84f9133ef859b50596085f7ae8e > author: Varun Gupta > committer: Varun Gupta > timestamp: 2018-01-11 01:51:23 +0530 > message: > > MDEV-6707: Wrong result (extra row) with group by, multi-part key > > Range optimizer creates ranges according to the where conditions. So this case involves using a composite key, > few parts of which are involved in GROUP BY and few in the MIN/MAX functions in the select list. > So during the execution of such queries, we try to find a prefix using the fields involved in GROUP BY > and we then check if this newly returned prefix lies within the range we had calculated earlier. > After we find a row that satisfies the partial range(involving fields of GROUP BY), then we look at the other fields > of the where clause. We look in all the ranges if we can satisfy the above retured row. > > The issue is while calculating these prefixes we might encounter same partial ranges for multiple ranges. > An example would be > where condition is f2 ='c' AND f1 <> 9 > so the ranges would be > (NULL,c <= f1,f2 <= c,9) > (c,9 <= f1,f2 <= c,+infinity) > > In this case for calculating prefixes with the group by field we take up the > partial ranges involving field f2 those would be > c<= f2 <=c > c<= f2 <=c > So we lookup rows with the same prefix in all such ranges and then we check for the other > part(in this case f1) in all the ranges. So lets take a row of the table to be (4,'c'), that would have > a prefix 'c' and value for f1 to be 4, so it would lie in the first range mentioned above. But we would > get this same record again when we read the partial range and this would again satisfy the first range. > This issue can be fixed if we compare such partial ranges and don't lookup if we see the same prefix again. This is a huge and long comment. Please make it smaller, and move at least part of it into the code. Good places would be: - The function comment for QUICK_RANGE_SELECT::get_next_prefix - the new lines in QUICK_RANGE_SELECT::get_next_prefix which use/update the value of save_last_range > > --- > mysql-test/r/range.result | 14 ++++++++++++++ > mysql-test/r/range_mrr_icp.result | 14 ++++++++++++++ > mysql-test/t/range.test | 13 +++++++++++++ > sql/opt_range.cc | 20 +++++++++++++++++--- > sql/opt_range.h | 6 ++++-- > 5 files changed, 62 insertions(+), 5 deletions(-) > > diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result > index 630a692cef6..72b31de4df4 100644 > --- a/mysql-test/r/range.result > +++ b/mysql-test/r/range.result > @@ -2144,3 +2144,17 @@ value1 1000685 12345 > value1 1003560 12345 > value1 1004807 12345 > drop table t1; > +# > +# MDEV-6707: Wrong result (extra row) with group by, multi-part key > +# > +CREATE TABLE t1 (f1 INT, f2 VARCHAR(1), KEY(f2,f1)) ENGINE=InnoDB; > +INSERT INTO t1 VALUES > +(7,'v'),(0,'s'),(9,'l'),(4,'c'); > +explain > +SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range f2 f2 9 NULL 2 Using where; Using index for group-by > +SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2; > +MAX(f1) f2 > +4 c > +DROP TABLE t1; > diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result > index 3f5de5b0189..1050bfcd887 100644 > --- a/mysql-test/r/range_mrr_icp.result > +++ b/mysql-test/r/range_mrr_icp.result > @@ -2146,4 +2146,18 @@ value1 1000685 12345 > value1 1003560 12345 > value1 1004807 12345 > drop table t1; > +# > +# MDEV-6707: Wrong result (extra row) with group by, multi-part key > +# > +CREATE TABLE t1 (f1 INT, f2 VARCHAR(1), KEY(f2,f1)) ENGINE=InnoDB; > +INSERT INTO t1 VALUES > +(7,'v'),(0,'s'),(9,'l'),(4,'c'); > +explain > +SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE t1 range f2 f2 9 NULL 2 Using where; Using index for group-by > +SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2; > +MAX(f1) f2 > +4 c > +DROP TABLE t1; > set optimizer_switch=@mrr_icp_extra_tmp; > diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test > index 393ca68e945..62e907b7b4a 100644 > --- a/mysql-test/t/range.test > +++ b/mysql-test/t/range.test > @@ -1718,3 +1718,16 @@ where (key1varchar='value1' AND (key2int <=1 OR key2int > 1)); > --echo # The following must show col1=12345 for all rows: > select * from t1; > drop table t1; > + > +--echo # > +--echo # MDEV-6707: Wrong result (extra row) with group by, multi-part key > +--echo # > + > +CREATE TABLE t1 (f1 INT, f2 VARCHAR(1), KEY(f2,f1)) ENGINE=InnoDB; > +INSERT INTO t1 VALUES > +(7,'v'),(0,'s'),(9,'l'),(4,'c'); > + > +explain > +SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2; > +SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2; > +DROP TABLE t1; > diff --git a/sql/opt_range.cc b/sql/opt_range.cc > index 25a9e729a8b..e985aba1140 100644 > --- a/sql/opt_range.cc > +++ b/sql/opt_range.cc > @@ -11249,6 +11249,7 @@ int QUICK_RANGE_SELECT::get_next() > @param prefix_length length of cur_prefix > @param group_key_parts The number of key parts in the group prefix > @param cur_prefix prefix of a key to be searched for > + @save_last_range Saving the last range we encountered You've meant "@param save_last_range" ? Also please use " INOUT " to indicate this parameter is used to pass info both from the caller and to the caller. > > Each subsequent call to the method retrieves the first record that has a > prefix with length prefix_length and which is different from cur_prefix, > @@ -11271,7 +11272,8 @@ int QUICK_RANGE_SELECT::get_next() > > int QUICK_RANGE_SELECT::get_next_prefix(uint prefix_length, > uint group_key_parts, > - uchar *cur_prefix) > + uchar *cur_prefix, > + QUICK_RANGE **save_last_range) > { > DBUG_ENTER("QUICK_RANGE_SELECT::get_next_prefix"); > const key_part_map keypart_map= make_prev_keypart_map(group_key_parts); > @@ -11301,8 +11303,19 @@ int QUICK_RANGE_SELECT::get_next_prefix(uint prefix_length, > last_range= 0; > DBUG_RETURN(HA_ERR_END_OF_FILE); > } > + > last_range= *(cur_range++); > > + if (*save_last_range) > + { > + if (!key_tuple_cmp(key_part_info, (*save_last_range)->min_key, > + last_range->min_key, prefix_length)) > + { > + last_range=NULL; > + continue; > + } > + } > + *save_last_range= last_range; > key_range start_key, end_key; > last_range->make_min_endpoint(&start_key, prefix_length, keypart_map); > last_range->make_max_endpoint(&end_key, prefix_length, keypart_map); > @@ -13315,7 +13328,7 @@ QUICK_GROUP_MIN_MAX_SELECT(TABLE *table, JOIN *join_arg, bool have_min_arg, > seen_first_key(FALSE), doing_key_read(FALSE), min_max_arg_part(min_max_arg_part_arg), > key_infix(key_infix_arg), key_infix_len(key_infix_len_arg), > min_functions_it(NULL), max_functions_it(NULL), > - is_index_scan(is_index_scan_arg) > + is_index_scan(is_index_scan_arg), save_last_range(NULL) > { > head= table; > index= use_index; > @@ -13968,7 +13981,8 @@ int QUICK_GROUP_MIN_MAX_SELECT::next_prefix() > uchar *cur_prefix= seen_first_key ? group_prefix : NULL; > if ((result= quick_prefix_select->get_next_prefix(group_prefix_len, > group_key_parts, > - cur_prefix))) > + cur_prefix, > + &save_last_range))) > DBUG_RETURN(result); > seen_first_key= TRUE; > } > diff --git a/sql/opt_range.h b/sql/opt_range.h > index b8b46ae5ab1..f4fb0642590 100644 > --- a/sql/opt_range.h > +++ b/sql/opt_range.h > @@ -455,6 +455,7 @@ class QUICK_RANGE_SELECT : public QUICK_SELECT_I > QUICK_RANGE **cur_range; /* current element in ranges */ > > QUICK_RANGE *last_range; > + QUICK_RANGE *save_last_range; So, now both QUICK_RANGE_SELECT and QUICK_GROUP_MIN_MAX_SELECT have 'save_last_range'. Does QUICK_RANGE_SELECT need one? > > KEY_PART *key_parts; > KEY_PART_INFO *key_part_info; > @@ -477,7 +478,7 @@ class QUICK_RANGE_SELECT : public QUICK_SELECT_I > int get_next(); > void range_end(); > int get_next_prefix(uint prefix_length, uint group_key_parts, > - uchar *cur_prefix); > + uchar *cur_prefix, QUICK_RANGE **save_last_range); > bool reverse_sorted() { return 0; } > bool unique_key_range(); > int init_ror_merged_scan(bool reuse_handler, MEM_ROOT *alloc); > @@ -916,7 +917,8 @@ class QUICK_GROUP_MIN_MAX_SELECT : public QUICK_SELECT_I > Use index scan to get the next different key instead of jumping into it > through index read > */ > - bool is_index_scan; > + bool is_index_scan; > + QUICK_RANGE *save_last_range; > public: > /* > The following two members are public to allow easy access from BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] 6ffd535e415: MDEV-14229: Stack trace is not resolved for shared objects
by Sergei Golubchik 18 Jan '18

18 Jan '18

Hi, Vicentiu! Looks mostly ok. Does it mean, it will replace my changes in 10.0+ related to calculating the offset? struct link_map *lm = (struct link_map*) dlopen(0, RTLD_NOW); etc As far as LIBDL and dependencies are concerned... 1. Don't add LIBDL to the link list. The server has it and clients should not link with my_addr_resove (and it's good to get a linker error, if by some mistake they do) 2. Check for dladdr in configure.cmake: CHECK_FUNCTION_EXISTS (dladdr HAVE_DLADDR) 3. handle dladdr in my_global.h (search for dlopen or dlerror) But, I think, you won't need any ifdefs in my_addr_resolve.c, because my_global.h will take care of that. Ok to push after that, thanks! On Jan 16, vicentiu(a)mariadb.org wrote: > revision-id: 6ffd535e415028f414b9d1c9e9efbfb81bf25798 (mariadb-5.5.58-42-g6ffd535e415) > parent(s): 6267be460ab5147e3bc0fffd03e690b3650f2fe2 > author: Vicențiu Ciorbaru > committer: Vicențiu Ciorbaru > timestamp: 2018-01-16 22:57:52 +0200 > message: > > MDEV-14229: Stack trace is not resolved for shared objects > > Resolving a stacktrace including functions in dynamic libraries requires > us to look inside the libraries for the symbols. Addr2line needs to be > started with the correct binary for each address on the stack. To do this, > figure out which library it is using dladdr, then if the addr2line > binary was started with a different binary, fork it again with the > correct one. > > We only have one addr2line process running at any point during the > stacktrace resolving step. The maximum number of forks for addr2line should > generally be around 6. > > One for server stacktrace code, one for plugin code, one when going back > into server code, one for pthread library, one for libc, one for the > _start function in the server. More can come up if plugin calls server > function which goes back to a plugin, etc. > > --- > mysys/CMakeLists.txt | 2 +- > mysys/my_addr_resolve.c | 97 +++++++++++++++++++++++++++++++++---------------- > 2 files changed, 67 insertions(+), 32 deletions(-) > > diff --git a/mysys/CMakeLists.txt b/mysys/CMakeLists.txt > index cb86850c2de..15f3fbad9bf 100644 > --- a/mysys/CMakeLists.txt > +++ b/mysys/CMakeLists.txt > @@ -67,7 +67,7 @@ ENDIF() > > ADD_CONVENIENCE_LIBRARY(mysys ${MYSYS_SOURCES}) > TARGET_LINK_LIBRARIES(mysys dbug strings ${ZLIB_LIBRARY} > - ${LIBNSL} ${LIBM} ${LIBRT} ${LIBSOCKET} ${LIBEXECINFO}) > + ${LIBNSL} ${LIBM} ${LIBRT} ${LIBSOCKET} ${LIBEXECINFO} ${LIBDL}) > DTRACE_INSTRUMENT(mysys) > > IF(HAVE_BFD_H) > diff --git a/mysys/my_addr_resolve.c b/mysys/my_addr_resolve.c > index 90e6f43f390..f831ad5121f 100644 > --- a/mysys/my_addr_resolve.c > +++ b/mysys/my_addr_resolve.c > @@ -132,15 +132,79 @@ const char *my_addr_resolve_init() > > #include <m_string.h> > #include <ctype.h> > + > +#include <sys/wait.h> > + > static int in[2], out[2]; > -static int initialized= 0; > +static pid_t pid; > +static char addr2line_binary[1024]; > static char output[1024]; > + > +int start_addr2line_fork(const char *binary_path) > +{ > + > + if (pid > 0) > + { > + /* Don't leak FDs */ > + close(in[1]); > + close(out[0]); > + /* Don't create zombie processes. */ > + waitpid(pid, NULL, 0); > + } > + > + if (pipe(in) < 0) > + return 1; > + if (pipe(out) < 0) > + return 1; > + > + pid = fork(); > + if (pid == -1) > + return 1; > + > + if (!pid) /* child */ > + { > + dup2(in[0], 0); > + dup2(out[1], 1); > + close(in[0]); > + close(in[1]); > + close(out[0]); > + close(out[1]); > + execlp("addr2line", "addr2line", "-C", "-f", "-e", binary_path, NULL); > + exit(1); > + } > + > + close(in[0]); > + close(out[1]); > + > + return 0; > +} > + > int my_addr_resolve(void *ptr, my_addr_loc *loc) > { > char input[32], *s; > size_t len; > > - len= my_snprintf(input, sizeof(input), "%p\n", ptr); > + Dl_info info; > + void *offset; > + > + if (!dladdr(ptr, &info)) > + return 1; > + > + if (strcmp(addr2line_binary, info.dli_fname)) > + { > + /* We use dli_fname in case the path is longer than the length of our static > + string. We don't want to allocate anything dynamicaly here as we are in > + a "crashed" state. */ > + if (start_addr2line_fork(info.dli_fname)) > + { > + addr2line_binary[0] = '\0'; > + return 1; > + } > + /* Save result for future comparisons. */ > + strnmov(addr2line_binary, info.dli_fname, sizeof(addr2line_binary)); > + } > + offset = info.dli_fbase; > + len= my_snprintf(input, sizeof(input), "%p\n", ptr - offset); > if (write(in[1], input, len) <= 0) > return 1; > if (read(out[0], output, sizeof(output)) <= 0) > @@ -168,35 +232,6 @@ int my_addr_resolve(void *ptr, my_addr_loc *loc) > > const char *my_addr_resolve_init() > { > - if (!initialized) > - { > - pid_t pid; > - > - if (pipe(in) < 0) > - return "pipe(in)"; > - if (pipe(out) < 0) > - return "pipe(out)"; > - > - pid = fork(); > - if (pid == -1) > - return "fork"; > - > - if (!pid) /* child */ > - { > - dup2(in[0], 0); > - dup2(out[1], 1); > - close(in[0]); > - close(in[1]); > - close(out[0]); > - close(out[1]); > - execlp("addr2line", "addr2line", "-C", "-f", "-e", my_progname, NULL); > - exit(1); > - } > - > - close(in[0]); > - close(out[1]); > - initialized= 1; > - } > return 0; > } > #endif Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] a doubt about locks on supremum
by hongxiang jiang 18 Jan '18

18 Jan '18

2 1

Re: [Maria-developers] [Commits] 4b4267288cb: MDEV-14743: Server crashes in Item_func_match::init_search
by Sergei Golubchik 12 Jan '18

12 Jan '18

Hi, Oleksandr! Two thoughts. 1. Please use if (!ifm->fixed) then you'll only need one #ifdef. 2. How does it work with the iterator? if you remove the current element from the list and then iterator with jump to the next one, won't it cause one element to be skipped? On Jan 10, Oleksandr Byelkin wrote: > revision-id: 4b4267288cb2668717cc70537eb7e79cecaff58a (mariadb-5.5.58-24-g4b4267288cb) > parent(s): a408e881cf73d06fc92097fce6ef9584e16edf77 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2018-01-10 12:22:56 +0100 > message: > > MDEV-14743: Server crashes in Item_func_match::init_search > > Remove non prepared (and so belonging to removed clauses FT functions) from the list. > > in later version it will be fixed by building the list during preparation. > > diff --git a/sql/sql_base.cc b/sql/sql_base.cc > index c06c4fcff29..26e302ded35 100644 > --- a/sql/sql_base.cc > +++ b/sql/sql_base.cc > @@ -9550,7 +9550,19 @@ int init_ftfuncs(THD *thd, SELECT_LEX *select_lex, bool no_order) > DBUG_PRINT("info",("Performing FULLTEXT search")); > > while ((ifm=li++)) > - ifm->init_search(no_order); > +#if MYSQL_VERSION_ID < 100213 > + if (ifm->fixed) > +#endif > + ifm->init_search(no_order); > +#if MYSQL_VERSION_ID < 100213 > + else > + /* > + it mean that clause where was FT function was removed, so we have > + to remove the function from the list. > + */ > + li.remove(); > +#endif > + > } > return 0; > } Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 1

[Maria-developers] Eliminating or replacing the InnoDB row_prebuilt_t::fetch_cache
by Marko Mäkelä 12 Jan '18

12 Jan '18

The handler API in MySQL and MariaDB is operating on a single record at a time. There are some ‘batched’ primitives that in my understanding were developed for the NDB cluster, but generally InnoDB does not use them. The only nontrivial thing that InnoDB does during reads is the Index Condition Pushdown (ICP) and the end-of-range detection (DsMrr) that was ported from the never-released MySQL 6.0 or 5.2 to MySQL 5.6. To improve the performance of range scans, InnoDB uses a caching mechanism to prefetch results. Starting with the 4th consecutive read on the same ha_innobase handle, InnoDB would prefetch 8 records into row_prebuilt_t::fetch_cache. There are several drawbacks of this: - The parameters of this cache are hard-coded, and these caches can make wrong guesses (for SELECT * FROM t LIMIT 5, it would unnecessarily read 8 records, possibly causing unnecessary page accesses) - The caches waste memory, especially on partitioned tables, where each partition handle would have its own cache. (The main benefit of ha_innopart or InnoDB native partitioning in MySQL 5.7 is that the fetch_cache of the table is shared between partitions. It is only a benefit of saving memory, because the cache will be emptied when switching partitions.) As far as I understand, the entire purpose of the prebuilt->fetch_cache is to reduce the cost of acquiring the page latch and repositioning the cursor on each read request. If we did not call mtr_t::commit() between each request, maybe we could remove this cache altogether. When I was in the InnoDB team at MySQL, I did bring up a few times the idea of eliminating the fetch_cache, but the optimizer developers were in a separate team, and to do this, we would have needed support from both managers. I am hoping that with MariaDB this would merely be a technical challenge to first write a proof-of-concept prototype, and then to polish and test it. When I discussed this with the late Olav Sandstå some years ago, there was some thinking that we should stop hard-coding the TABLE::record[0] in various parts of the code, and the storage engine should be able to return multiple records to a larger TABLE::record[] buffer. I wonder if a simpler approach would work, and if we could do without any prefetching: 1. Remove row_sel_dequeue_cached_row_for_mysql(), prebuilt->fetch_cache, and related code. 2. Add prebuilt->mtr and let it be reused across multiple subsequent calls to row_search_mvcc(), until the query executor stops the current read batch. 3. A call to btr_pcur_store_position() and mtr_t::commit() will be necessary before any potentially long delay, such as when the result is being sent to a client connection, or when the query executor is going to switch to read records from another table. 4. The "end of read batch" could be signalled by invoking handler::extra() with a parameter. With such changes, less copying would take place, and there should be less contention on dict_index_t::lock and the upper-level buf_block_t::lock, due to fewer calls to btr_pcur_restore_position(). While that function does have a fast path ("optimistic restore"), it cannot switch to the next page without a key lookup. Range scans would be more efficient if we can always simply advance to the right leaf page and release the lock on the left page. I would love to do this in InnoDB. But I think that the effort should be lead from the optimizer side. Someone should first implement the "end of batched read" calls and some DBUG_ASSERT() that would catch any wrong-doing. That is, whenever we start to send data to a client or start to write into a temporary table, or switch to read records from another cursor, we must assert that the "end of batched read" flag is set on all cursor handles. Similarly, at the end and start of a statement, and at commit or rollback, all cursor handles must be at "end of batched read". Who would take the challenge? Best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation DON’T MISS M|18 MariaDB User Conference February 26 - 27, 2018 New York City https://m18.mariadb.com/

1 0

[Maria-developers] Check constraints
by Martijn Tonies (Upscene Productions) 10 Jan '18

10 Jan '18

Hi, Where can I find meta information for check constraints on tables? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

3 4

[Maria-developers] subscribe
by Martijn Tonies (Upscene Productions) 09 Jan '18

09 Jan '18

subscribe With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

1 0

Re: [Maria-developers] [Commits] fbe0fc2: Fixed mdev-14879 Lost rows for query using recursive CTE
by Oleksandr Byelkin 09 Jan '18

09 Jan '18

Hi, Igor! Am 06.01.2018 um 18:32 schrieb IgorBabaev: > revision-id: fbe0fc2d87cb1bdb9f7040d44ad3c58a94857e20 (mariadb-10.2.12-14-gfbe0fc2) > parent(s): 15b1840f43be8c660382458826e9d5a47d148a67 > author: Igor Babaev > committer: Igor Babaev > timestamp: 2018-01-06 09:32:47 -0800 > message: > > Fixed mdev-14879 Lost rows for query using recursive CTE > with recursive reference in subquery > > If a recursive CTE uses a subquery with recursive reference then > the virtual function reset() must be called after each iteration > performed at the execution of the CTE. OK to push. [skip]

1 0

Re: [Maria-developers] [Commits] 81965f0: Fixed mdev-14852 Fails to reopen temp table within standard CTE
by Oleksandr Byelkin 05 Jan '18

05 Jan '18

Am 05.01.2018 um 08:40 schrieb IgorBabaev: > revision-id: 81965f03065b84322f2d03d784893179e8b79fd9 (mariadb-10.2.12-6-g81965f0) > parent(s): 0f253d3e644d3027face26371eb1bb59e8280728 > author: Igor Babaev > committer: Igor Babaev > timestamp: 2018-01-04 23:40:37 -0800 > message: > > Fixed mdev-14852 Fails to reopen temp table within standard CTE > > If the specification of a CTE contains a reference to a temporary table > then THD::open_temporary_table() must be called for this reference for > any occurrence of the CTE in the query. By mistake this was done only > for the first occurrences of CTEs. > The patch fixes this problem in With_element::clone_parsed_spec(). > It also moves there the call of check_dependencies_in_with_clauses() > to its proper place before the call of check_table_access(). > Additionally the patch optimizes the number of calls of the > function check_dependencies_in_with_clauses(). OK to push! > > --- > mysql-test/r/cte_nonrecursive.result | 30 ++++++++++++++++++++++++++++++ > mysql-test/t/cte_nonrecursive.test | 29 +++++++++++++++++++++++++++++ > sql/sql_cte.cc | 7 +++++++ > sql/sql_parse.cc | 22 +++++----------------- > sql/sql_prepare.cc | 11 +++-------- > 5 files changed, 74 insertions(+), 25 deletions(-) > > diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result > index b1b41b1..c1f4c9f 100644 > --- a/mysql-test/r/cte_nonrecursive.result > +++ b/mysql-test/r/cte_nonrecursive.result > @@ -1265,3 +1265,33 @@ a > 4 > deallocate prepare stmt; > drop table t1; > +# > +# MDEV-14852: CTE using temporary table in query > +# with two references to the CTE > +# > +create temporary table t1 (i int); > +insert into t1 values (5),(4),(1),(2),(3); > +with > +c1 as (select i from t1), > +c2 as (select i from c1 where c1.i=2) > +select i from c1 where i > 3 union select i from c2; > +i > +5 > +4 > +2 > +drop table t1; > +create table t1 (term char(10)); > +create temporary table t2 (term char(10)); > +insert into t1 values ('TERM01'),('TERM02'),('TERM03'); > +insert into t2 values ('TERM02'),('TERM03'),('TERM04'); > +with c1 as (select * from t1), c2 as (select * from t2) > +(select * from c1 left outer join c2 on c1.term = c2.term) > +union all > +(select * from c1 right outer join c2 on c1.term = c2.term > +where c1.term is null); > +term term > +TERM02 TERM02 > +TERM03 TERM03 > +TERM01 NULL > +NULL TERM04 > +drop table t1,t2; > diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test > index 1f21dbc..9436665 100644 > --- a/mysql-test/t/cte_nonrecursive.test > +++ b/mysql-test/t/cte_nonrecursive.test > @@ -853,3 +853,32 @@ execute stmt; > deallocate prepare stmt; > > drop table t1; > + > +--echo # > +--echo # MDEV-14852: CTE using temporary table in query > +--echo # with two references to the CTE > +--echo # > + > +create temporary table t1 (i int); > +insert into t1 values (5),(4),(1),(2),(3); > + > +with > +c1 as (select i from t1), > +c2 as (select i from c1 where c1.i=2) > +select i from c1 where i > 3 union select i from c2; > + > +drop table t1; > + > +create table t1 (term char(10)); > +create temporary table t2 (term char(10)); > + > +insert into t1 values ('TERM01'),('TERM02'),('TERM03'); > +insert into t2 values ('TERM02'),('TERM03'),('TERM04'); > + > +with c1 as (select * from t1), c2 as (select * from t2) > +(select * from c1 left outer join c2 on c1.term = c2.term) > +union all > +(select * from c1 right outer join c2 on c1.term = c2.term > + where c1.term is null); > + > +drop table t1,t2; > diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc > index 601c192..d12948f 100644 > --- a/sql/sql_cte.cc > +++ b/sql/sql_cte.cc > @@ -817,12 +817,19 @@ st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, > parse_status= parse_sql(thd, &parser_state, 0); > if (parse_status) > goto err; > + > + if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > + goto err; > + > spec_tables= lex->query_tables; > spec_tables_tail= 0; > for (TABLE_LIST *tbl= spec_tables; > tbl; > tbl= tbl->next_global) > { > + if (!tbl->derived && !tbl->schema_table && > + thd->open_temporary_table(tbl)) > + goto err; > spec_tables_tail= tbl; > } > if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE)) > diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc > index f00c74b..6d068eb 100644 > --- a/sql/sql_parse.cc > +++ b/sql/sql_parse.cc > @@ -2990,6 +2990,9 @@ mysql_execute_command(THD *thd) > thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); > } > > + if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) > + DBUG_RETURN(1); > + > #ifdef HAVE_REPLICATION > if (unlikely(thd->slave_thread)) > { > @@ -3446,14 +3449,6 @@ mysql_execute_command(THD *thd) > ulong privileges_requested= lex->exchange ? SELECT_ACL | FILE_ACL : > SELECT_ACL; > > - /* > - The same function must be called for DML commands > - when CTEs are supported in DML statements > - */ > - res= check_dependencies_in_with_clauses(thd->lex->with_clauses_list); > - if (res) > - break; > - > if (all_tables) > res= check_table_access(thd, > privileges_requested, > @@ -3879,8 +3874,7 @@ mysql_execute_command(THD *thd) > /* Copy temporarily the statement flags to thd for lock_table_names() */ > uint save_thd_create_info_options= thd->lex->create_info.options; > thd->lex->create_info.options|= create_info.options; > - if (!(res= check_dependencies_in_with_clauses(lex->with_clauses_list))) > - res= open_and_lock_tables(thd, create_info, lex->query_tables, TRUE, 0); > + res= open_and_lock_tables(thd, create_info, lex->query_tables, TRUE, 0); > thd->lex->create_info.options= save_thd_create_info_options; > if (res) > { > @@ -4493,8 +4487,7 @@ mysql_execute_command(THD *thd) > > unit->set_limit(select_lex); > > - if (!(res= check_dependencies_in_with_clauses(lex->with_clauses_list)) && > - !(res=open_and_lock_tables(thd, all_tables, TRUE, 0))) > + if (!(res=open_and_lock_tables(thd, all_tables, TRUE, 0))) > { > MYSQL_INSERT_SELECT_START(thd->query()); > /* > @@ -4821,9 +4814,6 @@ mysql_execute_command(THD *thd) > { > List<set_var_base> *lex_var_list= &lex->var_list; > > - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) > - goto error; > - > if ((check_table_access(thd, SELECT_ACL, all_tables, FALSE, UINT_MAX, FALSE) > || open_and_lock_tables(thd, all_tables, TRUE, 0))) > goto error; > @@ -6376,8 +6366,6 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables) > new (thd->mem_root) Item_int(thd, > (ulonglong) thd->variables.select_limit); > } > - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > - return 1; > > if (!(res= open_and_lock_tables(thd, all_tables, TRUE, 0))) > { > diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc > index be78a76..390b708 100644 > --- a/sql/sql_prepare.cc > +++ b/sql/sql_prepare.cc > @@ -1573,8 +1573,6 @@ static int mysql_test_select(Prepared_statement *stmt, > lex->select_lex.context.resolve_in_select_list= TRUE; > > ulong privilege= lex->exchange ? SELECT_ACL | FILE_ACL : SELECT_ACL; > - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > - goto error; > if (tables) > { > if (check_table_access(thd, privilege, tables, FALSE, UINT_MAX, FALSE)) > @@ -1841,9 +1839,6 @@ static bool mysql_test_create_table(Prepared_statement *stmt) > if (create_table_precheck(thd, tables, create_table)) > DBUG_RETURN(TRUE); > > - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > - DBUG_RETURN(TRUE); > - > if (select_lex->item_list.elements) > { > /* Base table and temporary table are not in the same name space. */ > @@ -2234,9 +2229,6 @@ static bool mysql_test_insert_select(Prepared_statement *stmt, > if (insert_precheck(stmt->thd, tables)) > return 1; > > - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > - return 1; > - > /* store it, because mysql_insert_select_prepare_tester change it */ > first_local_table= lex->select_lex.table_list.first; > DBUG_ASSERT(first_local_table != 0); > @@ -2339,6 +2331,9 @@ static bool check_prepared_statement(Prepared_statement *stmt) > if (tables) > thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); > > + if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) > + goto error; > + > if (sql_command_flags[sql_command] & CF_HA_CLOSE) > mysql_ha_rm_tables(thd, tables); > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0