summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/explain.result10
-rw-r--r--mysql-test/r/subselect.result26
-rw-r--r--mysql-test/t/explain.test7
-rw-r--r--mysql-test/t/subselect.test33
-rw-r--r--sql/item.cc4
-rw-r--r--sql/item_subselect.cc9
-rw-r--r--sql/item_sum.cc6
-rw-r--r--sql/sql_lex.cc5
-rw-r--r--sql/sql_lex.h28
-rw-r--r--sql/sql_partition.cc27
-rw-r--r--sql/sql_priv.h7
-rw-r--r--sql/sql_select.cc12
12 files changed, 124 insertions, 50 deletions
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index b8f791b27f4..887fb319adc 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -176,10 +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
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
+2 SUBQUERY t system NULL NULL NULL NULL 0 0.00 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 b516585fc37..3abc02358b8 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4442,6 +4442,32 @@ pk int_key
3 3
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;
End of 5.0 tests.
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (2,22),(1,11),(2,22);
diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test
index 8376fdf1ad1..a4fc4e3409c 100644
--- a/mysql-test/t/explain.test
+++ b/mysql-test/t/explain.test
@@ -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 f60ee247ba7..5230dd5ddc4 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -3387,6 +3387,39 @@ 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 End of 5.0 tests.
diff --git a/sql/item.cc b/sql/item.cc
index af3917c09c1..639a3a81b78 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4759,14 +4759,14 @@ mark_non_agg_field:
SELECT_LEX *select_lex= cached_table ?
cached_table->select_lex : context->select_lex;
if (!thd->lex->in_sum_func)
- select_lex->full_group_by_flag|= NON_AGG_FIELD_USED;
+ 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)
- select_lex->full_group_by_flag|= NON_AGG_FIELD_USED;
+ select_lex->set_non_agg_field_used(true);
}
}
return FALSE;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 118bae7342f..721b3fd5095 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -38,6 +38,7 @@
#include "set_var.h"
#include "sql_select.h"
#include "sql_parse.h" // check_stack_overrun
+#include "sql_test.h"
inline Item * and_items(Item* cond, Item *item)
{
@@ -1025,6 +1026,14 @@ Item_in_subselect::single_value_transformer(JOIN *join,
it.replace(item);
}
+ DBUG_EXECUTE("where",
+ print_where(item, "rewrite with MIN/MAX", QT_ORDINARY););
+ 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 8855ef05c84..42088616f4a 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -259,10 +259,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,
@@ -271,7 +271,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/sql_lex.cc b/sql/sql_lex.cc
index 5e8ecc6b815..6e3f0a33147 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1755,6 +1755,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()
@@ -1785,7 +1787,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 e6b5f23f17e..2b40741698e 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -800,16 +800,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();
@@ -918,7 +909,22 @@ public:
void clear_index_hints(void) { index_hints= NULL; }
-private:
+ /*
+ 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;
+
/* current index hint kind. used in filling up index_hints */
enum index_hint_type current_index_hint_type;
index_clause_map current_index_hint_clause;
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index 5c2c0bb95d6..b9bbec63e7d 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -1077,8 +1077,6 @@ static bool fix_fields_part_func(THD *thd, Item* func_expr, TABLE *table,
int error;
LEX *old_lex= thd->lex;
LEX lex;
- uint8 saved_full_group_by_flag;
- nesting_map saved_allow_sum_func;
DBUG_ENTER("fix_fields_part_func");
if (init_lex_with_single_table(thd, table, &lex))
@@ -1103,19 +1101,22 @@ static bool fix_fields_part_func(THD *thd, Item* func_expr, TABLE *table,
This is a tricky call to prepare for since it can have a large number
of interesting side effects, both desirable and undesirable.
*/
- saved_full_group_by_flag= thd->lex->current_select->full_group_by_flag;
- saved_allow_sum_func= thd->lex->allow_sum_func;
- thd->lex->allow_sum_func= 0;
-
- error= func_expr->fix_fields(thd, (Item**)&func_expr);
+ {
+ const bool save_agg_field= thd->lex->current_select->non_agg_field_used();
+ const bool save_agg_func= thd->lex->current_select->agg_func_used();
+ const nesting_map saved_allow_sum_func= thd->lex->allow_sum_func;
+ thd->lex->allow_sum_func= 0;
- /*
- Restore full_group_by_flag and allow_sum_func,
- fix_fields should not affect mysql_select later, see Bug#46923.
- */
- thd->lex->current_select->full_group_by_flag= saved_full_group_by_flag;
- thd->lex->allow_sum_func= saved_allow_sum_func;
+ error= func_expr->fix_fields(thd, (Item**)&func_expr);
+ /*
+ Restore agg_field/agg_func and allow_sum_func,
+ fix_fields should not affect mysql_select later, see Bug#46923.
+ */
+ thd->lex->current_select->set_non_agg_field_used(save_agg_field);
+ thd->lex->current_select->set_agg_func_used(save_agg_func);
+ thd->lex->allow_sum_func= saved_allow_sum_func;
+ }
if (unlikely(error))
{
DBUG_PRINT("info", ("Field in partition function not part of table"));
diff --git a/sql/sql_priv.h b/sql/sql_priv.h
index 434dfb1e306..da7f3f6f116 100644
--- a/sql/sql_priv.h
+++ b/sql/sql_priv.h
@@ -261,13 +261,6 @@ enum enum_yes_no_unknown
#ifdef MYSQL_SERVER
/*
- 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
-
-/*
External variables
*/
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3173b6c1a0e..28981e719b8 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -455,19 +455,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,
@@ -673,7 +672,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