- developers - lists.mariadb.org

[Maria-developers] Is it a big? CURSOR does not see changes made by ALTER TABLE
by Alexander Barkov 02 Mar '17

02 Mar '17

Hello Elena, all. I noticed that CURSORs in combination with ALTER TABLE work strange. Is it a known bug? Thanks. -- I create a table with two columns -- And a procedure fetching two columns from this table SET sql_mode=DEFAULT; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT, b VARCHAR(10)); INSERT INTO t1 VALUES (10,20); DROP PROCEDURE IF EXISTS p1; DELIMITER $$ CREATE PROCEDURE p1() BEGIN DECLARE a,b INT; DECLARE cur CURSOR FOR SELECT * FROM t1; OPEN cur; FETCH cur INTO a,b; CLOSE cur; SELECT a,b; END; $$ DELIMITER ; CALL p1(); -- It returns -- +------+------+ -- | a | b | -- +------+------+ -- | 10 | 20 | -- +------+------+ -- So far so good -- Now I add a new column to the table and call the procedure again ALTER TABLE t1 ADD c INT; CALL p1(); -- Woops. It should have failed, because there are now 3 columns in the table -- But it returns the result, like if the table still had 2 columns.

1 1

Re: [Maria-developers] [svoj@mariadb.org: [Commits] 02d155a: MDEV-11418 - AliSQL: [Feature] Issue#1 KILL IDLE TRANSACTIONS]
by Michael Widenius 01 Mar '17

01 Mar '17

Hi! On Tue, Feb 21, 2017 at 2:37 PM, Sergey Vojtovich <svoj(a)mariadb.org> wrote: > Hi Monty, > > Please review this patch. > > Thanks, > Sergey > > ----- Forwarded message from Sergey Vojtovich <svoj(a)mariadb.org> ----- > > Date: Tue, 21 Feb 2017 16:29:36 +0400 > From: Sergey Vojtovich <svoj(a)mariadb.org> > To: commits(a)mariadb.org > Subject: [Commits] 02d155a: MDEV-11418 - AliSQL: [Feature] Issue#1 KILL IDLE TRANSACTIONS > > revision-id: 02d155aeec87aac8a827a29328d68ecb156094cc (mariadb-10.2.2-162-g02d155a) > parent(s): 1563354570c5634cb2b448b47a138b4ceaff1a72 > committer: Sergey Vojtovich > timestamp: 2017-02-21 16:28:42 +0400 > message: > > MDEV-11418 - AliSQL: [Feature] Issue#1 KILL IDLE TRANSACTIONS > > Terminate idle transactions safely in server layer by setting up socket timeout > parameter. Percona provides another patch to resolve similar problem, but it > calls server layer's callback in InnoDB plugin to close THD, which crashes in > some testcases. See https://bugs.launchpad.net/percona-server/+bug/901060 for > more detailed information. > > 1. export parameter trx_idle_timeout to handle all kinds of transactions, the priority is highest > 2. export parameter trx_readonly_idle_timeout to handle read-only transactions > 3. export parameter trx_changes_idle_timeout to handle read-write transactions <cut> > +BEGIN; > +INSERT INTO t1 VALUES (4),(5),(6); > +SELECT * FROM t1; > +ERROR HY000: MySQL server has gone away It's not optimal to get this kind of error for a timeout as it's make it's very hard for the application to know what is going on. An alternative approach would be to instead of closing the connection do a rollback and for next query send an error "transactions aborted and rolled back because of transaction timeout" What do you think about this approach? To get this done, we should remember in 'net' that we are using a transaction timeout (not default) and when we get the timeout we should do: - Do part of the rollback code + unlock tables in THD::cleanup() (Probably move to a sub function) - Remember that we did a timeout rollback. - When we got next statement and timout_rollback was done, give an error, clear flag and wait for next statement. > +++ b/sql/sql_parse.cc > @@ -1209,7 +1209,23 @@ bool do_command(THD *thd) > number of seconds has passed. > */ > if(!thd->skip_wait_timeout) > - my_net_set_read_timeout(net, thd->variables.net_wait_timeout); > + { > + if (thd->in_active_multi_stmt_transaction()) > + { > + bool is_trx_rw= thd->transaction.all.is_trx_read_write(); > + > + if (thd->variables.trx_idle_timeout > 0) > + my_net_set_read_timeout(net, thd->variables.trx_idle_timeout); > + else if (thd->variables.trx_readonly_idle_timeout > 0 && !is_trx_rw) > + my_net_set_read_timeout(net, thd->variables.trx_readonly_idle_timeout); > + else if (thd->variables.trx_changes_idle_timeout > 0 && is_trx_rw) > + my_net_set_read_timeout(net, thd->variables.trx_changes_idle_timeout); > + else > + my_net_set_read_timeout(net, thd->variables.net_wait_timeout); > + } > + else > + my_net_set_read_timeout(net, thd->variables.net_wait_timeout); > + } Please do it this way instead (less code): if (!thd->skip_wait_timeout) { ulong timeout= thd->variables.net_wait_timeout; if (thd->in_active_multi_stmt_transaction()) { bool is_trx_rw= thd->transaction.all.is_trx_read_write(); if (thd->variables.trx_idle_timeout > 0) timeout= thd->variables.trx_idle_timeout; else if (thd->variables.trx_readonly_idle_timeout > 0 && !is_trx_rw) timeout= thd->variables.trx_readonly_idle_timeout; else if (thd->variables.trx_changes_idle_timeout > 0 && is_trx_rw) timeout= thd->variables.trx_changes_idle_timeout; } my_net_set_read_timeout(net, timeout); } Regards, Monty

1 0

Re: [Maria-developers] [Commits] 00c30e5: MDEV-11645: archive.archive fails in buildbot with valgrind (Use of uninitialised value)
by Sergey Petrunia 01 Mar '17

01 Mar '17

Hi Varun, Please * fix the commit comment * add a comment * fix the coding style as indicated below. (and also please conform to the coding style in the future. If its requirements are not clear, lets sit down once and discuss them) On Wed, Mar 01, 2017 at 07:50:01AM +0530, Varun wrote: > revision-id: 00c30e5a234fbd01484cd069c01e97c075025323 (mariadb-10.2.3-283-g00c30e5) > parent(s): b13cee83f9cfcffa98d227c492411a9acec85f42 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2017-03-01 07:46:26 +0530 > message: > > MDEV-11645: archive.archive fails in buildbot with valgrind (Use of uninitialised value) > > Set null values to record_buffer->buffer for the VARCHAR fields What "NULL values"?? The fix is filling with zeros the space beyond the end of VARCHAR values. VARCHAR NULLs are actually ok already as NULL value has its buffer filled with zeros. Please come up with a comment that describes the ix. > > --- > storage/archive/ha_archive.cc | 15 +++++++++++++++ > 1 file changed, 15 insertions(+) > > diff --git a/storage/archive/ha_archive.cc b/storage/archive/ha_archive.cc > index 9d6d100..5599b43 100644 > --- a/storage/archive/ha_archive.cc > +++ b/storage/archive/ha_archive.cc > @@ -372,10 +372,25 @@ int Archive_share::write_v1_metafile() > > unsigned int ha_archive::pack_row_v1(uchar *record) > { > + uint offset; > uint *blob, *end; > uchar *pos; > DBUG_ENTER("pack_row_v1"); > memcpy(record_buffer->buffer, record, table->s->reclength); Please add here a comment about what this loop is doing. > + for(Field** field= table->field; (*field) ; field++) space needed in 'for (' > + { > + Field *fld= *field; > + if (fld->type() == MYSQL_TYPE_VARCHAR) > + { > + if (!(fld->is_real_null(record - table->record[0]))) > + { > + ptrdiff_t start= (fld->ptr - table->record[0]); > + Field_varstring *const field_var= (Field_varstring *)fld; > + offset= field_var->data_length() + field_var->length_size(); > + bzero(record_buffer->buffer + start + offset, fld->field_length - offset + 1); The above line is longer than 80 chars. should be broken into: bzero(record_buffer->buffer + start + offset, fld->field_length - offset + 1); > + } > + } > + } > pos= record_buffer->buffer + table->s->reclength; > for (blob= table->s->blob_field, end= blob + table->s->blob_fields; > blob != end; blob++) -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] e8ca684: MDEV-11221 - main.events_restart failed in bb
by Sergei Golubchik 28 Feb '17

28 Feb '17

Hi, Sergey! Thanks! Ok to push! On Feb 28, Sergey Vojtovich wrote: > revision-id: e8ca684c6d5ce14eb021b4b691be50a8bf9c8281 (mariadb-10.0.29-38-ge8ca684) > parent(s): b54566d73bb96ef9eb7d734742d3743fb3369a40 > committer: Sergey Vojtovich > timestamp: 2017-02-28 15:23:44 +0400 > message: > > MDEV-11221 - main.events_restart failed in bb > > Applied lost in a merge revision: next time, please specify a commit you're applying. Like Applied commit 7f38a070baa that was lost in a merge: > MDEV-10043 - main.events_restart fails sporadically in buildbot (crashes upon > shutdown) > > There was race condition between shutdown thread and event worker threads. > > Shutdown thread waits for thread_count to become 0 in close_connections(). It > may happen so that event worker thread was started but didn't increment > thread_count by this time. In this case shutdown thread may miss wait for this > working thread and continue deinitialization. Worker thread in turn may continue > execution and crash on deinitialized data. > > Fixed by incrementing thread_count before thread is actually created like it is > done for connection threads. > > Also let event scheduler not to inc/dec running threads counter for symmetry > with other "service" threads. > Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] [Commits] b13cee8: MDEV-11078: NULL NOT IN (non-empty subquery) should never return results
by Sergei Golubchik 27 Feb '17

27 Feb '17

Hi, Varun! Really, copy-pasted *8* times? Why didn't you make it a method of Item_in_subselect? On Feb 27, Varun wrote: > revision-id: b13cee83f9cfcffa98d227c492411a9acec85f42 (mariadb-10.2.3-282-gb13cee8) > parent(s): b513e3711743e3ff24b3d14794a0fba87fd18c31 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2017-02-27 22:10:35 +0530 > message: > > MDEV-11078: NULL NOT IN (non-empty subquery) should never return results > > Setting the value of cond guard variables during the creation of Tricond Item for null values > > --- > mysql-test/r/subselect4.result | 34 +++++++++++++++++++++++++++++ > mysql-test/t/subselect4.test | 20 +++++++++++++++++ > sql/item_subselect.cc | 49 ++++++++++++++++++++++++++++++++++++++++++ > 3 files changed, 103 insertions(+) > > diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc > index 94bc71c..acdbabd 100644 > --- a/sql/item_subselect.cc > +++ b/sql/item_subselect.cc > @@ -2113,6 +2113,13 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > We can encounter "NULL IN (SELECT ...)". Wrap the added condition > within a trig_cond. > */ > + if(left_expr->basic_const_item() && !left_expr->is_expensive()) > + { > + left_expr->is_null(); > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + } > + > item= new (thd->mem_root) Item_func_trig_cond(thd, item, get_cond_guard(0)); > } > > @@ -2137,6 +2144,12 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > having= new (thd->mem_root) Item_is_not_null_test(thd, this, having); > if (left_expr->maybe_null) > { > + if(left_expr->basic_const_item() && !left_expr->is_expensive()) > + { > + left_expr->is_null(); > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + } > if (!(having= new (thd->mem_root) Item_func_trig_cond(thd, having, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2155,6 +2168,12 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > */ > if (!abort_on_null && left_expr->maybe_null) > { > + if(left_expr->basic_const_item() && !left_expr->is_expensive()) > + { > + left_expr->is_null(); > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + } > if (!(item= new (thd->mem_root) Item_func_trig_cond(thd, item, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2184,6 +2203,12 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > (char *)"<result>")); > if (!abort_on_null && left_expr->maybe_null) > { > + if(left_expr->basic_const_item() && !left_expr->is_expensive()) > + { > + left_expr->is_null(); > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + } > if (!(new_having= new (thd->mem_root) Item_func_trig_cond(thd, new_having, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2383,6 +2408,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, > Item_cond_or(thd, item_eq, item_isnull); > if (!abort_on_null && left_expr->element_index(i)->maybe_null) > { > + if(left_expr->element_index(i)->basic_const_item() > + && !left_expr->element_index(i)->is_expensive()) > + { > + if(left_expr->element_index(i)->is_null()) > + set_cond_guard_var(i,FALSE); > + } > if (!(col_item= new (thd->mem_root) > Item_func_trig_cond(thd, col_item, get_cond_guard(i)))) > DBUG_RETURN(true); > @@ -2400,6 +2431,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, > (char *)"<list ref>")); > if (!abort_on_null && left_expr->element_index(i)->maybe_null) > { > + if(left_expr->element_index(i)->basic_const_item() > + && !left_expr->element_index(i)->is_expensive()) > + { > + if(left_expr->element_index(i)->is_null()) > + set_cond_guard_var(i,FALSE); > + } > if (!(item_nnull_test= > new (thd->mem_root) > Item_func_trig_cond(thd, item_nnull_test, get_cond_guard(i)))) > @@ -2460,6 +2497,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, > item= new (thd->mem_root) Item_cond_or(thd, item, item_isnull); > if (left_expr->element_index(i)->maybe_null) > { > + if(left_expr->element_index(i)->basic_const_item() > + && !left_expr->element_index(i)->is_expensive()) > + { > + if(left_expr->element_index(i)->is_null()) > + set_cond_guard_var(i,FALSE); > + } > if (!(item= new (thd->mem_root) > Item_func_trig_cond(thd, item, get_cond_guard(i)))) > DBUG_RETURN(true); > @@ -2471,6 +2514,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, > } > if (!abort_on_null && left_expr->element_index(i)->maybe_null) > { > + if(left_expr->element_index(i)->basic_const_item() > + && !left_expr->element_index(i)->is_expensive()) > + { > + if(left_expr->element_index(i)->is_null()) > + set_cond_guard_var(i,FALSE); > + } > if (!(item= new (thd->mem_root) > Item_func_trig_cond(thd, item, get_cond_guard(i)))) > DBUG_RETURN(true); Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] On constructing ref access from mis-matched charset comparisons
by Sergey Petrunia 27 Feb '17

27 Feb '17

Hi Alexander, I've got a question about mis-matched charset comparisons and ref access method. == Short form == I know that VARCHAR comparisons over mis-matching charsets cannot be used for constructing index lookups into the index over the "narrower" character set column. But is this a real limitation or just an optimizer deficiency? If it is the latter, do people hit it in the real world sufficiently often to warrant lifting it? == Long form == === Example dataset === create table t0 ( a varchar(32) character set utf8 collate utf8_general_ci ); create table t1 ( a varchar(32) character set latin1 collate latin1_swedish_ci, col1 varchar(32), key(a) ); create table t2 ( a varchar(32) character set utf8 collate utf8_general_ci, col1 varchar(32), key(a) ); insert into t0 select a from ten; insert into t1 select a,a from one_k; insert into t2 select a,a from one_k; === Queries === MariaDB [test]> explain -> select * from t0, t2 where t0.a=t2.a; +------+-------------+-------+------+---------------+------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+-----------+------+-------------+ | 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 1 | SIMPLE | t2 | ref | a | a | 99 | test.t0.a | 1 | | +------+-------------+-------+------+---------------+------+---------+-----------+------+-------------+ Ok, good. MariaDB [test]> explain -> select * from t0, t1 where t0.a=t1.a; +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ 2 rows in set (0.00 sec) Oops, the index t1(a) is not used. FORMAT=JSON shows the reason: "attached_condition": "(t0.a = convert(t1.a using utf8))" Still it feels like the optimizer could * Try to convert the value of t0.a into latin1. * If it can't be represented in latin1, then we know that no row in t1 has t1.a=t0.a. * make a lookup on t1.a= convert(t0.a using latin1) , using t1.a's collation rules. Is this true [for some charsets] ? If yes, is it worth doing? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

3 2

[Maria-developers] bb-10.2-compatibility : delete compatibility
by jerome brauge 27 Feb '17

27 Feb '17

Hello Alexander, Can you add a task for delete statement with same table as source and target in sql_mode=oracle Example : create or replace table t1 (c1 int, c2 int); delete from t1 where c1 in (select b.c1 from t1 b where b.c2=0); Failed with : Error: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data SQLState: HY000 ErrorCode: 1093 Regards, Jérôme.

2 1

Re: [Maria-developers] [Commits] c6d172f: MDEV-11078: NULL NOT IN (non-empty subquery) should never return results
by Sergey Petrunia 27 Feb '17

27 Feb '17

Hi Varun, Things that look apparently wrong: 1. The patch checks left_expr->null_value without first having made a call that will fill that member. The calling convention is something like this: item->val_XXX() // val_str or val_int() // check item->null_value. If one doesn't need string or integer value, there is item->is_null() call. 2. The patch checks null_value for any kind of left_expr. This is wrong. For example, consider a query: select t1.col IN (select t2.col FROM t2 ) from t1 Here, it doesn't make any sense to check {t1.col}->null_value at query planning/rewrite time. The value (and NULL-ness) of t1.col is different for different rows of t1. As discussed on Slack: please only check for NULL value for items which are - constant, - and "not expensive" and so have a fixed value which we are allowed to compute at query optimization phase. 3. I see the patch handles single-value comparisons like expr IN (select inner_expr FROM ...) but what about tuple-based comparisons like: (expr1, expr2) IN (select inner_expr1, inner_expr2 FROM ...) ? I guess they should have a similar issue. If they do not, we need - an explanation why - test coverage On Sun, Feb 26, 2017 at 09:04:40PM +0530, Varun wrote: > revision-id: c6d172f785f31641832087a432d1966b67efc47a (mariadb-10.2.3-283-gc6d172f) > parent(s): 78153cf641aea41166d3e79ae99b57b154f6a027 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2017-02-26 21:03:29 +0530 > message: > > MDEV-11078: NULL NOT IN (non-empty subquery) should never return results > > Setting the value of cond guard variables during the creation of Tricond Item for null values > > --- > mysql-test/r/subselect4.result | 31 +++++++++++++++++++++++++++++++ > mysql-test/t/subselect4.test | 18 ++++++++++++++++++ > sql/item_subselect.cc | 10 ++++++++++ > 3 files changed, 59 insertions(+) > > diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result > index 6bbb80c..c3bd50a 100644 > --- a/mysql-test/r/subselect4.result > +++ b/mysql-test/r/subselect4.result > @@ -2454,3 +2454,34 @@ x > c1 > 1 > drop table t1; > +# > +# MDEV-11078: NULL NOT IN (non-empty subquery) should never return results > +# > +create table t1(a int,b int); > +create table t2(a int,b int); > +insert into t1 value (NULL,2); > +select 1 in (1,2, NULL); > +1 in (1,2, NULL) > +1 > +select (null) in (select 1 from t1); > +(null) in (select 1 from t1) > +NULL > +select (null) in (select 1 from t2); > +(null) in (select 1 from t2) > +0 > +select 1 in (select 1 from t1); > +1 in (select 1 from t1) > +1 > +select 1 in (select 1 from t2); > +1 in (select 1 from t2) > +0 > +select 1 from dual where null in (select 1 from t1); > +1 > +select 1 from dual where null in (select 1 from t2); > +1 > +select 1 from dual where null not in (select 1 from t1); > +1 > +select 1 from dual where null not in (select 1 from t2); > +1 > +1 > +drop table t1,t2; > diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test > index 253160c..785b79d 100644 > --- a/mysql-test/t/subselect4.test > +++ b/mysql-test/t/subselect4.test > @@ -2009,3 +2009,21 @@ insert into t1 values(2,1),(1,2); > select (select c1 from t1 group by c1,c2 order by c1 limit 1) as x; > (select c1 from t1 group by c1,c2 order by c1 limit 1); > drop table t1; > + > +--echo # > +--echo # MDEV-11078: NULL NOT IN (non-empty subquery) should never return results > +--echo # > + > +create table t1(a int,b int); > +create table t2(a int,b int); > +insert into t1 value (NULL,2); > +select 1 in (1,2, NULL); > +select (null) in (select 1 from t1); > +select (null) in (select 1 from t2); > +select 1 in (select 1 from t1); > +select 1 in (select 1 from t2); > +select 1 from dual where null in (select 1 from t1); > +select 1 from dual where null in (select 1 from t2); > +select 1 from dual where null not in (select 1 from t1); > +select 1 from dual where null not in (select 1 from t2); > +drop table t1,t2; > diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc > index 94bc71c..b9c798e 100644 > --- a/sql/item_subselect.cc > +++ b/sql/item_subselect.cc > @@ -2113,6 +2113,8 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > We can encounter "NULL IN (SELECT ...)". Wrap the added condition > within a trig_cond. > */ > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > item= new (thd->mem_root) Item_func_trig_cond(thd, item, get_cond_guard(0)); > } > > @@ -2137,6 +2139,9 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > having= new (thd->mem_root) Item_is_not_null_test(thd, this, having); > if (left_expr->maybe_null) > { > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + > if (!(having= new (thd->mem_root) Item_func_trig_cond(thd, having, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2155,6 +2160,9 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > */ > if (!abort_on_null && left_expr->maybe_null) > { > + if(left_expr->null_value) > + set_cond_guard_var(0, FALSE); > + > if (!(item= new (thd->mem_root) Item_func_trig_cond(thd, item, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2184,6 +2192,8 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > (char *)"<result>")); > if (!abort_on_null && left_expr->maybe_null) > { > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > if (!(new_having= new (thd->mem_root) Item_func_trig_cond(thd, new_having, > get_cond_guard(0)))) > DBUG_RETURN(true); > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] an observation
by AugustQ 22 Feb '17

22 Feb '17

Hi, by playing with the code I think I found something interesting. My environment: MariaDB 10.0.10, MyISAM-engine I played with a table-scan, no index is defined on this table. When I execute a SQL-statement that forces the server to do a second table- scan on a table this 2nd table-scan will be slow. The reason for this behaviour is the usage of a buffer: during the 1st scan this buffer is filled, used and filled again until the whole table is processed. At the end of the 1st scan it contains the last bytes of the file. When a 2nd scan is started the reading of the table starts from the beginning of the file but the buffer and all associated variables are not reset: the buffer still contains the bytes from the end of the file, the request cannot be fulfilled by the buffer so the request has to be handled by reading the bytes directly from the file using the read()-function of the Std-library. This takes much more time then simply copying the bytes from the internal buffer. My idea is: somewhere in the code this situation must be detected and the buffer (and all associated variables) reset to initial values. rein it_io_cache() looks like the right candidate for this. I assume the engine Maria will show the same effect (I didn't check other engines). Some questions: is my observation correct? Do you think this needs to be fixed? Or is there no further development on this part of the code? Thanks Regards AugustQ PS: you will find more details on this topic here: http://augustq.blogs pot.com/2017/01/subselect-execution-3.html

3 6

[Maria-developers] engine condition pushdown
by AugustQ 22 Feb '17

22 Feb '17

Hi, by playing with a SQL-statement I encountered a problem. I'm not sure if this is a bug or if I did not use the correct method to get the information I expected. So let me please explain the situation in which I encountered this problem. But it will take some lines of text to come to the point. My environment: MariaDB 10.0.10 source-code-version on Linux. The statement: select SQL_NO_CACHE B.PZN, B.ArtikelText from TestSmall B where exists ( select 1 from TestBig A where A.Hersteller = '36367' and A.PZN = B.PZN) and B.Hersteller = '00020' Engine used is MyISAM but the effect should be independent of any engine (not checked). No indexes exist on the tables involved. You will find a lot more of details here: http://augustq.blogspot.com/2 017/02/subselect-execution-4.html Execution of this (silly) statement is done by a table-scan on the table TestSmall. When a record is found the matches the WHERE-condition the server switches over to the table TestBig and does a table-scan on this table. When a match is found it returns to the table TestSmall, otherwise the table TestBig is read to the end and then the server returns to the table TestSmall. The server continues scanning the table TestSmall, switching over to TestBig, returning to TestSmall, continues reading TestSmall and so on, until it reaches the end of the table TestSmall. Of interest for me was the condition tree which can be inspected by looking at the function cond_push() (in my case ha_myisam::cond_push()). So let's start. The function cond_push() is initally called for the tables TestSmall and TestBig, this looks good. Then the server starts reading the table TestSmall and therefore the function cond_push() is called again. This looks good too. Now a matching record is found in TestSmall: it switches over to the table TestBig and calls cond_push() with the condition-tree for this table. This tree looks like this: COND-ITEM args: 0 type=[COND_AND_FUNC] FUNC-ITEM [=] args: 2 type=[EQ_FUNC] FIELD-ITEM [TestOpt] [A] [Hersteller] name=<Hersteller> STRING-ITEM str_length=<5> str_value=<36367> name=<36367> FUNC-ITEM [=] args: 2 type=[EQ_FUNC] FIELD-ITEM [TestOpt] [A] [PZN] name=<PZN> FIELD-ITEM [TestOpt] [B] [PZN] name=<PZN> The last line is of interest (I've marked it in bold). For the table with the alias B (= TestSmall) no value is given for the column PZN but this value is available. This is the first point of interest. Let's continue with the operation. When the table TestBig is read to the end, the scanning of TestSmall continues and a matching record is found over there the server again switches over to TestBig. Before it starts reading the records from this table the function cond_push() is called again. This is the output of the condition-tree, restricted to the line of interest (the last line): FIELD-ITEM [TestOpt] [B] [PZN] str_length=<2> str_value=<37> name=<PZN> So let's continue: when it switches to TestBig for the 3rd time this output looks like: FIELD-ITEM [TestOpt] [B] [PZN] str_length=<7> str_value=<222 > name=<PZN> Enough of data. I can deliver more examples if needed or you may look into my text. Judgement: The output of the first case des not show the value of the column PZN from the current record in TestSmall although the value is available. The output in the second case shows the value 37 (because of the length eq. 2) but the correct value would be 3717968 (and length eq. 7). The output in the third case shows the value 222 followed by 4 spaces (length eq. 7), but the correct value is 222 (length eq. 3). What I see is the correct value (exclude the first example presented here) but the length-information is one row behind (one row of TestSmall). For accessing the information presented here I used COND->str_value. So the question is: - the information is available but I used the wrong function/variable? OR - is this a bug in the code? What do you think? Thanks AugustQ

2 4