3579 Sergey Glukhov 2011-08-02
Bug#11766594 59736: SELECT DISTINCT.. INCORRECT RESULT WITH DETERMINISTIC FUNCTION IN WHERE C
There is an optimization of DISTINCT in JOIN::optimize()
which depends on THD::used_tables value. Each SELECT statement
inside SP resets used_tables value(see mysql_select()) and it
leads to wrong result. The fix is to replace THD::used_tables
with LEX::used_tables.
@ mysql-test/r/sp.result
test case
@ mysql-test/t/sp.test
test case
@ sql/sql_base.cc
THD::used_tables is replaced with LEX::used_tables
@ sql/sql_class.cc
THD::used_tables is replaced with LEX::used_tables
@ sql/sql_class.h
THD::used_tables is replaced with LEX::used_tables
@ sql/sql_insert.cc
THD::used_tables is replaced with LEX::used_tables
@ sql/sql_lex.cc
THD::used_tables is replaced with LEX::used_tables
@ sql/sql_lex.h
THD::used_tables is replaced with LEX::used_tables
@ sql/sql_prepare.cc
THD::used_tables is replaced with LEX::used_tables
@ sql/sql_select.cc
THD::used_tables is replaced with LEX::used_tables
modified:
mysql-test/r/sp.result
mysql-test/t/sp.test
sql/sql_base.cc
sql/sql_class.cc
sql/sql_class.h
sql/sql_insert.cc
sql/sql_lex.cc
sql/sql_lex.h
sql/sql_prepare.cc
sql/sql_select.cc
3578 Sven Sandberg 2011-07-27
Updated default.experimental; now rpl tests are up to date as of 2011-07-25.
modified:
mysql-test/collections/default.experimental
=== modified file 'mysql-test/r/sp.result'
--- a/mysql-test/r/sp.result 2010-02-09 10:30:50 +0000
+++ b/mysql-test/r/sp.result 2011-08-02 07:33:45 +0000
@@ -7053,6 +7053,25 @@ init_connect
SET @@GLOBAL.init_connect= @old_init_connect;
DROP PROCEDURE p2;
DROP PROCEDURE p5;
+#
+# Bug#11766594 59736: SELECT DISTINCT.. INCORRECT RESULT WITH DETERMINISTIC FUNCTION IN WHERE C
+#
+CREATE TABLE t1 (a INT, b INT, KEY(b));
+CREATE TABLE t2 (c INT, d INT, KEY(c));
+INSERT INTO t1 VALUES (1,1),(1,1),(1,2);
+INSERT INTO t2 VALUES (1,1),(1,2);
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+DECLARE a int;
+-- SQL statement inside
+SELECT 1 INTO a;
+RETURN a;
+END $
+SELECT COUNT(DISTINCT d) FROM t1, t2 WHERE a = c AND b = f1();
+COUNT(DISTINCT d)
+2
+DROP FUNCTION f1;
+DROP TABLE t1, t2;
# ------------------------------------------------------------------
# -- End of 5.1 tests
# ------------------------------------------------------------------
=== modified file 'mysql-test/t/sp.test'
--- a/mysql-test/t/sp.test 2009-12-23 13:44:03 +0000
+++ b/mysql-test/t/sp.test 2011-08-02 07:33:45 +0000
@@ -8350,6 +8350,33 @@ SET @@GLOBAL.init_connect= @old_init_con
DROP PROCEDURE p2;
DROP PROCEDURE p5;
+--echo #
+--echo # Bug#11766594 59736: SELECT DISTINCT.. INCORRECT RESULT WITH DETERMINISTIC FUNCTION IN WHERE C
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, KEY(b));
+CREATE TABLE t2 (c INT, d INT, KEY(c));
+INSERT INTO t1 VALUES (1,1),(1,1),(1,2);
+INSERT INTO t2 VALUES (1,1),(1,2);
+
+DELIMITER $;
+
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+ DECLARE a int;
+ -- SQL statement inside
+ SELECT 1 INTO a;
+ RETURN a;
+END $
+
+DELIMITER ;$
+
+SELECT COUNT(DISTINCT d) FROM t1, t2 WHERE a = c AND b = f1();
+
+DROP FUNCTION f1;
+DROP TABLE t1, t2;
+
+
--echo # ------------------------------------------------------------------
--echo # -- End of 5.1 tests
--echo # ------------------------------------------------------------------
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2011-07-03 15:47:37 +0000
+++ b/sql/sql_base.cc 2011-08-02 07:33:45 +0000
@@ -7576,7 +7576,7 @@ bool setup_fields(THD *thd, Item **ref_p
if (item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM &&
sum_func_list)
item->split_sum_func(thd, ref_pointer_array, *sum_func_list);
- thd->used_tables|= item->used_tables();
+ thd->lex->used_tables|= item->used_tables();
thd->lex->current_select->cur_pos_in_select_list++;
}
thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup;
@@ -7923,7 +7923,7 @@ insert_fields(THD *thd, Name_resolution_
views and natural joins this update is performed inside the loop below.
*/
if (table)
- thd->used_tables|= table->map;
+ thd->lex->used_tables|= table->map;
/*
Initialize a generic field iterator for the current table reference.
@@ -8008,7 +8008,7 @@ insert_fields(THD *thd, Name_resolution_
field_table= nj_col->table_ref->table;
if (field_table)
{
- thd->used_tables|= field_table->map;
+ thd->lex->used_tables|= field_table->map;
field_table->covering_keys.intersect(field->part_of_key);
field_table->merge_keys.merge(field->part_of_key);
field_table->used_fields++;
@@ -8016,7 +8016,7 @@ insert_fields(THD *thd, Name_resolution_
}
}
else
- thd->used_tables|= item->used_tables();
+ thd->lex->used_tables|= item->used_tables();
thd->lex->current_select->cur_pos_in_select_list++;
}
/*
=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc 2011-07-07 08:06:59 +0000
+++ b/sql/sql_class.cc 2011-08-02 07:33:45 +0000
@@ -649,7 +649,6 @@ THD::THD()
is_slave_error= thread_specific_used= FALSE;
hash_clear(&handler_tables_hash);
tmp_table=0;
- used_tables=0;
cuted_fields= sent_row_count= row_count= 0L;
limit_found_rows= 0;
row_count_func= -1;
=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h 2011-07-03 15:47:37 +0000
+++ b/sql/sql_class.h 2011-08-02 07:33:45 +0000
@@ -1734,13 +1734,6 @@ public:
*/
ha_rows examined_row_count;
- /*
- The set of those tables whose fields are referenced in all subqueries
- of the query.
- TODO: possibly this it is incorrect to have used tables in THD because
- with more than one subquery, it is not clear what does the field mean.
- */
- table_map used_tables;
USER_CONN *user_connect;
CHARSET_INFO *db_charset;
/*
=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc 2011-07-03 15:47:37 +0000
+++ b/sql/sql_insert.cc 2011-08-02 07:33:45 +0000
@@ -631,7 +631,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
lock_type= table_list->lock_type;
thd_proc_info(thd, "init");
- thd->used_tables=0;
+ thd->lex->used_tables=0;
values= its++;
value_count= values->elements;
@@ -779,7 +779,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
}
else
{
- if (thd->used_tables) // Column used in values()
+ if (thd->lex->used_tables) // Column used in values()
restore_record(table,s->default_values); // Get empty record
else
{
=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc 2011-07-07 08:06:59 +0000
+++ b/sql/sql_lex.cc 2011-08-02 07:33:45 +0000
@@ -360,6 +360,7 @@ void lex_start(THD *thd)
lex->server_options.port= -1;
lex->is_lex_started= TRUE;
+ lex->used_tables= 0;
DBUG_VOID_RETURN;
}
=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h 2011-07-07 08:06:59 +0000
+++ b/sql/sql_lex.h 2011-08-02 07:33:45 +0000
@@ -1836,6 +1836,16 @@ typedef struct st_lex : public Query_tab
uint create_select_pos;
bool create_select_in_comment;
+ /*
+ The set of those tables whose fields are referenced in all subqueries
+ of the query.
+ TODO: possibly this it is incorrect to have used tables in LEX because
+ with subquery, it is not clear what does the field mean. To fix this
+ we should aggregate used tables information for selected expressions
+ into the select_lex.
+ */
+ table_map used_tables;
+
st_lex();
virtual ~st_lex()
=== modified file 'sql/sql_prepare.cc'
--- a/sql/sql_prepare.cc 2011-07-03 15:47:37 +0000
+++ b/sql/sql_prepare.cc 2011-08-02 07:33:45 +0000
@@ -1382,7 +1382,7 @@ static int mysql_test_select(Prepared_st
if (open_normal_and_derived_tables(thd, tables, 0))
goto error;
- thd->used_tables= 0; // Updated by setup_fields
+ thd->lex->used_tables= 0; // Updated by setup_fields
/*
JOIN::prepare calls
@@ -1551,7 +1551,7 @@ static bool select_like_stmt_test(Prepar
if (specific_prepare && (*specific_prepare)(thd))
DBUG_RETURN(TRUE);
- thd->used_tables= 0; // Updated by setup_fields
+ thd->lex->used_tables= 0; // Updated by setup_fields
/* Calls JOIN::prepare */
DBUG_RETURN(lex->unit.prepare(thd, 0, setup_tables_done_option));
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-07-07 08:06:59 +0000
+++ b/sql/sql_select.cc 2011-08-02 07:33:45 +0000
@@ -406,7 +406,7 @@ fix_inner_refs(THD *thd, List<Item> &all
if (!ref->fixed && ref->fix_fields(thd, 0))
return TRUE;
- thd->used_tables|= item->used_tables();
+ thd->lex->used_tables|= item->used_tables();
}
return false;
}
@@ -1632,7 +1632,7 @@ JOIN::optimize()
if (exec_tmp_table1->distinct)
{
- table_map used_tables= thd->used_tables;
+ table_map used_tables= thd->lex->used_tables;
JOIN_TAB *last_join_tab= join_tab+tables-1;
do
{
@@ -2526,7 +2526,7 @@ mysql_select(THD *thd, Item ***rref_poin
if (!(join= new JOIN(thd, fields, select_options, result)))
DBUG_RETURN(TRUE);
thd_proc_info(thd, "init");
- thd->used_tables=0; // Updated by setup_fields
+ thd->lex->used_tables=0; // Updated by setup_fields
err= join->prepare(rref_pointer_array, tables, wild_num,
conds, og_num, order, group, having, proc_param,
select_lex, unit);
@@ -16949,7 +16949,7 @@ static void select_describe(JOIN *join,
need_order=0;
extra.append(STRING_WITH_LEN("; Using filesort"));
}
- if (distinct & test_all_bits(used_tables,thd->used_tables))
+ if (distinct & test_all_bits(used_tables, thd->lex->used_tables))
extra.append(STRING_WITH_LEN("; Distinct"));
for (uint part= 0; part < tab->ref.key_parts; part++)
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-5.1 branch (sergey.glukhov:3578 to 3579) Bug#11766594 | Sergey Glukhov | 3 Aug |
