summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/explain.result11
-rw-r--r--mysql-test/r/subselect.result26
-rw-r--r--mysql-test/t/explain.test9
-rw-r--r--mysql-test/t/subselect.test34
-rw-r--r--sql/item.cc4
-rw-r--r--sql/item_subselect.cc8
-rw-r--r--sql/item_sum.cc6
-rw-r--r--sql/mysql_priv.h7
-rw-r--r--sql/sql_lex.cc5
-rw-r--r--sql/sql_lex.h26
-rw-r--r--sql/sql_select.cc12
11 files changed, 110 insertions, 38 deletions
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index 90a4136d030..da2cc51b59b 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -176,11 +176,12 @@ SELECT @@session.sql_mode INTO @old_sql_mode;
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
EXPLAIN EXTENDED SELECT 1 FROM t1
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
-ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
-SHOW WARNINGS;
-Level Code Message
-Error 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
-Note 1003 select 1 AS `1` from `test`.`t1` where <not>(<exists>(...))
+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 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
+2 SUBQUERY t system NULL NULL NULL NULL 0 const row not found
+Warnings:
+Note 1003 select 1 AS `1` from `test`.`t1` where 0
SET SESSION sql_mode=@old_sql_mode;
DROP TABLE t1;
End of 5.0 tests.
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 7fbe4c08b08..7652cd7323b 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4528,6 +4528,32 @@ pk int_key
7 3
DROP TABLE t1,t2;
#
+# Bug#12329653
+# EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY
+#
+CREATE TABLE t1(a1 int);
+INSERT INTO t1 VALUES (1),(2);
+SELECT @@session.sql_mode INTO @old_sql_mode;
+SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
+1
+1
+1
+PREPARE stmt FROM
+'SELECT 1 UNION ALL
+SELECT 1 FROM t1
+ORDER BY
+(SELECT 1 FROM t1 AS t1_0
+ WHERE 1 < SOME (SELECT a1 FROM t1)
+)' ;
+EXECUTE stmt ;
+ERROR 21000: Subquery returns more than 1 row
+EXECUTE stmt ;
+ERROR 21000: Subquery returns more than 1 row
+SET SESSION sql_mode=@old_sql_mode;
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1;
+#
# Bug #52711: Segfault when doing EXPLAIN SELECT with
# union...order by (select... where...)
#
diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test
index 1b16c811dbd..2ae36152b73 100644
--- a/mysql-test/t/explain.test
+++ b/mysql-test/t/explain.test
@@ -1,5 +1,5 @@
#
-# Test of different EXPLAIN's
+# Test of different EXPLAINs
--disable_warnings
drop table if exists t1;
@@ -157,11 +157,12 @@ CREATE TABLE t1 (f1 INT);
SELECT @@session.sql_mode INTO @old_sql_mode;
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
-# EXPLAIN EXTENDED (with subselect). used to crash. should give NOTICE.
---error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
+# EXPLAIN EXTENDED (with subselect). used to crash.
+# This is actually a valid query for this sql_mode,
+# but it was transformed in such a way that it failed, see
+# Bug#12329653 - EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY
EXPLAIN EXTENDED SELECT 1 FROM t1
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
-SHOW WARNINGS;
SET SESSION sql_mode=@old_sql_mode;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 0956f91619d..b4cee13d3ac 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -3507,6 +3507,40 @@ ORDER BY outr.pk;
DROP TABLE t1,t2;
--echo #
+--echo # Bug#12329653
+--echo # EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY
+--echo #
+
+CREATE TABLE t1(a1 int);
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT @@session.sql_mode INTO @old_sql_mode;
+SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+
+## First a simpler query, illustrating the transformation
+## '1 < some (...)' => '1 < max(...)'
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
+
+## The query which made the server crash.
+PREPARE stmt FROM
+'SELECT 1 UNION ALL
+SELECT 1 FROM t1
+ORDER BY
+(SELECT 1 FROM t1 AS t1_0
+ WHERE 1 < SOME (SELECT a1 FROM t1)
+)' ;
+
+--error ER_SUBQUERY_NO_1_ROW
+EXECUTE stmt ;
+--error ER_SUBQUERY_NO_1_ROW
+EXECUTE stmt ;
+
+SET SESSION sql_mode=@old_sql_mode;
+
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1;
+
+--echo #
--echo # Bug #52711: Segfault when doing EXPLAIN SELECT with
--echo # union...order by (select... where...)
--echo #
diff --git a/sql/item.cc b/sql/item.cc
index 03d752a85d9..aaeb21f9948 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4080,14 +4080,14 @@ mark_non_agg_field:
aggregated or not.
*/
if (!thd->lex->in_sum_func)
- cached_table->select_lex->full_group_by_flag|= NON_AGG_FIELD_USED;
+ cached_table->select_lex->set_non_agg_field_used(true);
else
{
if (outer_fixed)
thd->lex->in_sum_func->outer_fields.push_back(this);
else if (thd->lex->in_sum_func->nest_level !=
thd->lex->current_select->nest_level)
- cached_table->select_lex->full_group_by_flag|= NON_AGG_FIELD_USED;
+ cached_table->select_lex->set_non_agg_field_used(true);
}
}
return FALSE;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 44d1378839b..6666525a270 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -936,6 +936,14 @@ Item_in_subselect::single_value_transformer(JOIN *join,
it.replace(item);
}
+ DBUG_EXECUTE("where",
+ print_where(item, "rewrite with MIN/MAX"););
+ if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
+ {
+ DBUG_ASSERT(select_lex->non_agg_field_used());
+ select_lex->set_non_agg_field_used(false);
+ }
+
save_allow_sum_func= thd->lex->allow_sum_func;
thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
/*
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 43102213b9b..9942ae199cb 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -246,10 +246,10 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
in_sum_func->outer_fields.push_back(field);
}
else
- sel->full_group_by_flag|= NON_AGG_FIELD_USED;
+ sel->set_non_agg_field_used(true);
}
if (sel->nest_level > aggr_level &&
- (sel->full_group_by_flag & SUM_FUNC_USED) &&
+ (sel->agg_func_used()) &&
!sel->group_list.elements)
{
my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
@@ -258,7 +258,7 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
}
}
}
- aggr_sel->full_group_by_flag|= SUM_FUNC_USED;
+ aggr_sel->set_agg_func_used(true);
update_used_tables();
thd->lex->in_sum_func= in_sum_func;
return FALSE;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index a811bbafdb6..97cad9e4b19 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1087,13 +1087,6 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables,
extern Item **not_found_item;
/*
- A set of constants used for checking non aggregated fields and sum
- functions mixture in the ONLY_FULL_GROUP_BY_MODE.
-*/
-#define NON_AGG_FIELD_USED 1
-#define SUM_FUNC_USED 2
-
-/*
This enumeration type is used only by the function find_item_in_list
to return the info on how an item has been resolved against a list
of possibly aliased items.
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 97d9fe99eb3..87916b201d2 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1232,6 +1232,8 @@ void st_select_lex::init_query()
exclude_from_table_unique_test= no_wrap_view_item= FALSE;
nest_level= 0;
link_next= 0;
+ m_non_agg_field_used= false;
+ m_agg_func_used= false;
}
void st_select_lex::init_select()
@@ -1266,7 +1268,8 @@ void st_select_lex::init_select()
non_agg_fields.empty();
cond_value= having_value= Item::COND_UNDEF;
inner_refs_list.empty();
- full_group_by_flag= 0;
+ m_non_agg_field_used= false;
+ m_agg_func_used= false;
}
/*
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index b3822f91afe..7b2227a9678 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -617,16 +617,7 @@ public:
joins on the right.
*/
List<String> *prev_join_using;
- /*
- Bitmap used in the ONLY_FULL_GROUP_BY_MODE to prevent mixture of aggregate
- functions and non aggregated fields when GROUP BY list is absent.
- Bits:
- 0 - non aggregated fields are used in this select,
- defined as NON_AGG_FIELD_USED.
- 1 - aggregate functions are used in this select,
- defined as SUM_FUNC_USED.
- */
- uint8 full_group_by_flag;
+
void init_query();
void init_select();
st_select_lex_unit* master_unit();
@@ -714,6 +705,21 @@ public:
select lexes.
*/
void cleanup_all_joins(bool full);
+ /*
+ For MODE_ONLY_FULL_GROUP_BY we need to maintain two flags:
+ - Non-aggregated fields are used in this select.
+ - Aggregate functions are used in this select.
+ In MODE_ONLY_FULL_GROUP_BY only one of these may be true.
+ */
+ bool non_agg_field_used() const { return m_non_agg_field_used; }
+ bool agg_func_used() const { return m_agg_func_used; }
+
+ void set_non_agg_field_used(bool val) { m_non_agg_field_used= val; }
+ void set_agg_func_used(bool val) { m_agg_func_used= val; }
+
+private:
+ bool m_non_agg_field_used;
+ bool m_agg_func_used;
};
typedef class st_select_lex SELECT_LEX;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index cb7add3a874..0d19dcb576b 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -391,19 +391,18 @@ inline int setup_without_group(THD *thd, Item **ref_pointer_array,
int res;
nesting_map save_allow_sum_func=thd->lex->allow_sum_func ;
/*
- Need to save the value, so we can turn off only the new NON_AGG_FIELD
+ Need to save the value, so we can turn off only any new non_agg_field_used
additions coming from the WHERE
*/
- uint8 saved_flag= thd->lex->current_select->full_group_by_flag;
+ const bool saved_non_agg_field_used=
+ thd->lex->current_select->non_agg_field_used();
DBUG_ENTER("setup_without_group");
thd->lex->allow_sum_func&= ~(1 << thd->lex->current_select->nest_level);
res= setup_conds(thd, tables, leaves, conds);
/* it's not wrong to have non-aggregated columns in a WHERE */
- if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
- thd->lex->current_select->full_group_by_flag= saved_flag |
- (thd->lex->current_select->full_group_by_flag & ~NON_AGG_FIELD_USED);
+ thd->lex->current_select->set_non_agg_field_used(saved_non_agg_field_used);
thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
res= res || setup_order(thd, ref_pointer_array, tables, fields, all_fields,
@@ -593,7 +592,8 @@ JOIN::prepare(Item ***rref_pointer_array,
aggregate functions with implicit grouping (there is no GROUP BY).
*/
if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY && !group_list &&
- select_lex->full_group_by_flag == (NON_AGG_FIELD_USED | SUM_FUNC_USED))
+ select_lex->non_agg_field_used() &&
+ select_lex->agg_func_used())
{
my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
View Lorry Controller Status