- developers - lists.mariadb.org

[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/]
by Sergey Petrunya 28 Feb '11

28 Feb '11

Hello Igor, Could you please review the below: ----- Forwarded message from Sergey Petrunya <psergey(a)askmonty.org> ----- From: Sergey Petrunya <psergey(a)askmonty.org> To: commits(a)mariadb.org X-Mailer: mail (GNU Mailutils 1.2) Date: 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: psergey(a)askmonty.org-20110228212959-hl3selurcdou7d22 parent: igor(a)askmonty.org-20110227181411-k81vmi35o0no21ab committer: Sergey Petrunya <psergey(a)askmonty.org> 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 +0000 +++ b/mysql-test/r/join.result 2011-02-28 21:29:59 +0000 @@ -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.test 2010-10-30 13:07:45 +0000 +++ b/mysql-test/t/join.test 2011-02-28 21:29:59 +0000 @@ -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 +0000 +++ b/sql/sql_select.cc 2011-02-28 21:29:59 +0000 @@ -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 commits(a)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

2 1

[Maria-developers] Buildbot Outage
by Adam M. Dutko 28 Feb '11

28 Feb '11

It is possible the buildbots I sponsor are offline today: mariadb-brs, adutko-centos-amd64 and adutko-ultrasparc3. The transformer that feeds our house was hit by lightning last night. It was still operational this morning but I believe the power company is replacing it today.

1 0

[Maria-developers] Fix for PBXT for problem with 'repair-pending'
by Michael Widenius 25 Feb '11

25 Feb '11

Hi! We have got some failures of the following type in pushbuild: pbxt.mysqlslap w2 [ fail ] Test ended at 2011-02-25 14:37:57 CURRENT_TEST: pbxt.mysqlslap mysqltest: At line 43: query 'drop database pbxt' failed: 1010: Error dropping database (can't rmdir './pbxt', errno: 39) The reason is that at the end of the test there was left in the pbxt directory a file "repair-pending" with a reference to some old already deleted table. There was nothing else in the directory. My assumption (and the code seams to agree with it) is that when you delete a table, you don't remove the table from the repair-pending file even if it had been recorded there. Here is a patch that fixes this: === modified file 'storage/pbxt/src/table_xt.cc' --- storage/pbxt/src/table_xt.cc 2010-11-26 22:37:34 +0000 +++ storage/pbxt/src/table_xt.cc 2011-02-25 14:40:53 +0000 @@ -1755,6 +1755,8 @@ xtPublic void xt_drop_table(XTThreadPtr tab_close_mapped_files(self, tab); tab_delete_table_files(self, tab_name, tab_id); + /* Remove table from "repair-pending" */ + xt_tab_table_repaired(tab); ASSERT(xt_get_self() == self); if ((te_ptr = (XTTableEntryPtr) xt_sl_find(self, db->db_ Regards, Monty

2 1

[Maria-developers] Please consider LP BUG#725050 - related to join_cache_hashed
by Timour Katchaounov 25 Feb '11

25 Feb '11

Igor, the following bug is present in different ways both in 5.3 and in 5.3-mwl89. https://bugs.launchpad.net/maria/+bug/725050 The crash in 5.3-mwl89 appeared after merging with the latest 5.3. The crash in 5.3 requires a rather artificial example, but it shows that there is some inconsistency in JOIN::keyuse in 5.3. Please look at the last bug comment - it contains a very small test case. I will ping you tonight after putting kids to sleep around 21:30. Timour

1 0

[Maria-developers] [psergey@askmonty.org: [Commits] Rev 2921: BUG#723822: Crash in get_constant_key_infix with EXISTS ( SELECT .. DISTINCT ) in file:///home/psergey/dev2/5.3/]
by Sergey Petrunya 25 Feb '11

25 Feb '11

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 <psergey(a)askmonty.org> ----- From: Sergey Petrunya <psergey(a)askmonty.org> To: commits(a)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: psergey(a)askmonty.org-20110225095856-qgykwziqv5h1suqr parent: psergey(a)askmonty.org-20110224203200-4uf4e4kngwdmtmgw committer: Sergey Petrunya <psergey(a)askmonty.org> 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.result 2011-02-10 08:36:43 +0000 +++ b/mysql-test/r/subselect4.result 2011-02-25 09:58:56 +0000 @@ -689,3 +689,21 @@ 2 DEPENDENT SUBQUERY X ALL NULL NULL NULL NULL 10 2 DEPENDENT SUBQUERY B ALL a,b NULL NULL NULL 1000 Range 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 +0000 +++ b/mysql-test/t/subselect4.test 2011-02-25 09:58:56 +0000 @@ -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 +0000 +++ b/sql/opt_range.cc 2011-02-25 09:58:56 +0000 @@ -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 commits(a)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

2 1

[Maria-developers] Using tilde in the version for debs?
by Henrik Ingo 24 Feb '11

24 Feb '11

Hi Kristian I've been a bit invisible for a few months now, as I had to spend time on job interviews, family and luckily also some coding! By March life is turning back to normal with a new job and everything. As you know by now, I have in the past months fulfilled my desire to spend some hands on time with the MariaDB build system. I intend to blog about it in the coming week, after which I'll email some MariaDB specific details about what improvements you may want to consider incorporating, and what I think are great improvements but probably not worth focusing on for you in the short term. But just as a quickie, I noticed you are changing the versioning of debs to include the distribution codename (lucid, maverick, etc..): http://bazaar.launchpad.net/~maria-captains/ourdelta/ourdelta-knielsen/revi… What caught my eye was the usage of tilde character for punctuation. In dpkg tilde is a special character with superpowers, it is sorted as greater than anything else: http://man.he.net/man5/deb-version You probably know that, of course, and have a good reason to use it (related to getting upgrades right, perhaps?). I just wanted to comment on it "just in case". "In case" there is no particular reason to use tilde, then of course using a dot would have been more natural. Percona uses a dot http://www.percona.com/downloads/Percona-Server-5.1/Percona-Server-5.1.54-1… ...and I use that with the MepSQL packages too. As a final observation - apart from the different puntuation, the version string seems to be similar in all three of percona, mepsql and ourdelta-knielsen. (So it is kind of a pity if you are forced to use the tilde for some reason - otoh MariaDB versioning is a bit different anyway as it doesn't follow the <mysql-base-ver>-<my-release-ver> approach.) cheers and I'll get back later with more interesting comments. henrik -- henrik.ingo(a)avoinelama.fi +358-40-5697354 skype: henrik.ingo irc: hingo www.openlife.cc My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559

2 2

Re: [Maria-developers] [Commits] Rev 2875: The patch adds the code that allows to use equi-join conditions in file:///home/igor/maria/maria-5.3-mwl128-hjkey/
by Timour Katchaounov 24 Feb '11

24 Feb '11

Igor, While merging 5.3-mwl89 with 5.3 I noticed that when you extracted a block of code from add_key_part() into the new function add_keyuse(), you skipped the initialization: keyuse.ref_table_rows= 0; was it intentional, and if so, why? Timour > At file:///home/igor/maria/maria-5.3-mwl128-hjkey/ > > ------------------------------------------------------------ > revno: 2875 > revision-id: igor(a)askmonty.org-20110105055941-xht2mpqe66a64fbm > parent: igor(a)askmonty.org-20101230021403-ly4ku1yq675l5d6b > committer: Igor Babaev<igor(a)askmonty.org> > branch nick: maria-5.3-mwl128-hjkey > timestamp: Tue 2011-01-04 21:59:41 -0800 > message: > The patch adds the code that allows to use equi-join conditions > for hash join in the cases when there are no suitable indexes > for these conditions. > > > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

Re: [Maria-developers] Fwd: [Commits] Rev 2921: Added build script which works with ICC v12.0 in file:///home/hakan/work/monty_program/maria-5.2/
by Kristian Nielsen 24 Feb '11

24 Feb '11

Hakan Kuecuekyilmaz <hakan(a)askmonty.org> writes: > Kristian, can you please review the new build script, so that I can > push it into our 5.2 tree. After we cleared the license details with Ok to push. - Kristian. > Begin forwarded message: > >> From: "Hakan Kuecuekyilmaz" <hakan.kuecuekyilmaz(a)t-online.de> >> Date: 10. Februar 2011 16:41:30 MEZ >> To: <commits(a)mariadb.org> >> Subject: [Commits] Rev 2921: Added build script which works with ICC v12.0 in file:///home/hakan/work/monty_program/maria-5.2/ >> Reply-To: maria-developers(a)lists.launchpad.net >> >> At file:///home/hakan/work/monty_program/maria-5.2/ >> >> ------------------------------------------------------------ >> revno: 2921 >> revision-id: hakan(a)askmonty.org-20110210154128-ncaubaj5i8rhf2pe >> parent: monty(a)askmonty.org-20110203144529-ah7majfmqscp4yn1 >> committer: Hakan Kuecuekyilmaz <hakan(a)askmonty.org> >> branch nick: maria-5.2 >> timestamp: Thu 2011-02-10 16:41:28 +0100 >> message: >> Added build script which works with ICC v12.0 >> === added file 'BUILD/compile-pentium64-icc-max' >> --- a/BUILD/compile-pentium64-icc-max 1970-01-01 00:00:00 +0000 >> +++ b/BUILD/compile-pentium64-icc-max 2011-02-10 15:41:28 +0000 >> @@ -0,0 +1,25 @@ >> +#! /bin/sh >> + >> +path=`dirname $0` >> +. "$path/SETUP.sh" >> + >> +# Note that we can't use ccache with icc as the generated .deps file will >> +# then contain wrong information. >> +CC=icc >> +CXX=icpc >> +LD=xild >> +AR=xiar >> +export CC CXX LD AR >> + >> +c_warnings="" >> +cxx_warnings="" >> +extra_flags="-O2 -unroll2 -fno-exceptions -fno-rtti -fno-omit-frame-pointer -static-intel -static-libgcc -fPIE" >> + >> +# Use -no-ipo if you get this error >> +# IPO link: can not find "-lstdc++_shared" >> +# icpc: error: problem during multi-file optimization compilation (code 1) >> +base_cxxflags="$extra_flags" >> +extra_configs="$pentium_configs" >> + >> +. "$path/FINISH.sh" >> +

1 0

[Maria-developers] WL#177 New (by Mdcallag): add server-side error logging
by worklog-noreply＠askmonty.org 22 Feb '11

22 Feb '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: add server-side error logging CREATION DATE..: Tue, 22 Feb 2011, 19:42 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 177 (http://askmonty.org/worklog/?tid=177) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: I want to log all or a sample of errors on the server side into a new log file (not general, error or slow query log) and into a file that can be rotated. In the facebook patch I added code to sample query errors into the slow query log using - -log-sample-error-rate: * when 0 do not sample * when 1 then log all errors * when > 1 then sample 1 of --log-sample-error-rate queries This should have: * query text * username * timestamp * database * errno * error text * client IP The log should support rotation and possibly auto-rotation when it reaches a certain size. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

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/
by Sergey Petrunya 22 Feb '11

22 Feb '11

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 <igor(a)askmonty.org> > Reply-To: maria-developers(a)lists.launchpad.net > To: <commits(a)mariadb.org> > > At file:///home/igor/maria/maria-5.3-mwl128-hashrange/ > > ------------------------------------------------------------ > revno: 2901 > revision-id: igor(a)askmonty.org-20110212044122-w9n3jdk3d2ps0w3o > parent: igor(a)askmonty.org-20110207231903-3rqbfs50d33lk3r9 > committer: Igor Babaev <igor(a)askmonty.org> > 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 +0000 > +++ b/sql/sql_select.cc 2011-02-12 04:41:22 +0000 ... > @@ -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 | <derived2> | 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); +----+-------------+-------------+--------+---------------+--------------+---------+------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+---------------+--------------+---------+------------+------+---------------------------------+ | 1 | PRIMARY | t10 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where | | 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 5 | test.t10.a | 1 | | | 2 | SUBQUERY | t10 | ALL | NULL | NULL | NULL | NULL | 1000 | Using temporary; Using filesort | +----+-------------+-------------+--------+---------------+--------------+---------+------------+------+---------------------------------+ 3 rows in set (0.01 sec) # The following is from current 5.3: MariaDB [j1]> explain select distinct * from t1 where a in (select b from t2); +----+-------------+------------+--------+---------------+------------+---------+------+------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+------------+---------+------+------+-----------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | Using temporary | | 1 | PRIMARY | subselect2 | eq_ref | unique_key | unique_key | 5 | func | 1 | | | 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 1000 | Distinct | +----+-------------+------------+--------+---------------+------------+---------+------+------+-----------------+ ... and it either uses no quoting at all (see "unique_key"), or angle brackets quoting (see <subquery2>). I think we should not multiply the ways of quoting, so please consider switching to <quoting> or to not using any quoting at all. ISSUE2 MariaDB [j1]> EXPLAIN -> SELECT City.Name, Country.Name, CountryLanguage.Language -> FROM City,Country,CountryLanguage -> WHERE City.Country=Country.Code AND -> CountryLanguage.Country=Country.Code AND -> City.Name LIKE 'L%' AND Country.Population > 3000000 AND -> CountryLanguage.Percentage > 50 AND -> LENGTH(Language) < LENGTH(City.Name) - 2; +----+-------------+-----------------+----------+--------------------+---------------+---------+----------------------------+------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+----------+--------------------+---------------+---------+----------------------------+------+--------------------------------------------------+ | 1 | SIMPLE | CountryLanguage | ALL | PRIMARY,Percentage | NULL | NULL | NULL | 984 | Using where | | 1 | SIMPLE | Country | hash_ALL | PRIMARY | #hash#PRIMARY | 3 | j1.CountryLanguage.Country | 239 | Using where; Using join buffer (flat, BNLH join) | | 1 | SIMPLE | City | hash_ALL | Country | #hash#Country | 3 | j1.CountryLanguage.Country | 4079 | Using where; Using join buffer (flat, BNLH join) | +----+-------------+-----------------+----------+--------------------+---------------+---------+----------------------------+------+--------------------------------------------------+ 3 rows in set (0.01 sec) "key" column shows "#hash#PRIMARY". I suppose, this is because hash join opimizer is internally hooked into ref optimizer, and it used the PRIMARY index. This might be useful for developer, but is this information meaningful for the user? If one changes #hash#PRIMARY to #hash#IDX2 (suppose there is an index called IDX2 which has the same definition as primary key), or to #hash#$hj, will it be a different query plan? I think it should be "<hashkey>" always. ISSUE3 For hash_range, "key" column shows {hashkey}:{range_key}, which allows to determine that which key was used for range access. For hash_index type, "key" column shows only the hash's pseudo-index: MariaDB [j3]> EXPLAIN SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl; +----+-------------+-------+------------+---------------+-----------+---------+------+------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+---------------+-----------+---------+------+------+--------------------------------------------------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 6 | | | 1 | SIMPLE | t1 | hash_index | cu1,cu2 | #hash#cu1 | 33 | func | 10 | Using where; Using join buffer (flat, BNLH join) | +----+-------------+-------+------------+---------------+-----------+---------+------+------+--------------------------------------------------+ 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. 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? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 2