Below is the list of changes that have just been committed into a local
5.0 repository of sanja. When sanja 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.1988 05/08/13 08:19:34 sanja@stripped +7 -0
Merge abelkin@stripped:/home/bk/mysql-4.1
into hasky.mysql.fi:/home/sanja/work-merge-5.0
sql/item_subselect.cc
1.116 05/08/13 08:19:23 sanja@stripped +3 -4
merge
sql/sql_select.cc
1.357 05/08/13 08:12:57 sanja@stripped +0 -0
Auto merged
sql/sql_parse.cc
1.468 05/08/13 08:12:56 sanja@stripped +0 -0
Auto merged
sql/item_cmpfunc.h
1.106 05/08/13 08:12:56 sanja@stripped +0 -0
Auto merged
sql/item.h
1.160 05/08/13 08:12:56 sanja@stripped +0 -0
Auto merged
mysql-test/t/subselect.test
1.107 05/08/13 08:12:56 sanja@stripped +0 -0
Auto merged
mysql-test/r/subselect.result
1.124 05/08/13 08:12:56 sanja@stripped +0 -0
Auto merged
# 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: sanja
# Host: hasky.mysql.fi
# Root: /home/sanja/work-merge-5.0/RESYNC
--- 1.159/sql/item.h 2005-08-12 17:57:14 +03:00
+++ 1.160/sql/item.h 2005-08-13 08:12:56 +03:00
@@ -1587,6 +1587,15 @@
bool val_bool();
bool get_date(TIME *ltime, uint fuzzydate);
void print(String *str);
+ /*
+ we add RAND_TABLE_BIT to prevent moving this item from HAVING to WHERE
+ */
+ table_map used_tables() const
+ {
+ return (depended_from ?
+ OUTER_REF_TABLE_BIT :
+ (*ref)->used_tables() | RAND_TABLE_BIT);
+ }
};
class Item_null_helper :public Item_ref_null_helper
--- 1.105/sql/item_cmpfunc.h 2005-07-04 16:00:55 +03:00
+++ 1.106/sql/item_cmpfunc.h 2005-08-13 08:12:56 +03:00
@@ -913,6 +913,11 @@
longlong val_int();
const char *func_name() const { return "<is_not_null_test>"; }
void update_used_tables();
+ /*
+ we add RAND_TABLE_BIT to prevent moving this item from HAVING to WHERE
+ */
+ table_map used_tables() const
+ { return used_tables_cache | RAND_TABLE_BIT; }
};
--- 1.467/sql/sql_parse.cc 2005-08-12 18:04:49 +03:00
+++ 1.468/sql/sql_parse.cc 2005-08-13 08:12:56 +03:00
@@ -2316,11 +2316,16 @@
/*
Skip if we are in the slave thread, some table rules have been
given and the table list says the query should not be replicated.
- Exception is DROP TEMPORARY TABLE IF EXISTS: we always execute it
- (otherwise we have stale files on slave caused by exclusion of one tmp
- table).
+
+ Exceptions are:
+
+ - SET: we always execute it (e.g., SET ONE_SHOT TIME_ZONE = 'XYZ')
+
+ - DROP TEMPORARY TABLE IF EXISTS: we always execute it (otherwise we
+ have stale files on slave caused by exclusion of one tmp table).
*/
- if (!(lex->sql_command == SQLCOM_DROP_TABLE &&
+ if (lex->sql_command != SQLCOM_SET_OPTION &&
+ !(lex->sql_command == SQLCOM_DROP_TABLE &&
lex->drop_temporary && lex->drop_if_exists) &&
all_tables_not_ok(thd, all_tables))
{
--- 1.356/sql/sql_select.cc 2005-08-12 18:04:49 +03:00
+++ 1.357/sql/sql_select.cc 2005-08-13 08:12:57 +03:00
@@ -1576,7 +1576,8 @@
curr_join->tmp_having= make_cond_for_table(curr_join->tmp_having,
~ (table_map) 0,
~used_tables);
- DBUG_EXECUTE("where",print_where(conds,"having after sort"););
+ DBUG_EXECUTE("where",print_where(curr_join->tmp_having,
+ "having after sort"););
}
}
{
--- 1.123/mysql-test/r/subselect.result 2005-08-12 18:04:47 +03:00
+++ 1.124/mysql-test/r/subselect.result 2005-08-13 08:12:56 +03:00
@@ -2754,13 +2754,26 @@
5 6 N
7 8 N
insert into t2 values (null,null,'N');
+insert into t2 values (null,3,'0');
+insert into t2 values (null,5,'0');
+insert into t2 values (10,null,'0');
+insert into t1 values (10,3,'0');
+insert into t1 values (10,5,'0');
+insert into t1 values (10,10,'0');
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
one two test
-1 2 0
-2 3 0
-3 4 0
+1 2 NULL
+2 3 NULL
+3 4 NULL
5 6 1
7 8 1
+10 3 NULL
+10 5 NULL
+10 10 NULL
+SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
+one two
+5 6
+7 8
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
one two test
1 2 NULL
@@ -2768,6 +2781,47 @@
3 4 NULL
5 6 1
7 8 1
+10 3 NULL
+10 5 NULL
+10 10 NULL
+SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
+one two test
+1 2 0
+2 3 NULL
+3 4 0
+5 6 0
+7 8 0
+10 3 NULL
+10 5 NULL
+10 10 NULL
+SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
+one two test
+1 2 0
+2 3 NULL
+3 4 0
+5 6 0
+7 8 0
+10 3 NULL
+10 5 NULL
+10 10 NULL
+explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where
+Warnings:
+Note 1003 select test.t1.one AS `one`,test.t1.two AS `two`,<in_optimizer>((test.t1.one,test.t1.two),<exists>(select test.t2.one AS `one`,test.t2.two AS `two` from test.t2 where ((test.t2.flag = _latin1'0') and ((<cache>(test.t1.one) = test.t2.one) or isnull(test.t2.one)) and ((<cache>(test.t1.two) = test.t2.two) or isnull(test.t2.two))) having (<is_not_null_test>(test.t2.one) and <is_not_null_test>(test.t2.two)))) AS `test` from test.t1
+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 Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where
+Warnings:
+Note 1003 select test.t1.one AS `one`,test.t1.two AS `two` from test.t1 where <in_optimizer>((test.t1.one,test.t1.two),<exists>(select test.t2.one AS `one`,test.t2.two AS `two` from test.t2 where ((test.t2.flag = _latin1'N') and (<cache>(test.t1.one) = test.t2.one) and (<cache>(test.t1.two) = test.t2.two))))
+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;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort
+Warnings:
+Note 1003 select test.t1.one AS `one`,test.t1.two AS `two`,<in_optimizer>((test.t1.one,test.t1.two),<exists>(select test.t2.one AS `one`,test.t2.two AS `two` from test.t2 where (test.t2.flag = _latin1'0') group by test.t2.one,test.t2.two having (((<cache>(test.t1.one) = test.t2.one) or isnull(test.t2.one)) and ((<cache>(test.t1.two) = test.t2.two) or isnull(test.t2.two)) and <is_not_null_test>(test.t2.one) and <is_not_null_test>(test.t2.two)))) AS `test` from test.t1
DROP TABLE t1,t2;
CREATE TABLE t1 (a char(5), b char(5));
INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
--- 1.106/mysql-test/t/subselect.test 2005-08-12 18:04:48 +03:00
+++ 1.107/mysql-test/t/subselect.test 2005-08-13 08:12:56 +03:00
@@ -1774,9 +1774,20 @@
WHERE ROW(one,two) IN (SELECT DISTINCT one,two FROM t1 WHERE flag = 'N');
insert into t2 values (null,null,'N');
+insert into t2 values (null,3,'0');
+insert into t2 values (null,5,'0');
+insert into t2 values (10,null,'0');
+insert into t1 values (10,3,'0');
+insert into t1 values (10,5,'0');
+insert into t1 values (10,10,'0');
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N') as 'test' from t1;
+SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N' group by one,two) as 'test' from t1;
-
+SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
+SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
+explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0') as 'test' from t1;
+explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '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;
DROP TABLE t1,t2;
#
--- 1.115/sql/item_subselect.cc 2005-08-11 09:29:51 +03:00
+++ 1.116/sql/item_subselect.cc 2005-08-13 08:19:23 +03:00
@@ -969,6 +969,7 @@
argument (reference) to fix_fields()
*/
select_lex->where= join->conds= and_items(join->conds, item);
+ select_lex->where->top_level_item();
/*
we do not check join->conds->fixed, because Item_and can't be fixed
after creation
@@ -1032,8 +1033,12 @@
Item_subselect::trans_res
Item_in_subselect::row_value_transformer(JOIN *join)
{
- Item *item= 0;
SELECT_LEX *select_lex= join->select_lex;
+ Item *having_item= 0;
+ uint cols_num= left_expr->cols();
+ bool is_having_used= (join->having || select_lex->with_sum_func ||
+ select_lex->group_list.first ||
+ !select_lex->table_list.elements);
DBUG_ENTER("Item_in_subselect::row_value_transformer");
if (select_lex->item_list.elements != left_expr->cols())
@@ -1065,71 +1070,156 @@
}
select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
+ if (is_having_used)
{
- uint n= left_expr->cols();
- List_iterator_fast<Item> li(select_lex->item_list);
- for (uint i= 0; i < n; i++)
+ /*
+ (l1, l2, l3) IN (SELECT v1, v2, v3 ... HAVING having) =>
+ EXISTS (SELECT ... HAVING having and
+ (l1 = v1 or is null v1) and
+ (l2 = v2 or is null v2) and
+ (l3 = v3 or is null v3) and
+ is_not_null_test(v1) and
+ is_not_null_test(v2) and
+ is_not_null_test(v3))
+ where is_not_null_test used to register nulls in case if we have
+ not found matching to return correct NULL value
+ */
+ Item *item_having_part2= 0;
+ for (uint i= 0; i < cols_num; i++)
{
- Item *func;
DBUG_ASSERT(left_expr->fixed && select_lex->ref_pointer_array[i]->fixed);
if (select_lex->ref_pointer_array[i]->
check_cols(left_expr->el(i)->cols()))
DBUG_RETURN(RES_ERROR);
- if (join->having || select_lex->with_sum_func ||
- select_lex->group_list.elements)
- func= new Item_ref_null_helper(&select_lex->context,
- this,
- select_lex->ref_pointer_array+i,
- (char *) "<no matter>",
- (char *) "<list ref>");
- else
- func= li++;
- func=
- eq_creator.create(new Item_direct_ref(&select_lex->context,
- (*optimizer->get_cache())->
- addr(i),
- (char *)"<no matter>",
- (char *)in_left_expr_name),
- func);
- item= and_items(item, func);
+ Item *item_eq=
+ new Item_func_eq(new
+ Item_direct_ref((*optimizer->get_cache())->
+ addr(i),
+ (char *)"<no matter>",
+ (char *)in_left_expr_name),
+ new
+ Item_direct_ref(select_lex->ref_pointer_array + i,
+ (char *)"<no matter>",
+ (char *)"<list ref>")
+ );
+ Item *item_isnull=
+ new Item_func_isnull(new
+ Item_direct_ref( select_lex->
+ ref_pointer_array+i,
+ (char *)"<no matter>",
+ (char *)"<list ref>")
+ );
+ having_item=
+ and_items(having_item,
+ new Item_cond_or(item_eq, item_isnull));
+ item_having_part2=
+ and_items(item_having_part2,
+ new
+ Item_is_not_null_test(this,
+ new
+ Item_direct_ref(select_lex->
+ ref_pointer_array + i,
+ (char *)"<no matter>",
+ (char *)"<list ref>")
+ )
+ );
+ item_having_part2->top_level_item();
}
+ having_item= and_items(having_item, item_having_part2);
+ having_item->top_level_item();
}
- if (join->having || select_lex->with_sum_func ||
- select_lex->group_list.first ||
- !select_lex->table_list.elements)
+ else
{
/*
- AND can't be changed during fix_fields()
- we can assign select_lex->having here, and pass 0 as last
- argument (reference) to fix_fields()
+ (l1, l2, l3) IN (SELECT v1, v2, v3 ... WHERE where) =>
+ EXISTS (SELECT ... WHERE where and
+ (l1 = v1 or is null v1) and
+ (l2 = v2 or is null v2) and
+ (l3 = v3 or is null v3)
+ HAVING is_not_null_test(v1) and
+ is_not_null_test(v2) and
+ is_not_null_test(v3))
+ where is_not_null_test register NULLs values but reject rows
+
+ in case when we do not need correct NULL, we have simplier construction:
+ EXISTS (SELECT ... WHERE where and
+ (l1 = v1) and
+ (l2 = v2) and
+ (l3 = v3)
*/
- select_lex->having= join->having= and_items(join->having, item);
- select_lex->having_fix_field= 1;
+ Item *where_item= 0;
+ for (uint i= 0; i < cols_num; i++)
+ {
+ Item *item, *item_isnull;
+ DBUG_ASSERT(left_expr->fixed && select_lex->ref_pointer_array[i]->fixed);
+ if (select_lex->ref_pointer_array[i]->
+ check_cols(left_expr->el(i)->cols()))
+ DBUG_RETURN(RES_ERROR);
+ item=
+ new Item_func_eq(new
+ Item_direct_ref((*optimizer->get_cache())->
+ addr(i),
+ (char *)"<no matter>",
+ (char *)in_left_expr_name),
+ new
+ Item_direct_ref( select_lex->
+ ref_pointer_array+i,
+ (char *)"<no matter>",
+ (char *)"<list ref>")
+ );
+ if (!abort_on_null)
+ {
+ having_item=
+ and_items(having_item,
+ new
+ Item_is_not_null_test(this,
+ new
+ Item_direct_ref(select_lex->
+ ref_pointer_array + i,
+ (char *)"<no matter>",
+ (char *)"<list ref>")
+ )
+ );
+ item_isnull= new
+ Item_func_isnull(new
+ Item_direct_ref( select_lex->
+ ref_pointer_array+i,
+ (char *)"<no matter>",
+ (char *)"<list ref>")
+ );
+
+ item= new Item_cond_or(item, item_isnull);
+ }
+
+ where_item= and_items(where_item, item);
+ }
/*
- join->having can't be fixed after creation, so we do not check
- join->having->fixed
+ AND can't be changed during fix_fields()
+ we can assign select_lex->where here, and pass 0 as last
+ argument (reference) to fix_fields()
*/
- if (join->having->fix_fields(thd, 0))
- {
- select_lex->having_fix_field= 0;
+ select_lex->where= join->conds= and_items(join->conds, where_item);
+ select_lex->where->top_level_item();
+ if (join->conds->fix_fields(thd, 0))
DBUG_RETURN(RES_ERROR);
- }
- select_lex->having_fix_field= 0;
}
- else
+ if (having_item)
{
+ bool res;
+ select_lex->having= join->having= and_items(join->having, having_item);
+ select_lex->having->top_level_item();
/*
AND can't be changed during fix_fields()
we can assign select_lex->having here, and pass 0 as last
argument (reference) to fix_fields()
*/
- select_lex->where= join->conds= and_items(join->conds, item);
- /*
- join->conds can't be fixed after creation, so we do not check
- join->conds->fixed
- */
- if (join->conds->fix_fields(thd, 0))
+ select_lex->having_fix_field= 1;
+ res= join->having->fix_fields(thd, 0);
+ select_lex->having_fix_field= 0;
+ if (res)
+ {
DBUG_RETURN(RES_ERROR);
+ }
}
DBUG_RETURN(RES_OK);
| Thread |
|---|
| • bk commit into 5.0 tree (sanja:1.1988) | sanja | 13 Aug |
