summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect.result18
-rw-r--r--mysql-test/t/subselect.test16
-rw-r--r--sql/item.cc44
-rw-r--r--sql/item_subselect.cc48
-rw-r--r--sql/item_subselect.h15
5 files changed, 133 insertions, 8 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 6e35b6e78c1..e91dc6e2149 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1439,3 +1439,21 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
drop table if exists t2, t3;
+CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
+INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
+CREATE TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`),FULLTEXT KEY `name` (`name`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
+INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0);
+CREATE TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY (`taskgenid`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
+INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);
+CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') TYPE=MyISAM CHARSET=latin1;
+INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
+select dbid, name, (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01') from t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND t4.task_id = taskid;
+dbid name (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')
+-1 Valid 1
+-1 Valid 2 1
+-1 Should Not Return 0
+SELECT dbid, name FROM t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND ((date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')) AND t4.task_id = taskid;
+dbid name
+-1 Valid
+-1 Valid 2
+drop table t1,t2,t3,t4;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 9ba91c7e0a6..861b37ec53e 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -965,3 +965,19 @@ insert into t2 values (2,2), (2,1), (3,3), (3,1);
select * from t3 where a > all (select max(b) from t2 group by a);
explain select * from t3 where a > all (select max(b) from t2 group by a);
drop table if exists t2, t3;
+
+#
+# correct used_tables()
+#
+
+CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
+INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());
+CREATE TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`),FULLTEXT KEY `name` (`name`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
+INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0);
+CREATE TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY (`taskgenid`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
+INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);
+CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') TYPE=MyISAM CHARSET=latin1;
+INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');
+select dbid, name, (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01') from t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND t4.task_id = taskid;
+SELECT dbid, name FROM t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND ((date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')) AND t4.task_id = taskid;
+drop table t1,t2,t3,t4;
diff --git a/sql/item.cc b/sql/item.cc
index 4de4951cb51..970a92992ed 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -817,9 +817,11 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
// Prevent using outer fields in subselects, that is not supported now
SELECT_LEX *cursel=(SELECT_LEX *) thd->lex.current_select;
if (cursel->master_unit()->first_select()->linkage != DERIVED_TABLE_TYPE)
- for (SELECT_LEX *sl= cursel->outer_select();
+ {
+ SELECT_LEX_UNIT *prev_unit= cursel->master_unit();
+ for (SELECT_LEX *sl= prev_unit->outer_select();
sl;
- sl= sl->outer_select())
+ sl= (prev_unit= sl->master_unit())->outer_select())
{
table_list= (last= sl)->get_table_list();
if (sl->resolve_mode == SELECT_LEX::INSERT_MODE && table_list)
@@ -830,16 +832,33 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
if ((tmp= find_field_in_tables(thd, this,
table_list, &where,
0)) != not_found_field)
+ {
+ prev_unit->item->used_tables_cache|= tmp->table->map;
+ prev_unit->item->const_item_cache= 0;
break;
+ }
if (sl->resolve_mode == SELECT_LEX::SELECT_MODE &&
(refer= find_item_in_list(this, sl->item_list, &counter,
REPORT_EXCEPT_NOT_FOUND)) !=
(Item **) not_found_item)
+ {
+ if (*refer && (*refer)->fixed) // Avoid crash in case of error
+ {
+ prev_unit->item->used_tables_cache|= (*refer)->used_tables();
+ prev_unit->item->const_item_cache&= (*refer)->const_item();
+ }
break;
+ }
+
+ // Reference is not found => depend from outer (or just error)
+ prev_unit->item->used_tables_cache|= OUTER_REF_TABLE_BIT;
+ prev_unit->item->const_item_cache= 0;
+
if (sl->master_unit()->first_select()->linkage ==
DERIVED_TABLE_TYPE)
break; // do not look over derived table
}
+ }
if (!tmp)
return -1;
else if (!refer)
@@ -1350,7 +1369,8 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
if (!ref)
{
TABLE_LIST *where= 0, *table_list;
- SELECT_LEX *sl= thd->lex.current_select->outer_select();
+ SELECT_LEX_UNIT *prev_unit= thd->lex.current_select->master_unit();
+ SELECT_LEX *sl= prev_unit->outer_select();
/*
Finding only in current select will be performed for selects that have
not outer one and for derived tables (which not support using outer
@@ -1378,7 +1398,7 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
cause error ER_NON_UNIQ_ERROR in find_item_in_list.
*/
SELECT_LEX *last=0;
- for ( ; sl ; sl= sl->outer_select())
+ for ( ; sl ; sl= (prev_unit= sl->master_unit())->outer_select())
{
last= sl;
if (sl->resolve_mode == SELECT_LEX::SELECT_MODE &&
@@ -1386,7 +1406,14 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
&counter,
REPORT_EXCEPT_NOT_FOUND)) !=
(Item **)not_found_item)
+ {
+ if (*ref && (*ref)->fixed) // Avoid crash in case of error
+ {
+ prev_unit->item->used_tables_cache|= (*ref)->used_tables();
+ prev_unit->item->const_item_cache&= (*ref)->const_item();
+ }
break;
+ }
table_list= sl->get_table_list();
if (sl->resolve_mode == SELECT_LEX::INSERT_MODE && table_list)
{
@@ -1396,7 +1423,16 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
if ((tmp= find_field_in_tables(thd, this,
table_list, &where,
0)) != not_found_field)
+ {
+ prev_unit->item->used_tables_cache|= tmp->table->map;
+ prev_unit->item->const_item_cache= 0;
break;
+ }
+
+ // Reference is not found => depend from outer (or just error)
+ prev_unit->item->used_tables_cache|= OUTER_REF_TABLE_BIT;
+ prev_unit->item->const_item_cache= 0;
+
if (sl->master_unit()->first_select()->linkage ==
DERIVED_TABLE_TYPE)
break; // do not look over derived table
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 5e0221ad6c7..4ad49ebec74 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -36,7 +36,8 @@ inline Item * and_items(Item* cond, Item *item)
Item_subselect::Item_subselect():
Item_result_field(), engine_owner(1), value_assigned(0), substitution(0),
- engine(0), have_to_be_excluded(0), engine_changed(0)
+ engine(0), used_tables_cache(0), have_to_be_excluded(0),
+ const_item_cache(1), engine_changed(0)
{
reset();
/*
@@ -146,12 +147,27 @@ void Item_subselect::fix_length_and_dec()
engine->fix_length_and_dec(0);
}
-inline table_map Item_subselect::used_tables() const
+table_map Item_subselect::used_tables() const
{
- return (table_map) (engine->dependent() ? 1L :
+ return (table_map) (engine->dependent() ? used_tables_cache :
(engine->uncacheable() ? RAND_TABLE_BIT : 0L));
}
+bool Item_subselect::const_item() const
+{
+ return engine->uncacheable()? 0 : const_item_cache;
+}
+
+void Item_subselect::update_used_tables()
+{
+ if (!engine->uncacheable())
+ {
+ // did all used tables become ststic?
+ if ((used_tables_cache & ~engine->upper_select_const_tables()) == 0)
+ const_item_cache= 1;
+ }
+}
+
Item_singlerow_subselect::Item_singlerow_subselect(st_select_lex *select_lex)
:Item_subselect(), value(0)
{
@@ -1138,3 +1154,29 @@ void subselect_uniquesubquery_engine::exclude()
//this never should be called
DBUG_ASSERT(0);
}
+
+
+table_map subselect_engine::calc_const_tables(TABLE_LIST *table)
+{
+ table_map map= 0;
+ for(; table; table= table->next)
+ {
+ TABLE *tbl= table->table;
+ if (tbl && tbl->const_table)
+ map|= tbl->map;
+ }
+ return map;
+}
+
+
+table_map subselect_single_select_engine::upper_select_const_tables()
+{
+ return calc_const_tables((TABLE_LIST *) select_lex->outer_select()->
+ table_list.first);
+}
+
+table_map subselect_union_engine::upper_select_const_tables()
+{
+ return calc_const_tables((TABLE_LIST *) unit->outer_select()->
+ table_list.first);
+}
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 312b453a5a2..3a543ff288c 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -41,11 +41,15 @@ protected:
/* substitution instead of subselect in case of optimization */
Item *substitution;
/* engine that perform execution of subselect (single select or union) */
- subselect_engine *engine;
+ subselect_engine *engine;
+ /* cache of used external tables */
+ table_map used_tables_cache;
/* allowed number of columns (1 for single value subqueries) */
uint max_columns;
/* work with 'substitution' */
bool have_to_be_excluded;
+ /* cache of constante state */
+ bool const_item_cache;
public:
/* changed engine indicator */
@@ -85,6 +89,8 @@ public:
bool exec();
virtual void fix_length_and_dec();
table_map used_tables() const;
+ bool const_item() const;
+ void update_used_tables();
void print(String *str)
{
if (name)
@@ -101,6 +107,8 @@ public:
friend class select_subselect;
friend class Item_in_optimizer;
+ friend bool Item_field::fix_fields(THD *, TABLE_LIST *, Item **);
+ friend bool Item_ref::fix_fields(THD *, TABLE_LIST *, Item **);
};
/* single value subselect */
@@ -264,6 +272,8 @@ public:
enum Item_result type() { return res_type; }
virtual void exclude()= 0;
bool may_be_null() { return maybe_null; };
+ virtual table_map upper_select_const_tables()= 0;
+ static table_map calc_const_tables(TABLE_LIST *);
};
@@ -285,6 +295,7 @@ public:
bool dependent();
bool uncacheable();
void exclude();
+ table_map upper_select_const_tables();
};
@@ -302,6 +313,7 @@ public:
bool dependent();
bool uncacheable();
void exclude();
+ table_map upper_select_const_tables();
};
@@ -328,6 +340,7 @@ public:
bool dependent() { return 1; }
bool uncacheable() { return 1; }
void exclude();
+ table_map upper_select_const_tables() { return 0; }
};
View Lorry Controller Status