Below is the list of changes that have just been committed into a local
5.2 repository of igor. When igor 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@stripped, 2007-09-15 14:36:00-07:00, igor@stripped +3 -0
Fixed bug #31040. This bug occurred for queries whose WHERE/ON conditions
contained several predicates with IN subqueries that were flattened.
mysql-test/r/subselect_sj2.result@stripped, 2007-09-15 14:35:57-07:00, igor@stripped +38 -0
Added a test case for bug #31040.
mysql-test/t/subselect_sj2.test@stripped, 2007-09-15 14:35:57-07:00, igor@stripped +90 -0
Added a test case for bug #31040.
sql/sql_select.cc@stripped, 2007-09-15 14:35:58-07:00, igor@stripped +16 -9
Fixed bug #31040. This bug occurred for queries whose WHERE/ON conditions
contained several predicates with IN subqueries that were flattened.
When such a subquery is flattened first it's pulled in a newly build
semijoin operation with ON condition formed by the WHERE condition of
the subquery and equality predicate with the left operand set as the left
argument of the subquery predicate and the right operand yielded by
the select list of the subquery. At this moment the subquery is substituted
for an item representing TRUE. After this, for a regular join query, the ON
condition of the semijoin operation is injected into the WHERE condition
of the query. During this injection the new WHERE condition is flattened
that results in a new AND list of the WHERE condition in which the references to
other subqueries differ from the original ones. So when replacing these
conditions for items representing TRUE we can't use the original references to
the subqueries. The existing code did not take into account this fact. This
ultimately could cause an assertion abort.
Now replacement of all flattened subqueries is performed before injections
into the WHERE clause of the upper query.
diff -Nrup a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
--- a/mysql-test/r/subselect_sj2.result 2007-06-30 09:07:27 -07:00
+++ b/mysql-test/r/subselect_sj2.result 2007-09-15 14:35:57 -07:00
@@ -265,3 +265,41 @@ id select_type table type possible_keys
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;
+CREATE TABLE t1 (
+ID int(11) NOT NULL auto_increment,
+Name char(35) NOT NULL default '',
+Country char(3) NOT NULL default '',
+Population int(11) NOT NULL default '0',
+PRIMARY KEY (ID),
+INDEX (Population),
+INDEX (Country)
+);
+CREATE TABLE t2 (
+Code char(3) NOT NULL default '',
+Name char(52) NOT NULL default '',
+SurfaceArea float(10,2) NOT NULL default '0.00',
+Population int(11) NOT NULL default '0',
+Capital int(11) default NULL,
+PRIMARY KEY (Code),
+UNIQUE INDEX (Name),
+INDEX (Population)
+);
+CREATE TABLE t3 (
+Country char(3) NOT NULL default '',
+Language char(30) NOT NULL default '',
+Percentage float(3,1) NOT NULL default '0.0',
+PRIMARY KEY (Country, Language),
+INDEX (Percentage)
+);
+EXPLAIN
+SELECT Name FROM t2
+WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000)
+AND
+t2.Code IN (SELECT Country FROM t3
+WHERE Language='English' AND Percentage > 10 AND
+t2.Population > 100000);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL Population,Country NULL NULL NULL 30 Using where; Start temporary
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where
+1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t3.Country 1 Using index condition; Using where; End temporary
+DROP TABLE t1,t2,t3;
diff -Nrup a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
--- a/mysql-test/t/subselect_sj2.test 2007-06-20 11:59:33 -07:00
+++ b/mysql-test/t/subselect_sj2.test 2007-09-15 14:35:57 -07:00
@@ -144,4 +144,94 @@ from t0 where a in
drop table t0, t1,t2,t3;
+#
+# Bug #27348: Assertion abort for a query with two subqueries to be flattened
+#
+CREATE TABLE t1 (
+ ID int(11) NOT NULL auto_increment,
+ Name char(35) NOT NULL default '',
+ Country char(3) NOT NULL default '',
+ Population int(11) NOT NULL default '0',
+ PRIMARY KEY (ID),
+ INDEX (Population),
+ INDEX (Country)
+);
+CREATE TABLE t2 (
+ Code char(3) NOT NULL default '',
+ Name char(52) NOT NULL default '',
+ SurfaceArea float(10,2) NOT NULL default '0.00',
+ Population int(11) NOT NULL default '0',
+ Capital int(11) default NULL,
+ PRIMARY KEY (Code),
+ UNIQUE INDEX (Name),
+ INDEX (Population)
+);
+CREATE TABLE t3 (
+ Country char(3) NOT NULL default '',
+ Language char(30) NOT NULL default '',
+ Percentage float(3,1) NOT NULL default '0.0',
+ PRIMARY KEY (Country, Language),
+ INDEX (Percentage)
+);
+
+--disable_query_log
+INSERT INTO t1 VALUES
+(1,'Kabul','AFG',1780000),(2,'Qandahar','AFG',237500),
+(3,'Herat','AFG',186800),(4,'Mazar-e-Sharif','AFG',127800),
+(5,'Amsterdam','NLD',731200),(6,'Rotterdam','NLD',593321),
+(7,'Haag','NLD',440900),(8,'Utrecht','NLD',234323),
+(9,'Eindhoven','NLD',201843),(10,'Tilburg','NLD',193238),
+(11,'Groningen','NLD',172701),(12,'Breda','NLD',160398),
+(13,'Apeldoorn','NLD',153491),(14,'Nijmegen','NLD',152463),
+(15,'Enschede','NLD',149544),(16,'Haarlem','NLD',148772),
+(17,'Almere','NLD',142465),(18,'Arnhem','NLD',138020),
+(19,'Zaanstad','NLD',135621),(20,'´s-Hertogenbosch','NLD',129170),
+(21,'Amersfoort','NLD',126270),(22,'Maastricht','NLD',122087),
+(23,'Dordrecht','NLD',119811),(24,'Leiden','NLD',117196),
+(25,'Haarlemmermeer','NLD',110722),(26,'Zoetermeer','NLD',110214),
+(27,'Emmen','NLD',105853),(28,'Zwolle','NLD',105819),
+(29,'Ede','NLD',101574),(30,'Delft','NLD',95268);
+
+INSERT INTO t2 VALUES
+('AFG','Afghanistan',652090.00,22720000,1),
+('NLD','Netherlands',41526.00,15864000,5),
+('ANT','Netherlands Antilles',800.00,217000,33),
+('ALB','Albania',28748.00,3401200,34),
+('DZA','Algeria',2381741.00,31471000,35),
+('ASM','American Samoa',199.00,68000,54),
+('AND','Andorra',468.00,78000,55),
+('AGO','Angola',1246700.00,12878000,56),
+('AIA','Anguilla',96.00,8000,62),
+('ATG','Antigua and Barbuda',442.00,68000,63),
+('ARE','United Arab Emirates',83600.00,2441000,65),
+('ARG','Argentina',2780400.00,37032000,69),
+('ARM','Armenia',29800.00,3520000,126),
+('ABW','Aruba',193.00,103000,129),
+('AUS','Australia',7741220.00,18886000,135),
+('AZE','Azerbaijan',86600.00,7734000,144);
+
+INSERT INTO t3 VALUES
+('AFG','Pashto',52.4),('NLD','Dutch',95.6),
+('ANT','Papiamento',86.2),('ALB','Albaniana',97.9),
+('DZA','Arabic',86.0),('ASM','Samoan',90.6),
+('AND','Spanish',44.6),('AGO','Ovimbundu',37.2),
+('AIA','English',0.0),('ATG','Creole English',95.7),
+('ARE','Arabic',42.0),('ARG','Spanish',96.8),
+('ARM','Armenian',93.4),('ABW','Papiamento',76.7),
+('AUS','English',81.2),('AZE','Azerbaijani',89.0),
+('BHS','Creole English',89.7),('BHR','Arabic',67.7),
+('BGD','Bengali',97.7),('BRB','Bajan',95.1),
+('BEL','Dutch',59.2),('BLZ','English',50.8);
+--enable_query_log
+
+EXPLAIN
+SELECT Name FROM t2
+ WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000)
+ AND
+ t2.Code IN (SELECT Country FROM t3
+ WHERE Language='English' AND Percentage > 10 AND
+ t2.Population > 100000);
+
+DROP TABLE t1,t2,t3;
+
\ No newline at end of file
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2007-09-14 22:51:38 -07:00
+++ b/sql/sql_select.cc 2007-09-15 14:35:58 -07:00
@@ -2985,10 +2985,8 @@ bool convert_subq_to_sj(JOIN *parent_joi
SELECT_LEX *parent_lex= parent_join->select_lex;
TABLE_LIST *emb_tbl_nest= NULL;
List<TABLE_LIST> *emb_join_list= &parent_lex->top_join_list;
- Query_arena *arena, backup;
THD *thd= parent_join->thd;
DBUG_ENTER("convert_subq_to_sj");
- arena= thd->activate_stmt_arena_if_needed(&backup);
/*
1. Find out where to put the predicate into.
@@ -3145,7 +3143,8 @@ bool convert_subq_to_sj(JOIN *parent_joi
/* A theory: no need to re-connect the next_global chain */
/* 3. Remove the original subquery predicate from the WHERE/ON */
- *(subq_pred->ref_ptr)= new Item_int(1);
+
+ // The subqueries were replaced for Item_int(1) earlier
subq_pred->exec_method= Item_in_subselect::SEMI_JOIN; // for subsequent executions
/*TODO: also reset the 'with_subselect' there. */
@@ -3270,8 +3269,6 @@ bool convert_subq_to_sj(JOIN *parent_joi
parent_lex->ftfunc_list->push_front(ifm);
}
- if (arena)
- thd->restore_active_arena(arena, &backup);
DBUG_RETURN(FALSE);
}
@@ -3292,9 +3289,10 @@ bool convert_subq_to_sj(JOIN *parent_joi
bool JOIN::flatten_subqueries()
{
- DBUG_ENTER("JOIN::flatten_subqueries");
+ Query_arena *arena, backup;
Item_in_subselect **in_subq;
Item_in_subselect **in_subq_end;
+ DBUG_ENTER("JOIN::flatten_subqueries");
if (sj_subselects.elements() == 0)
DBUG_RETURN(FALSE);
@@ -3320,17 +3318,26 @@ bool JOIN::flatten_subqueries()
*/
sj_subselects.sort(subq_sj_candidate_cmp);
// #tables-in-parent-query + #tables-in-subquery < MAX_TABLES
- bool do_converts= TRUE;
+ /* Replace all subqueries to be flattened for Item_int(1) */
+ arena= thd->activate_stmt_arena_if_needed(&backup);
+ for (in_subq= sj_subselects.front();
+ in_subq != in_subq_end &&
+ tables + ((*in_subq)->sj_convert_priority % MAX_TABLES) < MAX_TABLES;
+ in_subq++)
+ {
+ *((*in_subq)->ref_ptr)= new Item_int(1);
+ }
+
for (in_subq= sj_subselects.front();
in_subq != in_subq_end &&
tables + ((*in_subq)->sj_convert_priority % MAX_TABLES) < MAX_TABLES;
in_subq++)
{
- if (!do_converts)
- break;
if (convert_subq_to_sj(this, *in_subq))
DBUG_RETURN(TRUE);
}
+ if (arena)
+ thd->restore_active_arena(arena, &backup);
/* 3. Finalize those we didn't convert */
for (; in_subq!= in_subq_end; in_subq++)
| Thread |
|---|
| • bk commit into 5.2 tree (igor:1.2600) BUG#31040 | igor | 15 Sep |
