maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #03727
Patch for LP BUG#641203 for review
Igor,
Please review the attached patch for LP BUG#641203 we discussed last week.
Since there was no email service, the commit mail didn't get through, so
the commit comment is:
"
Fixed LP BUG#641203: Query returns rows where no result is expected (impossible WHERE)
The cause for the bug was two-fold:
1. Incorrect detection of whether a table is the first one in a query plan -
"used_table & 1" actually checks if used_table is table with number "1".
2. Missing logic to delay the evaluation of (expensive) constant conditions
during the execution phase.
The fix adds/changes:
The patch:
- removes expensive predicate treatment from make_cond_for_table, assuming
that the caller should decide whether to evaluate or not expensive predicates,
and what to do with them.
- saves expensive constant conditions in JOIN::exec_const_cond,
which is evaluated once in the beginning of JOIN::exec.
"
Timour
=== modified file 'mysql-test/r/subselect4.result'
--- mysql-test/r/subselect4.result 2010-10-28 17:04:23 +0000
+++ mysql-test/r/subselect4.result 2010-11-11 16:42:55 +0000
@@ -397,3 +397,42 @@
# Restore old value for Index condition pushdown
SET SESSION engine_condition_pushdown=@old_icp;
DROP TABLE t1,t2;
+#
+# LP BUG#641203 Query returns rows where no result is expected (impossible WHERE)
+#
+CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL);
+CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL);
+INSERT INTO t2 VALUES ('k'), ('d');
+CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL);
+INSERT INTO t3 VALUES ('a'), ('b'), ('c');
+CREATE TABLE t4 (c1 varchar(1) primary key);
+INSERT INTO t4 VALUES ('k'), ('d');
+EXPLAIN
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1 c1
+EXPLAIN
+SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1 c1
+EXPLAIN
+SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1 c1 c1
+EXPLAIN
+SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1 c1
+drop table t1, t2, t3, t4;
=== modified file 'mysql-test/t/subselect4.test'
--- mysql-test/t/subselect4.test 2010-10-28 17:04:23 +0000
+++ mysql-test/t/subselect4.test 2010-11-11 16:42:55 +0000
@@ -370,3 +370,29 @@
SET SESSION engine_condition_pushdown=@old_icp;
DROP TABLE t1,t2;
+
+--echo #
+--echo # LP BUG#641203 Query returns rows where no result is expected (impossible WHERE)
+--echo #
+
+CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL);
+CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL);
+INSERT INTO t2 VALUES ('k'), ('d');
+CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL);
+INSERT INTO t3 VALUES ('a'), ('b'), ('c');
+CREATE TABLE t4 (c1 varchar(1) primary key);
+INSERT INTO t4 VALUES ('k'), ('d');
+
+EXPLAIN
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+EXPLAIN
+SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+EXPLAIN
+SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
+SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
+EXPLAIN
+SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+drop table t1, t2, t3, t4;
=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc 2010-11-05 12:42:58 +0000
+++ sql/sql_select.cc 2010-11-11 16:42:55 +0000
@@ -178,13 +178,10 @@
int join_read_always_key_or_null(JOIN_TAB *tab);
int join_read_next_same_or_null(READ_RECORD *info);
static COND *make_cond_for_table(Item *cond,table_map table,
- table_map used_table,
- bool exclude_expensive_cond);
+ table_map used_table);
static COND *make_cond_for_table_from_pred(Item *root_cond, Item *cond,
table_map tables,
- table_map used_table,
- bool exclude_expensive_cond);
-
+ table_map used_table);
static Item* part_of_refkey(TABLE *form,Field *field);
uint find_shortest_key(TABLE *table, const key_map *usable_keys);
static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,
@@ -919,7 +916,7 @@
if (conds && !(thd->lex->describe & DESCRIBE_EXTENDED))
{
COND *table_independent_conds=
- make_cond_for_table(conds, PSEUDO_TABLE_BITS, 0, FALSE);
+ make_cond_for_table(conds, PSEUDO_TABLE_BITS, 0);
DBUG_EXECUTE("where",
print_where(table_independent_conds,
"where after opt_sum_query()",
@@ -1821,6 +1818,9 @@
if (tables)
thd->limit_found_rows= 0;
+ if (exec_const_cond && !exec_const_cond->val_int())
+ zero_result_cause= "Impossible WHERE noticed after reading const tables";
+
if (zero_result_cause)
{
(void) return_zero_rows(this, result, select_lex->leaf_tables,
@@ -2202,7 +2202,7 @@
Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having,
used_tables,
- (table_map)0, FALSE);
+ (table_map)0);
if (sort_table_cond)
{
if (!curr_table->select)
@@ -2225,7 +2225,7 @@
QT_ORDINARY););
curr_join->tmp_having= make_cond_for_table(curr_join->tmp_having,
~ (table_map) 0,
- ~used_tables, FALSE);
+ ~used_tables);
DBUG_EXECUTE("where",print_where(curr_join->tmp_having,
"having after sort",
QT_ORDINARY););
@@ -6626,11 +6626,12 @@
there inside the triggers.
*/
{ // Check const tables
- COND *const_cond=
+ join->exec_const_cond=
make_cond_for_table(cond,
join->const_table_map,
- (table_map) 0, TRUE);
- DBUG_EXECUTE("where",print_where(const_cond,"constants", QT_ORDINARY););
+ (table_map) 0);
+ DBUG_EXECUTE("where",print_where(join->exec_const_cond, "constants",
+ QT_ORDINARY););
for (JOIN_TAB *tab= join->join_tab+join->const_tables;
tab < join->join_tab+join->tables ; tab++)
{
@@ -6639,7 +6640,7 @@
JOIN_TAB *cond_tab= tab->first_inner;
COND *tmp= make_cond_for_table(*tab->on_expr_ref,
join->const_table_map,
- ( table_map) 0, FALSE);
+ (table_map) 0);
if (!tmp)
continue;
tmp= new Item_func_trig_cond(tmp, &cond_tab->not_null_compl);
@@ -6655,10 +6656,13 @@
cond_tab->select_cond->quick_fix_field();
}
}
- if (const_cond && !const_cond->val_int())
+
+ if (join->exec_const_cond && !join->exec_const_cond->is_expensive() &&
+ !join->exec_const_cond->val_int())
{
- DBUG_PRINT("info",("Found impossible WHERE condition"));
- DBUG_RETURN(1); // Impossible const condition
+ DBUG_PRINT("info",("Found impossible WHERE condition"));
+ join->exec_const_cond= NULL;
+ DBUG_RETURN(1); // Impossible const condition
}
}
}
@@ -6727,7 +6731,7 @@
tmp= NULL;
if (cond)
- tmp= make_cond_for_table(cond, used_tables, current_map, FALSE);
+ tmp= make_cond_for_table(cond, used_tables, current_map);
if (cond && !tmp && tab->quick)
{ // Outer join
if (tab->type != JT_ALL)
@@ -6782,8 +6786,7 @@
tab->table->file->pushed_cond= NULL;
if (thd->variables.engine_condition_pushdown && !first_inner_tab)
{
- COND *push_cond=
- make_cond_for_table(tmp, current_map, current_map, FALSE);
+ COND *push_cond= make_cond_for_table(tmp, current_map, current_map);
if (push_cond)
{
/* Push condition to handler */
@@ -6911,8 +6914,7 @@
if (cond &&
(tmp=make_cond_for_table(cond,
join->const_table_map |
- current_map,
- current_map, FALSE)))
+ current_map, current_map)))
{
DBUG_EXECUTE("where",print_where(tmp,"cache", QT_ORDINARY););
tab->cache_select=(SQL_SELECT*)
@@ -6942,7 +6944,7 @@
JOIN_TAB *cond_tab= join_tab->first_inner;
COND *tmp= make_cond_for_table(*join_tab->on_expr_ref,
join->const_table_map,
- (table_map) 0, FALSE);
+ (table_map) 0);
if (!tmp)
continue;
tmp= new Item_func_trig_cond(tmp, &cond_tab->not_null_compl);
@@ -6977,7 +6979,7 @@
current_map= tab->table->map;
used_tables2|= current_map;
COND *tmp_cond= make_cond_for_table(on_expr, used_tables2,
- current_map, FALSE);
+ current_map);
if (tmp_cond)
{
JOIN_TAB *cond_tab= tab < first_inner_tab ? first_inner_tab : tab;
@@ -14622,7 +14624,6 @@
}
-
/*
Extract a condition that can be checked after reading given table
@@ -14657,33 +14658,16 @@
*/
static Item *
-make_cond_for_table(Item *cond, table_map tables, table_map used_table,
- bool exclude_expensive_cond)
+make_cond_for_table(Item *cond, table_map tables, table_map used_table)
{
- return make_cond_for_table_from_pred(cond, cond, tables, used_table,
- exclude_expensive_cond);
+ return make_cond_for_table_from_pred(cond, cond, tables, used_table);
}
static Item *
make_cond_for_table_from_pred(Item *root_cond, Item *cond,
- table_map tables, table_map used_table,
- bool exclude_expensive_cond)
+ table_map tables, table_map used_table)
{
- if (used_table && !(cond->used_tables() & used_table) &&
- /*
- Exclude constant conditions not checked at optimization time if
- the table we are pushing conditions to is the first one.
- As a result, such conditions are not considered as already checked
- and will be checked at execution time, attached to the first table.
-
- psergey: TODO: "used_table & 1" doesn't make sense in nearly any
- context. Look at setup_table_map(), table bits reflect the order
- the tables were encountered by the parser. Check what we should
- replace this condition with.
- */
- !((used_table & 1) && cond->is_expensive()))
- return (COND*) 0; // Already checked
if (cond->type() == Item::COND_ITEM)
{
if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
@@ -14697,8 +14681,7 @@
while ((item=li++))
{
Item *fix=make_cond_for_table_from_pred(root_cond, item,
- tables, used_table,
- exclude_expensive_cond);
+ tables, used_table);
if (fix)
new_cond->argument_list()->push_back(fix);
}
@@ -14729,8 +14712,7 @@
while ((item=li++))
{
Item *fix=make_cond_for_table_from_pred(root_cond, item,
- tables, 0L,
- exclude_expensive_cond);
+ tables, 0L);
if (!fix)
return (COND*) 0; // Always true
new_cond->argument_list()->push_back(fix);
@@ -14751,12 +14733,7 @@
table_count times, we mark each item that we have examined with the result
of the test
*/
- if (cond->marker == 3 || (cond->used_tables() & ~tables) ||
- /*
- When extracting constant conditions, treat expensive conditions as
- non-constant, so that they are not evaluated at optimization time.
- */
- (!used_table && exclude_expensive_cond && cond->is_expensive()))
+ if (cond->marker == 3 || (cond->used_tables() & ~tables))
return (COND*) 0; // Can't check this yet
if (cond->marker == 2 || cond->eq_cmp_result() == Item::COND_OK)
return cond; // Not boolean op
@@ -14784,7 +14761,6 @@
}
-
static COND *
make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables,
table_map sjm_tables)
@@ -15956,8 +15932,7 @@
table_map used_tables= join->const_table_map | table->table->map;
DBUG_EXECUTE("where",print_where(*having,"having", QT_ORDINARY););
- Item* sort_table_cond=make_cond_for_table(*having, used_tables, used_tables,
- FALSE);
+ Item* sort_table_cond=make_cond_for_table(*having, used_tables, used_tables);
if (sort_table_cond)
{
if (!table->select)
@@ -15975,7 +15950,7 @@
DBUG_EXECUTE("where",print_where(table->select_cond,
"select and having",
QT_ORDINARY););
- *having= make_cond_for_table(*having,~ (table_map) 0,~used_tables, FALSE);
+ *having= make_cond_for_table(*having,~ (table_map) 0,~used_tables);
DBUG_EXECUTE("where",
print_where(*having,"having after make_cond", QT_ORDINARY););
}
=== modified file 'sql/sql_select.h'
--- sql/sql_select.h 2010-11-05 12:42:58 +0000
+++ sql/sql_select.h 2010-11-11 16:42:55 +0000
@@ -1593,6 +1593,12 @@
List<TABLE_LIST> *join_list; ///< list of joined tables in reverse order
COND_EQUAL *cond_equal;
COND_EQUAL *having_equal;
+ /*
+ Constant codition computed during optimization, but evaluated during
+ join execution. Typically expensive conditions that should not be
+ evaluated at optimization time.
+ */
+ Item *exec_const_cond;
SQL_SELECT *select; ///<created in optimisation phase
JOIN_TAB *return_tab; ///<used only for outer joins
Item **ref_pointer_array; ///<used pointer reference for this select
@@ -1689,6 +1695,7 @@
initialized= 0;
cond_equal= 0;
having_equal= 0;
+ exec_const_cond= 0;
group_optimized_away= 0;
no_rows_in_result_called= 0;
@@ -1781,7 +1788,6 @@
bool choose_subquery_plan(table_map join_tables);
void get_partial_join_cost(uint n_tables,
double *read_time_arg, double *record_count_arg);
-
private:
/**
TRUE if the query contains an aggregate function but has no GROUP
