- developers - lists.mariadb.org

[Maria-developers] Please review: [Commits] Rev 3377: BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate
by Sergei Petrunia 09 Jan '12

09 Jan '12

Hello Igor, Timour Could anybody of please review the below fix? additional details about the problem were posted at https://mariadb.atlassian.net/browse/MDEV-67. ----- Forwarded message from Sergey Petrunya <psergey(a)askmonty.org> ----- From: Sergey Petrunya <psergey(a)askmonty.org> To: commits(a)mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: Sun, 8 Jan 2012 14:43:17 +0400 (GST) Subject: [Commits] Rev 3377: BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... in file:///home/psergey/dev2/5.3-look46/ At file:///home/psergey/dev2/5.3-look46/ ------------------------------------------------------------ revno: 3377 revision-id: psergey(a)askmonty.org-20120108104314-xa6cxdbfive3elwf parent: igor(a)askmonty.org-20120103040636-nc6o55vsxqadd1n0 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.3-look46 timestamp: Sun 2012-01-08 14:43:14 +0400 message: BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... - Create/use do_copy_nullable_row_to_notnull() function for ref access, which is used when copying from not-NULL field in table that can be NULL-complemented to not-NULL field. === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2011-12-24 16:55:10 +0000 +++ b/mysql-test/r/subselect_sj.result 2012-01-08 10:43:14 +0000 @@ -2152,4 +2152,21 @@ set optimizer_prune_level= @opl_901399; set optimizer_switch= @os_091399; DROP TABLE t1,t2; +# +# BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +# +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); +SELECT a, COUNT(*) FROM t1 +WHERE a IN ( +SELECT b FROM t2 force index(b), t3 force index(c) +WHERE c = b AND b = a +); +a COUNT(*) +NULL 0 +DROP TABLE t1, t2, t3; set optimizer_switch=@subselect_sj_tmp; === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2011-12-24 16:55:10 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2012-01-08 10:43:14 +0000 @@ -2166,6 +2166,23 @@ set optimizer_prune_level= @opl_901399; set optimizer_switch= @os_091399; DROP TABLE t1,t2; +# +# BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +# +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); +SELECT a, COUNT(*) FROM t1 +WHERE a IN ( +SELECT b FROM t2 force index(b), t3 force index(c) +WHERE c = b AND b = a +); +a COUNT(*) +NULL 0 +DROP TABLE t1, t2, t3; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off === modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2011-12-24 16:55:10 +0000 +++ b/mysql-test/t/subselect_sj.test 2012-01-08 10:43:14 +0000 @@ -1997,6 +1997,25 @@ DROP TABLE t1,t2; +--echo # +--echo # BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +--echo # +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); + +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); + +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); + +SELECT a, COUNT(*) FROM t1 + WHERE a IN ( + SELECT b FROM t2 force index(b), t3 force index(c) + WHERE c = b AND b = a + ); + +DROP TABLE t1, t2, t3; # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; === modified file 'sql/field_conv.cc' --- a/sql/field_conv.cc 2011-12-11 09:34:44 +0000 +++ b/sql/field_conv.cc 2012-01-08 10:43:14 +0000 @@ -248,6 +248,25 @@ } } +/* + Copy: (not-NULL field in table that can be NULL-complemented) -> (not-NULL + field) +*/ +static void do_copy_nullable_row_to_notnull(Copy_field *copy) +{ + if (*copy->null_row || + (copy->from_null_ptr && (*copy->from_null_ptr & copy->from_bit))) + { + copy->to_field->set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + WARN_DATA_TRUNCATED, 1); + copy->to_field->reset(); + } + else + { + (copy->do_copy2)(copy); + } + +} /* Copy: (NULL-able field) -> (not NULL-able field) */ static void do_copy_not_null(Copy_field *copy) @@ -638,7 +657,15 @@ else if (to_field == to_field->table->next_number_field) do_copy= do_copy_next_number; else - do_copy= do_copy_not_null; + { + if (!from_null_ptr) + { + null_row= &from->table->null_row; + do_copy= do_copy_nullable_row_to_notnull; + } + else + do_copy= do_copy_not_null; + } } } else if (to_field->real_maybe_null()) === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-12-24 16:55:10 +0000 +++ b/sql/sql_select.cc 2012-01-08 10:43:14 +0000 @@ -646,6 +646,9 @@ aggregate functions and non-aggregate fields, any non-aggregated field may produce a NULL value. Set all fields of each table as nullable before semantic analysis to take into account this change of nullability. + + Note: this loop doesn't touch tables inside merged semi-joins, because + subquery-to-semijoin conversion has not been done yet. This is intended. */ if (mixed_implicit_grouping) tbl->table->maybe_null= 1; _______________________________________________ commits mailing list commits(a)mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits ----- End forwarded message ----- -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

Re: [Maria-developers] [Commits] Rev 3204: Fix some failing tests on Windows in file:///H:/bzr/5.5/
by Sergei Golubchik 06 Jan '12

06 Jan '12

Hi, Vladislav! On Jan 06, Vladislav Vaintroub wrote: > - disable perfschema all_instances test on Windows- there are > legitimate reasons for output to be different on Unix (some > different threads, some different locks), the differences are > expected to grow in the future, e.g with threadpool. 1. Why is it not an issue for vanilla MySQL? 2. please don't forget to close all related issues after you fix them :) Regards, Sergei

2 1

[Maria-developers] 回复: Re: IO statistic in slow log
by jiangchengyao 05 Jan '12

05 Jan '12

Yes, Logical IO means pages from buffer pool, physical IO means pages read from disk 2012-01-05 jiangchengyao 发件人：MARK CALLAGHAN 发送时间：2012-01-05 04:39 主题：Re: [Maria-developers] IO statistic in slow log 收件人：jiangchengyao 抄送：maria-developers What are the difference between logical and physical IO? I assume you are using Oracle's terminology where a logical IO means that a page was read from the InnoDB buffer cache. On Thu, Dec 29, 2011 at 6:41 PM, jiangchengyao <jiangchengyao(a)gmail.com> wrote: > Hi, all > > I write a patch to show physical & logical IO statistics in slow log. Now it > only support XtraDB storage engine, but it is easy to realize in other > engine. You may found it like this in slow log: > > # Time: 111227 16:29:54 > # User@Host: root[root] @ localhost [::1] > # Query_time: 0.310018 Lock_time: 0.203012 Rows_sent: 1 Rows_examined: > 30000 Logical_IO: 30145 Physical_IO: 50 > use tpcc; > SET timestamp=1324974594; > select count(1) from history; > > I think this feature is much helpful for DBA or Developer to know what the > real IO one SQL has. In addition, it is simple, only a few changes should be > made. > > The branch is: https://code.launchpad.net/~jiangchengyao/+junk/mariadb > > Regards, > David > > 2011-12-30 > ________________________________ > jiangchengyao > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-developers > Post to : maria-developers(a)lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-developers > More help : https://help.launchpad.net/ListHelp > -- Mark Callaghan mdcallag(a)gmail.com

1 0

Re: [Maria-developers] request for a feature -- LIMIT_ROWS_EXAMINED
by MARK CALLAGHAN 04 Jan '12

04 Jan '12

Start with it in the current development branch. If I am not able to backport that then we can talk again. On Tue, Jan 3, 2012 at 1:33 AM, Timour Katchaounov <timour(a)montyprogram.com> wrote: > Mark, > > There is one thing we didn't specify - in which MariaDB version > do you want the feature? If you want just a patch, then it could > be in any version, but if you'd like it to be in a released version, > then it should be in 5.5. > > If you want it to be both in an older version, and in 5.5, then > there will be slight differences IMO because in 5.3 there are few > new join algorithms, and the related code has changed. > > Timour > > > On 23.12.2011 06:38, MARK CALLAGHAN wrote: >> >> Yes, I don't think it is a lot of work. I started on it myself but >> don't trust myself to modify optimizer code. I think we can find a >> bigger project to collaborate on in 2012 -- probably something related >> to replication. >> >> On Wed, Dec 21, 2011 at 12:57 PM, Michael Widenius<monty(a)askmonty.org> >> wrote: >>> >>> >>> Hi! >>> >>>>>>>> "MARK" == MARK CALLAGHAN<mdcallag(a)gmail.com> writes: >>> >>> >>> MARK> A warning would be excellent. An error would be a problem. >>> Returning >>> MARK> OK is probably not the best result when the examined limit is >>> reached. >>> >>> Ok, we will try to develop this feature (with a ok + warning) this year. >>> (Should not be that many hours of work). >>> >>> Regards, >>> Monty >> >> >> >> > -- Mark Callaghan mdcallag(a)gmail.com

1 0

[Maria-developers] WL#248 New (by Igor): Collect engine independent statistics on table columns and indexes to store it in persistent system tables
by worklog-noreply＠askmonty.org 04 Jan '12

04 Jan '12

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Collect engine independent statistics on table columns and indexes to store it in persistent system tables CREATION DATE..: Wed, 04 Jan 2012, 04:11 SUPERVISOR.....: IMPLEMENTOR....: Igor COPIES TO......: Igor Knielsen Monty Psergey Sanja Sergei Timour Wlad CATEGORY.......: Server-BackLog TASK ID........: 248 (http://askmonty.org/worklog/?tid=248) VERSION........: Server-5.5 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 120 (hours remain) ORIG. ESTIMATE.: 120 PROGRESS NOTES: DESCRIPTION: In a special mode the ANALYZE command will collect statistics on table cardinality, table columns and table indexes. This statistics will be stored in system tables table_stat, column_stat and index_stat. For any database table the system table table_stat will contain the number of rows in the table. For any table column the system table column_stat will contain min and max value, the share of null values, the average length of a column value, and the the average frequency of the column values. For any index defined on a database table the system table index_stat will contain number of records per every key prefix. This task will be implemented in 3 steps: 1. A pure back-port of the WL#4777 from mysql-5.4 code line will be performed. 2. New possibility to collect statistics only on specified columns and/or indexes will be added. 3. A new possibility to scan only a certain percentage of the table records in a pseudo-random manner will be added. The collected statistics is supposed to be used by the optimizer, but this will be an objective of some other task. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] [Commits] Rev 3376: Fixed LP bug #910083. in file:///home/igor/maria/maria-5.3-bug910083/
by Igor Babaev 03 Jan '12

03 Jan '12

On 01/03/2012 06:44 AM, Timour Katchaounov wrote: > Igor, > >> On 01/03/2012 12:49 AM, Timour Katchaounov wrote: >>> Hi Igor, >>> >>> The reason for removing this specific call to >>> engine->set_thd() was because Monty claimed this is >>> the correct way, and because with the new assert we >>> couldn't find any case where the new assert would fail. >>> >>> Apparently the above assert was incorrect, however, the >>> current commit message doesn't have any explanation what >>> was wrong, why the assert you removed is wrong, and why >>> it is needed to call engine->set_thd(). >> >> Igor, >> >> I would rather say that you patch did not have any explanation >> why you had removed the call. Should it be a reference to Monty's >> opinion?I have to admit it would be pretty pretty lame. > > There is a comment in the patch for bug lp:685411, this is the > relevant diff: > > @@ -183,7 +185,8 @@ bool Item_subselect::fix_fields(THD *thd > bool res; > > DBUG_ASSERT(fixed == 0); > - engine->set_thd((thd= thd_param)); > + /* There is no reason to get a different THD. */ > + DBUG_ASSERT(thd == thd_param); > if (!done_first_fix_fields) > { > done_first_fix_fields= TRUE; > > The reasoning is simple - there is no reason for a subquery to > get a different THD object compared to that of the containing > query. > > So my question to your patch is whether you figured out why the > above assumption is not true. If you did, it would be nice to have > an explanation. I am pretty sure the assumption is true for SELECTs. > If that is the case, then I would refine your fix by testing whether > a statement is a SELECT, or UPDATE (or INSERT?) and updating the THD > only when needed. It's relevant for the scenario when the fixed item is created by a different client: for example, when the item belongs to SP and this SP was created by a different client. I returned the code that you removed. I don't think I have to explain why you were wrong. > > Timour > >> >> Regards, >> Igor. >> >>> >>> Timour >>> >>> On 3.01.2012 06:06, Igor Babaev wrote: >>>> At file:///home/igor/maria/maria-5.3-bug910083/ >>>> >>>> ------------------------------------------------------------ >>>> revno: 3376 >>>> revision-id: igor(a)askmonty.org-20120103040636-nc6o55vsxqadd1n0 >>>> parent: psergey(a)askmonty.org-20111230211905-he458ysn3sse6wlm >>>> committer: Igor Babaev<igor(a)askmonty.org> >>>> branch nick: maria-5.3-bug910083 >>>> timestamp: Mon 2012-01-02 20:06:36 -0800 >>>> message: >>>> Fixed LP bug #910083. >>>> The patch for bug 685411 erroneously removed a call of >>>> engine->set_thd() >>>> from Item_subselect::fix_fields(). >>>> >>>> >>>> >>>> _______________________________________________ >>>> commits mailing list >>>> commits(a)mariadb.org >>>> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits >>

1 0

Re: [Maria-developers] [Commits] Rev 3376: Fixed LP bug #910083. in file:///home/igor/maria/maria-5.3-bug910083/
by Igor Babaev 03 Jan '12

03 Jan '12

On 01/03/2012 12:49 AM, Timour Katchaounov wrote: > Hi Igor, > > The reason for removing this specific call to > engine->set_thd() was because Monty claimed this is > the correct way, and because with the new assert we > couldn't find any case where the new assert would fail. > > Apparently the above assert was incorrect, however, the > current commit message doesn't have any explanation what > was wrong, why the assert you removed is wrong, and why > it is needed to call engine->set_thd(). Igor, I would rather say that you patch did not have any explanation why you had removed the call. Should it be a reference to Monty's opinion?I have to admit it would be pretty pretty lame. Regards, Igor. > > Timour > > On 3.01.2012 06:06, Igor Babaev wrote: >> At file:///home/igor/maria/maria-5.3-bug910083/ >> >> ------------------------------------------------------------ >> revno: 3376 >> revision-id: igor(a)askmonty.org-20120103040636-nc6o55vsxqadd1n0 >> parent: psergey(a)askmonty.org-20111230211905-he458ysn3sse6wlm >> committer: Igor Babaev<igor(a)askmonty.org> >> branch nick: maria-5.3-bug910083 >> timestamp: Mon 2012-01-02 20:06:36 -0800 >> message: >> Fixed LP bug #910083. >> The patch for bug 685411 erroneously removed a call of >> engine->set_thd() >> from Item_subselect::fix_fields(). >> >> >> >> _______________________________________________ >> commits mailing list >> commits(a)mariadb.org >> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

Re: [Maria-developers] [Commits] Rev 3202: Fix compile error in file:///H:/bzr/5.5/
by Sergei Golubchik 03 Jan '12

03 Jan '12

Hi, Vladislav! What was the error that you fixed? On Jan 03, Vladislav Vaintroub wrote: > ------------------------------------------------------------ > revno: 3202 > committer: Vladislav Vaintroub <wlad(a)montyprogram.com> > branch nick: 5.5 > timestamp: Tue 2012-01-03 00:17:36 +0100 > message: > Fix compile error > === modified file 'storage/maria/ma_write.c' > --- a/storage/maria/ma_write.c 2012-01-02 20:56:16 +0000 > +++ b/storage/maria/ma_write.c 2012-01-02 23:17:36 +0000 > @@ -472,10 +472,11 @@ > int error; > my_off_t new_root= *root; > uchar key_buff[MARIA_MAX_KEY_BUFF]; > - MARIA_KEY UNINIT_VAR(org_key); /* Set/used when now_transactional=TRUE */ > + MARIA_KEY org_key; /* Set/used when now_transactional=TRUE */ > + my_bool transactional= share->now_transactional; > DBUG_ENTER("_ma_ck_write_btree_with_log"); > > - if (share->now_transactional) > + if (transactional) > { > /* Save original value as the key may change */ > org_key= *key; > @@ -483,7 +484,7 @@ > } > > error= _ma_ck_real_write_btree(info, key, &new_root, comp_flag); > - if (!error && share->now_transactional) > + if (!error && transactional) > { > /* Log the original value */ > *key= org_key; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits Regards, Sergei

2 1

[Maria-developers] win auth plugin
by sergii＠pisem.net 31 Dec '11

31 Dec '11

Hi. Attached is the patch for the windows authentication plugin. To avoid code duplication I've reused MySQL's windows authentication plugin and only wrote the server part. This plugin is different from MySQL's, because (as all our plugins) it does not do user name mapping and only preforms the authentication - that is, only verifies that the user really is who he says he is. Regards, Sergei

3 3

[Maria-developers] WL#247 New (by Igor): Make the optimizer use extended keys
by worklog-noreply＠askmonty.org 31 Dec '11

31 Dec '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Make the optimizer use extended keys CREATION DATE..: Sat, 31 Dec 2011, 05:49 SUPERVISOR.....: IMPLEMENTOR....: Igor COPIES TO......: Igor Monty Psergey Sanja Sergei Timour CATEGORY.......: Server-BackLog TASK ID........: 247 (http://askmonty.org/worklog/?tid=247) VERSION........: Server-5.5 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 60 (hours remain) ORIG. ESTIMATE.: 60 PROGRESS NOTES: DESCRIPTION: Currently the optimizer mostly does not use hidden components of InnoDB keys. Meanwhile these components contain fields of the primary key and could be used to to generate more efficient execution plans. In particular using these components in many cases allow to generate execution plans that that employs index only look-ups. This task will support usage of extended keys for: - ref and eq-ref accesses - range scans - index-merge scans - loose scans - min/max optimization ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0