[Maria-developers] Please review: MWL#121: DS-MRR support for clustered primary keys
Hello Igor,
Please find below the combined patch for MWL#121. It is ready for review.
diff -urN --exclude='.*'
maria-5.3-dsmrr-for-cpk-clean/mysql-test/r/innodb_mrr_cpk.result
maria-5.3-dsmrr-for-cpk-noc/mysql-test/r/innodb_mrr_cpk.result
--- maria-5.3-dsmrr-for-cpk-clean/mysql-test/r/innodb_mrr_cpk.result
1970-01-01 03:00:00.0 +0300
+++ maria-5.3-dsmrr-for-cpk-noc/mysql-test/r/innodb_mrr_cpk.result
2010-06-22 23:28:02.0 +0400
@@ -0,0 +1,148 @@
+drop table if exists t0,t1,t2,t3;
+set @save_join_cache_level=@@join_cache_level;
+set join_cache_level=6;
+set @save_storage_engine=@@storage_engine;
+set storage_engine=innodb;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a char(8), b char(8), filler char(100), primary key(a));
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` char(8) NOT NULL DEFAULT '',
+ `b` char(8) DEFAULT NULL,
+ `filler` char(100) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+insert into t1 select
+concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'),
+concat('b-', 1000 + A.a + B.a*10 + C.a*100, '=B'),
+'filler'
+from t0 A, t0 B, t0 C;
+create table t2 (a char(8));
+insert into t2 values ('a-1010=A'), ('a-1030=A'), ('a-1020=A');
+This should use join buffer:
+explain select * from t1, t2 where t1.a=t2.a;
+id select_type table typepossible_keys key key_len ref
rowsExtra
+1 SIMPLE t2 ALL NULLNULLNULLNULL3
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 8 test.t2.a 1
Using join buffer
+This output must be sorted by value of t1.a:
+select * from t1, t2 where t1.a=t2.a;
+a b filler a
+a-1010=A b-1010=Bfiller a-1010=A
+a-1020=A b-1020=Bfiller a-1020=A
+a-1030=A b-1030=Bfiller a-1030=A
+drop table t1, t2;
+create table t1(
+a char(8) character set utf8, b int, filler char(100),
+primary key(a,b)
+);
+insert into t1 select
+concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'),
+1000 + A.a + B.a*10 + C.a*100,
+'filler'
+from t0 A, t0 B, t0 C;
+create table t2 (a char(8) character set utf8, b int);
+insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A',
1020);
+explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+id select_type table typepossible_keys key key_len ref
rowsExtra
+1 SIMPLE t2 ALL NULLNULLNULLNULL3
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 28 test.t2.a,test.t2.b
1 Using join buffer
+select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+a b filler a b
+a-1010=A 1010filler a-1010=A1010
+a-1020=A 1020filler a-1020=A1020
+a-1030=A 1030filler a-1030=A1030
+insert into t2 values ('a-1030=A', 1030), ('a-1020=A', 1020);
+explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+id select_type table typepossible_keys key key_len ref
rowsExtra
+1 SIMPLE t2 ALL NULLNULLNULLNULL5
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 28 test.t2.a,test.t2.b
1 Using join buffer
+select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+a b filler a b
+a-1010=A 1010filler a-1010=A1010
+a-1020=A 1020filler a-1020=A1020
+a-1020=A 1020filler a-1020=A1020
+a-1030=A 1030filler a-1030=A1030
+a-1030=A 1030filler a-1030=A1030
+drop table t1, t2;
+create table t1(
+a varchar(8) character set utf8, b int, filler char(100),
+primary key(a,b)
+);
+insert into t1 select
+concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'),
+1000 + A.a + B.a*10 + C.a*100,
+'filler'
+from t0 A, t0 B, t0 C;
+create table t2 (a char(8) character set utf8, b int);
+insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A',
1020);
+explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+id select_type table typepossible_keys key key_len ref
rowsExtra
+1 SIMPLE t2 ALL NULLNULLNULLNULL3
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 30 test.t2.a,test.t2.b
1 Using index condition(BKA); Using join buffer
+select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
+a b filler a b
+a-1010=A 1010filler a-1010=A1010
+a-1020=A 1020filler a-1020=A1020
+a-1030=A 1030filler a-1030=A1030
+explain select * from t1, t2 where t1.a=t2.a;
+id select_type table typepossible_keys key key_len ref
rowsExtra
+1 SIMPLE t2 ALL NULLNULLNULLNULL3
+1 SIMPLE t1 ref PRIMARY PRIMARY 26 test.t2.a 1
Using index condition(BKA); Using join buf
[Maria-developers] MariaDB 5.3: Time to merge from 5.2?
Hello 5.3 developers, We all know that 5.3 tree have some buildbot failures that - are unlikely to be result of any 5.3 work, - cannot be observed in 5.2 - still are somehow present. I got suspicious about one failure, and investigated it: https://bugs.launchpad.net/maria/+bug/597742. Long story short, it was present in 5.2 at some earlier point but has been fixed there since then. I think, in order to avoid spent time in a way it was spent on analyzing the above mentioned bug, we should do a 5.2->5.3 merge. 5.2 now produces an almost green run in buildbot (the exception is plugin_load.test), and AFAIU the release of 5.2.1 can be interpreted as indication that 5.2's code is not going to change much anymore. Any objections to doing the merge? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] [5.3 merge] Item_in_subselect::init_left_expr_cache() question.
Hi Timour, I'm having difficulties with finishing 5.2->5.3 merge. Could you please take a look at https://bugs.launchpad.net/maria/+bug/598972 ? The questions are in the bug entry. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] update_virtual_fields() calls missing in sql_join_cache.cc?
Hello Igor,
It has come to my attention that sql_join_cache.cc does not have as many
update_virtual_fields() calls as I think it ought to have.
My reasoning was as follows: AFAIU when one has read a record from a table,
they must call update_virtual_fields() before they try to evaluate the
attached table condition (because the condition may refer to virtual fields).
Now if one opens sql_join_cache.cc and looks at these three functions:
JOIN_CACHE_BKA::join_matching_records(bool skip_last)
{
...
while (!(error= file->multi_range_read_next((char **) &rec_ptr)))
{
...
rc= generate_full_extensions(rec_ptr);
...
}
...
}
JOIN_CACHE::generate_full_extensions(uchar *rec_ptr)
{
...
if (check_match(rec_ptr))
{
...
}
JOIN_CACHE::check_match(uchar *rec_ptr)
{
/* Check whether pushdown conditions are satisfied */
if (join_tab->select && join_tab->select->skip_record())
return FALSE;
...
}
one can see a call path where we read a table record with multi_range_read_next
and then proceed to evaluating the attached condition with skip_record()
without calling update_virtual_fields(). Is there a problem.
Another thing I can't understand about update_virtual_fields() is the asymmetry
between rr_XXX() functions. Why do rr_quick() and rr_sequential() call
update_virtual_fields() while rr_index_first() and rr_from_pointers() don't? If
that is intentional, I think it deserves to be documented.
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
[Maria-developers] DS-MRR: extra work filed as new WL entries, questions
Hello Igor, Based on our discussions, I've filed * http://askmonty.org/worklog/Server-RawIdeaBin/index.pl?tid=123 "DS-MRR for clustered PKs: more efficient buffer use" * http://askmonty.org/worklog/Server-RawIdeaBin/index.pl?tid=124 "DS-MRR for clustered PKs: cost function" * http://askmonty.org/worklog/Client-BackLog/index.pl?tid=125 "Make DS-MRR sort the ranges before scanning the index" Could you please check if that correctly describes the conclusions we've arrived at? Also, WL texts contain several unresolved questions, marked with "TODO". BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [5.3 merge] Item_in_subselect::init_left_expr_cache() question.
Timour, On Sun, Jun 27, 2010 at 01:17:34PM +0400, Sergey Petrunya wrote: > > I'm having difficulties with finishing 5.2->5.3 merge. Could you please take > a look at > https://bugs.launchpad.net/maria/+bug/598972 ? The questions are in the bug > entry. Any update on this? This is the only thing holding the merge, and I'd like to get over the merge. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] MWL#123, sql layer part (was: Re: mwl#121: follow up)
Hello Igor, On Thu, Jul 01, 2010 at 11:55:58PM -0700, Igor Babaev wrote: > According to our agreement I introduced a new flag for MRR. > I called it HA_MRR_MATERIALIZED_KEYS. This flag passed to any MMR > interface function that takes mrr_mode as a parameter says: > key values used in ranges are materialized in some buffers external to MRR. > This patch only gives DS-MRR information that the keys are materialized. However, DS-MRR needs to work on (key, range_id) pairs. The patch doesn't allow to assoicate key with range_id (or vice versa), so DS-MRR will have to keep (key_pointer, range_id) tuples. If we consider 64-bit environment, then sizeof(key_value_pointer)== sizeof(key_value), and this patch won't bring any benefit at all. I was expecting that the patch will provide some means to associate key_value_pointer with range_id, so that DS-MRR won't need to store both. Can we discuss this on scrum meeting or Monday evening? > === modified file 'sql/handler.h' > --- sql/handler.h 2010-03-20 12:01:47 + > +++ sql/handler.h 2010-07-01 20:00:35 + > @@ -1212,6 +1212,12 @@ void get_sweep_read_cost(TABLE *table, h > */ > #define HA_MRR_NO_NULL_ENDPOINTS 128 > > +/* > + The MRR user has materialized range keys somewhere in the user's buffer. > + This can be used for optimization of the procedure that sorts these keys > + since in this case key values don't have to be copied into the MRR buffer. > +*/ > +#define HA_MRR_MATERIALIZED_KEYS 256 > > > /* > > === modified file 'sql/sql_join_cache.cc' > --- sql/sql_join_cache.cc 2010-03-07 15:41:45 + > +++ sql/sql_join_cache.cc 2010-07-01 19:59:55 + > @@ -651,6 +651,9 @@ int JOIN_CACHE_BKA::init() > >use_emb_key= check_emb_key_usage(); > > + if (use_emb_key) > +mrr_mode|= HA_MRR_MATERIALIZED_KEYS; > + >create_remaining_fields(FALSE); > >set_constants(); > @@ -2617,6 +2620,8 @@ int JOIN_CACHE_BKA_UNIQUE::init() >data_fields_offset+= copy->length; >} > > + mrr_mode|= HA_MRR_MATERIALIZED_KEYS; > + >DBUG_RETURN(rc); > } > > BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Fwd: [Commits] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (igor:2821) Bug#604549]
Hello Igor,
Please find the feedback below.
On Mon, Jul 12, 2010 at 07:08:34PM -0700, Igor Babaev wrote:
> Please review this patch for the 5.2 tree.
>
> Regards,
> Igor.
>
> Original Message
> Subject: [Commits] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2
> branch (igor:2821) Bug#604549
> Date: Mon, 12 Jul 2010 18:23:26 -0700 (PDT)
> From: Igor Babaev
> Reply-To: maria-developers@lists.launchpad.net
> To: comm...@mariadb.org
>
> #At lp:maria/5.2 based on
> revid:kniel...@knielsen-hq.org-20100709120309-xzhk02q8coq7m6tl
>
> 2821 Igor Babaev 2010-07-12
> Fixed bug #604549.
> There was no error thrown when creating a table with a virtual table
> computed by an expression returning a row.
> This caused a crash when inserting into the table.
>
> Removed periods at the end of the error messages for virtual columns.
> Adjusted output in test result files accordingly.
Periods at the end of error messages were apparent for the whole time. Why do
we suddenly decide to remove them now?
> === modified file 'mysql-test/r/plugin.result'
> --- a/mysql-test/r/plugin.result 2010-04-30 20:04:35 +
> +++ b/mysql-test/r/plugin.result 2010-07-13 01:23:07 +
> @@ -75,9 +75,9 @@ SET SQL_MODE='IGNORE_BAD_TABLE_OPTIONS';
> #illegal value fixed
> CREATE TABLE t1 (a int) ENGINE=example ULL=1000
> one_or_two='ttt' YESNO=SSS;
> Warnings:
> -Warning 1651Incorrect value '1000' for option 'ULL'
> -Warning 1651Incorrect value 'ttt' for option 'one_or_two'
> -Warning 1651Incorrect value 'SSS' for option 'YESNO'
> +Warning 1652Incorrect value '1000' for option 'ULL'
> +Warning 1652Incorrect value 'ttt' for option 'one_or_two'
> +Warning 1652Incorrect value 'SSS' for option 'YESNO'
Why did the warning code change? Is this intentional?
> === modified file 'sql/share/errmsg.txt'
> --- a/sql/share/errmsg.txt2010-06-01 19:52:20 +
> +++ b/sql/share/errmsg.txt2010-07-13 01:23:07 +
> @@ -6211,28 +6211,31 @@ ER_VCOL_BASED_ON_VCOL
> eng "A computed column cannot be based on a computed column"
>
> ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
> -eng "Function or expression is not allowed for column '%s'."
> +eng "Function or expression is not allowed for column '%s'"
>
> ER_DATA_CONVERSION_ERROR_FOR_VIRTUAL_COLUMN
> -eng "Generated value for computed column '%s' cannot be
> converted to type '%s'."
> +eng "Generated value for computed column '%s' cannot be
> converted to type '%s'"
>
> ER_PRIMARY_KEY_BASED_ON_VIRTUAL_COLUMN
> -eng "Primary key cannot be defined upon a computed column."
> +eng "Primary key cannot be defined upon a computed column"
>
> ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN
> -eng "Key/Index cannot be defined on a non-stored computed column."
> +eng "Key/Index cannot be defined on a non-stored computed column"
>
> ER_WRONG_FK_OPTION_FOR_VIRTUAL_COLUMN
> -eng "Cannot define foreign key with %s clause on a computed
> column."
> +eng "Cannot define foreign key with %s clause on a computed column"
>
> ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN
> -eng "The value specified for computed column '%s' in table '%s'
> ignored."
> +eng "The value specified for computed column '%s' in table '%s'
> ignored"
>
> ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN
> -eng "'%s' is not yet supported for computed columns."
> +eng "'%s' is not yet supported for computed columns"
>
> ER_CONST_EXPR_IN_VCOL
> - eng "Constant expression in computed column function is not
> allowed."
> +eng "Constant expression in computed column function is not
> allowed"
> +
> +ER_ROW_EXPR_FOR_VCOL
> +eng "Expression for computed column cannot return a row"
>
When one sees this pair of codes ER_CONST_EXPR_IN_VCOL and ER_ROW_EXPR_FOR_VCOL,
one can't help asking himself whether that's the only disallowed expressions,
and if not, do we have error codes for vcol expressions with
- user variables
- subqueries
- SP calls
- etc, etc.
Do we handle such cases at all?
> ER_DEBUG_SYNC_TIMEOUT
>eng "debug sync point wait timed out"
>
> === modified file 'sql/table.cc'
> --- a/sql/table.cc2010-06-05 14:53:36 +
> +++ b/sql/table.cc2010-07-13 01:23:07 +
> @@ -1859,6 +1859,14 @@ bool fix_vcol_expr(THD *thd,
> goto end;
>}
>thd->where= save_where;
> +#if 0
> +#else
> + if (unlikely(func_expr->result_type() == ROW_RESULT))
> + {
> + my_error(ER_ROW_EXPR_FOR_VCOL, MYF(0));
> + goto end;
> + }
> +#endif
Please remove #if/#else.
> #ifdef PARANOID
>/*
> Walk through the Item tree checking if all items are valid
>
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
Re: [Maria-developers] [Fwd: [Commits] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (igor:2822) Bug#603654]
Hello Igor,
Ok to push.
On Mon, Jul 12, 2010 at 07:08:58PM -0700, Igor Babaev wrote:
> Please review this patch for the 5.2 tree.
>
> Regards,
> Igor.
>
>
> Original Message
> Subject: [Commits] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2
> branch (igor:2822) Bug#603654
> Date: Mon, 12 Jul 2010 19:05:37 -0700 (PDT)
> From: Igor Babaev
> Reply-To: maria-developers@lists.launchpad.net
> To: comm...@mariadb.org
>
> #At lp:maria/5.2 based on
> revid:i...@askmonty.org-20100713012307-rnom77fx57ef900o
>
> 2822 Igor Babaev 2010-07-12
> Fixed bug #603654.
> If a virtual column was used in the ORDER BY clause of a query
> and some of the columns this virtual column was based upon were
> not referred anywhere in the query then the execution of the
> query could cause an assertion failure.
> It happened because in this case the bitmap of the columns used
> for ordering keys was not formed correctly.
> modified:
> mysql-test/suite/vcol/r/vcol_misc.result
> mysql-test/suite/vcol/t/vcol_misc.test
> sql/filesort.cc
>
> === modified file 'mysql-test/suite/vcol/r/vcol_misc.result'
> --- a/mysql-test/suite/vcol/r/vcol_misc.result2010-07-13 01:23:07
> +
> +++ b/mysql-test/suite/vcol/r/vcol_misc.result2010-07-13 02:05:28
> +
> @@ -35,3 +35,13 @@ a int NOT NULL DEFAULT '0',
> v double AS ((1, a)) VIRTUAL
> );
> ERROR HY000: Expression for computed column cannot return a row
> +CREATE TABLE t1 (
> +a CHAR(255) BINARY NOT NULL DEFAULT 0,
> +b CHAR(255) BINARY NOT NULL DEFAULT 0,
> +v CHAR(255) BINARY AS (CONCAT(a,b)) VIRTUAL );
> +INSERT INTO t1(a,b) VALUES ('4','7'), ('4','6');
> +SELECT 1 AS C FROM t1 ORDER BY v;
> +C
> +1
> +1
> +DROP TABLE t1;
>
> === modified file 'mysql-test/suite/vcol/t/vcol_misc.test'
> --- a/mysql-test/suite/vcol/t/vcol_misc.test 2010-07-13 01:23:07 +
> +++ b/mysql-test/suite/vcol/t/vcol_misc.test 2010-07-13 02:05:28 +
> @@ -30,6 +30,19 @@ CREATE TABLE t1 (
>v double AS ((1, a)) VIRTUAL
> );
>
> +#
> +# Bug#603654: Virtual column in ORDER BY, no other references of table
> columns
> +#
> +
> +CREATE TABLE t1 (
> + a CHAR(255) BINARY NOT NULL DEFAULT 0,
> + b CHAR(255) BINARY NOT NULL DEFAULT 0,
> + v CHAR(255) BINARY AS (CONCAT(a,b)) VIRTUAL );
> +INSERT INTO t1(a,b) VALUES ('4','7'), ('4','6');
> +SELECT 1 AS C FROM t1 ORDER BY v;
> +
> +DROP TABLE t1;
> +
>
>
>
>
> === modified file 'sql/filesort.cc'
> --- a/sql/filesort.cc 2010-06-01 19:52:20 +
> +++ b/sql/filesort.cc 2010-07-13 02:05:28 +
> @@ -1009,7 +1009,14 @@ static void register_used_fields(SORTPAR
> if ((field= sort_field->field))
> {
>if (field->table == table)
> - bitmap_set_bit(bitmap, field->field_index);
> + {
> +if (field->vcol_info)
> + {
> + Item *vcol_item= field->vcol_info->expr_item;
> + vcol_item->walk(&Item::register_field_in_read_map, 1, (uchar
> *) 0);
> +}
> +bitmap_set_bit(bitmap, field->field_index);
> + }
> }
> else
> {// Item
>
> ___
> commits mailing list
> comm...@mariadb.org
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
--
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Fwd: [Commits] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (igor:2823) Bug#603186]
Hello Igor,
On Thu, Jul 15, 2010 at 04:54:37PM -0700, Igor Babaev wrote:
> Please review this patch for the 5.2 tree.
>
> Regards,
> Igor.
Ok to push.
> Original Message
> Subject: [Commits] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2
> branch (igor:2823) Bug#603186
> Date: Thu, 15 Jul 2010 16:51:17 -0700 (PDT)
> From: Igor Babaev
> Reply-To: maria-developers@lists.launchpad.net
> To: comm...@mariadb.org
>
> #At lp:maria/5.2 based on
> revid:i...@askmonty.org-20100713174523-mjvsvvp6ow8dc81x
>
> 2823 Igor Babaev 2010-07-15
> Fixed bug #603186.
> There were two problems that caused wrong results reported with
> this bug.
> 1. In some cases stored(persistent) virtual columns were not marked
> in the write_set and in the vcol_set bitmaps.
> 2. If the list of fields in an insert command was empty then the
> values of
> the stored virtual columns were set to default.
>
> To fix the first problem the function
> st_table::mark_virtual_columns_for_write
> was modified. Now the function has a parameter that says whether
> the virtual
> columns are to be marked for insert or for update.
> To fix the second problem a special handling of empty insert lists is
> added in the function fill_record().
> modified:
> mysql-test/suite/vcol/r/vcol_misc.result
> mysql-test/suite/vcol/t/vcol_misc.test
> sql/sql_base.cc
> sql/sql_insert.cc
> sql/sql_lex.cc
> sql/sql_lex.h
> sql/sql_table.cc
> sql/table.cc
> sql/table.h
>
> === modified file 'mysql-test/suite/vcol/r/vcol_misc.result'
> --- a/mysql-test/suite/vcol/r/vcol_misc.result2010-07-13 17:45:23
> +
> +++ b/mysql-test/suite/vcol/r/vcol_misc.result2010-07-15 23:51:05
> +
> @@ -45,3 +45,20 @@ C
> 1
> 1
> DROP TABLE t1;
> +CREATE TABLE t1(a int, b int DEFAULT 0, v INT AS (b+10) PERSISTENT);
> +INSERT INTO t1(a) VALUES (1);
> +SELECT b, v FROM t1;
> +bv
> +010
> +DROP TABLE t1;
> +CREATE TABLE t1(a int DEFAULT 100, v int AS (a+1) PERSISTENT);
> +INSERT INTO t1 () VALUES ();
> +CREATE TABLE t2(a int DEFAULT 100 , v int AS (a+1));
> +INSERT INTO t2 () VALUES ();
> +SELECT a, v FROM t1;
> +av
> +100 101
> +SELECT a, v FROM t2;
> +av
> +100 101
> +DROP TABLE t1,t2;
>
> === modified file 'mysql-test/suite/vcol/t/vcol_misc.test'
> --- a/mysql-test/suite/vcol/t/vcol_misc.test 2010-07-13 17:45:23 +
> +++ b/mysql-test/suite/vcol/t/vcol_misc.test 2010-07-15 23:51:05 +
> @@ -43,5 +43,22 @@ SELECT 1 AS C FROM t1 ORDER BY v;
>
> DROP TABLE t1;
>
> +#
> +# Bug#603186: Insert for a table with stored vurtual columns
> +#
>
> +CREATE TABLE t1(a int, b int DEFAULT 0, v INT AS (b+10) PERSISTENT);
> +INSERT INTO t1(a) VALUES (1);
> +SELECT b, v FROM t1;
>
> +DROP TABLE t1;
> +
> +CREATE TABLE t1(a int DEFAULT 100, v int AS (a+1) PERSISTENT);
> +INSERT INTO t1 () VALUES ();
> +CREATE TABLE t2(a int DEFAULT 100 , v int AS (a+1));
> +INSERT INTO t2 () VALUES ();
> +
> +SELECT a, v FROM t1;
> +SELECT a, v FROM t2;
> +
> +DROP TABLE t1,t2;
>
> === modified file 'sql/sql_base.cc'
> --- a/sql/sql_base.cc 2010-06-01 19:52:20 +
> +++ b/sql/sql_base.cc 2010-07-15 23:51:05 +
> @@ -8204,6 +8204,8 @@ fill_record(THD * thd, List &field
> table->auto_increment_field_not_null= FALSE;
> f.rewind();
>}
> + else if (thd->lex->unit.insert_table_with_stored_vcol)
> +tbl_list.push_back(thd->lex->unit.insert_table_with_stored_vcol);
>while ((fld= f++))
>{
> if (!(field= fld->filed_for_view_update()))
>
> === modified file 'sql/sql_insert.cc'
> --- a/sql/sql_insert.cc 2010-06-01 19:52:20 +
> +++ b/sql/sql_insert.cc 2010-07-15 23:51:05 +
> @@ -273,7 +273,7 @@ static int check_insert_fields(THD *thd,
>}
>/* Mark virtual columns used in the insert statement */
>if (table->vfield)
> -table->mark_virtual_columns_for_write();
> +table->mark_virtual_columns_for_write(TRUE);
>// For the values we need select_priv
> #ifndef NO_EMBEDDED_ACCESS_CHECKS
>table->grant.want_privilege= (SELECT_ACL & ~table->grant.privilege);
> @@ -1267,7 +1267,6 @@ bool mysql_prepare_insert(THD *thd, TABL
>if (mysql_prepare_insert_check_table(thd, table_list, fields,
> select_insert))
> DBUG_RETURN(TRUE);
>
> -
>/* Prepare the fields in the statement. */
>if (values)
>{
> @@ -1320,6 +1319,18 @@ bool mysql_prepare_insert(THD *thd, TABL
>if (!table)
> table= table_list->table;
>
> + if (!fields.elements && table->vfield)
> + {
> +for (Field **vfield_ptr= table->vfield; *vfield_ptr; vfield_ptr++)
> +{
> + if ((*vfield_ptr)->stored_in_db)
> + {
> +thd->lex->unit.insert_table_with_stored_vcol= table;
> +break;
> + }
> +}
> + }
> +
>if (!select_insert)
>{
> Item *fake_conds= 0;
>
> === modified file 'sql/sql_lex.cc'
> --- a/sql/sql_lex.c
Re: [Maria-developers] [Fwd: [Commits] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (igor:2823) Bug#604503]
Hello Igor,
On Sat, Jul 17, 2010 at 12:42:49AM -0700, Igor Babaev wrote:
> === modified file 'sql/table.cc'
> --- a/sql/table.cc2010-07-13 14:34:14 +
> +++ b/sql/table.cc2010-07-17 07:37:48 +
> @@ -1930,8 +1930,6 @@ end:
> semantic analysis of the item by calling the the function
> fix_vcol_expr.
> Since the defining expression is part of the table definition the item
> for it is created in table->memroot within a separate Query_arena.
Please explicitly refer to TABLE::expr_arena in the above comment.
> -The free_list of this arena is saved in field->vcol_info.item_free_list
> -to be freed when the table defition is removed from the TABLE_SHARE
> cache.
>
>@note
> Before passing 'vcol_expr" to the parser the function embraces it in
...
> === modified file 'sql/table.h'
> --- a/sql/table.h 2010-06-03 09:28:54 +
> +++ b/sql/table.h 2010-07-17 07:37:48 +
> @@ -27,6 +27,7 @@ class st_select_lex;
> class partition_info;
> class COND_EQUAL;
> class Security_context;
> +class Query_arena;
>
> /*/
>
> @@ -869,6 +870,7 @@ struct st_table {
>MEM_ROOT mem_root;
>GRANT_INFO grant;
>FILESORT_INFO sort;
> + Query_arena *expr_arena;
Please add a comment saying what is the new member for.
> #ifdef WITH_PARTITION_STORAGE_ENGINE
>partition_info *part_info;/* Partition related information */
>bool no_partitions_used; /* If true, all partitions have been pruned
> away */
Ok to push after the above is addressed.
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Fwd: [Commits] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (igor:2827) Bug#607566]
Hello Igor,
Ok to push.
On Mon, Jul 19, 2010 at 10:43:18PM -0700, Igor Babaev wrote:
> Sergey,
>
> Please review this patch for the 5.2 tree.
>
> Regards,
> Igor.
>
>
> Original Message
> Subject: [Commits] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2
> branch (igor:2827) Bug#607566
> Date: Mon, 19 Jul 2010 22:41:37 -0700 (PDT)
> From: Igor Babaev
> Reply-To: maria-developers@lists.launchpad.net
> To: comm...@mariadb.org
>
> #At lp:maria/5.2 based on
> revid:i...@askmonty.org-20100717195808-mvh782jvt6c32u2d
>
> 2827 Igor Babaev 2010-07-19
> Fixed bug #607566.
> For queries with order by clauses that employed filesort usage of
> virtual column references in select lists could trigger assertion
> failures. It happened because a wrong vcol_set bitmap was used for
> filesort. It turned out that filesort required its own vcol_set
> bitmap.
>
> Made management of the vcol_set bitmaps similar to the management
> of the read_set and write_set bitmaps.
> modified:
> mysql-test/suite/vcol/r/vcol_misc.result
> mysql-test/suite/vcol/t/vcol_misc.test
> sql/field.cc
> sql/filesort.cc
> sql/sql_insert.cc
> sql/sql_select.cc
> sql/table.cc
> sql/table.h
>
> === modified file 'mysql-test/suite/vcol/r/vcol_misc.result'
> --- a/mysql-test/suite/vcol/r/vcol_misc.result2010-07-17 19:58:08
> +
> +++ b/mysql-test/suite/vcol/r/vcol_misc.result2010-07-20 05:41:24
> +
> @@ -87,3 +87,13 @@ a v
> 2002-02-15 00:00:00 0
> 2000-10-15 00:00:00 1
> DROP TABLE t1, t2;
> +CREATE TABLE t1 (
> +a char(255), b char(255), c char(255), d char(255),
> +v char(255) AS (CONCAT(c,d) ) VIRTUAL
> +);
> +INSERT INTO t1(a,b,c,d) VALUES ('w','x','y','z'), ('W','X','Y','Z');
> +SELECT v FROM t1 ORDER BY CONCAT(a,b);
> +v
> +yz
> +YZ
> +DROP TABLE t1;
>
> === modified file 'mysql-test/suite/vcol/t/vcol_misc.test'
> --- a/mysql-test/suite/vcol/t/vcol_misc.test 2010-07-17 19:58:08 +
> +++ b/mysql-test/suite/vcol/t/vcol_misc.test 2010-07-20 05:41:24 +
> @@ -87,3 +87,18 @@ INSERT INTO t2(a) VALUES ('2000-10-15');
> SELECT * FROM t2;
>
> DROP TABLE t1, t2;
> +
> +#
> +# Bug#607566: Virtual column in the select list of SELECT with ORDER BY
> +#
> +
> +CREATE TABLE t1 (
> + a char(255), b char(255), c char(255), d char(255),
> + v char(255) AS (CONCAT(c,d) ) VIRTUAL
> +);
> +
> +INSERT INTO t1(a,b,c,d) VALUES ('w','x','y','z'), ('W','X','Y','Z');
> +
> +SELECT v FROM t1 ORDER BY CONCAT(a,b);
> +
> +DROP TABLE t1;
>
> === modified file 'sql/field.cc'
> --- a/sql/field.cc2010-06-01 19:52:20 +
> +++ b/sql/field.cc2010-07-20 05:41:24 +
> @@ -57,7 +57,7 @@ const char field_separator=',';
> ((ulong) ((LL(1) << min(arg, 4) * 8) - LL(1)))
>
> #define ASSERT_COLUMN_MARKED_FOR_READ DBUG_ASSERT(!table ||
> (!table->read_set || bitmap_is_set(table->read_set, field_index)))
> -#define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED DBUG_ASSERT(!table
> || (!table->write_set || bitmap_is_set(table->write_set, field_index) ||
> bitmap_is_set(&table->vcol_set, field_index)))
> +#define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED DBUG_ASSERT(!table
> || (!table->write_set || bitmap_is_set(table->write_set, field_index) ||
> bitmap_is_set(table->vcol_set, field_index)))
>
> /*
>Rules for merging different types of fields in UNION
>
> === modified file 'sql/filesort.cc'
> --- a/sql/filesort.cc 2010-07-17 19:58:08 +
> +++ b/sql/filesort.cc 2010-07-20 05:41:24 +
> @@ -515,7 +515,7 @@ static ha_rows find_all_keys(SORTPARAM *
>THD *thd= current_thd;
>volatile THD::killed_state *killed= &thd->killed;
>handler *file;
> - MY_BITMAP *save_read_set, *save_write_set;
> + MY_BITMAP *save_read_set, *save_write_set, *save_vcol_set;
>DBUG_ENTER("find_all_keys");
>DBUG_PRINT("info",("using: %s",
> (select ? select->quick ? "ranges" : "where":
> @@ -552,6 +552,7 @@ static ha_rows find_all_keys(SORTPARAM *
>/* Remember original bitmaps */
>save_read_set= sort_form->read_set;
>save_write_set= sort_form->write_set;
> + save_vcol_set= sort_form->vcol_set;
>/* Set up temporary column read map for columns used by sort */
>bitmap_clear_all(&sort_form->tmp_set);
>/* Temporary set for register_used_fields and
> register_field_in_read_map */
> @@ -560,7 +561,8 @@ static ha_rows find_all_keys(SORTPARAM *
>if (select && select->cond)
> select->cond->walk(&Item::register_field_in_read_map, 1,
> (uchar*) sort_form);
> - sort_form->column_bitmaps_set(&sort_form->tmp_set, &sort_form->tmp_set);
> + sort_form->column_bitmaps_set(&sort_form->tmp_set, &sort_form->tmp_set,
> +&sort_form->tmp_set);
>
>for (;;)
>{
> @@ -643,7 +645,7 @@ static ha_rows find_all_keys(SORTPARAM *
> DBUG_RETURN(HA_POS_ERROR);
>
>/* Signal we should use orignal
Re: [Maria-developers] [Fwd: [Commits] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (igor:2827) Bug#607177]
Hello Igor,
Ok to push.
On Tue, Jul 20, 2010 at 10:01:30PM -0700, Igor Babaev wrote:
> Sergey,
>
> Please review this trivial patch for the 5.2 tree.
>
> Regards,
> Igor.
>
> Original Message
> Subject: [Commits] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2
> branch (igor:2827) Bug#607177
> Date: Tue, 20 Jul 2010 22:00:00 -0700 (PDT)
> From: Igor Babaev
> Reply-To: maria-developers@lists.launchpad.net
> To: comm...@mariadb.org
>
> #At lp:maria/5.2 based on
> revid:i...@askmonty.org-20100717195808-mvh782jvt6c32u2d
>
> 2827 Igor Babaev 2010-07-20
> Fixed bug #607177.
> Due to an invalid check for NULL of the second argument of the
> Item_func_round items performed in the code of
> Item_func_round::real_op
> the function ROUND sometimes could return wrong results.
> modified:
> mysql-test/suite/vcol/r/vcol_misc.result
> mysql-test/suite/vcol/t/vcol_misc.test
> sql/item_func.cc
>
> === modified file 'mysql-test/suite/vcol/r/vcol_misc.result'
> --- a/mysql-test/suite/vcol/r/vcol_misc.result2010-07-17 19:58:08
> +
> +++ b/mysql-test/suite/vcol/r/vcol_misc.result2010-07-21 04:59:47
> +
> @@ -87,3 +87,23 @@ a v
> 2002-02-15 00:00:00 0
> 2000-10-15 00:00:00 1
> DROP TABLE t1, t2;
> +CREATE TABLE t1 (p int, a double NOT NULL, v double AS (ROUND(a,p))
> VIRTUAL);
> +INSERT INTO t1 VALUES (0,1,0);
> +Warnings:
> +Warning 1645The value specified for computed column 'v' in table
> 't1'
> ignored
> +INSERT INTO t1 VALUES (NULL,0,0);
> +Warnings:
> +Warning 1645The value specified for computed column 'v' in table
> 't1'
> ignored
> +SELECT a, p, v, ROUND(a,p), ROUND(a,p+NULL) FROM t1;
> +ap v ROUND(a,p) ROUND(a,p+NULL)
> +10 1 1 NULL
> +0NULLNULLNULLNULL
> +DROP TABLE t1;
> +CREATE TABLE t1 (p int, a double NOT NULL);
> +INSERT INTO t1(p,a) VALUES (0,1);
> +INSERT INTO t1(p,a) VALUES (NULL,0);
> +SELECT a, p, ROUND(a,p), ROUND(a,p+NULL) FROM t1;
> +ap ROUND(a,p) ROUND(a,p+NULL)
> +10 1 NULL
> +0NULLNULLNULL
> +DROP TABLE t1;
>
> === modified file 'mysql-test/suite/vcol/t/vcol_misc.test'
> --- a/mysql-test/suite/vcol/t/vcol_misc.test 2010-07-17 19:58:08 +
> +++ b/mysql-test/suite/vcol/t/vcol_misc.test 2010-07-21 04:59:47 +
> @@ -87,3 +87,19 @@ INSERT INTO t2(a) VALUES ('2000-10-15');
> SELECT * FROM t2;
>
> DROP TABLE t1, t2;
> +
> +#
> +# Bug#607177: ROUND function in the expression for a virtual function
> +#
> +
> +CREATE TABLE t1 (p int, a double NOT NULL, v double AS (ROUND(a,p))
> VIRTUAL);
> +INSERT INTO t1 VALUES (0,1,0);
> +INSERT INTO t1 VALUES (NULL,0,0);
> +SELECT a, p, v, ROUND(a,p), ROUND(a,p+NULL) FROM t1;
> +DROP TABLE t1;
> +
> +CREATE TABLE t1 (p int, a double NOT NULL);
> +INSERT INTO t1(p,a) VALUES (0,1);
> +INSERT INTO t1(p,a) VALUES (NULL,0);
> +SELECT a, p, ROUND(a,p), ROUND(a,p+NULL) FROM t1;
> +DROP TABLE t1;
>
> === modified file 'sql/item_func.cc'
> --- a/sql/item_func.cc2010-06-01 19:52:20 +
> +++ b/sql/item_func.cc2010-07-21 04:59:47 +
> @@ -2040,10 +2040,12 @@ double Item_func_round::real_op()
> {
>double value= args[0]->val_real();
>
> - if (!(null_value= args[0]->null_value || args[1]->null_value))
> -return my_double_round(value, args[1]->val_int(),
> args[1]->unsigned_flag,
> - truncate);
> -
> + if (!(null_value= args[0]->null_value))
> + {
> +longlong dec= args[1]->val_int();
> +if (!(null_value= args[1]->null_value))
> + return my_double_round(value, dec, args[1]->unsigned_flag, truncate);
> + }
>return 0.0;
> }
>
>
> ___
> commits mailing list
> comm...@mariadb.org
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
--
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Commits] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (igor:2869) Bug#52005
Hello Igor, Ok to push. I'm sorry for the delay. On Sun, Jul 25, 2010 at 10:50:03PM -0700, Igor Babaev wrote: > #At lp:maria based on revid:mo...@askmonty.org-20100615220051-2xp3g51fysxle1r1 > > 2869 Igor Babaev 2010-07-25 > Fixed bug #52005. > Corrected coding for Warshall's algorithm. > modified: > mysql-test/r/join_outer.result > mysql-test/t/join_outer.test > sql/sql_select.cc > > === modified file 'mysql-test/r/join_outer.result' > --- a/mysql-test/r/join_outer.result 2010-03-19 06:21:37 + > +++ b/mysql-test/r/join_outer.result 2010-07-26 05:49:51 + > @@ -1308,4 +1308,63 @@ WHERE (COALESCE(t1.f1, t2.f1), f3) IN (( > f1 f2 f3 f1 f2 > 1NULL3 NULLNULL > DROP TABLE t1, t2; > +# > +# Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result > +# > +CREATE TABLE t1 (f1 INT NOT NULL); > +INSERT INTO t1 VALUES (9),(0); > +CREATE TABLE t2 (f1 INT NOT NULL); > +INSERT INTO t2 VALUES > +(5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1); > +SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1 > +RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1; > +COUNT(*) > +476 > +EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1 > +RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1; > +id select_type table typepossible_keys key key_len ref > rowsExtra > +1SIMPLE TA2 ALL NULLNULLNULLNULL20 Using > where > +1SIMPLE TA3 ALL NULLNULLNULLNULL20 Using > join buffer > +1SIMPLE TA1 ALL NULLNULLNULLNULL2 > +DROP TABLE t1, t2; > +# > +# Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990 > +# > +CREATE TABLE t1(f1 INT, PRIMARY KEY (f1)); > +INSERT INTO t1 VALUES (1),(2); > +EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1 > +LEFT JOIN t1 AS jt2 > +RIGHT JOIN t1 AS jt3 > +JOIN t1 AS jt4 ON 1 > +LEFT JOIN t1 AS jt5 ON 1 > +ON 1 > +RIGHT JOIN t1 AS jt6 ON jt6.f1 > +ON 1; > +id select_type table typepossible_keys key key_len ref > rowsfilteredExtra > +1SIMPLE jt1 index NULLPRIMARY 4 NULL2 100.00 > Using index > +1SIMPLE jt6 index NULLPRIMARY 4 NULL2 100.00 > Using index > +1SIMPLE jt3 index NULLPRIMARY 4 NULL2 100.00 > Using index > +1SIMPLE jt4 index NULLPRIMARY 4 NULL2 100.00 > Using index > +1SIMPLE jt5 index NULLPRIMARY 4 NULL2 100.00 > Using index > +1SIMPLE jt2 index NULLPRIMARY 4 NULL2 100.00 > Using index > +Warnings: > +Note 1003select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` > `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join > `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` > `jt2` on(1)) on((`test`.`jt6`.`f1` and 1))) on(1) where 1 > +EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1 > +RIGHT JOIN t1 AS jt2 > +RIGHT JOIN t1 AS jt3 > +JOIN t1 AS jt4 ON 1 > +LEFT JOIN t1 AS jt5 ON 1 > +ON 1 > +RIGHT JOIN t1 AS jt6 ON jt6.f1 > +ON 1; > +id select_type table typepossible_keys key key_len ref > rowsfilteredExtra > +1SIMPLE jt6 index NULLPRIMARY 4 NULL2 100.00 > Using index > +1SIMPLE jt3 index NULLPRIMARY 4 NULL2 100.00 > Using index > +1SIMPLE jt4 index NULLPRIMARY 4 NULL2 100.00 > Using index > +1SIMPLE jt5 index NULLPRIMARY 4 NULL2 100.00 > Using index > +1SIMPLE jt2 index NULLPRIMARY 4 NULL2 100.00 > Using index > +1SIMPLE jt1 index NULLPRIMARY 4 NULL2 100.00 > Using index > +Warnings: > +Note 1003select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` > `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join > `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) > on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1 > +DROP TABLE t1; > End of 5.1 tests > > === modified file 'mysql-test/t/join_outer.test' > --- a/mysql-test/t/join_outer.test2010-03-19 06:21:37 + > +++ b/mysql-test/t/join_outer.test2010-07-26 05:49:51 + > @@ -913,4 +913,48 @@ WHERE (COALESCE(t1.f1, t2.f1), f3) IN (( > > DROP TABLE t1, t2; > > +--echo # > +--echo # Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result > +--echo # > +CREATE TABLE t1 (f1 INT NOT NULL); > +INSERT INTO t1 VALUES (9),(0); > + > +CREATE TABLE t2 (f1 INT NOT NULL); > +INSERT INTO t2 VALUES > +(5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1); > + > +SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1 > +RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1; > + > +EXPLAIN SELE
Re: [Maria-developers] DS-MRR improvements patch ready for review
Hi Philip, On Fri, Aug 20, 2010 at 09:12:19AM +0200, Sergey Petrunya wrote: > Please find attached the combined patch of DS-MRR for clustered PKs and key > sorting. > > The tree is in launchpad and buildbot also: > https://code.launchpad.net/~maria-captains/maria/5.3-dsmrr-cpk > > and all observed buildbot failures in the tree are known to occur without the > new code as well. The above mentioned tree has DS-MRR improvements that are ready for testing. Overview of new functionality - The tree has implementations of MWL#121, MWL#123, MWL#124, MWL#125. All of those are additional sub-strategies of DS-MRR. They are only applicable when MRR is used by BKA (and not by range access). Checking if new code is used Execution passes through the new code if the EXPLAIN has tables that have type=[eq_]ref, Extra has 'Using join buffer'. Control --- Both before and after this work: BKA is not enabled by default, see http://askmonty.org/wiki/Manual:Batched_Key_Access for values of @@join_cache_level that enable it. There is no way to completely turn on/off the new behaviour. However, one can - Run SET join_cache_level=0 and then re-run the query without join buffering, which will give you the correct result (but rows most likely will be in different order). - Run the query with bigger/smaller value of @@join_buffer_size (this is where I've found and fixed a big number of problems already). - Compare with lp:~maria-captains/maria/5.3. 5.3-dsmrr-cpk has been recently merged with 5.3, the only difference between them is the code that needs to be tested. What to test (first, see above for how to hit the new code). Suppose we have the following query (to fix table names): MariaDB [j3]> explain select * from t0,t1 where t1.a=t0.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| ref | key1 | key1 | 5 | j3.t0.a | 1 | Using join buffer | ++-+---+--+---+--+-+-+--+---+ 2 rowe in set (0.01 sec) Then, interesting cases to test are: - where for various records of t0, table t1 has zero, one, or multiple matches. - where table t1 is an innodb table and is accessed through [a prefix of a] clustered primary key. - where t1.key1 is a simple/composite key made of different types (but this is more of a checklist test) - where the join buffer is exhausted at various stages of accessing table t1 (need to try with different key sizes, join buffer sizes, and many-table joins) BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Automatic annotated code coverage reports for MariaDB
Hi Philip, On Mon, Sep 27, 2010 at 08:17:57PM +0300, Philip Stoev wrote: > I have now managed to make buildbot produce automatic LCOV reports for > MariaDB. You can view them here > > http://fedora13.selfip.org/lcov/ I looked, and noticed a problem: this coverage generator ignores markup like 'purecov: inspected', 'purecov: deadcode' and 'purecov begin/end inspected/deadcode'. This means it generates a lot of false positives. > The buildbot host is capable of building any other launchpad-based tree, > so if you require lcov reports for a particular feature or storage engine > that you are developing, please drop me a note and I will configure > reports to be generated for your tree as well. Could we have lp:~maria-captains/maria/5.3-dsmrr-cpk there, please? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Commits] Rev 2827: Ported the fix for bug #57024 (a performance issue for outer joins). in file:///home/igor/maria/maria-5.3-mwl128-bug57024/
Hello Igor,
Please find some minor comments below. Ok to push after they are addressed.
On Wed, Oct 06, 2010 at 05:14:08AM -0700, Igor Babaev wrote:
> At file:///home/igor/maria/maria-5.3-mwl128-bug57024/
>
>
> revno: 2827
> revision-id: i...@askmonty.org-20101006121408-ra44i6o7eich5hrf
> parent: i...@askmonty.org-20101004014546-xirepv04xs7txxhw
> committer: Igor Babaev
> branch nick: maria-5.3-mwl128-bug57024
> timestamp: Wed 2010-10-06 05:14:08 -0700
> message:
> Ported the fix for bug #57024 (a performance issue for outer joins).
> Employed the same kind of optimization as in the fix for the cases
> when join buffer is used.
> The optimization performs early evaluation of the conditions from
> on expression with table references to only outer tables of
> an outer join.
...
> === modified file 'sql/sql_join_cache.cc'
> @@ -1604,6 +1625,12 @@
>uchar *init_pos= pos;
>CACHE_FIELD *copy= field_descr;
>CACHE_FIELD *copy_end= copy+flag_fields;
> + if (with_match_flag)
> + {
> +copy->str[0]= test((Match_flag) pos[0] == MATCH_FOUND);
> +pos+= copy->length;
> +copy++;
> + }
>for ( ; copy < copy_end; copy++)
>{
> memcpy(copy->str, pos, copy->length);
TODO where does the above copy to?
> @@ -1754,30 +1781,68 @@
>
>
> /*
> - Skip record from join buffer if its match flag is on: default
> implementation
> -
> - SYNOPSIS
> -skip_recurrent_match()
> -
> - DESCRIPTION
> -This default implementation of the virtual function skip_record_if_match
> -skips the next record from the join buffer if its match flag is set on.
> -If the record is skipped the value of 'pos' is set to points to the
> position
> -right after the record.
> -
> - RETURN VALUE
> -TRUEthe match flag is on and the record has been skipped
> -FALSE the match flag is off
> -*/
> -
> -bool JOIN_CACHE::skip_recurrent_match()
> -{
> - DBUG_ASSERT(with_length);
> - uint offset= size_of_rec_len;
> - if (prev_cache)
> -offset+= prev_cache->get_size_of_rec_offset();
> - /* Check whether the match flag is on */
> - if (get_match_flag_by_pos(pos+offset))
> + Skip record from join buffer if's already matched: default implementation
> +
> + SYNOPSIS
> +skip_as_matched()
> +
> + DESCRIPTION
> +This default implementation of the virtual function skip_as_matched
> +skips the next record from the join buffer if its match flag is set to
> 1.
> +If the record is skipped the value of 'pos' is set to points to the
> position
> +right after the record.
> +
> + RETURN VALUE
> +TRUEthe match flag is set to 1 and the record has been skipped
> +FALSE the match flag is not 1
Please s/1/MATCH_FOUND/
> +*/
> +
> +bool JOIN_CACHE::skip_as_matched()
Please rename the function to skip_if_matched(), because that what it actually
does: checks *if* the records is matched and skips it if it is so.
> +{
> + DBUG_ASSERT(with_length);
> + uint offset= size_of_rec_len;
> + if (prev_cache)
> +offset+= prev_cache->get_size_of_rec_offset();
> + /* Check whether the match flag is MATCH_FOUND */
> + if (get_match_flag_by_pos(pos+offset) == MATCH_FOUND)
> + {
> +pos+= size_of_rec_len + get_rec_length(pos);
> +return TRUE;
> + }
> + return FALSE;
> +}
> +
> +
> +/*
> + Skip record from join buffer if the match isn't needed: default
> implementation
> +
> + SYNOPSIS
> +skip_as_unneeded_match()
> +
> + DESCRIPTION
> +This default implementation of the virtual function
> skip_as_unneeded_match
> +skips the next record from the join buffer if its match flag is not
> +MATCH_NOT_FOUND, and, either its value is MATCH_FOUND and join_tab is the
> +first inner table of an inner join, or, its value is MATCH_IMPOSSIBLE
> +and join_tab is the first inner table of an outer join.
> +If the record is skipped the value of 'pos' is set to points to the
> position
Typo, s/points/point/
> +right after the record.
> +
> + RETURN VALUE
> +TRUEthe record has to be been skipped
^^^
wrong grammar.
> +FALSE otherwise
> +*/
> +
> +bool JOIN_CACHE::skip_as_unneeded_match()
> +{
> + DBUG_ASSERT(with_length);
> + enum Match_flag match_fl;
> + uint offset= size_of_rec_len;
> + if (prev_cache)
> +offset+= prev_cache->get_size_of_rec_offset();
> +
> + if ((match_fl= get_match_flag_by_pos(pos+offset)) != MATCH_NOT_FOUND &&
> + (join_tab->check_only_first_match() == (match_fl == MATCH_FOUND)) )
>{
> pos+= size_of_rec_len + get_rec_length(pos);
> return TRUE;
> @@ -1990,9 +2055,9 @@
> {
>int error;
>enum_nested_loop_state rc= NESTED_LOOP_OK;
> + join_tab->table->null_row= 0;
>bool check_only_first_match= join_tab->check_only_first_match();
> -
> - join_tab->table->null_row= 0;
> + bool outer_join_first_inner= join_tab->is_first_inner_for_outer_join();
>
>/* Return at once
Re: [Maria-developers] DS-MRR improvements patch r3 ready for review
On Mon, Oct 04, 2010 at 10:54:37AM +0400, Sergey Petrunya wrote: > Hello Igor, > > Please find attached the combined patch that addresses all of the review > feedback provided so far. > > The tree is in launchpad and buildbot also: > https://code.launchpad.net/~maria-captains/maria/5.3-dsmrr-cpk > [taking notes] This time, the review feedback is: - Add comments to long functions - Look at adding 4 classes, corresponding to boxes in the diagrams. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Performance problem: need index-based access when have only non-sargable conditions
Hi!
I was asked about this performance problem: consider a query
select * from tbl where tbl.key like '%foo%'
Table records are big, much larger than the length of the key. LIKE condition
is very selective, however its pattern starts with '%', so we can't construct
range access (and they are really searching for matches in word
suffixes/infixes, so fulltext index isn't an option).
The desired execution strategy is:
- Scan the index on tbl.key and collect index tuples that match the LIKE
condition.
- For those index tuples, retrieve full records.
One can simulate it by rewriting the query as a self join:
select * from tbl A, tbl B
where
B.primary_key = A.primary_key AND B.key LIKE '%foo%';
However, in this particular case they have a problem doing such rewrites
because the queries are generated by some ORM tool.
Proposed solution from our side
---
It is not difficult to add an execution strategy like this
get_next_record()
{
read index tuple;
if (condition satisified)
{
read full record
}
}
(with optional rowid-ordered retrieval for full records).
The difficult part is to find a way to decided whether this strategy should be
used. We have no meaningful selectivity estimates for 'column LIKE '%foo%'.
The only way we could achieve this is by having the user manually specify
condition selectivities. I can see one way to achieve this without getting into
painful syntax modifications:
select * from tbl where selectivity(tbl.key like '%foo%', 0.05)
here 'selectivity(X,y)' will evaluate to value of X, but will also inform the
optimizer that P(X is true)=y.
Any thoughts about this?
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
[Maria-developers] Buildbot builds/tests innodb_plugin.so, is that intentional?
Hello, I've noticed that centos5-amd64-minimal builds innodb plugin for MariaDB 5.2 and 5.3 (didn't check 5.1) For example, if one takes this build http://buildbot.askmonty.org/buildbot/builders/centos5-amd64-minimal/builds/706/ and looks at compliation log http://buildbot.askmonty.org/buildbot/builders/centos5-amd64-minimal/builds/706/steps/compile/logs/stdio they'll find: (cd .libs && rm -f ha_innodb_plugin.so.0 && ln -s ha_innodb_plugin.so.0.0.0 ha_innodb_plugin.so.0) (cd .libs && rm -f ha_innodb_plugin.so && ln -s ha_innodb_plugin.so.0.0.0 ha_innodb_plugin.so) ar cru .libs/ha_innodb_plugin.a ... ranlib .libs/ha_innodb_plugin.a creating ha_innodb_plugin.la (cd .libs && rm -f ha_innodb_plugin.la && ln -s ../ha_innodb_plugin.la ha_innodb_plugin.la) make[2]: Leaving directory `/home/buildbot/maria-slave/centos5-amd64-minimal/build/mysql-5.3.0-MariaDB-alpha/storage/innodb_plugin' Which shows we're building the plugin. Do we really need this? (I've discovered this by accident - in 5.3 it now runs innodb_plugin testsuite both with xtradb and innodb_plugin, and we've got a problem as xtradb includes index condition pushdown and xtradb doesn't, which causes them to produce different EXPLAIN outputs: when the test is run with xtradb, we get 'Using index condition', and with innodb_plugin we get 'Using where'). BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] subselect_cache fails
Hi Sanja, subselect_cache fails on sol-sparc-32 in current 5.3-merge-from-5.2: http://buildbot.askmonty.org/buildbot/builders/sol-sparc-32/builds/575/steps/test/logs/stdio Any idea about this? Have we observed such failure before or it has appeared in the merge? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] On possible bug in get_partial_join_cost()
Hi Timour, Answers to irc questions: spetrunia, I found a problem in get_partial_join_cost(), could you please look at this: for (uint i= join->const_tables; i < n_tables + join->const_tables ; i++) spetrunia, why "i < n_tables + join->const_tables" ? isn't the number of tables always the same? That is, shouldn't we just skip the constant tables assuming they are in the beginning of join->best_positions ? Yes we should skip the constant tables, and they are at the beginning of best_positions. The number of tables passed as n_tables varies across different invocations of get_partial_join_cost(): uint n_tables= my_count_bits(sj_nest->sj_inner_tables & ~join->const_table_map); SJ_MATERIALIZATION_INFO* sjm; if (!(sjm= new SJ_MATERIALIZATION_INFO) || !(sjm->positions= (POSITION*)join->thd->alloc(sizeof(POSITION)* n_tables))) DBUG_RETURN(TRUE); /* purecov: inspected */ sjm->tables= n_tables; sjm->is_used= FALSE; double subjoin_out_rows, subjoin_read_time; get_partial_join_cost(join, n_tables, &subjoin_read_time, &subjoin_out_rows); Here one can see that n_tables is "number of non-constant tables within an SJ-Materialization nest". Different nests will contain different numbers of tables. spetrunia, I changed the loop upper boundary to n_tables, then I get few improved EXPLAINS, and got a crash, then I had to leave. I think the change is wrong: if you stop at n_tables, that means that partial_join_cost() has done optimization for the first (n_tables - join->const_tables) non-constant tables, which is not right. If you need further help, could you give a concrete example that causes the problem? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Commits] Rev 2840: Fixed LP bug #668290. in file:///home/igor/maria/maria-5.3-mwl128-bug668290/
Hello Igor,
Plese find the comments below.
On Sat, Oct 30, 2010 at 12:56:10PM -0700, Igor Babaev wrote:
> At file:///home/igor/maria/maria-5.3-mwl128-bug668290/
>
>
> revno: 2840
> revision-id: i...@askmonty.org-20101030195609-h53be2cdzjlz38bz
> parent: i...@askmonty.org-20101030130745-563ypxtkcwv4vbfg
> committer: Igor Babaev
> branch nick: maria-5.3-mwl128-bug668290
> timestamp: Sat 2010-10-30 12:56:09 -0700
> message:
> Fixed LP bug #668290.
> Prohibited to use hash join algorithm BNLH if join attributes
> need non-binary collations. It has to be done because BNLH does
> not support join for such attributes yet.
> Later this limitations will be lifted.
>
> Changed default collations for the schemes of some test cases
> to preserve the old execution plans.
...
> === modified file 'sql/field.h'
> --- a/sql/field.h 2010-09-23 22:00:32 +
> +++ b/sql/field.h 2010-10-30 19:56:09 +
> @@ -585,6 +585,12 @@
>}
>/* Hash value */
>virtual void hash(ulong *nr, ulong *nr2);
> +
> + virtual bool hash_join_is_possible(bool globally_allowed)
> + {
> +return globally_allowed;
> + }
Is there any partiular reason why have this globally_allowed parameter?
In my understanding, a field either allows hash or not, and that is
regardless of whether it was "globally allowed", so the check of whether
hash join is globally allowed should be done outside of the function.
Also, it would be nice to have here a comment specifying the meaning of this
function. (I know all this is supposed to be temporary, but expirience shows
that often supposedly temporary things stay for a long time, that's why it's
nice to put a comment).
> +
>friend bool reopen_table(THD *,struct st_table *,bool);
>friend int cre_myisam(char * name, register TABLE *form, uint options,
> ulonglong auto_increment_value);
> @@ -760,6 +766,12 @@
>my_decimal *val_decimal(my_decimal *);
>virtual bool str_needs_quotes() { return TRUE; }
>uint is_equal(Create_field *new_field);
> +
> + bool hash_join_is_possible(bool globally_allowed)
> + {
> +/* TODO: support hash joins for non-binary collations */
> +return globally_allowed && (flags & BINARY_FLAG);
> + }
> };
>
>
> @@ -1904,6 +1916,7 @@
>uint size_of() const { return sizeof(*this); }
>int reset(void) { return !maybe_null() || Field_blob::reset(); }
>geometry_type get_geometry_type() { return geom_type; };
> + bool hash_join_is_possible(bool globally_allowed) { return FALSE; }
> };
> #endif /*HAVE_SPATIAL*/
>
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2010-10-30 13:07:45 +
> +++ b/sql/sql_select.cc 2010-10-30 19:56:09 +
> @@ -3547,7 +3547,7 @@
> /*
>Additional optimization: if we're processing
>"t.key BETWEEN c1 AND c1" then proceed as if we were processing
> - "t.key = c1".
> + "t.key = c1".sis
>TODO: This is a very limited fix. A more generic fix is possible.
Is the above a meaningful change?
>There are 2 options:
>A) Make equality propagation code be able to handle BETWEEN
> @@ -5946,6 +5946,44 @@
> }
>
>
> +/**
> + @brief
> + Check whether hash join algorithm can be used to join this table
> +
> + @param is_allowed usage of hash join is allowed
> +
> + @details
> + This function finds out whether the ref items that have been chosen
> + by the planner to access this table can be used for hash join algorithms.
> + The answer depends on a certain property of the the fields of the
> + joined tables on which the hash join key is built. If hash join is
> + allowed for all these fields the answer is positive unless hash join
> + algorithms are not allowed for the query at all.
> +
> + @note
> + The function is supposed to be called now only after the function
> + get_best_combination has been called.
> +
> + @retval TRUEit's possible to use hash join to join this table
> + @retval FALSE otherwise
> +*/
> +
> +bool JOIN_TAB::hash_join_is_possible(bool is_allowed)
> +{
> + if (!is_allowed)
> +return FALSE;
Same as with globally_allowed. I think the parameter is redundant and rather
confusing.
> + if (type != JT_REF && type != JT_EQ_REF)
> +return FALSE;
> + KEY *keyinfo= &table->key_info[ref.key];
> + for (uint i= 0; i < ref.key_parts; i++)
> + {
> +if (!(keyinfo->key_part[i].field->hash_join_is_possible(TRUE)))
> + return FALSE;
> + }
> + return TRUE;
> +}
> +
> +
> static uint
> cache_record_length(JOIN *join,uint idx)
> {
> @@ -7649,8 +7687,10 @@
>&bufsz, &flags, &cost);
>
> if ((cache_level <=4 && !no_hashed_cache) || no_bka_cache ||
> - (flags & HA_MRR_NO_ASSOCIATION) && cache_level <=6)
> + ((flags & HA_MRR_NO_ASSOCIATION) && cache_level <=6))
> {
> +
[Maria-developers] RQG
Hi Philip, On Tue, Oct 26, 2010 at 03:34:41PM +0300, Philip Stoev wrote: >> - Our chances of making Random Query Generator a little bit friendlier for >> bug analyzers/fixers. > > Hi, > > Can you list specific issues that you are having or specific bug numbers > that were not filed properly? I can't say that any bugs were filed improperly. All ones I've dealt with so far were 'proper', i.e. contained sufficient amount of info for me to reproduce and work on the bug. However, it seems there are some possible improvements that could be made (and it makes sense to do them because currently bugfixing is the bottleneck, not testing) > I already have a list of things from Igor, so if you give me your list, I > will try to dedicate specific time to make things more user-friendly. Here it goes: - Backtick quotes are redundant and must be gone. I've never seen a single RQG-reported bug where the quotes were really necessary. Yet, they are there and make the queries longer than necessary. We have to manually delete them. - Redundant spaces are also annoying, a typical excerpt looks like (all spaces kept): table1 . `col_varchar_key` ) which, again, also forces developer to delete all of the spaces manually. - It would be nice if tables were called t0,t1,t2,... as mysql-test-run guidelines specify - It would be nice if testcase simplification removed irrelevant columns from the tables. If it is not possible to remove them (I guess the reason is that the table size changes and the optimizer choses different QEP), then they should be replaced with one CHAR(N) column. - I understand the following might be difficult to achieve: it would be extremely nice if the SELECT statement that caused the crash is was pretty-printed (i.e. with identation that would make the structure of FROM/WHERE clauses apparent). If this is too difficult, no need to spend time on this. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] 5.3 compilation warnings
Hi Timour, When one compiles 5.3, it produces the following warnings which I believe fall into your turf. Could you please fix them: item_subselect.cc: In member function ‘bool subselect_rowid_merge_engine::init(MY_BITMAP*, MY_BITMAP*)’: item_subselect.cc:4826: warning: ignoring return value of ‘int handler::ha_rnd_init(bool)’, declared with attribute warn_unused_result item_subselect.cc: In member function ‘virtual bool subselect_rowid_merge_engine::partial_match()’: item_subselect.cc:5007: warning: ignoring return value of ‘int handler::ha_rnd_init(bool)’, declared with attribute warn_unused_result item_subselect.cc: In member function ‘virtual bool subselect_table_scan_engine::partial_match()’: item_subselect.cc:5174: warning: ignoring return value of ‘int handler::ha_rnd_init(bool)’, declared with attribute warn_unused_result sql_class.cc: In member function ‘virtual bool select_materialize_with_stats::create_result_table(THD*, List*, bool, ulonglong, const char*, bool)’: sql_class.cc:3020: warning: the address of ‘int stat(const char*, stat*)’ will always evaluate as ‘true’ BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Tree with DS-MRR improvements ready for testing
Hi Philip, The combined tree with DS-MRR improvements and MWL#128 is ready for testing. It is located at lp:~maria-captains/maria/maria-5.3-mwl128-dsmrr-cpk/ . I don't know how hard that is for you, but it would be useful to check the failures - against 5.3-main tree (to see whether the problem is in the new code or not) - against lp:~maria-captains/maria-5.3-mwl128 (to see whether the problem is in MWL#128 or in the DS-MRR code) BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Commits] Rev 2845: Fixed LP bug #664594 and other bugs leading to invalid execution in file:///home/igor/maria/maria-5.3-mwl128/
Hello Igor, Ok to push. On Wed, Nov 03, 2010 at 12:26:19PM -0700, Igor Babaev wrote: > At file:///home/igor/maria/maria-5.3-mwl128/ > > > revno: 2845 > revision-id: i...@askmonty.org-20101103192618-17ii8dyn1h2qzdy8 > parent: i...@askmonty.org-20101102235032-vh451jmuugv1gsr2 > committer: Igor Babaev > branch nick: maria-5.3-mwl128 > timestamp: Wed 2010-11-03 12:26:18 -0700 > message: > Fixed LP bug #664594 and other bugs leading to invalid execution > plans or wrong results due to the fact that JOIN_CACHE functions > ignored the possibility of interleaving materialized semijoin > tables with tables whose records were stored in join buffers. > This fixes would become mostly unnecessary if the new code of > mwl 90 was merged into 5.3 right now. > Yet the fix the code of optimize_wo_join_buffering was needed > in any case. > === modified file 'mysql-test/r/explain.result' > --- a/mysql-test/r/explain.result 2010-10-27 23:31:22 + > +++ b/mysql-test/r/explain.result 2010-11-03 19:26:18 + > @@ -195,16 +195,16 @@ > flush tables; > EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM > t2 AS INNR WHERE OUTR.dt IS NULL ); > id select_type table typepossible_keys key key_len ref > rowsExtra > -1PRIMARY OUTRALL NULLNULLNULLNULL2 Using > where > -1PRIMARY INNRALL NULLNULLNULLNULL2 Using > where; FirstMatch(OUTR) > +1PRIMARY OUTRALL NULLNULLNULLNULL2 Using > where; Start temporary > +1PRIMARY INNRALL NULLNULLNULLNULL2 Using > where; End temporary; Using join buffer (flat, BNL join) > flush tables; > SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS > INNR WHERE OUTR.dt IS NULL ); > dt > flush tables; > EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt > FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); > id select_type table typepossible_keys key key_len ref > rowsExtra > -1PRIMARY OUTRALL NULLNULLNULLNULL2 Using > where > -1PRIMARY INNRALL NULLNULLNULLNULL2 Using > where; FirstMatch(OUTR) > +1PRIMARY OUTRALL NULLNULLNULLNULL2 Using > where; Start temporary > +1PRIMARY INNRALL NULLNULLNULLNULL2 Using > where; End temporary; Using join buffer (flat, BNL join) > flush tables; > SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS > INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); > dt > > === modified file 'mysql-test/r/group_by.result' > --- a/mysql-test/r/group_by.result2010-10-27 23:31:22 + > +++ b/mysql-test/r/group_by.result2010-11-03 19:26:18 + > @@ -1543,7 +1543,8 @@ > (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); > id select_type table typepossible_keys key key_len ref > rowsExtra > 1PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL144 > Using index > -1PRIMARY t1 ALL NULLNULLNULLNULL144 Using > where; FirstMatch(t1) > +1PRIMARY subselect2 eq_ref unique_key unique_key 4 > func1 > +2SUBQUERYt1 ALL NULLNULLNULLNULL144 > CREATE TABLE t2 (a INT, b INT, KEY(a)); > INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); > EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; > > === modified file 'mysql-test/r/subselect.result' > --- a/mysql-test/r/subselect.result 2010-10-27 23:31:22 + > +++ b/mysql-test/r/subselect.result 2010-11-03 19:26:18 + > @@ -2831,9 +2831,10 @@ > explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT > one,two FROM t2 WHERE flag = 'N'); > id select_type table typepossible_keys key key_len ref > rowsfilteredExtra > 1PRIMARY t1 ALL NULLNULLNULLNULL8 100.00 > -1PRIMARY t2 ALL NULLNULLNULLNULL9 100.00 > Using where; FirstMatch(t1) > +1PRIMARY subselect2 eq_ref unique_key unique_key 10 > func1 1.00 > +2SUBQUERYt2 ALL NULLNULLNULLNULL9 > 100.00 Using where > Warnings: > -Note 1003select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` > from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = > `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and > (`test`.`t2`.`flag` = 'N')) > +Note 1003select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` > from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`flag` = 'N')) > explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 > WHERE flag = '0
Re: [Maria-developers] [Commits] Rev 2845: Fixed LP bug #668644. in file:///home/igor/maria/maria-5.3-bug668644/
Ok to push.
On Mon, Nov 08, 2010 at 08:36:33PM -0800, Igor Babaev wrote:
> At file:///home/igor/maria/maria-5.3-bug668644/
>
>
> revno: 2845
> revision-id: i...@askmonty.org-20101109043632-s053dbydv48cr9nz
> parent: mo...@askmonty.org-20101105103751-09kb6rx5tvpyywen
> committer: Igor Babaev
> branch nick: maria-5.3-bug668644
> timestamp: Mon 2010-11-08 20:36:32 -0800
> message:
> Fixed LP bug #668644.
> The pushdown condition for the sorted table in a query can be complemented
> by the conditions from HAVING. This transformation is done in JOIN::exec
> pretty late after the original pushdown condition have been saved in the
> field pre_idx_push_select_cond for the sorted table. So this field must
> be updated after the inclusion of the condition from HAVING.
> === modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
> --- a/mysql-test/suite/innodb/r/innodb_mysql.result 2010-10-28 17:04:23
> +
> +++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2010-11-09 04:36:32
> +
> @@ -2609,5 +2609,41 @@
> rows 3
> ExtraUsing index
> DROP TABLE t1;
> -#
> End of 5.1 tests
> +#
> +# Bug#668644: HAVING + ORDER BY
> +#
> +CREATE TABLE t1 (
> +pk int NOT NULL PRIMARY KEY, i int DEFAULT NULL,
> +INDEX idx (i)
> +) ENGINE=INNODB;
> +INSERT INTO t1 VALUES
> +(6,-1636630528),(2,-1097924608),(1,6),(3,6),(4,1148715008),(5,1541734400);
> +CREATE TABLE t2 (
> +i int DEFAULT NULL,
> +pk int NOT NULL PRIMARY KEY,
> +INDEX idx (i)
> +) ENGINE= INNODB;
> +INSERT INTO t2 VALUES
> +(-1993998336,20),(-1036582912,1),(-733413376,5),(-538247168,16),
> +(-514260992,4),(-249561088,9),(1,2),(1,6),(2,10),(2,19),(4,17),
> +(5,14),(5,15),(6,8),(7,13),(8,18),(9,11),(9,12),(257425408,7),
> +(576061440,3);
> +EXPLAIN
> +SELECT t1 .i AS f FROM t1, t2
> +WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
> +HAVING f > 7
> +ORDER BY f;
> +id select_type table typepossible_keys key key_len ref
> rowsExtra
> +1SIMPLE t1 range PRIMARY PRIMARY 4 NULL3 Using
> where; Using filesort
> +1SIMPLE t2 ref idx idx 5 test.t1.pk 1
> Using index
> +SELECT t1 .i AS f FROM t1, t2
> +WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
> +HAVING f > 7
> +ORDER BY f;
> +f
> +1148715008
> +1541734400
> +1541734400
> +DROP TABLE t1, t2;
> +End of 5.3 tests
>
> === modified file 'mysql-test/suite/innodb/t/innodb_mysql.test'
> --- a/mysql-test/suite/innodb/t/innodb_mysql.test 2010-10-19 13:58:35
> +
> +++ b/mysql-test/suite/innodb/t/innodb_mysql.test 2010-11-09 04:36:32
> +
> @@ -840,7 +840,41 @@
>
> DROP TABLE t1;
>
> ---echo #
> -
> -
> --echo End of 5.1 tests
> +
> +--echo #
> +--echo # Bug#668644: HAVING + ORDER BY
> +--echo #
> +
> +CREATE TABLE t1 (
> + pk int NOT NULL PRIMARY KEY, i int DEFAULT NULL,
> + INDEX idx (i)
> +) ENGINE=INNODB;
> +INSERT INTO t1 VALUES
> + (6,-1636630528),(2,-1097924608),(1,6),(3,6),(4,1148715008),(5,1541734400);
> +
> +CREATE TABLE t2 (
> + i int DEFAULT NULL,
> + pk int NOT NULL PRIMARY KEY,
> + INDEX idx (i)
> +) ENGINE= INNODB;
> +INSERT INTO t2 VALUES
> + (-1993998336,20),(-1036582912,1),(-733413376,5),(-538247168,16),
> + (-514260992,4),(-249561088,9),(1,2),(1,6),(2,10),(2,19),(4,17),
> + (5,14),(5,15),(6,8),(7,13),(8,18),(9,11),(9,12),(257425408,7),
> + (576061440,3);
> +
> +EXPLAIN
> +SELECT t1 .i AS f FROM t1, t2
> + WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
> + HAVING f > 7
> + ORDER BY f;
> +SELECT t1 .i AS f FROM t1, t2
> + WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
> + HAVING f > 7
> + ORDER BY f;
> +
> +DROP TABLE t1, t2;
> +
> +
> +--echo End of 5.3 tests
>
> === modified file 'sql/opt_index_cond_pushdown.cc'
> --- a/sql/opt_index_cond_pushdown.cc 2009-12-22 12:49:15 +
> +++ b/sql/opt_index_cond_pushdown.cc 2010-11-09 04:36:32 +
> @@ -318,7 +318,7 @@
> if (idx_cond)
> {
>Item *idx_remainder_cond= 0;
> - tab->pre_idx_push_select_cond= tab->select_cond;
> + tab->pre_idx_push_select_cond= tab->select->cond;
>/*
> For BKA cache we store condition to special BKA cache field
> because evaluation of the condition requires additional operations
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2010-10-28 17:04:23 +
> +++ b/sql/sql_select.cc 2010-11-09 04:36:32 +
> @@ -2213,6 +2213,14 @@
> DBUG_VOID_RETURN;
> curr_table->select->cond->fix_fields(thd, 0);
> }
> +if (curr_table->pre_idx_push_select_cond)
> + {
> + if (!(curr_table->pre_idx_push_select_cond=
> +new Item_cond_and(curr_table->pre_idx_push_select_cond,
> + sort_table_cond)))
> +DBUG_VOID_RETURN;
> + curr_table->pre_idx_push_select_cond->fix_fields(thd, 0);
> +}
> curr_table->set_sele
[Maria-developers] Is it possible to have RQG look for crashes only?
Hi Philip, I've been fixing bugs in maria-5.3-mwl128-dsmrr-cpk tree, fixed two of the four problems so far. My general impression is that crashes are easier to fix than wrong results. Is it possible to run RQG in a mode that will cause it to ignore the difference in query results, and stop only on crashes? if yes, I'd like to get a command/grammar that were used to test the -mwl128-dsmrr-cpk tree. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Review of MWL#128: Block Nested Loop Hash Join
On Fri, Nov 12, 2010 at 05:42:25PM +0300, Sergey Petrunya wrote: > * EXPLAIN currently shows hash join as follows: > > MariaDB [hj1]> explain extended select * from t1 A, t2 B where A.a=B.a; > ++-+---+--+---+--+-+-+--+-+ > | id | select_type | table | type | possible_keys | key | key_len | ref > | rows | Extra | > ++-+---+--+---+--+-+-+--+-+ > | 1 | SIMPLE | A | ALL | NULL | NULL | NULL| NULL > | 10 | Using where | > | 1 | SIMPLE | B | ref | a | a| 13 | hj1.A.a > |4 | Using join buffer (flat, BNLH join) | > ++-+---+--+---+--+-+-+--+-+ > Another note about EXPLAIN: the "key" column shows name of index "a". This is purely an artifact of how currently HASH join is hooked into ref optimizer, and makes no sense from user's point of view. We need to either remove it, or make sure this is addressed in the scope of "hash join on non-indexed columns" task. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Fwd: [Commits] Rev 2859: Fixed LP #bug 660963. in file:///home/igor/maria/maria-5.3-mwl128-bug660963/
Hello Igor,
On Thu, Nov 18, 2010 at 02:17:13PM -0800, Igor Babaev wrote:
> Sergey,
>
> Please review this patch ASAP as this bug blocks Philip with testing.
> If you have any questions contact me by skype.
>
Ok to push after the comment below is addressed.
> Original Message
> Subject: [Commits] Rev 2859: Fixed LP #bug 660963. in
> file:///home/igor/maria/maria-5.3-mwl128-bug660963/
> Date: Thu, 18 Nov 2010 14:13:58 -0800 (PST)
> From: Igor Babaev
> Reply-To: maria-developers@lists.launchpad.net
> To:
>
>
>
> At file:///home/igor/maria/maria-5.3-mwl128-bug660963/
>
>
> revno: 2859
> revision-id: i...@askmonty.org-20101118221357-zg55d3erru07ugzy
> parent: i...@askmonty.org-20101116050732-hpbqelsf8nvae4xt
> committer: Igor Babaev
> branch nick: maria-5.3-mwl128-bug660963
> timestamp: Thu 2010-11-18 14:13:57 -0800
> message:
> Fixed LP #bug 660963.
> The condition that was supposed to check whether a join table
> is an inner table of a nested outer join or semi-join was not
> quite correct in the code of the function check_join_cache_usage.
> That's why some queries with nested outer joins triggered
> an assertion failure.
> Encapsulated this condition in the new method called
> JOIN_TAB::is_nested_inner and provided a proper code for it.
>
> Also corrected a bug in the code of check_join_cache_usage()
> that caused a downgrade of not first join buffers of the
> level 5 and 7 to level 4 and 6 correspondingly.
>
...
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2010-11-16 05:07:32 +
> +++ b/sql/sql_select.cc 2010-11-18 22:13:57 +
> @@ -7635,8 +7635,7 @@
>if (cache_level == 0 || i == join->const_tables || !prev_tab)
> return 0;
>
> - if (force_unlinked_cache &&
> - (cache_level & JOIN_CACHE_INCREMENTAL_BIT))
> + if (force_unlinked_cache && (cache_level%2 == 0))
> cache_level--;
>
>if (options & SELECT_NO_JOIN_CACHE)
> @@ -7658,13 +7657,14 @@
>/*
> Non-linked join buffers can't guarantee one match
>*/
> - if ((force_unlinked_cache || cache_level == 1) &&
> - ((tab->is_inner_table_of_semi_join_with_first_match() &&
> -!tab->is_single_inner_of_semi_join_with_first_match()) ||
> - (tab->is_inner_table_of_outer_join() &&
> -!tab->is_single_inner_of_outer_join(
> - goto no_join_cache;
> -
> + if (tab->is_nested_inner())
> + {
> +if (force_unlinked_cache || cache_level == 1)
> + goto no_join_cache;
> +if (cache_level & 1)
> + cache_level--;
> + }
> +
>/*
> Don't use join buffering if we're dictated not to by no_jbuf_after (this
> ...)
> @@ -7757,9 +7757,6 @@
> (cache_level <= 6 || no_hashed_cache))
>goto no_join_cache;
>
> -if (prev_tab->cache && cache_level==7)
> - cache_level= 6;
> -
> if ((rows != HA_POS_ERROR) && !(flags & HA_MRR_USE_DEFAULT_IMPL))
> {
>if (cache_level <= 6 || no_hashed_cache)
>
> === modified file 'sql/sql_select.h'
> --- a/sql/sql_select.h2010-11-13 14:13:34 +
> +++ b/sql/sql_select.h2010-11-18 22:13:57 +
> @@ -359,6 +359,14 @@
> return (first_inner && first_inner->last_inner == this) ||
> last_sj_inner_tab == this;
>}
> + bool is_nested_inner()
Please add a comment clarifying what this function checks.
> + {
> +if (first_inner && (first_inner != last_inner || first_upper))
> + return TRUE;
> +if (first_sj_inner_tab && first_sj_inner_tab != last_sj_inner_tab)
> + return TRUE;
> +return FALSE;
> + }
>struct st_join_table *get_first_inner_table()
>{
> if (first_inner)
>
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Fwd: [Commits] Rev 2860: Fixed LP bug #675922. in file:///home/igor/maria/maria-5.3-mwl128-bug675922/
Hello Igor,
On Thu, Nov 18, 2010 at 10:04:24PM -0800, Igor Babaev wrote:
> Sergey,
>
> Please review this fix.
>
Ok to push after the irc feedback (lack of comments about JOIN_CACHE format) is
addressed.
>
> Original Message
> Subject: [Commits] Rev 2860: Fixed LP bug #675922. in
> file:///home/igor/maria/maria-5.3-mwl128-bug675922/
> Date: Thu, 18 Nov 2010 22:02:41 -0800 (PST)
> From: Igor Babaev
> Reply-To: maria-developers@lists.launchpad.net
> To:
>
>
>
> At file:///home/igor/maria/maria-5.3-mwl128-bug675922/
>
>
> revno: 2860
> revision-id: i...@askmonty.org-20101119060240-gzh1k5gxl3aazk45
> parent: i...@askmonty.org-20101118221357-zg55d3erru07ugzy
> committer: Igor Babaev
> branch nick: maria-5.3-mwl128-bug675922
> timestamp: Thu 2010-11-18 22:02:40 -0800
> message:
> Fixed LP bug #675922.
> The bug happened when BKA join algorithm used an incremental buffer
> and some of the fields over which access keys were constructed
> - were allocated in the previous join buffers
> - were non-nullable
> - belonged to inner tables of outer joins.
> For such fields an offset to the field value in the record is saved
> in the postfix of the record, and a zero offset indicates that the value
> is null. Before the key using the field value is constructed the
> value is read into the corresponding field of the record buffer and
> the null bit is set for the field if the offset is 0. However if
> the field is non-nullable the table->null_row must be set to 1
> for null values and to 0 for non-null values to ensure proper reading
> of the value from the record buffer.
>
> === modified file 'mysql-test/r/join_cache.result'
> --- a/mysql-test/r/join_cache.result 2010-11-18 22:13:57 +
> +++ b/mysql-test/r/join_cache.result 2010-11-19 06:02:40 +
> @@ -6004,4 +6004,56 @@
> SET SESSION optimizer_switch = 'outer_join_with_cache=off';
> SET SESSION join_cache_level = DEFAULT;
> DROP TABLE t1,t2,t3;
> +#
> +# Bug #675922: incremental buffer for BKA with access from previous
> +# buffers from non-nullable columns whose values may be null
> +#
> +CREATE TABLE t1 (a1 varchar(32)) ;
> +INSERT INTO t1 VALUES ('s'),('k');
> +CREATE TABLE t2 (a2 int PRIMARY KEY, b2 varchar(32)) ;
> +INSERT INTO t2 VALUES (7,'s');
> +CREATE TABLE t3 (a3 int PRIMARY KEY, b3 varchar(32)) ;
> +INSERT INTO t3 VALUES (7,'s');
> +CREATE TABLE t4 (a4 int) ;
> +INSERT INTO t4 VALUES (9);
> +CREATE TABLE t5(a5 int PRIMARY KEY, b5 int) ;
> +INSERT INTO t5 VALUES (7,0);
> +SET SESSION optimizer_switch = 'outer_join_with_cache=on';
> +SET SESSION join_cache_level = 0;
> +EXPLAIN
> +SELECT t4.a4, t5.b5
> +FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
> +LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
> +id select_type table typepossible_keys key key_len ref
> rowsExtra
> +1SIMPLE t1 ALL NULLNULLNULLNULL2
> +1SIMPLE t2 ALL PRIMARY NULLNULLNULL1 Using
> where
> +1SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1
> Using index
> +1SIMPLE t4 ALL NULLNULLNULLNULL1 Using
> where
> +1SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1
> Using where
> +SELECT t4.a4, t5.b5
> +FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
> +LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
> +a4 b5
> +90
> +9NULL
> +SET SESSION join_cache_level = 6;
> +EXPLAIN
> +SELECT t4.a4, t5.b5
> +FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
> +LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
> +id select_type table typepossible_keys key key_len ref
> rowsExtra
> +1SIMPLE t1 ALL NULLNULLNULLNULL2
> +1SIMPLE t2 ALL PRIMARY NULLNULLNULL1 Using
> where
> +1SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1
> Using index
> +1SIMPLE t4 ALL NULLNULLNULLNULL1 Using
> where; Using join buffer (flat, BNL join)
> +1SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1
> Using where; Using join buffer (incremental, BKA join)
> +SELECT t4.a4, t5.b5
> +FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
> +LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
> +a4 b5
> +90
> +9NULL
> +SET SESSION optimizer_switch = 'outer_join_with_cache=off';
> +SET SESSION join_cache_level = DEFAULT;
> +DROP TABLE t1,t2,t3,t4,t5;
> set @@optimizer_swit...@save_optimizer_switch;
>
> === modified file 'mysql-test/t/join_cache.test'
> --- a/mysql-test/t/join_cache.test2010-11-18 22:13:57 +
> +++ b/mysql-test/t/join_cache.test2010-11-19 06:02:40 +
>
Re: [Maria-developers] Problem with prep_where (LP BUG#675248)
Hi!
On Mon, Nov 15, 2010 at 04:47:54PM +0200, Oleksandr Byelkin wrote:
> Preparation statement for PS looks like this (example uses prep_where,
> but we have the same problem for other parts stored in
> st_select_lex::fix_prepare_information()):
>
>
> ...
>conds->fix_fields(...&conds...) /* (1.1) JOIN::prepare()*/
> ...
>prep_where= *conds; /* (1.2)
> st_select_lex::fix_prepare_information() */
>*conds= where= prep_where->copy_andor_structure()
> ...
>
> Then executing of the prepared statement is like this:
>
> where= prep_where->copy_andor_structure() /* (2.1) reinit_stmt_before_use */
>...
> where->fix_fields() /* (2.2) JOIN::prepare() */
>...
>
> The problem is that during (1.1) we could substitute conds with
> reference on other item (for example if we change reference on
> Item_field on Item_ref* or Item_ref on Item_field). The new item will be
> destroyed after preparation (i.e before (2.1)). So during (2.1)
> prep_where points on freed memory.
>
> How to solve the problem.
>
> 1. Workarounds
>
> 1.1. Make changing Items inherited from Item_ident (Item_field and
> Item_ref*) during resolving permanent.
> Pros:
> - Looks logical
> - reduce work during second preparation
> Cons:
> - Play with statement memory usually complex enough.
> - have to be sure that there is no any reference on the original Item
we will also need to make sure that each re-written item $i allows one to make
a $i->fix_fields() call (although I'm not sure what exactly its meaning should
be).
Another possible concern is that currently fix_fields() calls for some items
have side effects, e.g. running item->fix_fields() on a reference to a field
in ancestor query will mark the subquery predicate we're in as correlated.
I'm not certain, but it could turn out that for some kinds of items, we need to
re-do the item->fix_fields() call for every PS re-execution because of the side
effects it has. It's possible to move that code out somewhere of course, but
that can be labor-intensive and end up in a big patch.
> 1.2 Add new method for Item, which return 'this' pointer but for
> Item_idents creating during resolving original Item. (1.2) turns to
> something like:
> prep_where= (*conds)->original_ident_item();
> Pros:
> - Looks simple
> Cons:
> - Item already overloaded with methods like this
I really don't like the cons part here.
> 1.3 Assign prep_where before (1.1)
> Pros:
> - Easy to implement
> Cons:
> - Need changing logic and semantic of some calls and
> prep_where/prep_having... class variables.
>
> 2. Global solutions for the problem of double/triple/... reference on
> transformed expression (it is not applicadle for 5.1-5.3 but could de
> universal solution in 5.5 of higher):
>2.1 Absolutely transparent wrapper to save only one reference on any
> expression which could be transformed:
> Pros:
>- Could base class for many other wrappers
> Cons:
>- It is difficult to implement such absolutely transparent wrapper.
>- A lot of places where it should be put, make Item tree bigger
I suspect, without a clear definition of where it should be put, we will end up
having this wrapper injected in too many (and at the same time, too few :)
places.
>2.2 Turn THD::change_list to hash (for example) and chack presence of
> assigning variable in the list, if it found, also register changing for
> variable we are assigning value now. For example instead of prep_where=
> *conds; use thd->assign_changeable(&prep_where, conds), where:
> void THD::assign_changeable(void **newref, void **oldref)
> {
> if (in_changed_references(oldref)
> change_item_tree(newref, *oldref);
> else
> *newref= *oldref;
> }
> Pros:
>- no additional work for usual statements
> Cons:
>- List supports order of assigments easyly, but hash can't
>- time for finding pointers in the hash
So, this approach is an attempt to extend the thd->change_item_tree()
mechanism to handle Item_field->Item_ref change/reversal for the case when the
changed item is the top-most item of the WHERE clause. It seems to be nice that
we won't have to introduce additional entities.
With regards to performance, I don't think we should care to even have a hash
table: a query will have at most O(#subselects) assign_changeable() calls, and
I think we could safely assume that there are not more than O(#subselects)
thd->change_item_tree() calls, too.
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
[Maria-developers] Philip please test maria-5.3-mwl128-dsmrr-cpk
Hi Philip, The new version of MWL#121-125, DS-MRR impovements, is ready for your testing. I believe all previously reported crashes and wrong query result bugs are no longer there (they've been either targeted and fixed, or were gone after code re-working I've made when addressing review feedback). The slowdown bugs (https://bugs.launchpad.net/maria/+bug/637160, https://bugs.launchpad.net/maria/+bug/631504) are most likely still there (I didn't check if they are, but I haven't made any changes that could impact them). The code to be tested is at lp:~maria-captains/maria/maria-5.3-mwl128-dsmrr-cpk. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] MWL#90: need a list of "obvious things to improve"
Hi Timour, In Helsinki, we've noted the following: > MWL#90: Subqueries: Inside-out execution for non-semijoin materialized > subqueries in WHERE (4K diff); Critical task > = Get the code reviewed/pushed > TODO: > - Timour will tell the "obvious things to improve"; Timour 1 day I'm going to need those "obvious things" fairly soon. Do you need to have anything as a prerequisite to produce the list? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Philip please test maria-5.3-mwl128-dsmrr-cpk
On Thu, Dec 09, 2010 at 11:34:05AM +0200, Philip Stoev wrote: > I have a few questions with respect to the feature so that I can test it > better (we may have discussed those previously but I can not seem to find > the email): > > - apart from mrr_sort_keys ON and OFF and engine_condition_pushdown = ON > | OFF and all join_cache_levels , is there any other optimizer switch > that I should fiddle with? Is there any switch that I can set to ON or > OFF in order to drive more queries to use your code? @@engine_condition_pushdown variable does not have any effect with the current code. It controls table condition pushdown, which is only supported by NDB. XtraDB/MyISAM/Maria have *index* condition pushdown, which can be turned on and off by using SET @@optimizer_switch='index_condition_pushdown=on|off'; Overall, DS-MRR behavior can be modified by: - @@join_cache_level setting (need to be such that BKA is enabled in order for new code to wor) - @@join_buffer_size (sets size of the buffer that will be shared between BKA module and DS-MRR module) - mrr_sort_keys=on|off @@optimizer_switch flag - index_condition_pushdown=on|off @@optimizer_switch flag. > - apart from mrr_buffer_size , is there any other mysqld option I should > be conserned with. What values of mrr_buffer_size should be tested? > - what table sizes should I use and also how many tables should I join? > Is up to 1000 rows per table and 2-3-table joins sufficient? Or should I > go for the DBT-2 data set? I think that we should first test with up to 1K rows per table and 2-4 way joins. I was quite impressed by the issue you've found in BUG#671340 (it was a specific interaction of key sorting and rowid sorting, which could occur only when we're scanning a particular kind of data and exhaust the buffer at a certain point). The testcase for that bug was rather small, I take the original testcase found by RQG wasn't big either? > - how about multipart and prefix indexes? Yes. I don't expect bugs to be there, but the code does work with index tuples, so it would be nice to check how scanning on multipart indexes (and their prefixes) work. Prefix indexes can be checked, too. > - should I test enum/set/bit and other marginal data types? I can't think of realistic examples with those types, so we better focus on something else. > - are there any engine specific code or considerations? XtraDB is different in the regard that it has clustered primary keys, and MWL#121-125 adds support for MRR/BKA to work for clustered primary keys. Maria/MyISAM should be checked too, they both have very similar DS-MRR handling. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Proposals for improving BKA/MRR visibility in EXPLAIN and diagnostics
Hello, Below are some ideas on how to make DS-MRR/BKA easier to work with for the users (= those who don't run mysqld under debugger). Questions are marked with 'Q:' but any comments are welcome. Better EXPLAIN -- Philip has complained numerous times that it is not shown in EXPLAIN whether DS-MRR will use key sorting. Currently, MRR alone is show like this: MariaDB [test]> explain select * from t1 where key1<30; ++-+---+---+---+--+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---+---+---+--+-+--+--+--+ | 1 | SIMPLE | t1| range | key1 | key1 | 5 | NULL | 1 | Using index condition; Using MRR | ++-+---+---+---+--+-+--+--+--+ MRR with BKA are shown like this: MariaDB [test]> explain select * from t2,t1 where t2.a=t1.key1; ++-+---+--+---+--+-+---+--++ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+---+--++ | 1 | SIMPLE | t2| ALL | NULL | NULL | NULL| NULL | 10 | Using where| | 1 | SIMPLE | t1| ref | key1 | key1 | 5 | test.t2.a |1 | Using join buffer (flat, BKA join) | ++-+---+--+---+--+-+---+--++ The suggestion is to - get rid of "Using MRR" word, - instead, show "Sort rowids" and/or "Sort keys". - Show the above two whenever rowid-ordered and/or key-ordered retrieval is performed. The above examples will look as follows: MariaDB [test]> explain select * from t1 where key1<30; ++-+---+---+---+--+-+--+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+--+-+--+--+---+ | 1 | SIMPLE | t1| range | key1 | key1 | 5 | NULL | 1 | Using index condition; Sort keys; Sort rowids | ++-+---+---+---+--+-+--+--+---+ MRR with BKA are shown like this: MariaDB [test]> explain select * from t2,t1 where t2.a=t1.key1; ++-+---+--+---+--+-+---+--++ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+---+--++ | 1 | SIMPLE | t2| ALL | NULL | NULL | NULL| NULL | 10 | Using where| | 1 | SIMPLE | t1| ref | key1 | key1 | 5 | test.t2.a |1 | Using join buffer (flat, BKA join); Sort keys; Sort rowids | ++-+---+--+---+--+-+---+--++ Q: any comments? Counters Like it is done with other kinds of table accesses, we want to have counter-based way of analyzing of what has been happening around MRR. The most obvious are: 1. Handler_multi_range_read_init_count status variable This will tell how many multi_range_read_init() calls have been made that used non-default MRR implementation, i.e. one will be able to see how many times real MRR scans were performed. Q: this counter doesn't show how many times key sorting/rowid sorting/both strategies were used. Is that ok? One could argue that information about strategy choice is not in high demand as MRR strategy choice is based mostly on system settings and DDLs. 2. Handler_multi_range_read_next_count status variable This will tell how many records were returned by MRR to the upper layer. Q: MRR does index and rnd_pos scans under the hood, and these scans do increase counters. This means that, for a single row returned by MRR, multiple counters will be incremented. Less obvious suggestions: 3. It would be useful to have an idea about whether DS-MRR had sufficient buffer space to operate. One can get a rough picture by addi
Re: [Maria-developers] Proposals for improving BKA/MRR visibility in EXPLAIN and diagnostics
Added feedback provided by Igor over skype: On Wed, Dec 15, 2010 at 06:31:52PM +0300, Sergey Petrunya wrote: > Below are some ideas on how to make DS-MRR/BKA easier to work with for the > users (= those who don't run mysqld under debugger). Questions are marked with > 'Q:' but any comments are welcome. > > Better EXPLAIN > -- > Philip has complained numerous times that it is not shown in EXPLAIN whether > DS-MRR will use key sorting. > > Currently, MRR alone is show like this: > > MariaDB [test]> explain select * from t1 where key1<30; > ++-+---+---+---+--+-+--+--+--+ > | id | select_type | table | type | possible_keys | key | key_len | ref | > rows | Extra| > ++-+---+---+---+--+-+--+--+--+ > | 1 | SIMPLE | t1| range | key1 | key1 | 5 | NULL | > 1 | Using index condition; Using MRR | > ++-+---+---+---+--+-+--+--+--+ > > MRR with BKA are shown like this: > MariaDB [test]> explain select * from t2,t1 where t2.a=t1.key1; > ++-+---+--+---+--+-+---+--++ > | id | select_type | table | type | possible_keys | key | key_len | ref > | rows | Extra | > ++-+---+--+---+--+-+---+--++ > | 1 | SIMPLE | t2| ALL | NULL | NULL | NULL| NULL > | 10 | Using where| > | 1 | SIMPLE | t1| ref | key1 | key1 | 5 | > test.t2.a |1 | Using join buffer (flat, BKA join) | > ++-+---+--+---+--+-+---+--++ > > The suggestion is to > - get rid of "Using MRR" word, > - instead, show "Sort rowids" and/or "Sort keys". > - Show the above two whenever rowid-ordered and/or key-ordered retrieval is > performed. > > The above examples will look as follows: > > MariaDB [test]> explain select * from t1 where key1<30; > ++-+---+---+---+--+-+--+--+---+ > | id | select_type | table | type | possible_keys | key | key_len | ref | > rows | Extra | > ++-+---+---+---+--+-+--+--+---+ > | 1 | SIMPLE | t1| range | key1 | key1 | 5 | NULL | > 1| Using index condition; Sort keys; Sort rowids | > ++-+---+---+---+--+-+--+--+---+ The above will not have "Sort keys", key sorting is done only when working with BKA. > > MRR with BKA are shown like this: > MariaDB [test]> explain select * from t2,t1 where t2.a=t1.key1; > ++-+---+--+---+--+-+---+--++ > | id | select_type | table | type | possible_keys | key | key_len | ref > | rows | Extra | > ++-+---+--+---+--+-+---+--++ > | 1 | SIMPLE | t2| ALL | NULL | NULL | NULL| NULL > | 10 | Using where| > | 1 | SIMPLE | t1| ref | key1 | key1 | 5 | > test.t2.a |1 | Using join buffer (flat, BKA join); Sort keys; Sort rowids > | > ++-+---+--+---+--+-+---+--++ > Q: any comments? > > Counters > > Like it is done with other kinds of table accesses, we want to have > counter-based way of analyzing of what has been happening around MRR. The counters should also be collected as per-table statistics (probably that happens automatically, but we'll need to check that) > > The most obvious are: > > 1. Handler_multi_range_read_init_count status variable > This will tell how many multi_range_read_init() calls have been made that > used non-default MRR implementation, i.e. one will be able to see how man
[Maria-developers] No "Using index" support in sort-intersect - intentional?
Hello Igor, I was looking at sort-intersect and noticed the following: Consider this table: CREATE TABLE `t1` ( `k1` int(11) DEFAULT NULL, `k2` int(11) DEFAULT NULL, `filler` char(100) DEFAULT NULL, KEY `k1` (`k1`), KEY `k2` (`k2`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Let's try a sort-intersect query: MariaDB [timerge1]> EXPLAIN select 1 from t1 where k1<30 and k2 < 30\G *** 1. row *** id: 1 select_type: SIMPLE table: t1 type: range possible_keys: k1,k2 key: k1,k2 key_len: 5,5 ref: NULL rows: 1 Extra: Using sort_intersect(k1,k2); Using where 1 row in set (0.01 sec) The query doesn't use any columns not covered by the used indexes, however, we don't see "using index". Analogous ROR-intersect query will use "Using index": MariaDB [timerge1]> EXPLAIN select 1 from t1 where k1=30 and k2 = 30\G *** 1. row *** id: 1 select_type: SIMPLE table: t1 type: index_merge possible_keys: k1,k2 key: k1,k2 key_len: 5,5 ref: NULL rows: 1 Extra: Using intersect(k1,k2); Using where; Using index 1 row in set (0.00 sec) BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] [pser...@askmonty.org: No "Using index" support in sort-intersect - intentional?]
Another question: why is type='range' for sort_intersect? We have type=index_merge for intersect and [sort]_union, so it seems rather confusing that we have type=range for sort_intersect? - Forwarded message from Sergey Petrunya - Date: Wed, 19 Jan 2011 11:11:14 +0300 From: Sergey Petrunya To: i...@askmonty.org Cc: Maria Developers Subject: No "Using index" support in sort-intersect - intentional? Hello Igor, I was looking at sort-intersect and noticed the following: Consider this table: CREATE TABLE `t1` ( `k1` int(11) DEFAULT NULL, `k2` int(11) DEFAULT NULL, `filler` char(100) DEFAULT NULL, KEY `k1` (`k1`), KEY `k2` (`k2`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Let's try a sort-intersect query: MariaDB [timerge1]> EXPLAIN select 1 from t1 where k1<30 and k2 < 30\G *** 1. row *** id: 1 select_type: SIMPLE table: t1 type: range possible_keys: k1,k2 key: k1,k2 key_len: 5,5 ref: NULL rows: 1 Extra: Using sort_intersect(k1,k2); Using where 1 row in set (0.01 sec) The query doesn't use any columns not covered by the used indexes, however, we don't see "using index". Analogous ROR-intersect query will use "Using index": MariaDB [timerge1]> EXPLAIN select 1 from t1 where k1=30 and k2 = 30\G *** 1. row *** id: 1 select_type: SIMPLE table: t1 type: index_merge possible_keys: k1,k2 key: k1,k2 key_len: 5,5 ref: NULL rows: 1 Extra: Using intersect(k1,k2); Using where; Using index 1 row in set (0.00 sec) BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog - End forwarded message - -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] index_merge, @@sort_buffer_size, and Sort_XXX counters
Hello, index_merge uses @@sort_buffer_size as a guide of how much space it can use for sorting. I think there is an issue with the way it is done. Before index_merge was introduced, @@sort_buffer_size was used only by filesort (also for couple of other cases like GROUP_CONCAT with sorting, but I consider them to be too rare to be of practical importance). This allowed the following: 1. One can set @@sort_buffer_size to control how much space is to be allocated 2. SHOW STATUS will produce these numbers: Sort_merge_passes Sort_range Sort_scan Sort_range+Sort_scan gives the total number of filesort invocations. Sort_merge_passes is the number of merges. One can get average amount of merges per sort operation by calculating: Sort_merge_passes / (Sort_range + Sort_scan) (1) If the number is high, then it means that it could be useful to increase @@sort_buffer_size. Now, if we take index_merge into the picture, the counters are much less useful. index_merge causes Sort_merge_passes to be incremented, but we dont have a counter for index_merge operations, so formula (1) becomes invalid. Possible solutions are: Option#1: introduce a counter Sort_index_merge_count (name subject to discussion), which will mean "number of index_merge sort union/intersection scans". Option#2: make it so that index_merge does not cause Sort_merge_passes to be incremented. I think that in this case we'll need a separate variable to control index_merge's buffer size. it could have @@sort_buffer_size value as default. Any comments? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Benchmarking index_merge sort_intersect
Hi!
Please find below results of my attempts to figure out
if/when one should "index_merge_sort_intersection=on" setting.
General setup information:
* Experiment was done on my laptop
* I picked "on time flight statistics" dataset, because
- it has lots of columns, so one can't be expected to have all possible
composite indexes
- it allows for meaningful intersection queries
- the data is real and I don't expect it to be extremely correlated (although
some
aiports/airlines are probably worse than others, so it's not totally
uncrellated, either).
* Storage engine is XtraDB
* I loaded data for Q1 2009, which is 1.5M rows, and ontime.ibd file is 704M:
MariaDB [ontime]> select count(*), min(FlightDate), max(FlightDate) from ontime;
+--+-+-+
| count(*) | min(FlightDate) | max(FlightDate) |
+--+-+-+
| 1578171 | 2009-01-01 | 2009-03-31 |
+--+-+-+
1 row in set (15.92 sec)
MariaDB [ontime]> set optimizer_switch='index_merge_sort_intersection=on';
MariaDB [ontime]> explain select count(*) from ontime where depdelay > 30 and
OriginState IN ('WA', 'CA');
++-++---+--+--+-+--+---+-+
| id | select_type | table | type | possible_keys| key
| key_len | ref | rows | Extra
|
++-++---+--+--+-+--+---+-+
| 1 | SIMPLE | ontime | range | OriginState,DepDelay |
DepDelay,OriginState | 5,3 | NULL | 41832 | Using
sort_intersect(DepDelay,OriginState); Using where |
++-++---+--+--+-+--+---+-+
1 row in set (0.01 sec)
## (Run query several times until query time stabilizes)
MariaDB [ontime]> select count(*) from ontime where depdelay > 30 and
OriginState IN ('WA', 'CA');
+--+
| count(*) |
+--+
|18824 |
+--+
1 row in set (2.37 sec)
MariaDB [ontime]> set optimizer_switch='index_merge_sort_intersection=off';
MariaDB [ontime]> explain select count(*) from ontime where depdelay > 30 and
OriginState IN ('WA', 'CA');
++-++---+--+--+-+--++---+
| id | select_type | table | type | possible_keys| key | key_len
| ref | rows | Extra |
++-++---+--+--+-+--++---+
| 1 | SIMPLE | ontime | range | OriginState,DepDelay | DepDelay | 5
| NULL | 201894 | Using index condition; Using where; Using MRR |
++-++---+--+--+-+--++---+
1 row in set (0.01 sec)
## (Run query several times until query time stabilizes)
MariaDB [ontime]> select count(*) from ontime where depdelay > 30 and
OriginState IN ('WA', 'CA');
+--+
| count(*) |
+--+
|18824 |
+--+
1 row in set (18.27 sec)
## For comparison, without indexes whatsoever:
MariaDB [ontime]> select count(*) from ontime ignore index (depdelay,
originstate) where depdelay > 30 and OriginState IN ('WA', 'CA');
+--+
| count(*) |
+--+
|18824 |
+--+
1 row in set (15.50 sec)
"iostat -x" showed disk utilization as 0% during all of the above listed
queries, so all accessed data was either in cache or in buffer pool.
For an idea about predicate selectivities and correlation:
ROWS FRACTION
whole table 1578171 100.0%
depdelay>301518069.6%
OriginState IN ('WA', 'CA')206576 13.1%
both188241.1%
Conclusions:
- sort-intersect gives about 7x improvement
- Igor's statement that sort-intersect can't be useful when the load is not
IO-bound is not supported by the experiment.
My next step was to try with narrower ranges.
I changed the predicates to
- OriginState IN ('IL', 'VW') (100K rows instead of 200K we had with WA+CA)
- depdelay > 60 (75 K rows instead of 150K we had with depdelay>30)
MariaDB [ontime]> set optimizer_switch='index_merge_sort_intersection=on';
MariaDB [ontime]> explain select count(*) from ontime where depdelay > 60 and
OriginState IN ('IL', 'VT');
++-++---+--+--+-+--+---+-
Re: [Maria-developers] Benchmarking index_merge sort_intersect
Per Philip's request, details to replicate the dataset: == Server settings == [mysqld] innodb_file_per_table=1 innodb_file_format=barracuda innodb_log_file_size=100M == DDL == I based on Percona's DDL http://www.mysqlperformanceblog.com/2009/10/02/analyzing-air-traffic-performance-with-infobright-and-monetdb/ and added a couple of indexes. CREATE TABLE `ontime` ( `Year` year(4) DEFAULT NULL, `Quarter` tinyint(4) DEFAULT NULL, `Month` tinyint(4) DEFAULT NULL, `DayofMonth` tinyint(4) DEFAULT NULL, `DayOfWeek` tinyint(4) DEFAULT NULL, `FlightDate` date DEFAULT NULL, `UniqueCarrier` char(7) DEFAULT NULL, `AirlineID` int(11) DEFAULT NULL, `Carrier` char(2) DEFAULT NULL, `TailNum` varchar(50) DEFAULT NULL, `FlightNum` varchar(10) DEFAULT NULL, `Origin` char(5) DEFAULT NULL, `OriginCityName` varchar(100) DEFAULT NULL, `OriginState` char(2) DEFAULT NULL, `OriginStateFips` varchar(10) DEFAULT NULL, `OriginStateName` varchar(100) DEFAULT NULL, `OriginWac` int(11) DEFAULT NULL, `Dest` char(5) DEFAULT NULL, `DestCityName` varchar(100) DEFAULT NULL, `DestState` char(2) DEFAULT NULL, `DestStateFips` varchar(10) DEFAULT NULL, `DestStateName` varchar(100) DEFAULT NULL, `DestWac` int(11) DEFAULT NULL, `CRSDepTime` int(11) DEFAULT NULL, `DepTime` int(11) DEFAULT NULL, `DepDelay` int(11) DEFAULT NULL, `DepDelayMinutes` int(11) DEFAULT NULL, `DepDel15` int(11) DEFAULT NULL, `DepartureDelayGroups` int(11) DEFAULT NULL, `DepTimeBlk` varchar(20) DEFAULT NULL, `TaxiOut` int(11) DEFAULT NULL, `WheelsOff` int(11) DEFAULT NULL, `WheelsOn` int(11) DEFAULT NULL, `TaxiIn` int(11) DEFAULT NULL, `CRSArrTime` int(11) DEFAULT NULL, `ArrTime` int(11) DEFAULT NULL, `ArrDelay` int(11) DEFAULT NULL, `ArrDelayMinutes` int(11) DEFAULT NULL, `ArrDel15` int(11) DEFAULT NULL, `ArrivalDelayGroups` int(11) DEFAULT NULL, `ArrTimeBlk` varchar(20) DEFAULT NULL, `Cancelled` tinyint(4) DEFAULT NULL, `CancellationCode` char(1) DEFAULT NULL, `Diverted` tinyint(4) DEFAULT NULL, `CRSElapsedTime` int(11) DEFAULT NULL, `ActualElapsedTime` int(11) DEFAULT NULL, `AirTime` int(11) DEFAULT NULL, `Flights` int(11) DEFAULT NULL, `Distance` int(11) DEFAULT NULL, `DistanceGroup` tinyint(4) DEFAULT NULL, `CarrierDelay` int(11) DEFAULT NULL, `WeatherDelay` int(11) DEFAULT NULL, `NASDelay` int(11) DEFAULT NULL, `SecurityDelay` int(11) DEFAULT NULL, `LateAircraftDelay` int(11) DEFAULT NULL, `FirstDepTime` varchar(10) DEFAULT NULL, `TotalAddGTime` varchar(10) DEFAULT NULL, `LongestAddGTime` varchar(10) DEFAULT NULL, `DivAirportLandings` varchar(10) DEFAULT NULL, `DivReachedDest` varchar(10) DEFAULT NULL, `DivActualElapsedTime` varchar(10) DEFAULT NULL, `DivArrDelay` varchar(10) DEFAULT NULL, `DivDistance` varchar(10) DEFAULT NULL, `Div1Airport` varchar(10) DEFAULT NULL, `Div1WheelsOn` varchar(10) DEFAULT NULL, `Div1TotalGTime` varchar(10) DEFAULT NULL, `Div1LongestGTime` varchar(10) DEFAULT NULL, `Div1WheelsOff` varchar(10) DEFAULT NULL, `Div1TailNum` varchar(10) DEFAULT NULL, `Div2Airport` varchar(10) DEFAULT NULL, `Div2WheelsOn` varchar(10) DEFAULT NULL, `Div2TotalGTime` varchar(10) DEFAULT NULL, `Div2LongestGTime` varchar(10) DEFAULT NULL, `Div2WheelsOff` varchar(10) DEFAULT NULL, `Div2TailNum` varchar(10) DEFAULT NULL, `Div3Airport` varchar(10) DEFAULT NULL, `Div3WheelsOn` varchar(10) DEFAULT NULL, `Div3TotalGTime` varchar(10) DEFAULT NULL, `Div3LongestGTime` varchar(10) DEFAULT NULL, `Div3WheelsOff` varchar(10) DEFAULT NULL, `Div3TailNum` varchar(10) DEFAULT NULL, `Div4Airport` varchar(10) DEFAULT NULL, `Div4WheelsOn` varchar(10) DEFAULT NULL, `Div4TotalGTime` varchar(10) DEFAULT NULL, `Div4LongestGTime` varchar(10) DEFAULT NULL, `Div4WheelsOff` varchar(10) DEFAULT NULL, `Div4TailNum` varchar(10) DEFAULT NULL, `Div5Airport` varchar(10) DEFAULT NULL, `Div5WheelsOn` varchar(10) DEFAULT NULL, `Div5TotalGTime` varchar(10) DEFAULT NULL, `Div5LongestGTime` varchar(10) DEFAULT NULL, `Div5WheelsOff` varchar(10) DEFAULT NULL, `Div5TailNum` varchar(10) DEFAULT NULL, KEY `AirlineID` (`AirlineID`), KEY `OriginState` (`OriginState`), KEY `Origin` (`Origin`), KEY `DepDelay` (`DepDelay`), KEY `DepDelayMinutes` (`DepDelayMinutes`), KEY `ArrDelay` (`ArrDelay`), KEY `ArrDelayMinutes` (`ArrDelayMinutes`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 == The dataset === You should get the same* by doing the following: for i in `seq 1 3` ; do wget http://www.transtats.bts.gov/Download/On_Time_On_Time_Performance_2009_$i.zip ; done LOAD DATA INFILE 'On_Time_On_Time_Performance_2009_1.csv' INTO TABLE ontime FIELDS TERMINATED BY ',' ENCLOSED BY '\"' IGNORE 1 LINES; LOAD DATA INFILE 'On_Time_On_Time_Performance_2009_2.csv' INTO TABLE ontime FIELDS TERMINATED BY ',' ENCLOSED BY '\"' IGNORE 1 LINES; LOAD DATA INFILE 'On_Time_On_Time_Performance_2009_3.csv' INTO TABLE ontime FIELDS TERMINATED
Re: [Maria-developers] Benchmarking index_merge sort_intersect
Hello Philip, One more note about correlated results: I agree that it's true that the optimizer can't make a good choice if the data is correlated. But since this is intersection, we could take this from other end: when doing intersection scan, calculate utility of using extra indexes on the fly. If we scan some amount of rows and see that using extra indexes doesn't provide any benefit, we could fall back to "unary intersection" which would be very similar to what range+DS-MRR do. The question is, is the issue of slowdowns because of correlations is such a big problem that we need to drop other things and start working on a solution like the above... BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] IMPORTANT: LP bug #611648 does not allow running benchmarks for DS-MRR normally
Hi! On Tue, Feb 01, 2011 at 09:16:58PM -0800, Igor Babaev wrote: > I've just reported bug #611648 that probably explains the strange > results I had when trying to run my benchmark queries for DS-MRR > about which I talked at today's optimizer meeting. > I don't think that the posted queries show any problems. Everything is working as designed. I've posted the details at: https://bugs.launchpad.net/maria/+bug/711648 BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Benchmarking mrr_sort_keys=on|off.
Hi!
Following the discussion of benchmarks of mrr_sort_keys=on|off functionality,
I've done a re-run. Please find the results below.
## Dataset: DBT-3, scale=10, xtradb,
## innodb_file_per_table=1
## innodb_file_format=barracuda
## innodb_log_file_size=256M
## innodb_buffer_pool_size=4000M
##
## Platform: work.askmonty.org host
## Cold-cache is achieved by:
## - stopping the server
## - echo 1 > /proc/sys/vm/drop_caches
## - starting the server
## - running EXPLAIN {query}
##
## MariaDB: 5.3-current, release build (compile-pentium-max)
##
Result summary:
* mrr_sort_keys=on provides speedup (2.25 min vs 13.5 min) on cold cache.
* on hot cache, there is no big difference (~1.3 sec in both cases).
* I was able to repeat Igor's August results with the current 5.3. It is
not clear why Igor can't repeat them anymore. (An evasive bug? or just
mistake in experiments?)
set join_buffer_size=1024*1024*32;
set join_buffer_space_limit=1024*1024*32;
set join_cache_level=6;
##
## Cold cache, mrr_sort_keys=on:
##
[22:12] root@dbt3sf10>explain select avg(l_extendedprice) from orders, lineitem
where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date
'1992-07-31';
++-+--+---++---+-++++
| id | select_type | table| type | possible_keys
| key | key_len | ref| rows | Extra
|
++-+--+---++---+-++++
| 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate
| i_o_orderdate | 4 | NULL | 142680 | Using
where; Using index |
| 1 | SIMPLE | lineitem | ref |
PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 |
dbt3sf10.orders.o_orderkey | 1 | Using join buffer (flat, BKA join) |
++-+--+---++---+-++++
2 rows in set (3.04 sec)
[22:06] root@dbt3sf10>select avg(l_extendedprice) from orders, lineitem where
l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date
'1992-07-31';
+--+
| avg(l_extendedprice) |
+--+
| 38229.0683265894 |
+--+
1 row in set (2 min 13.40 sec)
[22:09] root@dbt3sf10> show status like "%handler%";
+++
| Variable_name | Value |
+++
| Handler_commit | 2 |
| Handler_read_key | 193396 |
| Handler_read_next | 966535 |
+++
15 rows in set (0.29 sec)
#
# mrr_sort_keys=off, cold cache:
#
[22:20] root@dbt3sf10>explain select avg(l_extendedprice) from orders, lineitem
where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date
'1992-07-31';
++-+--+---++---+-+++--+
| id | select_type | table| type | possible_keys
| key | key_len | ref| rows | Extra
|
++-+--+---++---+-+++--+
| 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate
| i_o_orderdate | 4 | NULL | 142680 | Using
where; Using index |
| 1 | SIMPLE | lineitem | ref |
PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 |
dbt3sf10.orders.o_orderkey | 2 | |
++-+--+---++---+-+++--+
[22:20] root@dbt3sf10>select avg(l_extendedprice) from orders, lineitem where
l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date
'1992-07-31';
+--+
| avg(l_extendedprice) |
+--+
| 38229.0683265898 |
+--+
1 row in set (13 min 23.68 sec)
[22:09] root@dbt3sf10> show status like "%handler%";
+++
| Variable_name | Value |
+++
| Handler_commit | 2 |
| Handler_read_key | 193396 |
| Handler_read_next | 966535 |
+++
Re: [Maria-developers] [Commits] Rev 2833: Fixed LP bug #664508. in file:///home/igor/maria/maria-5.3-mwl128-bug664508/
Hello Igor,
On Sun, Oct 24, 2010 at 02:22:02PM -0700, Igor Babaev wrote:
> At file:///home/igor/maria/maria-5.3-mwl128-bug664508/
>
>
> revno: 2833
> revision-id: i...@askmonty.org-20101024212201-t8iius4jikcvm3zb
> parent: i...@askmonty.org-2010103047-phunrh5g3xzrx2d9
> committer: Igor Babaev
> branch nick: maria-5.3-mwl128-bug664508
> timestamp: Sun 2010-10-24 14:22:01 -0700
> message:
> Fixed LP bug #664508.
> When join buffers are employed no index scan for the first
> table with grouping columns can be used.
...
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2010-10-18 20:56:48 +
> +++ b/sql/sql_select.cc 2010-10-24 21:22:01 +
> @@ -7980,8 +7980,11 @@
> JOIN_TAB *tab=join->join_tab+i;
> if (tab->use_join_cache)
> {
> - JOIN_TAB *sort_by_tab= join->get_sort_by_join_tab();
> - if (sort_by_tab && !join->need_tmp)
> + JOIN_TAB *sort_by_tab= join->group && join->simple_group &&
> + join->group_list ?
> +join->join_tab+join->const_tables :
> + join->get_sort_by_join_tab();
> + if (sort_by_tab)
>{
> join->need_tmp= 1;
> join->simple_order= join->simple_group= 0;
Here sort_by_tab is assigned inside the " for (i=join->const_tables ; ..."
loop. I think the code will look much more cleaner if it was calculated before
the loop (and actually we don't need to loop at all if sort_by_tab==NULL).
> ___
> commits mailing list
> comm...@mariadb.org
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
--
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Fwd: [Commits] Rev 2901: BNLH algorithm always used a full table scan over the joined table in file:///home/igor/maria/maria-5.3-mwl128-hashrange/
On Fri, Feb 11, 2011 at 08:49:37PM -0800, Igor Babaev wrote:
> Hi Sergey,
>
> Please review this patch that fixes a defect of the current
> implementation of the mwl #128 that never couples hash join
> with range/index_merge scans.
> The patch also adds prefix #hash# to the names of the used hash indexes.
> (we agreed upon this at the last optimizer meeting).
>
> Regards,
> Igor.
>
> Original Message
> Subject: [Commits] Rev 2901: BNLH algorithm always used a full table
> scan over the joined table in
> file:///home/igor/maria/maria-5.3-mwl128-hashrange/
> Date: Fri, 11 Feb 2011 20:41:23 -0800 (PST)
> From: Igor Babaev
> Reply-To: maria-developers@lists.launchpad.net
> To:
>
> At file:///home/igor/maria/maria-5.3-mwl128-hashrange/
>
>
> revno: 2901
> revision-id: i...@askmonty.org-20110212044122-w9n3jdk3d2ps0w3o
> parent: i...@askmonty.org-20110207231903-3rqbfs50d33lk3r9
> committer: Igor Babaev
> branch nick: maria-5.3-mwl128-hashrange
> timestamp: Fri 2011-02-11 20:41:22 -0800
> message:
> BNLH algorithm always used a full table scan over the joined table
> even in the cases when there existed range/index-merge scans that
> were cheaper than the full table scan.
> This was a defect/bug of the implementation of mwl #128.
> Now hash join can work not only with full table scan of the joined
> table, but also with full index scan, range and index-merge scans.
> Accordingly, in the cases when hash join is used the column 'type'
> in the EXPLAINs can contain now 'hash_ALL', 'hash_index', 'hash_range'
> and 'hash_index_merge'. If hash join is coupled with a range/index_merge
> scan then the columns 'key' and 'key_len' contain info not only on
> the used hash index, but also on the indexes used for the scan.
...
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2011-02-06 04:57:03 +
> +++ b/sql/sql_select.cc 2011-02-12 04:41:22 +
...
> @@ -8419,6 +8438,11 @@
>sort_by_tab->type= JT_ALL;
>sort_by_tab->read_first_record= join_init_read_record;
> }
> +else if (sort_by_tab->type == JT_HASH_NEXT)
> +{
> + sort_by_tab->type= JT_HASH;
> + sort_by_tab->read_first_record= join_init_read_record;
> +}
>}
>break;
> }
I have a question only to this part of the patch: I don't understand how the
above code could work.
The if statement above the one you've added checks if sort_by_tab was using
[ordered] index scan to produce records in orderer, and if yes, switches it to
a full scan (because use of join buffering will break ordering anyway).
I suppose the part you've added does something similar, but I totally fail to
understand what exactly it does.
Now, some comments on the new EXPLAIN output:
ISSUE1.
Consider a query:
MariaDB [j1]> explain select * from t3, t4 where t3.col1=t4.col2;
++-+---+--+---+---+-++--+--+
| id | select_type | table | type | possible_keys | key | key_len |
ref| rows | Extra|
++-+---+--+---+---+-++--+--+
| 1 | SIMPLE | t3| ALL | NULL | NULL | NULL|
NULL | 1000 | Using where |
| 1 | SIMPLE | t4| hash_ALL | NULL | #hash#$hj | 5 |
j1.t3.col1 | 1000 | Using where; Using join buffer (flat, BNLH join) |
++-+---+--+---+---+-++--+--+
2 rows in set (0.00 sec)
AFAIU #hash#$hj means the hash key on the hashtable that's used for
hash join. I think the name is too convoluted. The hashtable has only one
key, if we need to tell it from t4's indexes it would be sufficient to use
"#hashkey#", without the cryptic "$hj".
As for "#" characters: EXPLAIN already shows "internal" tables/indexes:
MariaDB [j1]> explain select * from (select * from t1) X;
++-++--+---+--+-+--+--+---+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-++--+---+--+-+--+--+---+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL| NULL
| 1000 | |
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL| NULL
| 1000 | |
++-++--+---+--+-+--+--+---+
# The following is from MWL#90 tree:
MariaDB [test]> explain select * from t10 where a in (select max(b) from t10
group by a);
++-+---
[Maria-developers] MWL#90 combined patch for review
Hi Monty, Please find attached the complete patch of MWL#90 for review. The tree with the code is on launchpad/buildbot: https://code.launchpad.net/~maria-captains/maria/5.3-subqueries-mwl90 http://buildbot.askmonty.org/buildbot/grid?branch=5.3-subqueries-mwl90 and it has no failures other than those also found in mainline 5.3. The merge from 5.3-main was about a week ago. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Fwd: [Commits] Rev 2901: BNLH algorithm always used a full table scan over the joined table in file:///home/igor/maria/maria-5.3-mwl128-hashrange/
On Mon, Feb 21, 2011 at 03:20:13PM -0800, Igor Babaev wrote:
> >> === modified file 'sql/sql_select.cc'
> >> --- a/sql/sql_select.cc2011-02-06 04:57:03 +
> >> +++ b/sql/sql_select.cc2011-02-12 04:41:22 +
> > ...
> >> @@ -8419,6 +8438,11 @@
> >>sort_by_tab->type= JT_ALL;
> >>sort_by_tab->read_first_record= join_init_read_record;
> >> }
> >> +else if (sort_by_tab->type == JT_HASH_NEXT)
> >> +{
> >> + sort_by_tab->type= JT_HASH;
> >> + sort_by_tab->read_first_record= join_init_read_record;
> >> +}
> >>}
> >>break;
> >> }
> > I have a question only to this part of the patch: I don't understand how the
> > above code could work.
> >
> > The if statement above the one you've added checks if sort_by_tab was using
> > [ordered] index scan to produce records in orderer, and if yes, switches it
> > to
> > a full scan (because use of join buffering will break ordering anyway).
> >
> > I suppose the part you've added does something similar, but I totally fail
> > to
> > understand what exactly it does.
>
> The above code says:
> if hash join is used do not use full index scan to look through the
> joined table, rather use full table scan in this case.
>
I've put a DBUG_ASSERT(0) right after the "sort_by_tab->type= JT_HASH;" line
and ran the testsuite. The assertion didn't fire. I did this because I suspect
(although can't provide a sound proof right away) that the part inside the
else if () { ...} is deadcode.
Do you think you could easily prove me wrong by giving a testcase? If
constructing testcase is complicated, let's meet online and discuss my
suspicions about the deadcode.
> >
> > Now, some comments on the new EXPLAIN output:
> >
> >
> > ISSUE1.
> >
> > Consider a query:
> > MariaDB [j1]> explain select * from t3, t4 where t3.col1=t4.col2;
> > ++-+---+--+---+---+-++--+--+
> > | id | select_type | table | type | possible_keys | key | key_len
> > | ref| rows | Extra|
> > ++-+---+--+---+---+-++--+--+
> > | 1 | SIMPLE | t3| ALL | NULL | NULL | NULL
> > | NULL | 1000 | Using where |
> > | 1 | SIMPLE | t4| hash_ALL | NULL | #hash#$hj | 5
> > | j1.t3.col1 | 1000 | Using where; Using join buffer (flat, BNLH join) |
> > ++-+---+--+---+---+-++--+--+
> > 2 rows in set (0.00 sec)
> >
> > AFAIU #hash#$hj means the hash key on the hashtable that's used for
> > hash join. I think the name is too convoluted. The hashtable has only one
> > key, if we need to tell it from t4's indexes it would be sufficient to use
> > "#hashkey#", without the cryptic "$hj".
> >
> > As for "#" characters: EXPLAIN already shows "internal" tables/indexes:
>
> If you look into #maria-call log you'll see that I followed strictly
> Monty's instructions:
> to use the prefix #hash# before the index name I used to use, i.e.
> should be substituted for #hash#.
>
> The only deviation I afforded myself was: I used $hj instead of hj_key.
>
Ok. I still think that my objections are valid and current EXPLAIN output is
not the best but we could discuss/address this outside the scope of this
bugfix.
> > What is the index that's used for scanning the table t1? "#hash#cu1" shows
> > that hash join optimizer used access on index "cu1" in its analysis, but
> > generally it doesn't mean that full index scan on table t1 was done on index
> > cu1, doesn't it?
> > I would very much prefer to see #hash#cu1:USED_INDEX there (or taking into
> > account previous suggestions, hashkey:USED_INDEX). It will also be
> > consistent with how hash_range and hash_index_merge use that column.
> >
> Yes, the scan index is missing here. I will add.
OK.
> > ISSUE4
> > If key and key_len use hash_part:real_part notation, i.e. use semicolon, why
> > use underscore in hash_ALL, hash_index, etc? Won't hash:ALL, hash:index look
> > more consistent with other columns?
>
> again: I follow here Monty's recommendations. Nothing more, nor less.
>
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
[Maria-developers] [pser...@askmonty.org: [Commits] Rev 2921: BUG#723822: Crash in get_constant_key_infix with EXISTS ( SELECT .. DISTINCT ) in file:///home/psergey/dev2/5.3/]
Hi Timour,
Could you please review the below? (Asking you because the fix is in the loose
index scan optimizer)
- Forwarded message from Sergey Petrunya -
From: Sergey Petrunya
To: comm...@mariadb.org
X-Mailer: mail (GNU Mailutils 1.2)
Date: Fri, 25 Feb 2011 12:59:00 +0300 (MSK)
Subject: [Commits] Rev 2921: BUG#723822: Crash in get_constant_key_infix
with EXISTS ( SELECT .. DISTINCT ) in file:///home/psergey/dev2/5.3/
At file:///home/psergey/dev2/5.3/
revno: 2921
revision-id: pser...@askmonty.org-20110225095856-qgykwziqv5h1suqr
parent: pser...@askmonty.org-20110224203200-4uf4e4kngwdmtmgw
committer: Sergey Petrunya
branch nick: 5.3
timestamp: Fri 2011-02-25 12:58:56 +0300
message:
BUG#723822: Crash in get_constant_key_infix with EXISTS ( SELECT .. DISTINCT )
- Make get_constant_key_infix() take into account that there may be SEL_TREEs
with type=SEL_ARG::MAYBE_KEY,
which it cannot process. check_quick_select() already has a check for such
condition.
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result2011-02-10 08:36:43 +
+++ b/mysql-test/r/subselect4.result2011-02-25 09:58:56 +
@@ -689,3 +689,21 @@
2 DEPENDENT SUBQUERY X ALL NULLNULLNULLNULL
10
2 DEPENDENT SUBQUERY B ALL a,b NULLNULLNULL
1000Range checked for each record (index map: 0x3)
drop table t1, t2;
+#
+# BUG#723822: Crash in get_constant_key_infix with EXISTS ( SELECT .. DISTINCT
)
+#
+CREATE TABLE t1 ( f1 int(11), f3 varchar(1)) ;
+INSERT INTO t1 VALUES ('8','c'),('5','f');
+ALTER TABLE t1 ADD KEY (f3,f1);
+CREATE TABLE t2 ( f4 varchar(1)) ;
+INSERT INTO t2 VALUES ('f'),('d');
+SELECT * FROM t2
+WHERE EXISTS (
+SELECT DISTINCT f3
+FROM t1
+WHERE f3 <= t2.f4
+);
+f4
+f
+d
+drop table t1,t2;
=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test 2011-02-10 08:36:43 +
+++ b/mysql-test/t/subselect4.test 2011-02-25 09:58:56 +
@@ -619,3 +619,24 @@
from t1 A;
drop table t1, t2;
+
+--echo #
+--echo # BUG#723822: Crash in get_constant_key_infix with EXISTS ( SELECT ..
DISTINCT )
+--echo #
+CREATE TABLE t1 ( f1 int(11), f3 varchar(1)) ;
+INSERT INTO t1 VALUES ('8','c'),('5','f');
+
+ALTER TABLE t1 ADD KEY (f3,f1);
+
+CREATE TABLE t2 ( f4 varchar(1)) ;
+INSERT INTO t2 VALUES ('f'),('d');
+
+SELECT * FROM t2
+WHERE EXISTS (
+SELECT DISTINCT f3
+FROM t1
+WHERE f3 <= t2.f4
+);
+
+drop table t1,t2;
+
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2011-02-22 09:15:47 +
+++ b/sql/opt_range.cc 2011-02-25 09:58:56 +
@@ -11857,7 +11857,8 @@
Find the range tree for the current keypart. We assume that
index_range_tree points to the leftmost keypart in the index.
*/
-for (cur_range= index_range_tree; cur_range;
+for (cur_range= index_range_tree;
+ cur_range && cur_range->type == SEL_ARG::KEY_RANGE;
cur_range= cur_range->next_key_part)
{
if (cur_range->field->eq(cur_part->field))
___
commits mailing list
comm...@mariadb.org
https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
- End forwarded message -
--
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
[Maria-developers] Igor please review: [Commits] Rev 2926: BUG#724275: Crash in JOIN::optimize in maria-5.3 in file:///home/psergey/dev2/5.3-2/]
Hello Igor,
Could you please review the below:
- Forwarded message from Sergey Petrunya -
From: Sergey Petrunya
To: comm...@mariadb.org
X-Mailer: mail (GNU Mailutils 1.2)
Date: Tue, 1 Mar 2011 00:30:02 +0300 (MSK)
Subject: [Commits] Rev 2926: BUG#724275: Crash in JOIN::optimize in
maria-5.3 in file:///home/psergey/dev2/5.3-2/
At file:///home/psergey/dev2/5.3-2/
revno: 2926
revision-id: pser...@askmonty.org-20110228212959-hl3selurcdou7d22
parent: i...@askmonty.org-20110227181411-k81vmi35o0no21ab
committer: Sergey Petrunya
branch nick: 5.3-2
timestamp: Tue 2011-03-01 00:29:59 +0300
message:
BUG#724275: Crash in JOIN::optimize in maria-5.3
- Make equality-substitution-for-ref-access code in JOIN::optimize() treat
join_tab->ref.key_copy correctly
(in the way create_ref_for_key() has filled it).
=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result 2010-10-30 13:07:45 +
+++ b/mysql-test/r/join.result 2011-02-28 21:29:59 +
@@ -1251,3 +1251,37 @@
Handler_read_rnd_next 1
DROP TABLE t1, t2;
End of 5.1 tests
+#
+# BUG#724275: Crash in JOIN::optimize in maria-5.3
+#
+create table t1 (a int);
+insert into t1 values (1),(2);
+insert into t1 select * from t1;
+create table t2 (a int, b int, key(a,b));
+insert into t2 values (1,1),(1,2),(1,3),(1,4),(2,5),(2,6),(2,7),(2,8),(2,9);
+insert into t2 select * from t2;
+insert into t2 select * from t2;
+insert into t2 select * from t2;
+create table t3 (a int, b int, key(a));
+insert into t3 values (1,1),(2,2);
+select * from
+t3 straight_join t1 straight_join t2 force index(a)
+where t2.a=1 and t2.b=t1.a and t1.a=t3.b and t3.a=1;
+a b a a b
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+1 1 1 1 1
+drop table t1,t2,t3;
=== modified file 'mysql-test/t/join.test'
--- a/mysql-test/t/join.test2010-10-30 13:07:45 +
+++ b/mysql-test/t/join.test2011-02-28 21:29:59 +
@@ -941,3 +941,26 @@
DROP TABLE t1, t2;
--echo End of 5.1 tests
+
+--echo #
+--echo # BUG#724275: Crash in JOIN::optimize in maria-5.3
+--echo #
+
+create table t1 (a int);
+insert into t1 values (1),(2);
+insert into t1 select * from t1;
+
+create table t2 (a int, b int, key(a,b));
+insert into t2 values (1,1),(1,2),(1,3),(1,4),(2,5),(2,6),(2,7),(2,8),(2,9);
+insert into t2 select * from t2;
+insert into t2 select * from t2;
+insert into t2 select * from t2;
+
+create table t3 (a int, b int, key(a));
+insert into t3 values (1,1),(2,2);
+select * from
+ t3 straight_join t1 straight_join t2 force index(a)
+where t2.a=1 and t2.b=t1.a and t1.a=t3.b and t3.a=1;
+
+drop table t1,t2,t3;
+
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-02-27 18:14:11 +
+++ b/sql/sql_select.cc 2011-02-28 21:29:59 +
@@ -1056,6 +1056,7 @@
*/
for (JOIN_TAB *tab= join_tab + const_tables; tab < join_tab + tables; tab++)
{
+uint key_copy_index=0;
for (uint i=0; i < tab->ref.key_parts; i++)
{
@@ -1071,13 +1072,14 @@
{
*ref_item_ptr= ref_item;
Item *item= ref_item->real_item();
-store_key *key_copy= tab->ref.key_copy[i];
+store_key *key_copy= tab->ref.key_copy[key_copy_index];
if (key_copy->type() == store_key::FIELD_STORE_KEY)
{
store_key_field *field_copy= ((store_key_field *)key_copy);
field_copy->change_source_field((Item_field *) item);
}
}
+ key_copy_index++;
}
}
___
commits mailing list
comm...@mariadb.org
https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
- End forwarded message -
--
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
[Maria-developers] Dgcov coverage for: Re: MWL#90 combined patch for review
On Tue, Feb 22, 2011 at 06:03:38PM +0300, Sergey Petrunya wrote:
> On Tue, Feb 22, 2011 at 05:24:37PM +0300, Sergey Petrunya wrote:
> > Hi Monty,
> >
> > Please find attached the complete patch of MWL#90 for review. The tree with
> > the code is on launchpad/buildbot:
> > https://code.launchpad.net/~maria-captains/maria/5.3-subqueries-mwl90
> > http://buildbot.askmonty.org/buildbot/grid?branch=5.3-subqueries-mwl90
Please find below dgcov coverage report for the tree. My summary is that
uncovered parts are
- error handling
- handling of semi-joins together with outer joins (a known bug/problem area)
- make_in_exists_conversion() which serves as a fallback in case we've failed
to convert the subquery to [merged] semi-join. I think I should be able to
come up with a test for this.
-- dgcov.out file starts --
File: sql/item_subselect.cc
---
| 505: 3844: uint len= my_snprintf(buf, sizeof(buf), "",
subquery_id);
|-: 3845: char *name;
| 505: 3846: if (!(name= (char*)thd->alloc(len + 1)))
|#: 3847:DBUG_RETURN(TRUE);
| 505: 3848: memcpy(name, buf, len+1);
|-: 3849:
. 505: 3850: if (!(result= new select_materialize_with_stats))
File: sql/item_subselect.h
---
.-: 536: THD * get_thd() { return thd; }
.-: 537: virtual int prepare()= 0;
.-: 538: virtual void fix_length_and_dec(Item_cache** row)= 0;
|#: 539: virtual int optimize() { DBUG_ASSERT(0); return 0; }
.-: 540: /*
.-: 541:Execute the engine
.-: 542:
File: sql/opt_subselect.cc
---
|-: 527:*/
|-: 528:
|-: 529:static
|#: 530:bool make_in_exists_conversion(THD *thd, JOIN *join,
Item_in_subselect *item)
|-: 531:{
|#: 532: DBUG_ENTER("make_in_exists_conversion");
|#: 533: JOIN *child_join= item->unit->first_select()->join;
|-: 534: Item_subselect::trans_res res;
|#: 535: item->changed= 0;
|#: 536: item->fixed= 0;
|-: 537:
|#: 538: SELECT_LEX *save_select_lex= thd->lex->current_select;
|#: 539: thd->lex->current_select= item->unit->first_select();
|-: 540:
|#: 541: res= item->select_transformer(child_join);
|-: 542:
|#: 543: thd->lex->current_select= save_select_lex;
|-: 544:
|#: 545: if (res == Item_subselect::RES_ERROR)
|#: 546:DBUG_RETURN(TRUE);
|-: 547:
|#: 548: item->changed= 1;
|#: 549: item->fixed= 1;
|-: 550:
|#: 551: Item *substitute= item->substitution;
|#: 552: bool do_fix_fields= !item->substitution->fixed;
|-: 553: /*
|-: 554:The Item_subselect has already been wrapped with
Item_in_optimizer, so we
|-: 555:should search for item->optimizer, not 'item'.
|-: 556: */
|#: 557: Item *replace_me= item->optimizer;
|#: 558: DBUG_ASSERT(replace_me==substitute);
|-: 559:
|-: 560: Item **tree= (item->emb_on_expr_nest == (TABLE_LIST*)1)?
|#: 561: &join->conds :
&(item->emb_on_expr_nest->on_expr);
|#: 562: if (replace_where_subcondition(join, tree, replace_me,
substitute,
|-: 563: do_fix_fields))
|#: 564:DBUG_RETURN(TRUE);
|#: 565: item->substitution= NULL;
|-: 566:
|#: 567: if (!thd->stmt_arena->is_conventional())
|-: 568: {
|-: 569:tree= (item->emb_on_expr_nest == (TABLE_LIST*)1)?
|-: 570: &join->select_lex->prep_where :
|#: 571: &(item->emb_on_expr_nest->prep_on_expr);
|-: 572:
|#: 573:if (replace_where_subcondition(join, tree, replace_me,
substitute,
|-: 574: FALSE))
|#: 575: DBUG_RETURN(TRUE);
|-: 576: }
|#: 577: DBUG_RETURN(FALSE);
|-: 578:}
|-: 579:
|-: 580:
| 1451: 695:if ((*in_subq)->is_flattenable_semijoin)
|-: 696:{
| 1375: 697: if (convert_subq_to_sj(join, *in_subq))
|#: 698:DBUG_RETURN(TRUE);
|-: 699:}
|-: 700:else
|-: 701:{
| 76: 702: if (convert_subq_to_jtbm(join, *in_subq, &remove_item))
|#: 703:DBUG_RETURN(TRUE);
|-: 704:}
| 1451: 705:if (remove_item)
|-: 706:{
| 76: 1236: DBUG_EN
[Maria-developers] Igor please review: Rev 2929: BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init in file:///home/psergey/dev2/5.3-2/]
Hello Igor,
Could you please review the below:
- Forwarded message from Sergey Petrunya -
From: Sergey Petrunya
To: comm...@mariadb.org
X-Mailer: mail (GNU Mailutils 1.2)
Date: Wed, 2 Mar 2011 00:17:53 +0300 (MSK)
Subject: [Commits] Rev 2929: BUG#693747: Assertion multi_range_read.cc:908:
int DsMrr_impl::dsmrr_init in file:///home/psergey/dev2/5.3-2/
At file:///home/psergey/dev2/5.3-2/
revno: 2929
revision-id: pser...@askmonty.org-20110301211747-8wvngodwxzr9i2e9
parent: pser...@askmonty.org-2011030107-1rmapkelx9l1kw8u
committer: Sergey Petrunya
branch nick: 5.3-2
timestamp: Wed 2011-03-02 00:17:47 +0300
message:
BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init
- Make DsMrr_impl::dsmrr_init() handle the case of
1. 1st MRR scan using DS-MRR strategy (i.e. doing key sorting and rowid
sorting)
2. 2nd MRR scan getting a buffer that's too small to fit one key element
and one rowid element, and so falling back to default MRR implementation
In this case, dsmrr_init() is invoked with {primary_handler,
secondary_handler}
initialized for DS-MRR scan and have to reset them to be initialized for the
default MRR scan.
=== modified file 'mysql-test/r/maria_mrr.result'
--- a/mysql-test/r/maria_mrr.result 2010-12-02 11:10:52 +
+++ b/mysql-test/r/maria_mrr.result 2011-03-01 21:17:47 +
@@ -403,3 +403,30 @@
count(*)
0
drop table t1, t2;
+#
+# BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init(
+#
+set @_save_join_cache_level= @@join_cache_level;
+set @_save_join_buffer_size= @@join_buffer_size;
+set join_cache_level=8;
+set join_buffer_size=1;
+CREATE TABLE t1 (
+f1 int(11), f2 varchar(32) COLLATE latin1_swedish_ci,
+f3 int(11), f4 varchar(1024) COLLATE utf8_bin,
+f5 varchar(1024) COLLATE latin1_bin,
+KEY (f5)
+) ENGINE=Aria TRANSACTIONAL=0 ;
+# Fill the table with some data
+SELECT alias2.f1 AS field1 , alias1.f2 AS field2
+FROM t1 AS alias1
+LEFT OUTER JOIN t1 AS alias2 ON alias1.f2 = alias2.f5
+WHERE alias2.f3 > 'k'
+AND alias2.f3 <= 'z'
+OR alias2.f4 IN ('o', 'y')
+AND alias2.f4 >= 'g' AND alias2.f4 < 'z';
+field1 field2
+Warnings:
+Warning1292Truncated incorrect DOUBLE value: 'k'
+set join_cache_level=@_save_join_cache_level;
+set join_buffer_size=@_save_join_buffer_size;
+drop table t1;
=== modified file 'mysql-test/t/maria_mrr.test'
--- a/mysql-test/t/maria_mrr.test 2010-11-09 16:02:08 +
+++ b/mysql-test/t/maria_mrr.test 2011-03-01 21:17:47 +
@@ -125,3 +125,81 @@
table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND
table1.pk<>0 ;
drop table t1, t2;
+
+--echo #
+--echo # BUG#693747: Assertion multi_range_read.cc:908: int
DsMrr_impl::dsmrr_init(
+--echo #
+set @_save_join_cache_level= @@join_cache_level;
+set @_save_join_buffer_size= @@join_buffer_size;
+
+set join_cache_level=8;
+set join_buffer_size=1;
+
+CREATE TABLE t1 (
+ f1 int(11), f2 varchar(32) COLLATE latin1_swedish_ci,
+ f3 int(11), f4 varchar(1024) COLLATE utf8_bin,
+ f5 varchar(1024) COLLATE latin1_bin,
+ KEY (f5)
+) ENGINE=Aria TRANSACTIONAL=0 ;
+
+--echo # Fill the table with some data
+--disable_query_log
+INSERT IGNORE INTO t1 VALUES
+('0','cueikuirqr','0','okay','hcueikuirqrzflno'),('2009','her','0','ULWAH','ehcueikuirqrzfln'),
+('0','YKAOE','0','of','qieehcueikuirqrz'),('2009','you\'re','0','k','nkqieehcueikuirq'),
+('2003','b','0','bgsnkqieehcueiku','the'),('0','MGUDG','0','p','m'),
+('0','UXAGU','0','elfhjawbwbgsnkqi','HZXVA'),('2004','bwbgsnkqie','0','x','something'),
+('2007','s','0','okay','slelfhjawbwbgsnk'),('2007','the','0','or','if'),
+('2008','TDLKE','0','mean','MGWNJ'),('2006','do','0','KGFVV','see'),
+('2001','why','0','a','mean'),(NULL,'THKCG','0','no','YFLDY'),
+(NULL,'x','0','mean','e'),('2007','yncitaeysb','0','WDOSC','tgyncitaeysbgucs'),
+(NULL,'ZEOXX','0','awbwbgsnkqieehcu','jawbwbgsnkqieehc'),('2006','hjawbwbgsn','0&
[Maria-developers] Igor please review: BUG#707925: Wrong result with join_cache_level=6 optimizer_use_mrr = force (incremental, BKA join) in file:///home/psergey/dev2/5.3/]
Hello Igor,
Could you please review the below:
- Forwarded message from Sergey Petrunya -
From: Sergey Petrunya
To: comm...@mariadb.org
X-Mailer: mail (GNU Mailutils 1.2)
Date: Wed, 2 Mar 2011 16:27:43 +0300 (MSK)
Subject: [Commits] Rev 2929: BUG#707925: Wrong result with
join_cache_level=6 optimizer_use_mrr = force (incremental,
BKA join) in file:///home/psergey/dev2/5.3/
At file:///home/psergey/dev2/5.3/
revno: 2929
revision-id: pser...@askmonty.org-20110302132739-s6vasca1nm6eh2le
parent: pser...@askmonty.org-2011030107-1rmapkelx9l1kw8u
committer: Sergey Petrunya
branch nick: 5.3
timestamp: Wed 2011-03-02 16:27:39 +0300
message:
BUG#707925: Wrong result with join_cache_level=6 optimizer_use_mrr = force
(incremental, BKA join)
- Add debugging aid which maps MRR's range_ids (which are pointers so are
different on every execution
and hard to track) to range numbers (which allow much easier debugging).
The mapper is not compiled
unless the developer puts a #define into code to request it.
- Fix the bug: the problem was that Mrr_ordered_index_reader's
interrupt_read() and resume_read() would
save and restore 1) index tuple 2) the rowid (as bytes returned by
handler->position()). Clustered
primary key columns were not saved/restored.
They are not explicitly present in the index tuple (i.e.
table->key_info[secondary_key].key_parts
doesn't list them), but they are actually there, in particular
table->field[clustered_primary_key_member].part_of_key(secondary_key) == 1.
Index condition pushdown
code [correctly] uses the latter as inidication that pushed index condition
can refer to clustered PK
members.
The fix was to make interrupt_read()/resume_read() to save/restore
clustered primary key members as well,
so that we get correct values for them when evaluating pushed index
condition.
=== modified file 'mysql-test/r/innodb_mrr.result'
--- a/mysql-test/r/innodb_mrr.result2011-01-17 21:26:04 +
+++ b/mysql-test/r/innodb_mrr.result2011-03-02 13:27:39 +
@@ -736,3 +736,31 @@
c1 c2 c3
08:29:45 NULL2009-02-01
drop table `t1`;
+#
+# BUG#707925: Wrong result with join_cache_level=6 optimizer_use_mrr = force
(incremental, BKA join)
+#
+set @_save_join_cache_level= @@join_cache_level;
+set join_cache_level = 6;
+CREATE TABLE t1 (
+f1 int(11), f2 int(11), f3 varchar(1), f4 varchar(1),
+PRIMARY KEY (f1),
+KEY (f3),
+KEY (f2)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('11','8','f','f'),('12','5','v','v'),('13','8','s','s'),
+('14','8','a','a'),('15','6','p','p'),('16','7','z','z'),('17','2','a','a'),
+('18','5','h','h'),('19','7','h','h'),('20','2','v','v'),('21','9','v','v'),
+('22','142','b','b'),('23','3','y','y'),('24','0','v','v'),('25','3','m','m'),
+('26','5','z','z'),('27','9','n','n'),('28','1','d','d'),('29','107','a','a');
+select count(*) from (
+SELECT alias1.f2
+FROM
+t1 AS alias1 JOIN (
+t1 AS alias2 FORCE KEY (f3) JOIN
+t1 AS alias3 FORCE KEY (f2) ON alias3.f2 = alias2.f2 AND alias3.f4 = alias2.f3
+) ON alias3.f1 <= alias2.f1
+) X;
+count(*)
+361
+set join_cache_level=@_save_join_cache_level;
+drop table t1;
=== modified file 'mysql-test/t/innodb_mrr.test'
--- a/mysql-test/t/innodb_mrr.test 2011-01-17 21:26:04 +
+++ b/mysql-test/t/innodb_mrr.test 2011-03-02 13:27:39 +
@@ -426,3 +426,31 @@
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2;
drop table `t1`;
+--echo #
+--echo # BUG#707925: Wrong result with join_cache_level=6 optimizer_use_mrr =
force (incremental, BKA join)
+--echo #
+set @_save_join_cache_level= @@join_cache_level;
+set join_cache_level = 6;
+CREATE TABLE t1 (
+ f1 int(11), f2 int(11), f3 varchar(1), f4 varchar(1),
+ PRIMARY KEY (f1),
+ KEY (f3),
+ KEY (f2)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('11','8','f','f'),('12','5','v','v'),('13','8','s','s'),
+('14','8','
[Maria-developers] Igor please review: BUG#707925: Wrong result with join_cache_level=6 optimizer_use_mrr = force (incremental, BKA join) in file:///home/psergey/dev2/5.3/]
On Wed, Mar 02, 2011 at 08:04:39AM -0800, Igor Babaev wrote:
> > +SELECT alias2.f1 AS field1 , alias1.f2 AS field2
> > +FROM t1 AS alias1
> > +LEFT OUTER JOIN t1 AS alias2 ON alias1.f2 = alias2.f5
> > +WHERE alias2.f3 > 'k'
> > +AND alias2.f3 <= 'z'
> > +OR alias2.f4 IN ('o', 'y')
> > +AND alias2.f4 >= 'g' AND alias2.f4 < 'z';
> > +field1 field2
> > +Warnings:
> > +Warning1292Truncated incorrect DOUBLE value: 'k'
>
> Why do we need comparison of a string with an integer that generates the
> above warning? Can we do without it? Why not to use char(4) instead
> if integer?
Got rid of string vs integer comparison, as well as simplified the testcase a
bit (further simplification is difficult). Please find the result below:
- Forwarded message from Sergey Petrunya -
From: Sergey Petrunya
To: comm...@mariadb.org
X-Mailer: mail (GNU Mailutils 1.2)
Date: Wed, 2 Mar 2011 17:06:15 +0300 (MSK)
Subject: [Commits] Rev 2929: BUG#707925: Wrong result with
join_cache_level=6 optimizer_use_mrr = force (incremental,
BKA join) in file:///home/psergey/dev2/5.3/
At file:///home/psergey/dev2/5.3/
----
revno: 2929
revision-id: pser...@askmonty.org-20110302140613-4qt8xvdvxs4t4m98
parent: pser...@askmonty.org-2011030107-1rmapkelx9l1kw8u
committer: Sergey Petrunya
branch nick: 5.3
timestamp: Wed 2011-03-02 17:06:13 +0300
message:
BUG#707925: Wrong result with join_cache_level=6 optimizer_use_mrr = force
(incremental, BKA join)
- Add debugging aid which maps MRR's range_ids (which are pointers so are
different on every execution
and hard to track) to range numbers (which allow much easier debugging).
The mapper is not compiled
unless the developer puts a #define into code to request it.
- Fix the bug: the problem was that Mrr_ordered_index_reader's
interrupt_read() and resume_read() would
save and restore 1) index tuple 2) the rowid (as bytes returned by
handler->position()). Clustered
primary key columns were not saved/restored.
They are not explicitly present in the index tuple (i.e.
table->key_info[secondary_key].key_parts
doesn't list them), but they are actually there, in particular
table->field[clustered_primary_key_member].part_of_key(secondary_key) == 1.
Index condition pushdown
code [correctly] uses the latter as inidication that pushed index condition
can refer to clustered PK
members.
The fix was to make interrupt_read()/resume_read() to save/restore
clustered primary key members as well,
so that we get correct values for them when evaluating pushed index
condition.
[Second attempt, make the code compile without the debugging aid]
=== modified file 'mysql-test/r/innodb_mrr.result'
--- a/mysql-test/r/innodb_mrr.result2011-01-17 21:26:04 +
+++ b/mysql-test/r/innodb_mrr.result2011-03-02 14:06:13 +
@@ -736,3 +736,31 @@
c1 c2 c3
08:29:45 NULL2009-02-01
drop table `t1`;
+#
+# BUG#707925: Wrong result with join_cache_level=6 optimizer_use_mrr = force
(incremental, BKA join)
+#
+set @_save_join_cache_level= @@join_cache_level;
+set join_cache_level = 6;
+CREATE TABLE t1 (
+f1 int(11), f2 int(11), f3 varchar(1), f4 varchar(1),
+PRIMARY KEY (f1),
+KEY (f3),
+KEY (f2)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('11','8','f','f'),('12','5','v','v'),('13','8','s','s'),
+('14','8','a','a'),('15','6','p','p'),('16','7','z','z'),('17','2','a','a'),
+('18','5','h','h'),('19','7','h','h'),('20','2','v','v'),('21','9','v','v'),
+('22','142','b','b'),('23','3','y','y'),('24','0','v','v'),('25','3','m','m'),
+('26','5','z','z'),('27','9','n','n'),('28','1','d','d'),('29','107','a','a');
+select count(*) from (
+SELECT alias1.f2
+FROM
+t1 AS alias1 JOIN (
+t1 AS alias2 FORCE KEY (f3) JOIN
+t1 AS alias3 FORCE KEY (f2) ON alias3.f2 = alias2.f2 AND alias3.f4 = alias2.f3
+) ON alias3.f1 <= alias2.f1
+) X;
+count(*)
+361
+set join_cache_level=@_save_join_cache_level;
+drop table t1;
=
Re: [Maria-developers] Fwd: [Commits] Rev 2901: BNLH algorithm always used a full table scan over the joined table in file:///home/igor/maria/maria-5.3-mwl128-hashrange/
Hello Igor,
Here's the promised proof of deadcode:
On Fri, Feb 11, 2011 at 08:49:37PM -0800, Igor Babaev wrote:
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2011-02-06 04:57:03 +
> +++ b/sql/sql_select.cc 2011-02-12 04:41:22 +
> @@ -8419,6 +8438,11 @@
>sort_by_tab->type= JT_ALL;
>sort_by_tab->read_first_record= join_init_read_record;
> }
> +else if (sort_by_tab->type == JT_HASH_NEXT)
> +{
> + sort_by_tab->type= JT_HASH;
> + sort_by_tab->read_first_record= join_init_read_record;
> +}
>}
>break;
> }
Statement: the above else-if branch can never be taken, i.e. equality
sort_by_tab->type == JT_HASH_NEXT (1)
can never be true.
Proof:
First, let's see what JOIN_TABs can be in sort_by_tab. sort_by_tab is assigned
in this statement:
JOIN_TAB *sort_by_tab= join->group && join->simple_group &&
join->group_list ?
join->join_tab+join->const_tables :
join->get_sort_by_join_tab();
i.e. possible values are
1. join->join_tab+join->const_tables
2. join->get_sort_by_join_tab()
get_sort_by_join_tab is defined in sql_select.h as:
JOIN_TAB *get_sort_by_join_tab()
{
return (need_tmp || !sort_by_table || skip_sort_order ||
((group || tmp_table_param.sum_func_count) && !group_list)) ?
NULL : join_tab+const_tables;
}
i.e. it can return:
1. NULL (which is not interesting)
2. join->join_tab + const_tables.
this draws us to conclusion that sort_by_tab can be
- NULL
- join->join_tab + const_tables.
so equality (1) can be rewritten as
join->join_tab[const_tables].type == JT_HASH_NEXT (2)
Now, let's explore the question, what join_tab elements can have
type==JT_HASH_NEXT. The value JT_HASH_NEXT
is assigned in only one place:
sql_select.cc|8399| tab->type= tab->type == JT_ALL ? JT_NEXT : JT_HASH_NEXT;
this line is inside the case block of code:
break;
case JT_ALL:
case JT_HASH:
tab->type= tab->type == JT_ALL ? JT_NEXT : JT_HASH_NEXT;
which means that tab->type can become JT_HASH_NEXT only if it was JT_HASH
before.
Let's see when a join_tab can get type==JT_HASH. There's only one assignment
that can assign type=JT_HASH.
It is at sql_select.cc:8279 :
if (tab->cache && tab->cache->get_join_alg() == JOIN_CACHE::BNLH_JOIN_ALG)
tab->type= JT_HASH;
i.e. tab->type can become JT_HASH only if tab->cache!=NULL. tab->cache can be
set to non-NULL value only inside
check_join_cache_usage(). However, the first lines in check_join_cache_usage()
have this code:
uint i= tab - join->join_tab;
join->return_tab= 0;
if (cache_level == 0 || i == join->const_tables || !prev_tab)
return 0;
from which we can conclude that
join->join_tab[join->const_tables].cache == NULL
will always hold. Going back on our chain of reasoning, we get that
join->join_tab[join->const_tables].cache == NULL at all times=>
join->join_tab[join->const_tables].type != JT_HASH at all times =>
join->join_tab[join->const_tables].type != JT_HASH_NEXT at all times =>
equality (2) is false always => original equality (1) is false always,
q.e.d.
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
[Maria-developers] Igor please review: Rev 2933: BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 in file:///home/psergey/dev2/5.3/]
Hello Igor, Could you please review the below? The bug page https://bugs.launchpad.net/maria/+bug/727667 has verbose explanations of how we ended up having this bug, as well as justifications for fixing it in this particular way. - Forwarded message from Sergey Petrunya - From: Sergey Petrunya To: comm...@mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: Sat, 5 Mar 2011 12:56:26 +0300 (MSK) Subject: [Commits] Rev 2933: BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 in file:///home/psergey/dev2/5.3/ At file:///home/psergey/dev2/5.3/ revno: 2933 revision-id: pser...@askmonty.org-20110305095622-wzlnwr9srfbsu3pv parent: pser...@askmonty.org-20110304091446-qmsezak3c0cfm9b1 committer: Sergey Petrunya branch nick: 5.3 timestamp: Sat 2011-03-05 12:56:22 +0300 message: BUG#727667 Wrong result with OR + NOT NULL in maria-5.3 - put the code that sets HA_NULL_PART bit back - Fix test_if_ref/part_of_refkey() so that = NULL-ability of lookup columns does not prevent the equality from being removed (we now have early/late NULLs filtering which will filter out NULL values) = equality is not removed if it is ref_or_null access, and the value of the lookup column can alternate between the lookup value and NULL. === modified file 'mysql-test/r/null.result' --- a/mysql-test/r/null.result 2009-12-15 07:16:46 + +++ b/mysql-test/r/null.result 2011-03-05 09:56:22 + @@ -170,7 +170,7 @@ insert into t1 values(null); explain select * from t1 where i=2 or i is null; id select_type table typepossible_keys key key_len ref rowsExtra -1 SIMPLE t1 ref_or_null i i 5 const 9 Using index +1 SIMPLE t1 ref_or_null i i 5 const 9 Using where; Using index select count(*) from t1 where i=2 or i is null; count(*) 10 === modified file 'mysql-test/r/null_key.result' --- a/mysql-test/r/null_key.result 2010-10-06 20:27:12 + +++ b/mysql-test/r/null_key.result 2011-03-05 09:56:22 + @@ -21,10 +21,10 @@ 1 SIMPLE t1 range a,b a 9 NULL3 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7; id select_type table typepossible_keys key key_len ref rowsExtra -1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7 order by a; id select_type table typepossible_keys key key_len ref rowsExtra -1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index; Using filesort +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index @@ -151,7 +151,7 @@ insert into t1 values (7,null), (8,null), (8,7); explain select * from t1 where a = 7 and (b=7 or b is null); id select_type table typepossible_keys key key_len ref rowsExtra -1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using index +1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index select * from t1 where a = 7 and (b=7 or b is null); a b 7 7 @@ -166,7 +166,7 @@ NULL 7 explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); id select_type table typepossible_keys key key_len ref rowsExtra -1 SIMPLE t1 ref_or_null a a 5 const 5 Using index +1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); a b 7 NULL @@ -192,7 +192,7 @@ explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); id select_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE t2 ALL NULLNULLNULLNULL2 Using where -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); a a b 7 7 7 @@ -202,7 +202,7 @@ explain select * from t2,t1 w
Re: [Maria-developers] [Commits] Rev 2934: Fix LP BUG#719198 in file:///home/tsk/mprog/src/5.3/
Hi Timour, On Tue, Mar 08, 2011 at 04:35:24PM +0200, tim...@askmonty.org wrote: > At file:///home/tsk/mprog/src/5.3/ > > > revno: 2934 > revision-id: tim...@askmonty.org-20110308143512-0m7uotvxp9rmegak > parent: pser...@askmonty.org-20110304155430-l0ad4iag9gn5zeka > committer: tim...@askmonty.org > branch nick: 5.3 > timestamp: Tue 2011-03-08 16:35:12 +0200 > message: > Fix LP BUG#719198 > > Analysis: > The assert failed because the execution code for > partial matching is designed with the assumption that > NULLs on the left side are detected as early as possible, > and a NULL result is returned before any lookups are > performed at all. > > However, in the case of an Item_cache object on the left > side, null was not detected properly, because detection > was done via Item::is_null(), which is not implemented at > all for Item_cache, and resolved to the default Item::is_null() > which always returns FALSE. > > Solution: > Use the property Item::null_value instead of is_null(), which > is properly updated for Item_cache objects as well. > > Alternatively one could implement Item_cache:is_null(), but > its not quite clear how that will interact with the cache > update methods, so the current fix seems simpler and cleaner. Ok to push. Since it is guaranteed that cache_value() have been called for the object, it's ok to use null_value instead of is_null(). Btw, out of interest I've implemented Item_cache::is_null() and t/subselect*.test ran successfully, so it seems to be ok to have. I don't insist that it is added as part of this fix, though. > === modified file 'mysql-test/r/subselect_mat.result' > --- a/mysql-test/r/subselect_mat.result 2011-01-14 10:51:30 + > +++ b/mysql-test/r/subselect_mat.result 2011-03-08 14:35:12 + > @@ -1371,3 +1371,41 @@ SELECT pk FROM t1 WHERE (b,c,d) IN (SELE > pk > 2 > DROP TABLE t1, t2; > +# > +# LPBUG#719198 Ordered_key::cmp_key_with_search_key(rownum_t): Assertion > `!compare_pred[i]->null_value' > +# failed with subquery on both sides of NOT IN and materialization > +# > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3),(4); > +CREATE TABLE t3 (f3a int, f3b int); > +set session > optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; > +EXPLAIN > +SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); > +id select_type table typepossible_keys key key_len ref >rowsExtra > +1 PRIMARY t2 ALL NULLNULLNULLNULL2 > Using where > +3 SUBQUERYt1 ALL NULLNULLNULLNULL2 > > +2 SUBQUERYt3 system NULLNULLNULLNULL0 >const row not found > +SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); > +f2 > +EXPLAIN > +SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b > FROM t1); > +id select_type table typepossible_keys key key_len ref >rowsExtra > +1 PRIMARY t2 ALL NULLNULLNULLNULL2 > Using where > +3 SUBQUERYt1 ALL NULLNULLNULLNULL2 > > +2 SUBQUERYt3 system NULLNULLNULLNULL0 >const row not found > +SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b > FROM t1); > +f2 > +insert into t3 values (1,1),(2,2); > +EXPLAIN > +SELECT * FROM t2 WHERE (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a > FROM t1); > +id select_type table typepossible_keys key key_len ref >rowsExtra > +1 PRIMARY t2 ALL NULLNULLNULLNULL2 > Using where > +3 SUBQUERYt1 ALL NULLNULLNULLNULL2 > > +2 SUBQUERYt3 ALL NULLNULLNULLNULL2 >Using where > +SELECT * FROM t2 WHERE (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a > FROM t1); > +f2 > +3 > +4 > +drop table t1, t2, t3; > > === modified file 'mysql-test/t/subselect_mat.test' > --- a/mysql-test/t/subselect_mat.test 2011-01-14 10:51:30 + > +++ b/mysql-test/t/subselect_mat.test 2011-03-08 14:35:12 + > @@ -1011,3 +1011,31 @@ SELECT pk FROM t1 WHERE (a) IN (SELECT a > SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0); > DROP TABLE t1, t2; > > +--echo # > +--echo # LPBUG#719198 Ordered_key::cmp_key_with_search_key(rownum_t): > Assertion `!compare_pred[i]->null_value' > +--echo # failed with subquery on both sides of NOT IN and materialization > +--echo # > + > +CREATE TABLE t1 (f1a int, f1b int) ; > +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); > +CREATE TABLE t2 ( f2 int); > +INSERT IGNORE INTO t2 VALUES (3)
Re: [Maria-developers] [Commits] Rev 2947: Fix LP BUG#613029 in file:///home/tsk/mprog/src/5.3/
Hi Timour, Ok to push. On Mon, Mar 28, 2011 at 12:55:43PM +0300, tim...@askmonty.org wrote: > At file:///home/tsk/mprog/src/5.3/ > > > revno: 2947 > revision-id: tim...@askmonty.org-20110328095536-wbmu1hiwsnhw6bs8 > parent: tim...@askmonty.org-20110324143406-04q5peh1r7nthcyb > committer: tim...@askmonty.org > branch nick: 5.3 > timestamp: Mon 2011-03-28 12:55:36 +0300 > message: > Fix LP BUG#613029 > > Analysis: > There are two code paths through which JOIN::exec may produce > an all-NULL row for an empty result set. One goes via the > function return_zero_rows(), when query processing detectes > early that the where clause is false, the other one is via > do_select() in the case of join execution. > > In the case of do_select(), the problem was that the executioner > didn't set TABLE::null_row to 1. As result when sending the only > result row, the evaluation of each field didn't detect that all > non-aggregated fields are NULL, because Field::is_null returned > true, after checking that field->table->null_row was false. > > Given that the each non-aggregated field was not considered NULL, > select_result::send_data sent whatever was in the buffer of each > field. However, since there was no actual data in the field buffer, > send_data() accessed and sent whatever junk was in the field's > data buffer. > > Solution: > Similar to the analogous case in return_zero_rows() mark all > tables that their current row is NULL before sending the > artificailly created NULL row. > === modified file 'mysql-test/r/subselect4.result' > --- a/mysql-test/r/subselect4.result 2011-03-24 14:34:06 + > +++ b/mysql-test/r/subselect4.result 2011-03-28 09:55:36 + > @@ -1133,3 +1133,55 @@ SELECT * FROM t1 WHERE (2, 0) NOT IN (SE > f1 f2 > set @@optimizer_switch=@save_optimizer_switch; > drop table t1,t2; > +# > +# LP BUG#613029 Wrong result with materialization and semijoin, and > +# valgrind warnings in Protocol::net_store_data with materialization > +# for implicit grouping > +# > +CREATE TABLE t1 ( > +pk int(11) NOT NULL AUTO_INCREMENT, > +f2 int(11) NOT NULL, > +f3 varchar(1) NOT NULL, > +PRIMARY KEY (pk), > +KEY f2 (f2)); > +INSERT INTO t1 VALUES (1,9,'x'); > +INSERT INTO t1 VALUES (2,5,'g'); > +CREATE TABLE t2 ( > +pk int(11) NOT NULL AUTO_INCREMENT, > +f2 int(11) NOT NULL, > +f3 varchar(1) NOT NULL, > +PRIMARY KEY (pk), > +KEY f2 (f2)); > +INSERT INTO t2 VALUES (1,7,'p'); > +set @save_optimizer_switch=@@optimizer_switch; > +set @@optimizer_switch='materialization=off,semijoin=off'; > +EXPLAIN > +SELECT t1.f3, MAX(t1.f2) > +FROM t1, t2 > +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1); > +id select_type table typepossible_keys key key_len ref >rowsExtra > +1 PRIMARY NULLNULLNULLNULLNULLNULLNULL > Impossible WHERE noticed after reading const tables > +2 DEPENDENT SUBQUERY t1 index_subquery f2 f2 4 >func2 Using index > +SELECT t1.f3, MAX(t1.f2) > +FROM t1, t2 > +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1); > +f3 MAX(t1.f2) > +NULLNULL > +set @@optimizer_switch='materialization=on,semijoin=off'; > +EXPLAIN > +SELECT t1.f3, MAX(t1.f2) > +FROM t1, t2 > +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1); > +id select_type table typepossible_keys key key_len ref >rowsExtra > +1 PRIMARY t2 system PRIMARY NULLNULLNULL1 > +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 > +2 SUBQUERYt1 index NULLf2 4 NULL2 >Using index > +SELECT t1.f3, MAX(t1.f2) > +FROM t1, t2 > +WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1); > +f3 MAX(t1.f2) > +NULLNULL > +TODO: add a test case for semijoin when the wrong result is fixed > +set @@optimizer_switch='materialization=off,semijoin=on'; > +set @@optimizer_switch=@save_optimizer_switch; > +drop table t1, t2; > > === modified file 'mysql-test/t/subselect4.test' > --- a/mysql-test/t/subselect4.test2011-03-24 14:34:06 + > +++ b/mysql-test/t/subselect4.test2011-03-28 09:55:36 + > @@ -906,3 +906,60 @@ SELECT * FROM t1 WHERE (2, 0) NOT IN (SE > set @@optimizer_switch=@save_optimizer_switch; > > drop table t1,t2; > + > +--echo # > +--echo # LP BUG#613029 Wrong result with materialization and semijoin, and > +--echo # valgrind warnings in Protocol::net_store_data with materialization > +--echo # for implicit grouping > +--echo # > + > +CREATE TABLE t1 ( > + pk int(11) NOT NULL AUTO_INCREMENT, > + f2 int(11) NOT NULL, > + f3 varchar(1) NOT NULL, > + PRIMARY KEY (pk), > + KEY f2 (f2)); > + > +INSERT INTO t1 VALUES (1,9,'x'); > +INSERT INTO t1 VALUES (2,5,'g'); > + > +CREATE TABLE t2 ( > + pk int(11) NOT NULL AUTO_INCREMENT, > + f2 int(11) NOT NULL, > + f3 varcha
Re: [Maria-developers] [Commits] Rev 2908: Fixed LP bugs #717577, #724942. in file:///home/igor/maria/maria-5.3-bug717577/
Hello Igor,
First, an overall comment: there are lots of typos/coding style violations in
the patch. To reduce amount of effort spent on such things, I was just fixing
them as I saw them and I'm attaching the patch with all the fixes (i.e. this
patch should be applied on top of the patch that I was reviewing).
More important comments are bellow, marked with 'psergey:'
On Fri, Mar 25, 2011 at 10:31:19PM -0700, Igor Babaev wrote:
> At file:///home/igor/maria/maria-5.3-bug717577/
>
>
> revno: 2908
> revision-id: i...@askmonty.org-20110326053117-x50cah9krd4f1345
> parent: w...@montyprogram.com-20110212174322-f7ptnc0u32vasdwy
> committer: Igor Babaev
> branch nick: maria-5.3-bug717577
> timestamp: Fri 2011-03-25 22:31:17 -0700
> message:
> Fixed LP bugs #717577, #724942.
>
> Both these two bugs happened due to the following problem.
> When a view column is referenced in the query an Item_direct_view_ref
> object is created that is refers to the Item_field for the column.
> All references to the same view column refer to the same Item_field.
> Different references can belong to different AND/OR levels and,
> as a result, can be included in different Item_equal object.
> These Item_equal objects may include different constant objects.
> If these constant objects are substituted for the Item_field created
> for a view column we have a conflict situation when the second
> substitution annuls the first substitution. This leads to
> wrong result sets returned by the query. Bug #724942 demonstrates
> such an erroneous behaviour.
> Test case of the bug #717577 produces wrong result sets because best
> equal fields of the multiple equalities built for different OR levels
> of the WHERE condition differs. The subsitution for the best equal field
> in the second OR branch overwrites the the substitution made for the
> first branch.
>
> To avoid such conflicts we have to substitute for the references
> to the view columns rather than for the underlying field items.
> To make such substitutions possible we have to include into
> multiple equalities references to view columns rather than
> field items created for such columns.
>
> This patch modifies the Item_equal class to include references
> to view columns into multiple equality objects. It also performs
> a clean up of the class methods and adds more comments. The methods
> of the Item_direct_view_ref class that assist substitutions for
> references to view columns has been also added by this patch.
> === modified file 'sql/item.cc'
> --- a/sql/item.cc 2011-02-01 03:33:32 +
> +++ b/sql/item.cc 2011-03-26 05:31:17 +
> @@ -7226,6 +7233,129 @@
>return FALSE;
> }
>
> +
> +Item_equal *Item_direct_view_ref::find_item_equal(COND_EQUAL *cond_equal)
> +{
> + Item* field_item= real_item();
> + if (field_item->type() != FIELD_ITEM)
> +return NULL;
> + return ((Item_field *) field_item)->find_item_equal(cond_equal);
> +}
> +
> +
> +/**
> + Check whether a reference to field item can be substituted b an equal item
> +
> + @details
> + The function checks whether a substitution of a reference to field item for
> + an equal item is valid.
> +
> + @param arg *arg != NULL && **arg <-> the reference is in the context
> + where substitution for an equal item is valid
> +
> + @note
> +See also the note for Item_field::subst_argument_checker
> +
> + @retval
> +TRUE substitution is valid
> + @retval
> +FALSE otherwise
> +*/
> +bool Item_direct_view_ref::subst_argument_checker(uchar **arg)
> +{
> + bool res= (!(*arg) && (result_type() != STRING_RESULT)) ||
> + ((*arg) && (**arg));
> + if (*arg)
> +**arg= (uchar) 0;
psergey: What is the above for? Do I understand it correctly that this is
needed so
that Item_field that this item is wrapping is not substituted?
Please add a comment.
> + return res;
> +}
> +
> +
> +/**
> + Set a pointer to the multiple equality the view field reference belongs to
> + (if any).
> +
> + @details
> + The function looks for a multiple equality containing this item of the type
> + Item_direct_view_ref among those referenced by arg.
> + In the case such equality exists the function does the following.
> + If the found multiple equality contains a constant, then the item
> + is substituted for this constant, otherwise the function sets a pointer
> + to the multiple equality in the item.
> +
> + @param argreference to list of multiple equalities where
> +the item (this object) is to be looked for
> +
> + @note
> +This function is supposed to be called as a callback parameter in calls
> +of the compile method.
> +
> + @note
> +The function calls Item_field::equal_fields_propagator for the field item
> +this->real_item() to do the job. Then it takes the pointer to equal_item
> +from this field item a
Re: [Maria-developers] [Commits] Rev 2908: Fixed LP bugs #717577, #724942. in file:///home/igor/maria/maria-5.3-bug717577/
On Sun, Apr 24, 2011 at 03:08:12AM +0400, Sergey Petrunya wrote: > Hello Igor, > > First, an overall comment: there are lots of typos/coding style violations in > the patch. To reduce amount of effort spent on such things, I was just fixing > them as I saw them and I'm attaching the patch with all the fixes (i.e. this > patch should be applied on top of the patch that I was reviewing). Now really with attached file. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog diff -ur /home/psergey/dev2/5.3-review-717577-clean/sql/item.cc 5.3-review-717577-comments/sql/item.cc --- /home/psergey/dev2/5.3-review-717577-clean/sql/item.cc 2011-04-24 00:43:19.0 +0400 +++ 5.3-review-717577-comments/sql/item.cc 2011-04-24 00:10:10.0 +0400 @@ -4669,7 +4669,7 @@ /** - Check whether a field item can be substituted b an equal item + Check whether a field item can be substituted by an equal item @details The function checks whether a substitution of a field item for @@ -7264,7 +7264,7 @@ /** - Check whether a reference to field item can be substituted b an equal item + Check whether a reference to field item can be substituted by an equal item @details The function checks whether a substitution of a reference to field item for diff -ur /home/psergey/dev2/5.3-review-717577-clean/sql/item_cmpfunc.cc 5.3-review-717577-comments/sql/item_cmpfunc.cc --- /home/psergey/dev2/5.3-review-717577-clean/sql/item_cmpfunc.cc 2011-04-24 00:43:19.0 +0400 +++ 5.3-review-717577-comments/sql/item_cmpfunc.cc 2011-04-24 00:10:07.0 +0400 @@ -5535,7 +5535,7 @@ @details The constructor builds a new item equal object for the equality f1=f2. - One if the equal items can be constant. If this is the case it is passed + One of the equal items can be constant. If this is the case it is passed always as the first parameter and the parameter with_const_item serves as an indicator of this case. Currently any non-constant parameter items must refer to an item of the @@ -5590,12 +5590,11 @@ (this parameter is optional) @details - The method adds the constant item c to the list equal_items. If the list - hasn't not contained any constant item yet the item c is just added - to the very beginning of the list. Otherwise the value of c is compared - with the value of the constant item from equal_items. If they are not - equal cond_false is set to TRUE. This serves as an indicator that this - Item_equal is always FALSE. + The method adds the constant item c to the equal_items list. If the list + doesn't yet have any constant item, the item c is put into the front of + the list. Otherwise the value of c is compared with the value of the + constant item from equal_items. If they are not equal cond_false is set + to TRUE. This serves as an indicator that this Item_equal is always FALSE. The optional parameter f is used to adjust the flag compare_as_dates. */ @@ -5636,7 +5635,7 @@ @param field field whose occurrence is to be checked @details - The function checks whether field is reffered to by one of the + The function checks whether field is referred to by one of the items from the equal_items list. @retval diff -ur /home/psergey/dev2/5.3-review-717577-clean/sql/item_cmpfunc.h 5.3-review-717577-comments/sql/item_cmpfunc.h --- /home/psergey/dev2/5.3-review-717577-clean/sql/item_cmpfunc.h 2011-04-24 00:43:19.0 +0400 +++ 5.3-review-717577-comments/sql/item_cmpfunc.h 2011-04-24 00:09:32.0 +0400 @@ -1630,7 +1630,7 @@ over all items from the list of the Item_field/Item_direct_view_ref classes. */ List equal_items; - /* + /* TRUE <-> one of the items is a const item. Such item is always first in in the equal_items list */ @@ -1643,17 +1643,17 @@ /* This initially is set to FALSE. It becomes TRUE when this item is evaluated as being always false. If the flag is TRUE the contents of the list - the equal_items should be ignored. +the equal_items should be ignored. */ bool cond_false; /* compare_as_dates=TRUE <-> constants equal to fields from equal_items -must be compared as datetimesnot as strings. +must be compared as datetimes and not as strings. compare_as_dates can be TRUE only if with_const=TRUE */ bool compare_as_dates; /* -The comomparator used to compare constants equal to fields from equal_items +The comparator used to compare constants equal to fields from equal_items as datetimes. The comparator is used only if compare_as_dates=TRUE */ Arg_comparator cmp; ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Commits] Rev 2908: Fixed LP bugs #717577, #724942. in file:///home/igor/maria/maria-5.3-bug717577/
On Sat, Apr 23, 2011 at 06:54:23PM -0700, Igor Babaev wrote: > On 04/23/2011 05:26 PM, Sergey Petrunya wrote: > > On Sun, Apr 24, 2011 at 03:08:12AM +0400, Sergey Petrunya wrote: > >> Hello Igor, > >> > >> First, an overall comment: there are lots of typos/coding style violations > >> in > >> the patch. To reduce amount of effort spent on such things, I was just > >> fixing > >> them as I saw them and I'm attaching the patch with all the fixes (i.e. > >> this > >> patch should be applied on top of the patch that I was reviewing). > > > > Now really with attached file. > > > > BR > > Sergey > > Sergey, > > In your diff I found only corrections for typos/phrasing. Not with all > of them I agree. Nevertheless I'll fix them. > I did not find any corrections of coding style violations. Do I miss > anything? I meant the changes like the following: +++ 5.3-review-717577-comments/sql/item_cmpfunc.h 2011-04-24 00:09:32.0 +0400 @@ -1630,7 +1630,7 @@ over all items from the list of the Item_field/Item_direct_view_ref classes. */ List equal_items; - /* + /* TRUE <-> one of the items is a const item. Such item is always first in in the equal_items list */ @@ -1643,17 +1643,17 @@ /* This initially is set to FALSE. It becomes TRUE when this item is evaluated as being always false. If the flag is TRUE the contents of the list - the equal_items should be ignored. +the equal_items should be ignored. */ BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Commits] Rev 2983: MWL#89 - Automatic merge with 5.3 in file:///home/tsk/mprog/src/5.3-mwl89-merge/
Hi Timour,
Congratulations on having pushed MWL#89 into 5.3-main! This is quite an
achievement which really moves us forward.
I've been studying the new code and has got some feedback which I thought
I need to share. Please find the notes below, marked with 'psergey'.
> diff -urN --exclude='.*' 5.3-timours-wl-clean/sql/filesort.cc
> 5.3-timours-wl/sql/filesort.cc
> --- 5.3-timours-wl-clean/sql/filesort.cc 2011-05-11 20:12:50.0
> +0100
> +++ 5.3-timours-wl/sql/filesort.cc2011-05-11 20:08:50.0 +0100
> @@ -612,10 +612,34 @@
>}
>DBUG_RETURN(HA_POS_ERROR); /* purecov: inspected */
> }
> +
> +bool write_record= false;
> if (error == 0)
> +{
>param->examined_rows++;
> -
> -if (error == 0 && (!select || select->skip_record(thd) > 0))
> + if (select && select->cond)
> + {
> +/*
> + If the condition 'select->cond' contains a subquery, restore the
> + original read/write sets of the table 'sort_form' because when
> + SQL_SELECT::skip_record evaluates this condition. it may include a
psergey:
- The last sentence doesn't parse, please fix.
- Why is the check done *for each record we enumerate*? Please move it outside
of the loop.
- I suspect that now, with Sanja's subquery code, each subquery has a list of
references it makes to outside, so this shortcut is not necessary (let's
discuss that on the next optimizer call)
> + correlated subquery predicate, such that some field in the subquery
> + refers to 'sort_form'.
> +*/
> +if (select->cond->with_subselect)
> + sort_form->column_bitmaps_set(save_read_set, save_write_set,
> +save_vcol_set);
> +write_record= (select->skip_record(thd) > 0);
> +if (select->cond->with_subselect)
> + sort_form->column_bitmaps_set(&sort_form->tmp_set,
> +&sort_form->tmp_set,
> +&sort_form->tmp_set);
> + }
> + else
> +write_record= true;
> +}
> +
> +if (write_record)
> {
>if (idx == param->keys)
>{
> diff -urN --exclude='.*' 5.3-timours-wl-clean/sql/item_cmpfunc.cc
> 5.3-timours-wl/sql/item_cmpfunc.cc
> --- 5.3-timours-wl-clean/sql/item_cmpfunc.cc 2011-05-11 20:12:50.0
> +0100
> +++ 5.3-timours-wl/sql/item_cmpfunc.cc2011-05-11 20:08:50.0
> +0100
> @@ -2072,6 +2072,18 @@
> }
>
>
> +bool Item_in_optimizer::is_expensive_processor(uchar *arg)
> +{
> + return args[1]->is_expensive_processor(arg);
> +}
> +
> +
> +bool Item_in_optimizer::is_expensive()
> +{
> + return args[1]->is_expensive();
> +}
> +
psergey: what about the cases when args[0] is expensive? It should either be
checked, or a comment here is needed with explanation why we don't need to
check it.
> longlong Item_func_eq::val_int()
> {
>DBUG_ASSERT(fixed == 1);
> diff -urN --exclude='.*' 5.3-timours-wl-clean/sql/item.h
> 5.3-timours-wl/sql/item.h
> --- 5.3-timours-wl-clean/sql/item.h 2011-05-11 20:12:50.0 +0100
> +++ 5.3-timours-wl/sql/item.h 2011-05-11 20:08:50.0 +0100
> @@ -510,7 +521,7 @@
> SUBSELECT_ITEM, ROW_ITEM, CACHE_ITEM, TYPE_HOLDER,
> PARAM_ITEM, TRIGGER_FIELD_ITEM, DECIMAL_ITEM,
> XPATH_NODESET, XPATH_NODESET_CMP,
> - VIEW_FIXER_ITEM, EXPR_CACHE_ITEM};
> + VIEW_FIXER_ITEM, EXPR_CACHE_ITEM, UNKNOWN_ITEM};
psergey: When I grep for UNKNOWN_ITEM, I find only this occurence. Why add it?
>
>enum cond_result { COND_UNDEF,COND_OK,COND_TRUE,COND_FALSE };
>
...
> diff -urN --exclude='.*' 5.3-timours-wl-clean/sql/item_subselect.cc
> 5.3-timours-wl/sql/item_subselect.cc
> --- 5.3-timours-wl-clean/sql/item_subselect.cc2011-05-11
> 20:12:50.0 +0100
> +++ 5.3-timours-wl/sql/item_subselect.cc 2011-05-11 20:08:50.0
> +0100
...
> @@ -1659,49 +1696,40 @@
> {
>if (!(new_having= new Item_func_trig_cond(new_having,
> get_cond_guard(0
> -DBUG_RETURN(RES_ERROR);
> +DBUG_RETURN(true);
> }
> -new_having->name= (char*)in_having_cond;
> - select_lex->having= join->having= new_having;
> - select_lex->having_fix_field= 1;
> -
> -/*
> - we do not check join->having->fixed, because comparison function
> - (from func->create) can't be fixed after creation
> -*/
> - tmp= join->having->fix_fields(thd, 0);
> -select_lex->having_fix_field= 0;
> -if (tmp)
> - DBUG_RETURN(RES_ERROR);
> +
> +new_having->name= (char*) in_having_cond;
> +if (fix_having(new_having, select_lex))
> + DBUG_RETURN(true);
> +*having_item= new_having;
>}
>else
> - {
> - // it is single select without
Re: [Maria-developers] [Commits] Rev 2983: MWL#89 - Automatic merge with 5.3 in file:///home/tsk/mprog/src/5.3-mwl89-merge/
Hi Timour, On Mon, May 23, 2011 at 12:10:44PM +0300, Timour Katchaounov wrote: > Thank you for the review, you manged to spot a couple of real omissions/ > problems with the implementation. I implemented or responded to all your > review suggestions except two. The two ones that I didn't implement are > marked with 'timour-todo'. All remaining replies are marked with 'timour:'. > We already discussed the two issues I didn't address this time. They > are not critical, but are time-consuming. I will add them to my todo for > the task to complete after completing the regression test. > > The patch that addresses your review was merged with the latest 5.3 and > pushed into 5.3-mwl89. If you have no objections, I'd like to push to 5.3 > ASAP. Specifically, please look at my change to sort_and_filter_keyuse(). Looked. I have no objections to pushing this into 5.3-main. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] More MWL#89 questions
Hi Timour,
While merging, I also got the following questions:
== exclude_expensive_cond ==
make_cond_for_table()'s exclude_expensive_cond parameter is not used anymore.
Was it intentional that you kept it in the code?
(minimizing MWL#89's diff size could not be a reason because the diff changes
make_cond_for_table signature anyway)
== exec_const_cond ==
The patch introduces JOIN::exec_const_cond. On the other hand, the code
already had JOIN::outer_ref_cond which seems to be nearly the same.
== join_tab_idx ==
make_join_select() attaches parts of WHERE/ON clauses to JOIN_TABs. It does
so with a help of two functions:
1. make_cond_for_table()
2. make_cond_after_sjm()
The first one does make set_join_tab_idx() calls, the second one doesn't. Why?
== join_tab_idx #2 ===
> @@ -7150,24 +7174,27 @@
> there inside the triggers.
>*/
>{// Check const tables
> +join->exec_const_cond=
> + make_cond_for_table(thd, cond,
>join->const_table_map,
> - (table_map) 0, TRUE, FALSE);
> + (table_map) 0, MAX_TABLES, FALSE, FALSE);
As far as I understand the code, the MAX_TABLES part informs the constant
subquery predicate that it will be evaluated O(join_output_records) times,
which is not true.
This is not the only such case, I see plenty of make_cond_for_table() calls
which use MAX_TABLES for join_tab_idx argument, where we can actually tell for
certain that the condition will be evaluated much fewer than
O(join_output_records) times.
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
[Maria-developers] FYI: MWL#90 pushed into 5.3 main
Hello, This is to inform you that today I've pushed MWL#90 into 5.3-main. There was a buildbot run on 5.3-subqueries-mwl90 tree before the push, and it did not show any failures that weren't also present in 5.3-main. The result of my merge has an issue that conceptually it is not a full merge, because MWL#90 code is not able yet to take advantage of the possibily of dynamic choice between IN->EXISTS (which was introduced in MWL#89 which Timour pushed two weeks ago). Adding support for MWL#89 and MWL#90 working together seems easy. Actually, I initially intended to do it while merging, but then decided to do one thing at a time. My further plans are: - look at the open bugs I have - look at making MWL#89 and MWL#90 work together. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Buildbot failure in filesort.cc:make_sortkey
Hi Sergei, Current 5.3 fails as follows: http://buildbot.askmonty.org/buildbot/builders/mac-mini-x86-dbg/builds/771/steps/compile/logs/stdio g++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME='"/usr/local/mysql"' -DMYSQL_DATADIR='"/usr/local/mysql/var"' -DSHAREDIR='"/usr/local/mysql/share/mysql"' -DPLUGINDIR='"/usr/local/mysql/lib/mysql/plugin"' -DHAVE_EVENT_SCHEDULER -DHAVE_CONFIG_H -I. -I../include -I../include -I../include -I../regex -I. -I../extra/libevent -Wall -Wextra -Wunused -Wwrite-strings -Wno-strict-aliasing -Werror -DFORCE_INIT_OF_VARS -Wno-unused-parameter -g -Wall -Wextra -Wunused -Wwrite-strings -Wno-uninitialized -Wno-unused-parameter -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mtune=native -DUNIV_MUST_NOT_INLINE -DEXTRA_DEBUG -DSAFEMALLOC -DPEDANTIC_SAFEMALLOC -O0 -g3 -gdwarf-2 -O2 -Wuninitialized -DFORCE_INIT_OF_VARS-fno-implicit-templates -fno-exceptions -fno-rtti -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DIGNORE_SIGHUP_SIGQUIT -DDONT_DECLARE_CXA_PURE_VIRTUAL -MT debug_sync.o -MD -MP -MF .deps/debug_sync.Tpo -c -o debug_sync.o debug_sync.cc cc1plus: warnings being treated as errors filesort.cc: In function 'void make_sortkey(SORTPARAM*, uchar*, uchar*)': filesort.cc:878: warning: 'value' may be used uninitialized in this function make[3]: *** [filesort.o] Error 1 make[3]: *** Waiting for unfinished jobs It is not apparent for me whether this is a real error or not, bazaar history shows that the failure is in your code of MWL#173. Could you please take a look? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Buildbot failures: Microsecond-related changes break innodb_plugin but not xtradb
Hi! All of the recent 5.3 builds in buildbot have two failures: innodb_plugin.innodb_bug54044 'innodb_plugin' innodb_plugin.innodb_information_schema 'innodb_plugin' The failure happens only with innodb_plugin. XtraDB is fine. I've investigated the first problem. Its cause is in storage/xtradb/handler/ha_innodb.cc:get_innobase_type_from_mysql_type(): - On 2010-08-04, Kristian did a merge from XtraDB in Percona-Server-5.1.47-11 and among everything else pulled in the code that will produce error when the function's argument is Field_null. This seems to be the bugfix for BUG#54044 - On 2011-03-29, Sergei was making fix for lp:743017 in 5.1-micro, switched the function from analyzing field->type() to analyzing field->key_type() and in the process removed the part of code that Kristian has added. For some reason, he did that storage/innodb_plugin and storage/innobase but not to storage/xtradb. - On May, 28th Monty has merged in Sergei's change into 5.3-main and we started to get the failure. I see two problems here: P1: (the apparent one) Sergei's new code doesn't produce error when passed a Field_null object. P2: (less apparent one) Why do we have Microsecond changes in innodb_plugin but not in xtradb? Could there be more fixes missing in a similar way? Sergei, could you please take a look? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Buildbot failures: Microsecond-related changes break innodb_plugin but not xtradb
On Sat, Jun 04, 2011 at 01:19:42PM +0400, Sergey Petrunya wrote: > Hi! > > All of the recent 5.3 builds in buildbot have two failures: > > innodb_plugin.innodb_bug54044 'innodb_plugin' The problematic changesets are: revno: 3009 [merge] revision-id: mo...@askmonty.org-20110528030022-b9c4gk1db5lw9fmo parent: w...@montyprogram.com-20110527170535-wb0dbkccp4imlmvj parent: mo...@askmonty.org-20110528025816-olzav9xlvgnmq4ub committer: Michael Widenius branch nick: maria-5.3 timestamp: Sat 2011-05-28 06:00:22 +0300 message: Automatic merge revno: 2502.1147.35 revision-id: ser...@pisem.net-20110329124848-r3n2hc7sntw89hpd parent: ser...@pisem.net-20110328170156-tym8c702vt09owe9 fixes bug(s): https://launchpad.net/bugs/743017 committer: Sergei Golubchik branch nick: microseconds-5.1.54 timestamp: Tue 2011-03-29 14:48:48 +0200 message: lp:743017 Diverging results with TIME(3) and ranges depending on the execution plan in 5.1-micro rewrite get_innobase_type_from_mysql_type() to use types as reported by the Field objects, instead of relying on ad-hoc assumptions. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Proposal to split @@optimizer_switch
Hello, Please find below descriptions of problems with current @@optimizer_switch variable, as well as a proposal on how to fix it. Problems with @@optimizer_switch Proposed solution Details about hooking this into parser Alternate approach1: grouping Problems with @@optimizer_switch @@optimizer_switch variable use has outgrown its syntax. There are too many settings to fit on the single line: MariaDB [(none)]> select @@optimizer_switch; +---+ | @@optimizer_switch | +---+ | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on | +---+ 1 row in set (0.00 sec) The line is now 510 characters long. It's hard to find the flag you're looking for, and one needs to write regular expressions if they want to check the value of a particular flag in some program. There is no easy way to get settings for a subset of flags (which means we've banned "select @@optimizer_switch" from almost all .test files, because one had to change .result file all the time) The extra-long line also gets into "SHOW VARIABLES" output, and makes it hard-to-read, too. The situation is going to get worse, as we've already committed to adding even to add even more switches (Igor's patch adds some, then we've decided that two semi-join optimizations that do not have their switches, should have them) The benefits of having all settings grouped together are: - It was easy to implement - One can easily see all optimizer switches without having to fish them out of the list of all server variables. - One can reset all optimizer settings with a single statement: SET @@optimizer_switch=default Proposed solution - Break optimizer_switch into multiple variables, with names having the dots in them. That is, current @@optimizer_switch flags will change into this set of variables: optimizer.index_merge=on optimizer.index_merge.union=on optimizer.index_merge.sort_union=on optimizer.index_merge.intersection=on optimizer.index_merge.sort_intersection=off optimizer.index_condition_pushdown=on optimizer.join_cache.bka=on optimizer.join_cache.hashed=on optimizer.join_cache.incremental=on optimizer.join_cache.optimize_buffer_size=on optimizer.join_cache.outer_join=off optimizer.join_cache.semijoin=off optimizer.mrr=on optimizer.mrr.cost_based=off optimizer.mrr.sort_keys=on optimizer.semijoin=on optimizer.semijoin.firstmatch=on optimizer.semijoin.loosescan=on optimizer.subquery.cache=on optimizer.subquery.in_to_e
Re: [Maria-developers] Proposal to split @@optimizer_switch
On Fri, Jun 10, 2011 at 12:13:12PM +0400, Sergey Petrunya wrote: > Hello, > > Please find below descriptions of problems with current @@optimizer_switch > variable, as well as a proposal on how to fix it. > Results of IRC discussion on June, 22 (as I interpret them): Everyone agrees that we need to keep current @@optimizer_switch for compatibility reasons (it was actually present as early as in MySQL 5.1) Monty considers the "SET optimizer.xxx.yyy.*" syntax strange, it's better to allow selecting/setting of any prefix of the variable name. There is no opposition to this. It is okay to do implementation in steps - first support the dotted syntax and SET xxx.yy.prefix=DEFAULT, and then support setting/selecting arbitrary prefixes. Sergei sees the goal as "introducing a consistent hierarchical naming system for variables". That is, - every implicit variable group, like aria_ or myisam_ should be switched to the dotted notation right away. - Old variable names should be supported, too. In order to meet the second, we could A. Have both kinds of variables (dotted and un-dotted) be settable with SET and listed in SHOW VARIABLES - Plus: straightforward, compatible. - Minus: having everything listed twice under two names is confusing. B. Introduce some scheme where we have '.'=='_' when comparing variable names. SHOW VARIABLES output will be controlled by @@SHOW_VARIABLES_USES_UNDERSCORE/ - Plus: we could use a dotted notation while some of the scripts that use "SHOW VARIABLES LIKE setting" would still work. (As long as they don't try comparing the names of the variables they got:) - Minus: The scheme is difficult to understand. Also, our intent is not always to change @@xxx_yyy_zzz to @@@xxx.yyy.zzz, e.g. we don't need @@table.definition.cache or @@system.time.zone. C. Allow SET to set both old and new names, and use some setting to control whether SHOW VARIABLES will display old, new, or both kinds of variables. The question is, what should be the default setting. Sergei thinks that "optimizer switch can survive in its current form for a while", to which SergeyP strongly disagrees. We need to discuss whether we should follow Sergei's suggestion of global system of hierarchical names or focus on @@optimizer_switch only. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Proposal to split @@optimizer_switch
On Fri, Jun 10, 2011 at 12:50:34PM +0200, Sergei Golubchik wrote:
> Hi, Sergey!
>
> On Jun 10, Sergey Petrunya wrote:
> > Hello,
> >
> > Please find below descriptions of problems with current @@optimizer_switch
> > variable, as well as a proposal on how to fix it.
> >
> >
> > Problems with @@optimizer_switch
> > Proposed solution
> > Details about hooking this into parser
> > Alternate approach1: grouping
> >
> >
> > Problems with @@optimizer_switch
> >
> >
> > @@optimizer_switch variable use has outgrown its syntax. There are
> > too many settings to fit on the single line:
> >
> > Proposed solution
> > -
> >
> > Break optimizer_switch into multiple variables, with names having the dots
> > in them. That is, current @@optimizer_switch flags will change into this set
> > of variables:
> >
> > optimizer.index_merge=on
> > optimizer.index_merge.union=on
>
> Assorted thoughts:
>
> * I'd suggest to do any changes not in 5.3 but in 5.5, in the new
> sys_vars code
>
> * hierarchical structure of settings is good
>
> * but it has to be done the drizzle way, I mean, all options converted
> at once (you cannot switch the country from right-hand traffic to
> left-hand, by switching only few roads or few cars first :)
I don't see how this analogy works. I understand that a switched car will
collide with non-switched car. If we switch only optimizer variables, what will
that collide with?
> * on the other hand, we don't want to break the backward compatibility,
> so there's a contradiction
>
> * and we want to be compatible with MySQL, right?
Yes.
> * one possibile solution: introduce dots and hierarchy, but for the
> purpose of name matching, compare them as underscores. have an option
> to choouse between printing dots or underscores in show variables.
(replied in another email)
> * another solution, create hierarchies implicitly, splitting on
> underscores. Rename variables that don't fit into the correct
> hierarchy, keep old names as obsolete aliases, remove them later.
If there was a direct mapping from underscores to dots, there would have been
no point in the dot notation.
There is no point in changing @@time_zone to @@time.zone or
@@table_definition_cache to table.definition.cache.
> Allow user to write SET optimizer_semijoin_*= default
> Frankly speaking, I'd prefer a dot as a separator, not an underscore.
>
> * you forgot saving and restoring the value of a group. Now one can save
> @@optimizer_switch in a variable and restore it later. How to do it
> with hierarchies?
We've rejected the SET optimizer.xxx.yyy.*=... syntax in favor of SET
optimizer.prefix=...
> * named keycaches once again will be a problem, the syntax is so out of
> line, it never fits anywhere
I think that disallowing a few names for named key caches ('optimizer',
'aria','innodb') is not a big problem?
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Proposal to split @@optimizer_switch
On Tue, Jun 28, 2011 at 01:20:07AM +0200, Vladislav Vaintroub wrote: > > > -Original Message- > > From: maria-developers- > > bounces+wlad=montyprogram@lists.launchpad.net [mailto:maria- > > developers-bounces+wlad=montyprogram@lists.launchpad.net] On > > Behalf Of Sergey Petrunya > > Sent: Montag, 27. Juni 2011 15:28 > > To: maria-developers@lists.launchpad.net > > Subject: Re: [Maria-developers] Proposal to split @@optimizer_switch > > > > On Fri, Jun 10, 2011 at 12:13:12PM +0400, Sergey Petrunya wrote: > > > Hello, > > > > > > Please find below descriptions of problems with current > > @@optimizer_switch > > > variable, as well as a proposal on how to fix it. > > > > > I would actually prefer more traditional underscore syntax, just for the > sake of uniform parameter handling. Whether the dotted notation is better > readable that underscore one, is mostly matter of individual taste. > Besides, I would remove underscore from some already existing underscored > keyword (this also eliminated differences between semijoin and outer_join > for example). Stuff like indexconditionpushdown becomes unreadable, perhaps > can be "icp", it is referred as ICP everywhere anyway, and there are other > abbreviations already in place, like mrr and bka. > > With this proposal , keywords would look like : > > optimizer_indexmerge=on > optimizer_indexmerge_union=on > optimizer_indexmerge_sortunion=on > optimizer_indexmerge_intersection=on > optimizer_indexmerge_sortintersection=off > optimizer_icp=on > optimizer_joincache_bka=on > optimizer_joincache_hashed=on > optimizer_joincache_incremental=on > optimizer_joincache_optimizebuffersize=on > optimizer_joincache_outerjoin=off > optimizer_joincache_semijoin=off > optimizer_mrr=on > optimizer_mrr_costbased=off > optimizer_mrr_sortkeys=on > optimizer_semijoin=on > optimizer_semijoin_firstmatch=on > optimizer_semijoin_loosescan=on > optimizer_subquery_cache=on > optimizer_subquery_intoexists=on > optimizer_subquery_materialization=off > optimizer_subquery_partialmatch_rowidmerge=on > optimizer_subquery_partialmatch_tablescan=on > optimizer_tableelimination=on > Ok. What syntax should be used to set all optimizer (or optimizer.join_cache) settings to their defaults? Lots of programming languages use xxx.yyy notation to denote access to member yyy of some composite entity xxx, so when one writes optimizer.join_cache.bka it hints that 'optimizer' is an entity that you could operate on as a whole. If we use optimizer_joincache_bka than personally for me it is not clear that 'optimizer' is an entity I could operate on. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Philip please test: Duplicate Elimination for outer joins + semi joins
Hello Philip, I'd like to get the tree at lp:~maria-captains/maria/5.3-subqueries-mwl90 tested for Duplicate Elimination with outer+semi-join processing. I'm interested in crashes, as well as wrong query result bugs. The code path that needs to be tested is selected with this @@optimizer_switch setting: semijoin=on,materialization=off,firstmatch=off,loosescan=off this set of switches disables all semi-join subquery strategies except DuplicateElimination, so you should have no problem with hitting the If you also set "semijoin=off" then you get the execution path that we assume will always produce correct results. Query pattern SELECT FROM complex_join WHERE top_where AND (SELECT ... FROM complex_join WHERE ...) where - subqueries must be "mergeable semi-joins": = no aggregates, unions, or ORDER BYs - both correlated and ucorrelated subqueries are OK. - complex_join is arbitrary join structure (comma join, "t1 JOIN t2", various kinds of outer joins - everything should work) - multiple sibling subqueries, as well as nested subqueries are covered by what you're testing. EXPLAIN - Should have id=='1' && select_type='PRIMARY' on all lines. - Should use Start Temporary/End temporary. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Commits] Rev 3062: Fixed bug lp:800679 in file:///home/tsk/mprog/src/5.3-mwl89/
Hi Timour,
Ok to push.
On Mon, Jun 27, 2011 at 05:40:25PM +0300, tim...@askmonty.org wrote:
> At file:///home/tsk/mprog/src/5.3-mwl89/
>
>
> revno: 3062
> revision-id: tim...@askmonty.org-20110627144014-r14ahro6uzf77423
> parent: i...@askmonty.org-20110625210227-kdneawayskx5jl4q
> fixes bug(s): https://launchpad.net/bugs/800679
> committer: tim...@askmonty.org
> branch nick: 5.3-mwl89
> timestamp: Mon 2011-06-27 17:40:14 +0300
> message:
> Fixed bug lp:800679
>
> Analysis:
> The failed assert ensured that the choice of subquery strategy
> is performed only for queries with at least one table. If there
> is a LIMIT 0 clause all tables are removed, and the subquery is
> neither optimized, nor executed during actual optimization. However,
> if the query is EXPLAIN-ed, the EXPLAIN execution path doesn't remove
> the query tables if there is a LIMIT 0 clause. As a result, the
> subquery optimization code is called, which violates the ASSERT
> condition.
>
> Solution:
> Transform the assert into a condition, and if the outer query
> has no tables assume that there will be at most one subquery
> execution.
>
> There is potentially a better solution by reengineering the
> EXPLAIN/optimize code, so that subquery optimization is not
> done if not needed. Such a solution would be a lot bigger and
> more complex than a bug fix.
> === modified file 'mysql-test/r/subselect_mat_cost_bugs.result'
> --- a/mysql-test/r/subselect_mat_cost_bugs.result 2011-06-05 02:56:06
> +
> +++ b/mysql-test/r/subselect_mat_cost_bugs.result 2011-06-27 14:40:14
> +
> @@ -295,3 +295,24 @@ id select_type table typepossible_keys
> select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4)
> or c1 > 7;
> c1
> drop table t1, t2;
> +#
> +# LP BUG#800679: Assertion `outer_join->table_count > 0' failed in
> +# JOIN::choose_subquery_plan() with materialization=on,semijoin=off
> +#
> +CREATE TABLE t1 ( f1 int);
> +insert into t1 values (1),(2);
> +CREATE TABLE t2 ( f1 int);
> +insert into t2 values (1),(2);
> +SET @@optimizer_switch='materialization=on,semijoin=off';
> +EXPLAIN
> +SELECT * FROM t1
> +WHERE (f1) IN (SELECT f1 FROM t2)
> +LIMIT 0;
> +id select_type table typepossible_keys key key_len ref
>rowsExtra
> +1 PRIMARY NULLNULLNULLNULLNULLNULLNULL
> Impossible WHERE
> +2 DEPENDENT SUBQUERY t2 ALL NULLNULLNULLNULL
>2 Using where
> +SELECT * FROM t1
> +WHERE (f1) IN (SELECT f1 FROM t2)
> +LIMIT 0;
> +f1
> +drop table t1, t2;
>
> === modified file 'mysql-test/t/subselect_mat_cost_bugs.test'
> --- a/mysql-test/t/subselect_mat_cost_bugs.test 2011-05-23 07:56:05
> +
> +++ b/mysql-test/t/subselect_mat_cost_bugs.test 2011-06-27 14:40:14
> +
> @@ -326,3 +326,26 @@ select c1 from t1 where c1 in (select kp
> select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4)
> or c1 > 7;
>
> drop table t1, t2;
> +
> +--echo #
> +--echo # LP BUG#800679: Assertion `outer_join->table_count > 0' failed in
> +--echo # JOIN::choose_subquery_plan() with materialization=on,semijoin=off
> +--echo #
> +
> +CREATE TABLE t1 ( f1 int);
> +insert into t1 values (1),(2);
> +CREATE TABLE t2 ( f1 int);
> +insert into t2 values (1),(2);
> +
> +SET @@optimizer_switch='materialization=on,semijoin=off';
> +
> +EXPLAIN
> +SELECT * FROM t1
> +WHERE (f1) IN (SELECT f1 FROM t2)
> +LIMIT 0;
> +
> +SELECT * FROM t1
> +WHERE (f1) IN (SELECT f1 FROM t2)
> +LIMIT 0;
> +
> +drop table t1, t2;
>
> === modified file 'sql/opt_subselect.cc'
> --- a/sql/opt_subselect.cc2011-06-21 13:00:41 +
> +++ b/sql/opt_subselect.cc2011-06-27 14:40:14 +
> @@ -4358,7 +4358,7 @@ bool JOIN::choose_subquery_plan(table_ma
>by the IN predicate.
> */
> outer_join= unit->outer_select() ? unit->outer_select()->join : NULL;
> -if (outer_join)
> +if (outer_join && outer_join->table_count > 0)
> {
>/*
> The index of the last JOIN_TAB in the outer JOIN where in_subs is
> @@ -4371,7 +4371,6 @@ bool JOIN::choose_subquery_plan(table_ma
> JOIN_TAB, and their join_tab_idx remains MAX_TABLES. Such predicates
> are evaluated for each complete row of the outer join.
>*/
> - DBUG_ASSERT(outer_join->table_count > 0);
>max_outer_join_tab_idx= (in_subs->get_join_tab_idx() == MAX_TABLES) ?
> outer_join->table_count - 1:
> in_subs->get_join_tab_idx();
>
> ___
> commits mailing list
> comm...@mariadb.org
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
--
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
__
Re: [Maria-developers] [Maria-docs] MariaDB 5.3 docs
Hi Daniel, On Tue, Jun 28, 2011 at 09:51:44AM -0400, Daniel Bartholomew wrote: > We're getting closer to the first beta release of MariaDB 5.3 and > several of the new features have been documented in the Knowledgebase > and linked to from this page: > > http://kb.askmonty.org/en/what-is-mariadb-53 > > What I don't know is if there are any features which have either: > > 1. not been documented in the Knowledgebase at all, I've found several missing things and added them. I've also split the list into groups by function. > If you have worked on 5.3, please review the above page to make sure > the items you worked on are present and fully documented. If they aren't > please document them in the Knowledgebase and add them to the above > page (or send me enough information about them so that I can document > them). If they are present, but inadequately documented, please add what > is missing or let me know what is missing. Don't worry about your > documentation being perfect, I will clean it up. If you need any help, > please contact me via email or on IRC. There is lots of optimizer stuff without adequate documentation. Here, optimizer team should provide info. Some of it is already out there. For instance, could you check out these slides http://s.petrunia.net/scratch/new-qep-features-in-mariadb-fosdem2011.odp http://en.oreilly.com/mysql2011/public/schedule/detail/19899 http://en.oreilly.com/mysql2011/public/schedule/detail/20238 and see if they have enough info to document index_merge/sort-intersect and "Fair choice between index_merge and range"? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Default optimizer_switch settings
Hello, Following what has been decided on the optimizer call, I'm nearly done with making semi-join optimizations off by default in 5.3. However, some of 5.3's new optimizations are still turned on by default. Here is a list of 5.3 features and flags that control them: index_merge_sort_intersection=off semijoin=off firstmatch=off loosescan=off ^^ just done the above. materialization=off derived_merge=on derived_with_keys=on ^^ Are we sure about this? index_condition_pushdown=on ^^ This needs to be discussed too. partial_match_rowid_merge=on partial_match_table_scan=on ^^ These are still off because materialization is off. subquery_cache=on ^^ Need to discuss this. mrr=on mrr_cost_based=off mrr_sort_keys=on ^^ Need to discuss this. I take we should disable MRR, too (since BKA is disabled anyway?) join_cache_incremental=on join_cache_hashed=on join_cache_bka=on optimize_join_buffer_size=on ^^ These are not really on because default join_cache_level setting prevents any of these from being used. Another question is that we *MUST* have documentation in place if we're making a release with features that are disabled by default. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Derived table nests not removed by simplify_joins?
Hello Igor, I've discovered the following: Consider a testcase for https://bugs.launchpad.net/maria/+bug/803365. There we have a query that's using a derived table that's on the inner side of an outer join: SELECT * FROM t1 WHERE t1.f1 IN ( SELECT t2.f2 FROM t2 LEFT JOIN ( SELECT * FROM t3 ) AS alias1 ON alias1.f3 = t2.f2 ); If I follow it in debugger to right after simplify_joins(), I can see this structure (the [] brackets denote List bounds): ['t1'---'(sj-nest)'] | ['alias1'---'t2' ] | [t3] 'alias1' is on the inner side of an outer join (it has non-NULL on_expr, and outer_join==1). It has got a single child, t3, which has on_expr==NULL and outer_join==0. Is it really correct that simplify_joins() didn't remove 'alias1', like it does with regular join nests? Could it be that simplify_joins() code can't handle single-child join nests just because the parser never produced them (but after conversion of derived tables to join nests they are now possible?) BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Fwd: [Commits] Rev 3071: Fix LP bug lp:802979 in file:///home/tsk/mprog/src/5.3-mwl89/
Hi Timour, On Mon, Jul 04, 2011 at 03:40:49PM +0300, Timour Katchaounov wrote: > Sergey > > Could you please review my fix for bug lp:802979. > I need you specifically because I needed to touch > the range optimzizer to disable evaluation of > single-row subqueries. > > Please consider if I chose the best place in the > range optimizer to disable this evaluation of > single-row subqueries. > > I will be back from vacation on Tuesday, so if there > is need for discussion, we can talk on Tuesday evening > or Wednesday any time. > I think I have some questions about this fix. Could you catch me on irc so we could discuss it? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Timour please review: [Commits] Rev 3096: BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
Hi Timour,
Could you please review the below:
- Forwarded message from Sergey Petrunya -
From: Sergey Petrunya
To: comm...@mariadb.org
X-Mailer: mail (GNU Mailutils 1.2)
Date: Thu, 14 Jul 2011 00:43:12 +0400 (MSD)
Subject: [Commits] Rev 3096: BUG#778434 Wrong result with in_to_exists=on in
maria-5.3-mwl89 in file:///home/psergey/dev2/5.3-push7/
At file:///home/psergey/dev2/5.3-push7/
revno: 3096
revision-id: pser...@askmonty.org-20110713204306-tsxnddr8v3v0i6bg
parent: pser...@askmonty.org-20110709123340-1qe0558i8p352p2v
committer: Sergey Petrunya
branch nick: 5.3-push7
timestamp: Thu 2011-07-14 00:43:06 +0400
message:
BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
- Make {ha_myisam,ha_maria}::index_read_idx_map check pushed index condition.
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc 2011-02-09 03:17:12 +
+++ b/mysql-test/include/icp_tests.inc 2011-07-13 20:43:06 +
@@ -225,3 +225,32 @@
DROP PROCEDURE insert_data;
DROP TABLE t1, t2, t3;
+
+--echo #
+--echo # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
+--echo #
+CREATE TABLE t1 ( f11 int) ;
+INSERT IGNORE INTO t1 VALUES (0);
+
+CREATE TABLE t2 ( f10 int) ;
+INSERT IGNORE INTO t2 VALUES (0);
+
+CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
+INSERT IGNORE INTO t3 VALUES (6,0),(10,0);
+
+CREATE TABLE t4 ( f11 int) ;
+INSERT IGNORE INTO t4 VALUES
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(NULL),
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
+
+set @tmp_778434=@@optimizer_switch;
+SET
optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off,semijoin=off';
+
+SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
+WHERE (6, 234) IN (
+SELECT t3.f1, t3.f1
+FROM t3 JOIN t4 ON t4.f11 = t3.f10
+);
+
+DROP TABLE t1,t2,t3,t4;
+set optimizer_switch= @tmp_778434;
=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result2011-07-08 14:46:47 +
+++ b/mysql-test/r/innodb_icp.result2011-07-13 20:43:06 +
@@ -202,5 +202,28 @@
12
DROP PROCEDURE insert_data;
DROP TABLE t1, t2, t3;
+#
+# BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
+#
+CREATE TABLE t1 ( f11 int) ;
+INSERT IGNORE INTO t1 VALUES (0);
+CREATE TABLE t2 ( f10 int) ;
+INSERT IGNORE INTO t2 VALUES (0);
+CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
+INSERT IGNORE INTO t3 VALUES (6,0),(10,0);
+CREATE TABLE t4 ( f11 int) ;
+INSERT IGNORE INTO t4 VALUES
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(NULL),
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
+set @tmp_778434=@@optimizer_switch;
+SET
optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off,semijoin=off';
+SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
+WHERE (6, 234) IN (
+SELECT t3.f1, t3.f1
+FROM t3 JOIN t4 ON t4.f11 = t3.f10
+);
+f11f10
+DROP TABLE t1,t2,t3,t4;
+set optimizer_switch= @tmp_778434;
set optimizer_switch=@innodb_icp_tmp;
set storage_engine= @save_storage_engine;
=== modified file 'mysql-test/r/maria_icp.result'
--- a/mysql-test/r/maria_icp.result 2011-07-08 14:46:47 +
+++ b/mysql-test/r/maria_icp.result 2011-07-13 20:43:06 +
@@ -202,5 +202,28 @@
12
DROP PROCEDURE insert_data;
DROP TABLE t1, t2, t3;
+#
+# BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
+#
+CREATE TABLE t1 ( f11 int) ;
+INSERT IGNORE INTO t1 VALUES (0);
+CREATE TABLE t2 ( f10 int) ;
+INSERT IGNORE INTO t2 VALUES (0);
+CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
+INSERT IGNORE INTO t3 VALUES (6,0),(10,0);
+CREATE TABLE t4 ( f11 int) ;
+INSERT IGNORE INTO t4 VALUES
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(NULL),
+(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
+set @tmp_778434=@@optimizer_switch;
+SET
optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off,semijoin=off';
+SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
+WHERE (6, 234) IN (
+SELECT t3.f1, t3.f1
+FROM t3 JOIN t4 ON t4.f11 = t3.f10
+);
+f11f10
+DROP TABLE t1,t2,t3,t4;
+set optimizer_switch= @tmp_778434;
set storage_engine= @save_storage_engine;
set optimizer_switch=@maria_icp_tmp;
=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result2011-07-08 14:46:47 +
+++ b/mysql-test/r/myisam_icp.result2011-07-13 20:43:06 +
@@ -198,6 +198,29 @@
12
DROP PROCEDURE insert_data;
DROP TABLE t1, t2, t3;
+#
+# BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89
+#
+CREATE TABLE t1 ( f11 int) ;
+INSERT IGNORE INTO t1 VALUES (0);
+CREATE TABLE t2 ( f10 int) ;
+INSERT IGNORE INTO t2 VALUES (0);
+CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
+INSERT IGNORE INTO t3 VALUES (6,0),(10,0);
+CREATE TABLE t4 ( f11 int) ;
+INSERT IGNORE INTO t4 VALUES
+(0),(0),(0),(0),(0),(0),(0),(0),(0),
[Maria-developers] Semi-join processing with Duplicate Elimination
Hello,
The below really should be in documentation, but for now, I'm posting it here:
Semi-join processing with Duplicate Elimination
===
There are three cases:
Semi-join next to outer join
ot1 left join (ot10, ot11) semi-join (it1, it2)
interesting possible join orders:
ot1 {ot10 ot11} it1 it2 <-- here we have just a "tail" so no problem.
it1 ot1 {ot10, ot11} it2
Here the range fully covers the inner join with all its
NULL-complemented record generation, etc. We need to take
care about
- NULL rowids are provided for NULL-complemented rows
- being able to jump over all this when doing weedout (can we
have a problem with some OJ's match flag not being reset?)
it1 ot1 it2 {ot10 ot11}
| |
+--+
dups-w range
This is a special case of the previous case. Need to
- check dups-w for NULL-complemented record.
(in case of nested outer joins: check for all NULL-complemented records
generated at this point? well they all call
evaluate_null_complemented_record, do they?)
- check if this works with Not-exists optimization.
it1 ot1 it2 {ot10 ot11}
||
++
dups-w range
This case is the most interesting. The question is: should we first
check dups-w, or care about outer joins?
- dups-w check can be moved to the right.
if we take a record combination of
{ot1.row it2.row ot10.row}
we must not discard it, because we'll need to know if this record
combination extends to
{ot1.row, it2.row ot10.row ot11.row }
If it does, outer join will not have NULL-complemented record, otherwise it
will.
this means: no dups-w checks inside outer joins. Move them to the right
until we've got the table that's last for all inner joins we're in.
Outer join inside semi-join
---
ot1 semi join (it1 left join (iit2, iit3))
Possible join orders:
1) ot1 it1 {iit2, iit3}
2) it1 ot1 {iit2, iit3}
3) it1 {iit2, iit3} ot1
4) ot1 {iit2, iit3} it1
#1 and #2 have an "outside" prefix followed by "tail" of SJ-inner OJ-inner
tables. For iit3, we must first form a record for which both parts of
WHERE and ON have been checked (or the record could be NULL-complemented)
for #3 and #3, dups-w check is done after OJ processing.
Semi-join inside outer join
---
- Not possible in current scheme.
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Commits] Rev 3135: Subquery cache going on disk management fix: Do not go on disk if hit/miss ration less than 2. in file:///home/bell/maria/bzr/work-maria-5.3-subquerycachedis
Hi Sanja
A summary of our discussion on irc/skype:
Please change EXPCACHE_DISK_HITMISS_RATIO to be a "hit ratio", i.e "fraction
of accesses that result in cache hits", as that's this is the most common
number people use when they think about cache performance.
Please change one limit into two:
1. A very poor hit ratio that will cause cache to stop working altogether
(like the below patch does)
2. A moderately poor ratio that will cause the in-memory table to be emptied
(instead of being converted to on-disk table).
Suggested names: EXPCACHE_MIN_HIT_RATIO_FOR_DISK_TABLE,
EXPCACHE_MIN_HIT_RATIO_FOR_MEM_TABLE. Please supply both definitions with
comments about their meaning.
On Thu, Jul 28, 2011 at 01:18:56PM +0300, sa...@askmonty.org wrote:
> === modified file 'sql/sql_expression_cache.cc'
> --- a/sql/sql_expression_cache.cc 2011-07-19 20:19:10 +
> +++ b/sql/sql_expression_cache.cc 2011-07-28 10:18:55 +
> @@ -16,6 +16,8 @@
> #include "mysql_priv.h"
> #include "sql_select.h"
>
> +#define EXPCACHE_DISK_HITMISS_RATIO 2
> /*
>Expression cache is used only for caching subqueries now, so its statistic
>variables we call subquery_cache*.
> @@ -26,7 +28,7 @@ Expression_cache_tmptable::Expression_ca
> List &dependants,
> Item *value)
>:cache_table(NULL), table_thd(thd), items(dependants), val(value),
> - inited (0)
> + hit(0), miss(0), inited (0)
> {
>DBUG_ENTER("Expression_cache_tmptable::Expression_cache_tmptable");
>DBUG_VOID_RETURN;
> @@ -180,10 +182,12 @@ Expression_cache::result Expression_cach
> if (res)
> {
>subquery_cache_miss++;
> + miss++;
>DBUG_RETURN(MISS);
> }
>
> subquery_cache_hit++;
> +hit++;
> *value= cached_result;
> DBUG_RETURN(Expression_cache::HIT);
>}
> @@ -224,12 +228,26 @@ my_bool Expression_cache_tmptable::put_v
>if ((error= cache_table->file->ha_write_tmp_row(cache_table->record[0])))
>{
> /* create_myisam_from_heap will generate error if needed */
> -if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP) &&
> -create_internal_tmp_table_from_heap(table_thd, cache_table,
> -cache_table_param.start_recinfo,
> -&cache_table_param.recinfo,
> -error, 1))
> +if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP))
>goto err;
> +else
> +{
> + if ((hit/miss) < EXPCACHE_DISK_HITMISS_RATIO)
> + {
> +DBUG_PRINT("info", ("hit/miss ratio is not so good to go to disk"));
> +free_tmp_table(table_thd, cache_table);
> +cache_table= NULL;
> +DBUG_RETURN(FALSE);
> + }
> + else
> + {
> +if (create_internal_tmp_table_from_heap(table_thd, cache_table,
> +
> cache_table_param.start_recinfo,
> +&cache_table_param.recinfo,
> +error, 1))
> + goto err;
> + }
> +}
>}
>cache_table->status= 0; /* cache_table->record contains an existed record
> */
>ref.has_record= TRUE; /* the same as above */
>
> === modified file 'sql/sql_expression_cache.h'
> --- a/sql/sql_expression_cache.h 2011-07-19 20:19:10 +
> +++ b/sql/sql_expression_cache.h 2011-07-28 10:18:55 +
> @@ -85,6 +85,8 @@ private:
>List &items;
>/* Value Item example */
>Item *val;
> + /* hit/miss counters */
> + uint hit, miss;
Please change to be ulong (same type as subquery_cache_hit/miss)
>/* Set on if the object has been succesfully initialized with init() */
>bool inited;
> };
>
--
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
[Maria-developers] Is it time to change 5.3 bugs status from Fix Committed to Fix Released?
Hello, The bug database has 197 bugs with status="Fix committed" and target_milestone=5.3. Since we've released 5.3, is it time to change status on these bugs to "Fix released"? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Commits] Rev 3135: Subquery cache going on disk management fix: Do not go on disk if hit rate is not good. in file:///home/bell/maria/bzr/work-maria-5.3-subquerycachedisk/
Hi Sanja, Ok to push. On Thu, Jul 28, 2011 at 05:10:29PM +0300, sa...@askmonty.org wrote: > At file:///home/bell/maria/bzr/work-maria-5.3-subquerycachedisk/ > > > revno: 3135 > revision-id: sa...@askmonty.org-20110728141029-adkbj1vedh86m3uc > parent: i...@askmonty.org-20110723064728-j2sgq39rp528eaol > committer: sa...@askmonty.org > branch nick: work-maria-5.3-subquerycachedisk > timestamp: Thu 2011-07-28 17:10:29 +0300 > message: > Subquery cache going on disk management fix: Do not go on disk if hit rate > is not good. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Commits] Rev 3166: Fixed bug lp:825018 in file:///home/tsk/mprog/src/5.3/
Hi Timour, Ok to push. On Tue, Aug 23, 2011 at 03:41:55PM +0300, tim...@askmonty.org wrote: > At file:///home/tsk/mprog/src/5.3/ > > > revno: 3166 > revision-id: tim...@askmonty.org-20110823123915-jxf9hvxq2hy03rr7 > parent: tim...@askmonty.org-20110822210013-egubev0wgyi00wjt > fixes bug(s): https://launchpad.net/bugs/825018 > committer: tim...@askmonty.org > branch nick: 5.3 > timestamp: Tue 2011-08-23 15:39:15 +0300 > message: > Fixed bug lp:825018 > > Analysis: > During the first execution of the query through the stored > procedure, the optimization phase calls > substitute_for_best_equal_field(), which calls > Item_in_optimizer::transform(). The latter replaces > Item_in_subselect::left_expr with args[0] via assignment. > In this test case args[0] is an Item_outer_ref which is > created/deallocated for each re-execution. As a result, > during the second execution Item_in_subselect::left_expr > pointed to freed memory, which resulted in a crash. > > Solution: > The solution is to use change_item_tree(), so that the > origianal left expression is restored after each execution. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Commits] Rev 3165: Fix bug lp:825095 in file:///home/tsk/mprog/src/5.3/
Hi Timour, ok to push. On Tue, Aug 23, 2011 at 12:00:29AM +0300, tim...@askmonty.org wrote: > At file:///home/tsk/mprog/src/5.3/ > > > revno: 3165 > revision-id: tim...@askmonty.org-20110822210013-egubev0wgyi00wjt > parent: i...@askmonty.org-20110820040205-1suikiokqfn8s81j > fixes bug(s): https://launchpad.net/bugs/825095 > committer: tim...@askmonty.org > branch nick: 5.3 > timestamp: Tue 2011-08-23 00:00:13 +0300 > message: > Fix bug lp:825095 > BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] [Commits] Rev 3156: MWL#182: Explain running statements
Hi Sergei, On Tue, Aug 23, 2011 at 10:55:40PM +0200, Sergei Golubchik wrote: > Hi, Sergey! > > On Aug 23, Sergey Petrunya wrote: > > MWL#182: Explain running statements > > First code > > - "Asynchronous procedure call" system > > I would like to review that part (at least), could you please tell me > when it's ready for a review? Ok. > > - new THD::check_killed() that serves APC request is called from > > within most important loops > > - put THD::check_killed() call into every loop where we could spend > > significant amount of time > > This shouldn't be necessary, by trying to keep all long operations > killable we've already put a "killed" check everywhere. I've been unclear with the comment. I meant, I need to go through all these "if (thd->killed)" checks and replace them with "if (thd->check_killed())" calls. check_killed() will process pending async procedure calls. > If some long time consuming code needs a "killed" check but doesn't have > it - it's a bug on itself (some code does not respond to KILL). BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] MWL#182: a problem select_type column, SIMPLE vs PRIMARY
Hello, When coding MWL#182, I've found out that it is very difficult to print the same value of select_type as EXPLAIN does. Problem description --- Moreover, any attempt to unify EXPLAIN and SHOW EXPLAIN code cause numerous test failures because of changed select_type. http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_select_type defines: - SIMPLESimple SELECT (not using UNION or subqueries) - PRIMARY Outermost SELECT In practice, this is is not always true. As a most striking example, grep for this query in subselect4.test: PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design'))"; The first execution will produce rows with select_type='PRIMARY', while the second will produce the same rows with select_type='SIMPLE'. Apparently, one of these is wrong. If you do a plain select from a VIEW that has a subquery create view V as select ... (subquery) ...; explain select * from V; you get EXPLAIN output which starts with a row that select_type='SIMPLE' and continues with rows describing the subquery, which causes a self-contradiction. Current solution My approach to dealing with this is as follows: - MWL#182 code should not make changes to output of regular EXPLAIN SELECT queries. - Outputs of SHOW EXPLAIN and EXPLAIN SELECT of the same query may be slightly different This causes some ugliness in the code though, because I had to keep two ways to generate select_type values. I think this should be ok (if you disagree, please let me know) Future solution #1: get rid of SIMPLE - I don't see a value of having select_type=SIMPLE (other than term "simple" being encouraging for novice users:) We could change SIMPLE to PRIMARY everywhere and get rid of the problem Future solution #2: use SIMPLE/PRIMARY approach of SHOW EXPLAIN --- EXPLAIN SELECT produces incorrect values because it calls st_select_lex::set_explain_type() before the query rewrites are done, so it can't see that - used VIEWs have subqueries - used subqueries will be flattened - etc. SHOW EXPLAIN operates on a query that is being executed, and that alone guarantees its output is closer to reality. This means, EXPLAIN SELECT output must be changed to match SHOW EXPLAIN output. This is a lot of changes across a number of .result files. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Preparations for 5.3.2 release
Hi! As discussed on the yesterday's call, we're starting to prepare for the next 5.3 release, 5.3.2 beta. The intent is to build it on this or on the next weekend. Before the release, we need to do 1. Bugs 2. optimizer_switch changes. 3. Merge from mariadb-5.1 We don't need to address: 1. Pushing Kristian's replication features. If I heard correctly, the decision was that they won't be pushed into 5.3 (please correct me if I am wrong) == Bugs == Current situation with 5.3-targeted bugs is as follows: Crashing bugs: - SergeyP: 3 + 2 non-repeatable (#860535 #860553 #861147 #860561 (non-repeatable) #860580 (non-repeatable)) - Timour: 2 (#824425 #858148) - Nobody: 1 (#859375) Wrong query result bugs: - Timour: 8 (#858038 #817966 #833777 #825051 #826150 #747278 #833702 #856152) - Igor : 2 (#823301 #791761) - Sanja: 1 (#825075) I have a slight excuse that all my crasing bugs were filed just yesterday. I'll try to fix them before the release. Another problem area is Timour's crashes + wrong query result bugs. Also, we need to assign #859375. == optimizer_switch changes == Some of the new optimizations are already sufficiently stable so that they can be made enabled by default. >From the code quality point of view, Philip's opinion is: - subquery_cache can be turned ON. - join_cache_level can be set to value higher than 1, and also various variants of BKA/Hash join may be enabled. - MRR can be enabled - index_condition_pushdown can be enabled - Materialization still has bugs, cannot be enabled. - semi-joins+materialization cannot be enabled together, there are bugs. - semi-joins without materialization cause non-repeatable crashes, so cannot be enabled yet. - FROM subqueries/derived tables optimization didn't recieve sufficient testing yet, so cannot be enabled. Note that the above is based only on the number of known crashes/wrong query result problems, performance issues are not counted. My opinion is that - subquery_cache can be enabled. - index_condition_pushdown can be enabled. - I'm hesitant to enable MRR because it may cause slowdowns for small-dataset sysbench-like tests. - perhaps, some part of hash join could be enabled (as far as I understand, its overhead is rather small, so it doesn't cause regressions?) Anybody (and especially feature "owners") have any thoughts? == Merge from MariaDB 5.1 == Monty mentioned we'll need to do it, but nobody took this task? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Out of semi-join bugs, time for another testing?
Hi Philip, I've fixed/pushed all semi-join bugs. If I recall correctly, you intended to do more semi-join testing, now everything is ready for it. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] optimizer_switch default settings change for 5.3.2: status?
Hi Sanja, I was wondering if you have any progress with optimizer_switch default settings change that we decided to do for the 5.3.2 release? Any ETA on when that will be pushed? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Buildbot questions
Hi! I've got several questions for our instance of buildbot: Grid display (http://askmonty.org/buildbot/grid) is very nice, but it would be even nicer if one could display only pushes into one particular branch, like in pushbuild. Is it difficult to achieve? If not, can I request it from somebody or will need to spend the effort myself? Can we have buildbot automatically re-build things, say, daily when no other load is present? I've re-ran maria-5.1 tests on my machine several times and it failed all three times with different number of failing tests, which is an indication of precence of random failures. (In Sun/MySQL I used to push fixes that touch only comments in order to trigger re-builds and catch random failures) Can we have buildbot also process other branches? maria trees are public- facing trees, so I suppose that means we should not break them too often, and one way to do that is to have staging trees where features could be tested before they are pushed into the main tree. One could argue that this could be achieved by autopush, but autopush only uses one platform, doesn't save/keep track of test run results, etc. My experience at Sun/MySQL shows that pushbuild (and I suppose buildbot) over a separate branch is much better. It runs different tests on different platforms, pedantically records test results (where everyone can see them), etc etc. Is it possible to set up buildbot to run several configurations, but one after the other? Looking at the buildbot status page, I see that - there are not too many hosts - they are idle most of the time. Suppose I want to use my 32/64 box to build 32 and 64 bit, debug and release, using gcc and sunstudio. That's 2^3 = 8 combinations, but I don't want to have 8 build bots running in parallel (I dont think the box has enough RAM for that)? p.s. I myself have almost repaired my desktop computer, so we can expect to have a dual 32/64-bit buildbot slave to be added as soon as I finish setting things up (sometime this week). BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Patches to consider to include
Hi! On Wed, May 13, 2009 at 10:26:41PM -0700, Vadim Tkachenko wrote: > What do you think about including patches > global_trx_ids, binlog event checksums and fixes to crash-proof slaves > from https://code.launchpad.net/~jtolmer/mysql-server/global-trx-ids > into tree ? Speaking in general (and not about these particular patches), my personal opinion is that right now the blocker to including patches is lack of infrastructure to test both the patch and how it applied to maria tree. Me and Kristian have been trying to sort this out last week - added several buildbot slaves and a staging branch to do testing. We're not fully done with this yet [1]. Once we've sorted this out, it will be possible to make changes to the tree more easily. [1]. The problem I'm looking at is that for some reason it takes unreasonable amounts of time and bandwidth to do any operations on the staging trees. I have no idea why, perhaps we're hitting a bug in bazaar's handling of stacked branches. Maybe we should switch to non-stacked ones. Once that is sorted out, there is a mysql-test run failure I'd prefer to see fixed before we add more changes. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Please add into buildbot: lp:~maria-captains/maria-5.1-table-elimination
Hi Kristian, I've got a branch with the code for MWL#17. Could you please add it to what BuildBot builds? Thanks, Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Tungsten Replicator and MariaDB
Hi Robert, On Thu, Jun 11, 2009 at 08:45:08AM -0700, Robert Hodges wrote: > This brings up a question for the Maria dev team-what are your plans, if > any, for replication support in MariaDB? In particular, are there any > plans that would affect binlog formats? So far we don't have any planned or in-development features that would have an effect on replication or binlog format. MariaDB's replication is expected to be the same as MySQL replication. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Re: [Maria-developers] Tungsten Replicator and MariaDB
On Fri, Jun 19, 2009 at 10:46:04PM +1000, Arjen Lentz wrote: > Hi Sergey > > On 19/06/2009, at 8:12 PM, Sergey Petrunya wrote: >> On Thu, Jun 11, 2009 at 08:45:08AM -0700, Robert Hodges wrote: >>> This brings up a question for the Maria dev team-what are your plans, if >>> any, for replication support in MariaDB? In particular, are there any >>> plans that would affect binlog formats? >> >> So far we don't have any planned or in-development features that would >> have >> an effect on replication or binlog format. MariaDB's replication is >> expected to be the same as MySQL replication. > > > This is the real world, and pragmatism is expected. > Things are broken in the land of MySQL replication, and thus we should fix > it. > The fixes are actually available, so why even bother coming up with > excuses? Oops. I've totally forgot about those. Was thinking of what we ourselves have already in development. About those patches, I have no idea. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] MyISAM locking question
Hi!
I've got this test failure in maria-5.1-table-elimination tree:
main.mysql-bug41486 [ fail ]
http://askmonty.org/buildbot/builders/jaunty-amd64-rel/builds/54/steps/test/logs/stdio
CURRENT_TEST: main.mysql-bug41486
--- .../r/mysql-bug41486.result
+++ .../r/mysql-bug41486.reject
@@ -8,6 +8,5 @@
SET @@global.general_log = @old_general_log;
SELECT LENGTH(data) FROM t1;
LENGTH(data)
-2097152
DROP TABLE t1;
SET @@global.max_allowed_packet = @old_max_allowed_packet;
mysqltest: Result length mismatch
Here's the relevant part of the .test file:
CREATE TABLE t1(data LONGBLOB);
INSERT INTO t1 SELECT REPEAT('1', 2*1024*1024);
let $outfile= $MYSQLTEST_VARDIR/tmp/bug41486.sql;
--error 0,1
remove_file $outfile;
--exec $MYSQL_DUMP test t1 > $outfile
SET @old_general_log = @@global.general_log;
SET @@global.general_log = 0;
# Check that the mysql client does not insert extra newlines when loading
# strings longer than client's max_allowed_packet
--exec $MYSQL --max_allowed_packet=1M test < $outfile 2>&1
SET @@global.general_log = @old_general_log;
SELECT LENGTH(data) FROM t1;
My analysis relvealed that this part of the test
INSERT INTO t1 SELECT REPEAT('1', 2*1024*1024);
let $outfile= $MYSQLTEST_VARDIR/tmp/bug41486.sql;
--error 0,1
remove_file $outfile;
--exec $MYSQL_DUMP test t1 > $outfile
gets executed as follows: when $MYSQL_DUMP runs the
SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
statement to get the table data, the select produces nothing, even though
INSERT statement has already finished by that time (at least from client
point of view).
The reason for select producing nothing is that the optimizer identifies
table t1 as constant (it has one or zero rows), then it tries to get the
record with handler->read_first_row() call, and it gets HA_ERR_END_OF_FILE.
So far I've fixed the test case by adding SELECT COUNT(*) FROM t1 (as an
arbitrary select statement involving t1) after the INSERT.
The questions are:
- Is the above behavior expected of MyISAM? (I suppose it is but I'm not
sure)
- Any ideas why does this suddenly show up when I make totally unrelated
changes in table elimination code. The changed part of the code is never
executed by the test...
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
[Maria-developers] Rev 2730: TEst commits 3 in file:///home/psergey/dev/maria-5.1-table-elim-emailcommittests/
At file:///home/psergey/dev/maria-5.1-table-elim-emailcommittests/ revno: 2730 revision-id: pser...@askmonty.org-20090630181749-29kxcglcbfaiyygp parent: pser...@askmonty.org-20090630180521-32redd6z13g9tluc committer: Sergey Petrunya branch nick: maria-5.1-table-elim-emailcommittests timestamp: Tue 2009-06-30 22:17:49 +0400 message: TEst commits 3 === modified file 'sql/opt_sum.cc' --- a/sql/opt_sum.cc2009-04-25 10:05:32 + +++ b/sql/opt_sum.cc2009-06-30 18:17:49 + @@ -13,7 +13,7 @@ along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ - +# error Test commits 3 /** @file ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
[Maria-developers] Rev 2814: Better comments in file:///home/psergey/dev/mysql-next/
At file:///home/psergey/dev/mysql-next/ revno: 2814 revision-id: pser...@askmonty.org-20090704004450-4pqbx9pm50bzky0l parent: a...@sun.com-20090702085822-8svd0aslr7qnddbb committer: Sergey Petrunya branch nick: mysql-next timestamp: Sat 2009-07-04 04:44:50 +0400 message: Better comments === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2009-06-30 08:03:05 + +++ b/sql/sql_select.cc 2009-07-04 00:44:50 + @@ -3407,8 +3407,8 @@ sj_nest->sj_on_expr->fix_fields(parent_join->thd, &sj_nest->sj_on_expr); /* -Walk through sj nest's WHERE and ON expressions and call -item->fix_table_changes() for all items. +Fix attributes (mainly item->table_map()) for sj-nest's WHERE and ON +expressions. */ sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr); fix_list_after_tbl_changes(parent_lex, &sj_nest->nested_join->join_list); ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
