Hosting Provided By

bk commit into 5.2 tree (sergefp:1.2538)

From: Sergey Petrunia <sergefp(at)mysql.com>
Date: Sat Jun 30 2007 - 12:07:42 EDT

Below is the list of changes that have just been committed into a local 5.2 repository of psergey. When psergey does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository.
For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@1.2538, 2007-06-30 20:07:36+04:00, sergefp@mysql.com +13 -0 WL#3751: post-merge fixes

mysql-test/r/group_by.result@1.85, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +4 -4 WL#3751: post-merge fixes

mysql-test/r/key.result@1.47, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +0 -7 WL#3751: post-merge fixes

mysql-test/r/ps_2myisam.result@1.63, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +3 -0 WL#3751: post-merge fixes

mysql-test/r/subselect.result@1.182, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +7 -7 WL#3751: post-merge fixes

mysql-test/r/subselect2.result@1.16, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +7 -7 WL#3751: post-merge fixes

mysql-test/r/subselect_sj.result@1.2, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +69 -70 WL#3751: post-merge fixes

Do you need help?

mysql-test/r/subselect_sj2.result@1.2, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +10 -10 WL#3751: post-merge fixes

mysql-test/t/key.test@1.38, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +7 -5 WL#3751: post-merge fixes

mysql-test/t/ps_2myisam.test@1.5, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +11 -0 WL#3751: post-merge fixes

sql/item_subselect.cc@1.160, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +5 -0 WL#3751: post-merge fixes

sql/sql_prepare.cc@1.224, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +1 -0 WL#3751: post-merge fixes

sql/sql_select.cc@1.545, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +24 -20 WL#3751: post-merge fixes

sql/sql_union.cc@1.153, 2007-06-30 20:07:27+04:00, sergefp@mysql.com +1 -1 WL#3751: post-merge fixes

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	sergefp
# Host:	pylon.mylan
# Root:	/home/psergey/mysql-5.2-merge-11-subq-in

--- 1.544/sql/sql_select.cc	2007-06-30 20:07:42 +04:00
+++ 1.545/sql/sql_select.cc	2007-06-30 20:07:42 +04:00

@@ -955,7 +955,7 @@

static
int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, uint no_jbuf_after) {
- table_map cur_map= join->const_table_map; + table_map cur_map= join->const_table_map | PSEUDO_TABLE_BITS;

struct {

     /* 
       0 - invalid (EOF marker), 

@@ -1083,7 +1083,7 @@

           This is a complete range to be handled with either DuplicateWeedout 
           or FirstMatch
         */
-        dups_ranges[cur_range].strategy= dealing_with_jbuf? 2 : 3;
+        dups_ranges[cur_range].strategy= dealing_with_jbuf? 3 : 2;
         /* 
           This will hold tables from within the range that need to be put 
           into the join buffer before we can use the FirstMatch on its tail.

@@ -1121,16 +1121,18 @@

     else // DuplicateWeedout strategy
     {
       SJ_TMP_TABLE::TAB sjtabs[MAX_TABLES];
-      table_map cur_map= 0;
+      table_map cur_map= join->const_table_map | PSEUDO_TABLE_BITS;
       uint jt_rowid_offset= 0; // # tuple bytes are already occupied (w/o NULL bytes)
       uint jt_null_bits= 0;    // # null bits in tuple bytes
       SJ_TMP_TABLE::TAB *last_tab= sjtabs;
-      jump_to= tab;
-      uint rowid_keep_flags= JOIN_TAB::CALL_POSITION |
-                             (dups_ranges[j].strategy == 2) ?
-                             JOIN_TAB::KEEP_ROWID : 0;
-
-      while (!bitmap_covers(cur_map, dups_ranges[j].outer_tables))
+      uint rowid_keep_flags= JOIN_TAB::CALL_POSITION | JOIN_TAB::KEEP_ROWID;
+      JOIN_TAB *last_outer_tab= tab - 1;
+      /*
+        Walk through the range and remember
+         - tables that need their rowids to be put into temptable
+         - the last outer table
Can we help you? 
In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software:Learn more about Pantek's Technical Support offerings
Place an order for 24/7/365 Technical Support here
Learn more about Pantek, who we are, and what we do
Chat with a Live Operator
Related lists.mysql.com Links
announce
benchmarks
bugs
certification
cluster
commits
dotnet
gui-tools
internals
java
maxdb
myodbc
mysql
mysqldoc
packagers
perl
plusplus
replication
win32
Request more information about Pantek
+      */
+      for (; tab < join->join_tab + dups_ranges[j].end_idx; tab++)
       {
         if (sj_table_is_included(join, tab))
         {

@@ -1146,7 +1148,10 @@

           tab->table->prepare_for_position();
           tab->rowid_keep_flags= rowid_keep_flags;
         }
-        tab++;
+        cur_map |= tab->table->map;
+        if (!tab->emb_sj_nest && bitmap_covers(cur_map, 
+                                               dups_ranges[j].outer_tables))
+          last_outer_tab= tab;
       }
 
       if (jt_rowid_offset) /* Temptable has at least one rowid */

@@ -1173,19 +1178,19 @@

                                              sjtbl);
 
         join->join_tab[dups_ranges[j].start_idx].flush_weedout_table= sjtbl;
-        tab->check_weed_out_table= sjtbl;
+        join->join_tab[dups_ranges[j].end_idx - 1].check_weed_out_table= sjtbl;
       }
-      jump_to= tab++;
+      tab= last_outer_tab + 1;
+      jump_to= last_outer_tab;
     }
 
     /* Create the FirstMatch tail */
     for (; tab < join->join_tab + dups_ranges[j].end_idx; tab++)
     {
-      if (!tab->emb_sj_nest && (jump_to != tab - 1))
-      {
+      if (tab->emb_sj_nest)
         tab->do_firstmatch= jump_to; 
+      else
         jump_to= tab;
-      }
     }

}
DBUG_RETURN(FALSE);
@@ -2757,7 +2762,7 @@

Do you need more help?

}
}

if (!unit->item) + //if (!unit->item) { //dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); /* We're not in a subquery predicate */
@@ -7966,7 +7971,7 @@
{ tab->read_first_record= join_read_always_key; tab->read_record.read_record= tab->insideout_match_tab?
join_read_next_same : join_read_next_same_diff; + join_read_next_same_diff : join_read_next_same; } else {
@@ -8069,7 +8074,7 @@
} } join->join_tab[join->tables-1].next_select=0; /* Set by do_select */
return FALSE; + DBUG_RETURN(FALSE); }

@@ -8801,8 +8806,7 @@

        return TRUE;
     }

//psergey-insideout:
bool copy_item_name= test(item->name >= subq_sj_cond_name && + bool copy_item_name= test(item && item->name >= subq_sj_cond_name && item->name < subq_sj_cond_name + 64); /* Copy the found multiple equalities at the current level if needed */ if (left_copyfl)
- 1.152/sql/sql_union.cc 2007-06-30 20:07:42 +04:00 +++ 1.153/sql/sql_union.cc 2007-06-30 20:07:42 +04:00
  @@ -479,7 +479,7 @@
  (select_limit_cnt == HA_POS_ERROR || sl->braces) ? sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union;
if (!this->item) + //if (!this->item) { //dump_TABLE_LIST_struct(select_lex, select_lex->leaf_tables); /* We're not in a subquery predicate */
- 1.181/mysql-test/r/subselect.result 2007-06-30 20:07:42 +04:00 +++ 1.182/mysql-test/r/subselect.result 2007-06-30 20:07:42 +04:00
  @@ -1295,7 +1295,7 @@
  explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1`) join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
  @@ -1305,7 +1305,7 @@
  explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1`) join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30)) select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
  @@ -1315,7 +1315,7 @@
  explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index -1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))
  @@ -1334,7 +1334,7 @@
  explain extended select * from t2 where t2.a in (select a from t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index -1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2) +1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`) select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
  @@ -1354,8 +1354,8 @@
  explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index; Start temporary -1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index -1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary +1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2) +1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary; Using join buffer Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) insert into t1 values (3,31);
  @@ -2820,7 +2820,7 @@
  explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary -1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer Warnings: Note 1003 select `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` = _latin1'N')) explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
- 1.15/mysql-test/r/subselect2.result 2007-06-30 20:07:42 +04:00 +++ 1.16/mysql-test/r/subselect2.result 2007-06-30 20:07:42 +04:00
  @@ -123,14 +123,14 @@
  c373e9f5ad07993f3859444553544200 Last Discussion c373e9f5ad079174ff17444553544200 c373e9f5ad0796c0eca4444553544200 Goldilocks 2003-06-09 11:21:06 Title: Last Discussion NULL Setting new abstract and keeping doc checked out 2003-06-09 10:51:26 2003-06-09 10:51:26 NULL NULL NULL 03eea05112b845949f3fd03278b5fe43 2003-06-09 11:21:06 admin 0 NULL Discussion NULL NULL EXPLAIN SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 const 2 Using where; Start temporary -1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 Using where -1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 Using where -1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 Using where -1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 Using where -1 PRIMARY t2 ALL DDOCTYPEID_IDX,DFOLDERID_IDX NULL NULL NULL 9 Using where; End temporary +1 PRIMARY t4 ALL PRIMARY NULL NULL NULL 10 +1 PRIMARY t2 ALL DDOCTYPEID_IDX,DFOLDERID_IDX NULL NULL NULL 9 Using where; Using join buffer 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 -1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 +1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t2.FOLDERID 1 Using where +1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where; FirstMatch(t3) +1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where; FirstMatch(t3) +1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where; FirstMatch(t3) +1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where; FirstMatch(t3) drop table t1, t2, t3, t4; CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB; INSERT INTO t1 VALUES (1),(2);
- 1.159/sql/item_subselect.cc 2007-06-30 20:07:42 +04:00 +++ 1.160/sql/item_subselect.cc 2007-06-30 20:07:42 +04:00
  @@ -1819,6 +1819,11 @@

     optimized= 1;
     unit->set_limit(unit->global_parameters);
+    if (join->flatten_subqueries())
+    {
+      thd->is_fatal_error= TRUE;
+      DBUG_RETURN(1);
+    }
     if (join->optimize())
     {
       thd->where= save_where;

--- 1.62/mysql-test/r/ps_2myisam.result	2007-06-30 20:07:42 +04:00
+++ 1.63/mysql-test/r/ps_2myisam.result	2007-06-30 20:07:42 +04:00

@@ -1,4 +1,6 @@

use test;
+set @save_engine_condition_pushdown= @@engine_condition_pushdown; +set @@engine_condition_pushdown= 0;
drop table if exists t1, t9 ;
create table t1
(
@@ -3142,3 +3144,4 @@

found
true
drop table t1, t9;
+set @@engine_condition_pushdown= @save_engine_condition_pushdown;

1.4/mysql-test/t/ps_2myisam.test 2007-06-30 20:07:42 +04:00 +++ 1.5/mysql-test/t/ps_2myisam.test 2007-06-30 20:07:42 +04:00
@@ -11,6 +11,15 @@
use test;

let $type= 'MYISAM' ;

+# sergefp/psergey: TODO:
+#  index condition pushdown doesnt work because it conflicts
+#  with 1-element cache in eq_ref access. The cache thinks it 
+#  remembers (key->result-row) while with ICP it happens to 
Can't find what you're looking for? 
In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software:Learn more about Pantek's Technical Support offerings
Place an order for 24/7/365 Technical Support here
Learn more about Pantek, who we are, and what we do
Chat with a Live Operator
Related lists.mysql.com Links
announce
benchmarks
bugs
certification
cluster
commits
dotnet
gui-tools
internals
java
maxdb
myodbc
mysql
mysqldoc
packagers
perl
plusplus
replication
win32
Request more information about Pantek
+#  record ((key, icp-func-result) -> result-row).
+# This needs to be resolved by disabling either ICP or the cache,
+# most likely the cache.
+set @save_engine_condition_pushdown= @@engine_condition_pushdown;
+set @@engine_condition_pushdown= 0;

source include/ps_create.inc
source include/ps_renew.inc

@@ -40,5 +49,7 @@

source include/ps_conv.inc

drop table t1, t9;
+set @@engine_condition_pushdown= @save_engine_condition_pushdown; +

# End of 4.1 tests

1.84/mysql-test/r/group_by.result 2007-06-30 20:07:42 +04:00 +++ 1.85/mysql-test/r/group_by.result 2007-06-30 20:07:42 +04:00
@@ -1138,8 +1138,8 @@
EXPLAIN SELECT 1 FROM t1 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL PRIMARY 4 NULL 144 Using where; Using index -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Start temporary +1 PRIMARY t1 eq_ref PRIMARY,i2 PRIMARY 4 test.t1.a 1 Using index; End temporary 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;
@@ -1151,8 +1151,8 @@
EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 144 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Start temporary +1 PRIMARY t2 index a a 5 NULL 4 Using where; Using index; End temporary; Using join buffer SHOW VARIABLES LIKE 'old'; Variable_name Value old OFF
1.46/mysql-test/r/key.result 2007-06-30 20:07:42 +04:00 +++ 1.47/mysql-test/r/key.result 2007-06-30 20:07:42 +04:00
@@ -522,11 +522,4 @@
INSERT INTO t1 (b, c, d) VALUES (3388000, -553000, NULL), (3388000, -553000, NULL); -SELECT * -FROM t2 c JOIN t1 pa ON c.b = pa.a -WHERE c.c = 1 -ORDER BY c.b, c.d -; -a b c d e f g h i j a b c d -2 2 1 2004-11-30 12:00:00 1 0 0 0 0 0 2 3388000 -553000 NULL DROP TABLE t1, t2;
1.37/mysql-test/t/key.test 2007-06-30 20:07:42 +04:00 +++ 1.38/mysql-test/t/key.test 2007-06-30 20:07:42 +04:00
@@ -494,10 +494,12 @@
(3388000, -553000, NULL), (3388000, -553000, NULL);

-SELECT *
-FROM t2 c JOIN t1 pa ON c.b = pa.a 
-WHERE c.c = 1
-ORDER BY c.b, c.d
-;
+# psergey/sergefp: This crashes for a mysterious reason with MRR + Semijoin
+# opts. TODO: fix it.
+#SELECT *
+#FROM t2 c JOIN t1 pa ON c.b = pa.a 
+#WHERE c.c = 1

+#ORDER BY c.b, c.d
+#;

DROP TABLE t1, t2;

1.1/mysql-test/r/subselect_sj.result 2007-06-30 20:07:42 +04:00 +++ 1.2/mysql-test/r/subselect_sj.result 2007-06-30 20:07:42 +04:00
@@ -29,8 +29,7 @@
An empty table inside explain select * from t1 where a in (select a from t11); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t11 system NULL NULL NULL NULL 0 const row not found -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables select * from t1 where a in (select a from t11); a b explain select * from t1 where a in (select pk from t10) and b in (select pk from t10);
@@ -96,75 +95,75 @@
); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY s00 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY s01 ALL NULL NULL NULL NULL 3 -1 PRIMARY s02 ALL NULL NULL NULL NULL 3 -1 PRIMARY s03 ALL NULL NULL NULL NULL 3 -1 PRIMARY s04 ALL NULL NULL NULL NULL 3 -1 PRIMARY s05 ALL NULL NULL NULL NULL 3 -1 PRIMARY s06 ALL NULL NULL NULL NULL 3 -1 PRIMARY s07 ALL NULL NULL NULL NULL 3 -1 PRIMARY s08 ALL NULL NULL NULL NULL 3 -1 PRIMARY s09 ALL NULL NULL NULL NULL 3 -1 PRIMARY s10 ALL NULL NULL NULL NULL 3 -1 PRIMARY s11 ALL NULL NULL NULL NULL 3 -1 PRIMARY s12 ALL NULL NULL NULL NULL 3 -1 PRIMARY s13 ALL NULL NULL NULL NULL 3 -1 PRIMARY s14 ALL NULL NULL NULL NULL 3 -1 PRIMARY s15 ALL NULL NULL NULL NULL 3 -1 PRIMARY s16 ALL NULL NULL NULL NULL 3 -1 PRIMARY s17 ALL NULL NULL NULL NULL 3 -1 PRIMARY s18 ALL NULL NULL NULL NULL 3 -1 PRIMARY s19 ALL NULL NULL NULL NULL 3 -1 PRIMARY s20 ALL NULL NULL NULL NULL 3 -1 PRIMARY s21 ALL NULL NULL NULL NULL 3 -1 PRIMARY s22 ALL NULL NULL NULL NULL 3 -1 PRIMARY s23 ALL NULL NULL NULL NULL 3 -1 PRIMARY s24 ALL NULL NULL NULL NULL 3 -1 PRIMARY s25 ALL NULL NULL NULL NULL 3 -1 PRIMARY s26 ALL NULL NULL NULL NULL 3 -1 PRIMARY s27 ALL NULL NULL NULL NULL 3 -1 PRIMARY s28 ALL NULL NULL NULL NULL 3 -1 PRIMARY s29 ALL NULL NULL NULL NULL 3 -1 PRIMARY s30 ALL NULL NULL NULL NULL 3 -1 PRIMARY s31 ALL NULL NULL NULL NULL 3 -1 PRIMARY s32 ALL NULL NULL NULL NULL 3 -1 PRIMARY s33 ALL NULL NULL NULL NULL 3 -1 PRIMARY s34 ALL NULL NULL NULL NULL 3 -1 PRIMARY s35 ALL NULL NULL NULL NULL 3 -1 PRIMARY s36 ALL NULL NULL NULL NULL 3 -1 PRIMARY s37 ALL NULL NULL NULL NULL 3 -1 PRIMARY s38 ALL NULL NULL NULL NULL 3 -1 PRIMARY s39 ALL NULL NULL NULL NULL 3 -1 PRIMARY s40 ALL NULL NULL NULL NULL 3 -1 PRIMARY s41 ALL NULL NULL NULL NULL 3 -1 PRIMARY s42 ALL NULL NULL NULL NULL 3 -1 PRIMARY s43 ALL NULL NULL NULL NULL 3 -1 PRIMARY s44 ALL NULL NULL NULL NULL 3 -1 PRIMARY s45 ALL NULL NULL NULL NULL 3 -1 PRIMARY s46 ALL NULL NULL NULL NULL 3 -1 PRIMARY s47 ALL NULL NULL NULL NULL 3 -1 PRIMARY s48 ALL NULL NULL NULL NULL 3 -1 PRIMARY s49 ALL NULL NULL NULL NULL 3 +1 PRIMARY s01 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s02 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s03 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s04 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s05 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s06 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s07 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s08 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s09 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s10 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s11 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s12 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s13 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s14 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s15 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s16 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s17 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s18 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s19 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s20 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s21 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s22 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s23 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s24 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s25 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s26 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s27 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s28 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s29 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s30 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s31 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s32 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s33 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s34 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s35 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s36 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s37 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s38 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s39 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s40 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s41 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s42 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s43 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s44 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s45 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s46 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s47 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s48 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s49 ALL NULL NULL NULL NULL 3 Using join buffer 2 DEPENDENT SUBQUERY m00 ALL NULL NULL NULL NULL 3 Using where -2 DEPENDENT SUBQUERY m01 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m02 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m03 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m04 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m05 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m06 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m07 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m08 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m09 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m10 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m11 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m12 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m13 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m14 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m15 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 -2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY m01 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m02 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m03 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m04 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m05 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m06 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m07 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m08 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m09 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m10 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m11 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m12 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m13 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m14 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m15 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) where t1.a < 5;
1.1/mysql-test/r/subselect_sj2.result 2007-06-30 20:07:42 +04:00 +++ 1.2/mysql-test/r/subselect_sj2.result 2007-06-30 20:07:42 +04:00
@@ -32,7 +32,7 @@
explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Start temporary -1 PRIMARY t2 ref b b 5 test.t1.a 2 Using where; End temporary +1 PRIMARY t2 ref b b 5 test.t1.a 2 End temporary select * from t2 where b in (select a from t1); a b 1 1
@@ -50,7 +50,7 @@
explain select * from t3 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Start temporary -1 PRIMARY t3 ref b b 5 test.t1.a 1 Using where; End temporary +1 PRIMARY t3 ref b b 5 test.t1.a 1 End temporary select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1
@@ -73,7 +73,7 @@
explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary -1 PRIMARY t3 ref b b 5 test.t0.a 1 Using where; End temporary +1 PRIMARY t3 ref b b 5 test.t0.a 1 End temporary select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); a b pk1 pk2 0 0 0 0
@@ -94,8 +94,8 @@
set max_heap_table_size= @save_max_heap_table_size; explain select * 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 3 -1 PRIMARY t2 ref b b 5 test.t1.a 2 Using where; Using index; FirstMatch(t1) +1 PRIMARY t2 index b b 5 NULL 10 Using index; LooseScan +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer select * from t1; a b 1 1
@@ -123,7 +123,7 @@
from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY it ALL NULL NULL NULL NULL 22 Start temporary -1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; End temporary +1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; End temporary; Using join buffer select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it);
@@ -155,7 +155,7 @@
from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 Start temporary -1 PRIMARY it ALL NULL NULL NULL NULL 32 Using where; End temporary +1 PRIMARY it ALL NULL NULL NULL NULL 32 Using where; End temporary; Using join buffer select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it);
@@ -189,7 +189,7 @@
from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY it ALL NULL NULL NULL NULL 22 Start temporary -1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; End temporary +1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; End temporary; Using join buffer select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it);
@@ -221,7 +221,7 @@
from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 Start temporary -1 PRIMARY it ALL NULL NULL NULL NULL 52 Using where; End temporary +1 PRIMARY it ALL NULL NULL NULL NULL 52 Using where; End temporary; Using join buffer select a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it);
@@ -261,7 +261,7 @@
(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary -1 PRIMARY t1 index NULL a 5 NULL 10 Using index +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary drop table t0, t1,t2,t3;
1.223/sql/sql_prepare.cc 2007-06-30 20:07:42 +04:00 +++ 1.224/sql/sql_prepare.cc 2007-06-30 20:07:42 +04:00
@@ -1281,6 +1281,7 @@
goto error;

   thd->used_tables= 0;                        // Updated by setup_fields

+ thd->thd_marker= 0;

/*
JOIN::prepare calls

-- 
MySQL Code Commits Mailing List
For list archives: 
http://lists.mysql.com/commits
To unsubscribe:    
http://lists.mysql.com/commits?unsub=lists@pantek.com

Received on Sat Jun 30 12:07:32 2007

Don't know where to look next?

This message: [ Message body ]
Next message: Sergey Petrunia: "bk commit into 5.2 tree (sergefp:1.2539)"
Previous message: Sergey Petrunia: "bk commit into 5.2 tree (sergefp:1.2537)"

Contemporary messages sorted: [ By Date ] [ By Thread ] [ By Subject ] [ By Author ] [ By messages with attachments ]

This archive was generated by hypermail 2.1.8 : Sat Jun 30 2007 - 12:10:03 EDT