summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect_sj.result36
-rw-r--r--mysql-test/r/subselect_sj_jcl6.result36
-rw-r--r--mysql-test/t/subselect_sj.test38
-rw-r--r--sql/opt_subselect.cc6
-rw-r--r--sql/sql_expression_cache.cc2
-rw-r--r--sql/sql_select.cc37
-rw-r--r--sql/sql_select.h3
-rw-r--r--sql/sql_show.cc2
-rw-r--r--sql/sql_union.cc5
-rw-r--r--sql/sql_update.cc2
10 files changed, 150 insertions, 17 deletions
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 56c3044c4e4..3fc91b452a5 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2884,4 +2884,40 @@ HAVING c <> ( SELECT MAX( c ) FROM t1 )
ORDER BY sm;
c sm
DROP TABLE t1,t2;
+#
+# mdev-4173 ignored duplicate value when converting heap to temp table
+# could lead to extra rows in semijoin queries or missing row in union queries
+#
+CREATE TABLE t1 (i1 INT) engine=myisam;
+INSERT INTO t1 VALUES
+(4),(8),(0),(0),(0),(7),(7),(5),(3),(4),(9),(6),(1),(5),(6),(2),(4),(4);
+CREATE TABLE t2 (i2 INT, j2 INT) engine=myisam;
+INSERT INTO t2 VALUES
+(7,1),(0,7),(9,4),(3,7),(4,0),(2,2),(5,9),(3,4),
+(1,0),(3,9),(5,8),(1,8),(204,18),(224,84),(9,6);
+CREATE TABLE t3 (i3 INT, KEY(i3)) engine=myisam;
+INSERT INTO t3 VALUES
+(0),(8),(1),(8),(9),(24),(6),(1),(6),(2),(4),(2),(1);
+select @@max_heap_table_size into @tmp_max_heap_table_size;
+select @@join_buffer_size into @tmp_join_buffer_size;
+set max_heap_table_size=16*1024;
+COUNT(*)
+2834
+COUNT(*)
+2834
+COUNT(*)
+2834
+COUNT(*)
+2834
+COUNT(*)
+2834
+COUNT(*)
+2834
+COUNT(*)
+2834
+COUNT(*)
+2834
+DROP TABLE t1, t2, t3;
+set join_buffer_size = @tmp_join_buffer_size;
+set max_heap_table_size = @tmp_max_heap_table_size;
set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 7be29201c63..f18de3c4d0b 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2898,6 +2898,42 @@ HAVING c <> ( SELECT MAX( c ) FROM t1 )
ORDER BY sm;
c sm
DROP TABLE t1,t2;
+#
+# mdev-4173 ignored duplicate value when converting heap to temp table
+# could lead to extra rows in semijoin queries or missing row in union queries
+#
+CREATE TABLE t1 (i1 INT) engine=myisam;
+INSERT INTO t1 VALUES
+(4),(8),(0),(0),(0),(7),(7),(5),(3),(4),(9),(6),(1),(5),(6),(2),(4),(4);
+CREATE TABLE t2 (i2 INT, j2 INT) engine=myisam;
+INSERT INTO t2 VALUES
+(7,1),(0,7),(9,4),(3,7),(4,0),(2,2),(5,9),(3,4),
+(1,0),(3,9),(5,8),(1,8),(204,18),(224,84),(9,6);
+CREATE TABLE t3 (i3 INT, KEY(i3)) engine=myisam;
+INSERT INTO t3 VALUES
+(0),(8),(1),(8),(9),(24),(6),(1),(6),(2),(4),(2),(1);
+select @@max_heap_table_size into @tmp_max_heap_table_size;
+select @@join_buffer_size into @tmp_join_buffer_size;
+set max_heap_table_size=16*1024;
+COUNT(*)
+2834
+COUNT(*)
+2834
+COUNT(*)
+2834
+COUNT(*)
+2834
+COUNT(*)
+2834
+COUNT(*)
+2834
+COUNT(*)
+2834
+COUNT(*)
+2834
+DROP TABLE t1, t2, t3;
+set join_buffer_size = @tmp_join_buffer_size;
+set max_heap_table_size = @tmp_max_heap_table_size;
set optimizer_switch=@subselect_sj_tmp;
#
# BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index bfdc6335f1d..cde8d5d0e19 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2584,5 +2584,43 @@ ORDER BY sm;
DROP TABLE t1,t2;
+--echo #
+--echo # mdev-4173 ignored duplicate value when converting heap to temp table
+--echo # could lead to extra rows in semijoin queries or missing row in union queries
+--echo #
+CREATE TABLE t1 (i1 INT) engine=myisam;
+INSERT INTO t1 VALUES
+(4),(8),(0),(0),(0),(7),(7),(5),(3),(4),(9),(6),(1),(5),(6),(2),(4),(4);
+
+CREATE TABLE t2 (i2 INT, j2 INT) engine=myisam;
+INSERT INTO t2 VALUES
+(7,1),(0,7),(9,4),(3,7),(4,0),(2,2),(5,9),(3,4),
+(1,0),(3,9),(5,8),(1,8),(204,18),(224,84),(9,6);
+
+CREATE TABLE t3 (i3 INT, KEY(i3)) engine=myisam;
+INSERT INTO t3 VALUES
+(0),(8),(1),(8),(9),(24),(6),(1),(6),(2),(4),(2),(1);
+
+select @@max_heap_table_size into @tmp_max_heap_table_size;
+select @@join_buffer_size into @tmp_join_buffer_size;
+set max_heap_table_size=16*1024;
+
+--disable_query_log
+let $n = 8;
+while ($n) {
+ eval set join_buffer_size= 128 + 128*$n;
+ SELECT COUNT(*) FROM t1 outer_t1, t2 outer_t2, t3
+ WHERE outer_t1.i1 IN (
+ SELECT j2 FROM t2 LEFT JOIN t3 ON ( i3 = j2 )
+ WHERE i2 <> outer_t2.j2
+ );
+ dec $n;
+}
+--enable_query_log
+
+DROP TABLE t1, t2, t3;
+set join_buffer_size = @tmp_join_buffer_size;
+set max_heap_table_size = @tmp_max_heap_table_size;
+
# The following command must be the last one the file
set optimizer_switch=@subselect_sj_tmp;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 660d52e20fe..773897474f3 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -4227,9 +4227,13 @@ int SJ_TMP_TABLE::sj_weedout_check_row(THD *thd)
/* create_internal_tmp_table_from_heap will generate error if needed */
if (!tmp_table->file->is_fatal_error(error, HA_CHECK_DUP))
DBUG_RETURN(1); /* Duplicate */
+
+ bool is_duplicate;
if (create_internal_tmp_table_from_heap(thd, tmp_table, start_recinfo,
- &recinfo, error, 1))
+ &recinfo, error, 1, &is_duplicate))
DBUG_RETURN(-1);
+ if (is_duplicate)
+ DBUG_RETURN(1);
}
DBUG_RETURN(0);
}
diff --git a/sql/sql_expression_cache.cc b/sql/sql_expression_cache.cc
index e65ec3c22b0..f3c96ee2d2f 100644
--- a/sql/sql_expression_cache.cc
+++ b/sql/sql_expression_cache.cc
@@ -288,7 +288,7 @@ my_bool Expression_cache_tmptable::put_value(Item *value)
if (create_internal_tmp_table_from_heap(table_thd, cache_table,
cache_table_param.start_recinfo,
&cache_table_param.recinfo,
- error, 1))
+ error, 1, NULL))
goto err;
}
}
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d69fb926de0..f1b08806411 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -154,7 +154,7 @@ static COND *optimize_cond(JOIN *join, COND *conds,
bool const_expression_in_where(COND *conds,Item *item, Item **comp_item);
static bool create_internal_tmp_table_from_heap2(THD *, TABLE *,
ENGINE_COLUMNDEF *, ENGINE_COLUMNDEF **,
- int, bool, handlerton *, const char *);
+ int, bool, handlerton *, const char *, bool *);
static int do_select(JOIN *join,List<Item> *fields,TABLE *tmp_table,
Procedure *proc);
@@ -9458,7 +9458,7 @@ end_sj_materialize(JOIN *join, JOIN_TAB *join_tab, bool end_of_records)
if (table->file->is_fatal_error(error, HA_CHECK_DUP) &&
create_internal_tmp_table_from_heap(thd, table,
sjm->sjm_table_param.start_recinfo,
- &sjm->sjm_table_param.recinfo, error, 1))
+ &sjm->sjm_table_param.recinfo, error, 1, NULL))
DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */
}
}
@@ -15471,13 +15471,15 @@ bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table,
ENGINE_COLUMNDEF *start_recinfo,
ENGINE_COLUMNDEF **recinfo,
int error,
- bool ignore_last_dupp_key_error)
+ bool ignore_last_dupp_key_error,
+ bool *is_duplicate)
{
return create_internal_tmp_table_from_heap2(thd, table,
start_recinfo, recinfo, error,
ignore_last_dupp_key_error,
maria_hton,
- "converting HEAP to Aria");
+ "converting HEAP to Aria",
+ is_duplicate);
}
#else
@@ -15636,13 +15638,15 @@ bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table,
ENGINE_COLUMNDEF *start_recinfo,
ENGINE_COLUMNDEF **recinfo,
int error,
- bool ignore_last_dupp_key_error)
+ bool ignore_last_dupp_key_error,
+ bool *is_duplicate)
{
return create_internal_tmp_table_from_heap2(thd, table,
start_recinfo, recinfo, error,
ignore_last_dupp_key_error,
myisam_hton,
- "converting HEAP to MyISAM");
+ "converting HEAP to MyISAM",
+ is_duplicate);
}
#endif /* WITH_MARIA_STORAGE_ENGINE */
@@ -15661,7 +15665,8 @@ create_internal_tmp_table_from_heap2(THD *thd, TABLE *table,
int error,
bool ignore_last_dupp_key_error,
handlerton *hton,
- const char *proc_info)
+ const char *proc_info,
+ bool *is_duplicate)
{
TABLE new_table;
TABLE_SHARE share;
@@ -15738,6 +15743,13 @@ create_internal_tmp_table_from_heap2(THD *thd, TABLE *table,
if (new_table.file->is_fatal_error(write_err, HA_CHECK_DUP) ||
!ignore_last_dupp_key_error)
goto err;
+ if (is_duplicate)
+ *is_duplicate= TRUE;
+ }
+ else
+ {
+ if (is_duplicate)
+ *is_duplicate= FALSE;
}
/* remove heap table and change to use myisam table */
@@ -17614,11 +17626,14 @@ end_write(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),
{
if (!table->file->is_fatal_error(error, HA_CHECK_DUP))
goto end;
+ bool is_duplicate;
if (create_internal_tmp_table_from_heap(join->thd, table,
join->tmp_table_param.start_recinfo,
&join->tmp_table_param.recinfo,
- error,1))
+ error, 1, &is_duplicate))
DBUG_RETURN(NESTED_LOOP_ERROR); // Not a table_is_full error
+ if (is_duplicate)
+ goto end;
table->s->uniques=0; // To ensure rows are the same
}
if (++join->send_records >= join->tmp_table_param.end_write_records &&
@@ -17703,7 +17718,7 @@ end_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),
if (create_internal_tmp_table_from_heap(join->thd, table,
join->tmp_table_param.start_recinfo,
&join->tmp_table_param.recinfo,
- error, 0))
+ error, 0, NULL))
DBUG_RETURN(NESTED_LOOP_ERROR); // Not a table_is_full error
/* Change method to update rows */
if ((error= table->file->ha_index_init(0, 0)))
@@ -17808,7 +17823,7 @@ end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),
create_internal_tmp_table_from_heap(join->thd, table,
join->tmp_table_param.start_recinfo,
&join->tmp_table_param.recinfo,
- error, 0))
+ error, 0, NULL))
DBUG_RETURN(NESTED_LOOP_ERROR);
}
if (join->rollup.state != ROLLUP::STATE_NONE)
@@ -21638,7 +21653,7 @@ int JOIN::rollup_write_data(uint idx, TABLE *table_arg)
if (create_internal_tmp_table_from_heap(thd, table_arg,
tmp_table_param.start_recinfo,
&tmp_table_param.recinfo,
- write_error, 0))
+ write_error, 0, NULL))
return 1;
}
}
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 7168fd54024..478eede7108 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1819,7 +1819,8 @@ void free_tmp_table(THD *thd, TABLE *entry);
bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table,
ENGINE_COLUMNDEF *start_recinfo,
ENGINE_COLUMNDEF **recinfo,
- int error, bool ignore_last_dupp_key_error);
+ int error, bool ignore_last_dupp_key_error,
+ bool *is_duplicate);
bool create_internal_tmp_table(TABLE *table, KEY *keyinfo,
ENGINE_COLUMNDEF *start_recinfo,
ENGINE_COLUMNDEF **recinfo,
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 962c709d34d..223a9d30b5a 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -3133,7 +3133,7 @@ bool schema_table_store_record(THD *thd, TABLE *table)
{
TMP_TABLE_PARAM *param= table->pos_in_table_list->schema_table_param;
if (create_internal_tmp_table_from_heap(thd, table, param->start_recinfo,
- &param->recinfo, error, 0))
+ &param->recinfo, error, 0, NULL))
return 1;
}
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 77d4df1b398..248296b2bf4 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -83,13 +83,16 @@ int select_union::send_data(List<Item> &values)
*/
return -1;
}
+ bool is_duplicate;
/* create_internal_tmp_table_from_heap will generate error if needed */
if (table->file->is_fatal_error(write_err, HA_CHECK_DUP) &&
create_internal_tmp_table_from_heap(thd, table,
tmp_table_param.start_recinfo,
&tmp_table_param.recinfo,
- write_err, 1))
+ write_err, 1, &is_duplicate))
return 1;
+ if (is_duplicate)
+ return -1;
}
return 0;
}
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index d36c36ca592..42e12f4cb76 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -2008,7 +2008,7 @@ int multi_update::send_data(List<Item> &not_used_values)
create_internal_tmp_table_from_heap(thd, tmp_table,
tmp_table_param[offset].start_recinfo,
&tmp_table_param[offset].recinfo,
- error, 1))
+ error, 1, NULL))
{
do_update= 0;
DBUG_RETURN(1); // Not a table_is_full error
View Lorry Controller Status