summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/group_by.result146
-rw-r--r--mysql-test/r/temp_table.result11
-rw-r--r--mysql-test/t/group_by.test117
-rw-r--r--mysql-test/t/temp_table.test14
-rw-r--r--sql/item.cc28
-rw-r--r--sql/item_subselect.cc14
-rw-r--r--sql/item_sum.cc64
-rw-r--r--sql/item_sum.h7
-rw-r--r--sql/mysql_priv.h7
-rw-r--r--sql/sql_insert.cc25
-rw-r--r--sql/sql_lex.cc3
-rw-r--r--sql/sql_lex.h10
-rw-r--r--sql/sql_select.cc34
-rw-r--r--storage/myisam/mi_delete_all.c11
14 files changed, 445 insertions, 46 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 268f290ddca..30c4d1e4ca1 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1128,6 +1128,152 @@ id c1 c2
4 2 3
1 5 1
DROP TABLE t1;
+#
+# Bug#27219: Aggregate functions in ORDER BY.
+#
+SET @save_sql_mode=@@sql_mode;
+SET @@sql_mode='ONLY_FULL_GROUP_BY';
+CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
+INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
+CREATE TABLE t2 SELECT * FROM t1;
+SELECT 1 FROM t1 ORDER BY COUNT(*);
+1
+1
+SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
+1
+1
+SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
+1
+1
+SELECT 1 FROM t1 ORDER BY COUNT(*), a;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT 1 FROM t1 ORDER BY SUM(a);
+1
+1
+SELECT 1 FROM t1 ORDER BY SUM(a + 1);
+1
+1
+SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
+1
+1
+SELECT 1 FROM t1 ORDER BY SUM(a), b;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT a FROM t1 ORDER BY COUNT(b);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
+a
+3
+2
+3
+2
+3
+4
+SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a), t2.a FROM t2);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1
+WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
+a
+2
+3
+4
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1
+WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT 1 FROM t1 GROUP BY t1.a
+HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
+1
+1
+1
+1
+SELECT 1 FROM t1 GROUP BY t1.a
+HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
+1
+1
+1
+1
+SELECT 1 FROM t1 GROUP BY t1.a
+HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
+1
+1
+1
+1
+SELECT 1 FROM t1 GROUP BY t1.a
+HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT 1 FROM t1 GROUP BY t1.a
+HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT 1 FROM t1 GROUP BY t1.a
+HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT t1.a FROM t1
+WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
+ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
+a
+4
+SELECT t1.a, SUM(t1.b) FROM t1
+WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
+ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
+GROUP BY t1.a;
+a SUM(t1.b)
+4 4
+SELECT t1.a, SUM(t1.b) FROM t1
+WHERE t1.a = (SELECT SUM(t2.b) FROM t2
+ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
+GROUP BY t1.a;
+a SUM(t1.b)
+SELECT t1.a, SUM(t1.b) FROM t1
+WHERE t1.a = (SELECT SUM(t2.b) FROM t2
+ORDER BY SUM(t2.b + t1.a) LIMIT 1)
+GROUP BY t1.a;
+a SUM(t1.b)
+SELECT t1.a FROM t1 GROUP BY t1.a
+HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
+a
+select avg (
+(select
+(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
+from t1 as outr order by outr.a limit 1))
+from t1 as most_outer;
+avg (
+(select
+(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
+from t1 as outr order by outr.a limit 1))
+29.0000
+select avg (
+(select (
+(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
+from t1 as outr order by count(outr.a) limit 1)) as tt
+from t1 as most_outer;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
+tt
+29
+29
+35
+35
+35
+41
+SET sql_mode=@save_sql_mode;
+DROP TABLE t1, t2;
End of 5.0 tests
CREATE TABLE t1 (a INT, b INT,
PRIMARY KEY (a),
diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result
index 1c846700346..6df09463d02 100644
--- a/mysql-test/r/temp_table.result
+++ b/mysql-test/r/temp_table.result
@@ -184,3 +184,14 @@ select * from t1;
a
42
drop table t1;
+CREATE TEMPORARY TABLE t1(a INT, b VARCHAR(20));
+INSERT INTO t1 VALUES(1, 'val1'), (2, 'val2'), (3, 'val3');
+DELETE FROM t1 WHERE a=1;
+SELECT count(*) FROM t1;
+count(*)
+2
+DELETE FROM t1;
+SELECT * FROM t1;
+a b
+DROP TABLE t1;
+End of 5.1 tests
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 3211db5d6ed..d02e8e10a2b 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -823,6 +823,123 @@ SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
DROP TABLE t1;
+--echo #
+--echo # Bug#27219: Aggregate functions in ORDER BY.
+--echo #
+SET @save_sql_mode=@@sql_mode;
+SET @@sql_mode='ONLY_FULL_GROUP_BY';
+
+CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
+INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
+CREATE TABLE t2 SELECT * FROM t1;
+
+SELECT 1 FROM t1 ORDER BY COUNT(*);
+SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
+--error 1140
+SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
+SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
+--error 1140
+SELECT 1 FROM t1 ORDER BY COUNT(*), a;
+
+SELECT 1 FROM t1 ORDER BY SUM(a);
+SELECT 1 FROM t1 ORDER BY SUM(a + 1);
+SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
+--error 1140
+SELECT 1 FROM t1 ORDER BY SUM(a), b;
+
+--error 1140
+SELECT a FROM t1 ORDER BY COUNT(b);
+
+SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
+
+--error 1140
+SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a), t2.a FROM t2);
+--error 1140
+SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
+--error 1140
+SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
+
+--error 1140
+SELECT t1.a FROM t1
+ WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
+--error 1140
+SELECT t1.a FROM t1 GROUP BY t1.a
+ HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
+
+SELECT t1.a FROM t1 GROUP BY t1.a
+ HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
+--error 1140
+SELECT t1.a FROM t1 GROUP BY t1.a
+ HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
+--error 1140
+SELECT t1.a FROM t1 GROUP BY t1.a
+ HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
+
+--error 1140
+SELECT t1.a FROM t1
+ WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
+
+SELECT 1 FROM t1 GROUP BY t1.a
+ HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
+SELECT 1 FROM t1 GROUP BY t1.a
+ HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
+SELECT 1 FROM t1 GROUP BY t1.a
+ HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
+
+--error 1140
+SELECT 1 FROM t1 GROUP BY t1.a
+ HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
+--error 1140
+SELECT 1 FROM t1 GROUP BY t1.a
+ HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
+--error 1140
+SELECT 1 FROM t1 GROUP BY t1.a
+ HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
+
+# Both SUMs are aggregated in the subquery, no mixture:
+SELECT t1.a FROM t1
+ WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
+ ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
+
+# SUM(t1.b) is aggregated in the subquery, no mixture:
+SELECT t1.a, SUM(t1.b) FROM t1
+ WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
+ ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
+ GROUP BY t1.a;
+
+# 2nd SUM(t1.b) is aggregated in the subquery, no mixture:
+SELECT t1.a, SUM(t1.b) FROM t1
+ WHERE t1.a = (SELECT SUM(t2.b) FROM t2
+ ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
+ GROUP BY t1.a;
+
+# SUM(t2.b + t1.a) is aggregated in the subquery, no mixture:
+SELECT t1.a, SUM(t1.b) FROM t1
+ WHERE t1.a = (SELECT SUM(t2.b) FROM t2
+ ORDER BY SUM(t2.b + t1.a) LIMIT 1)
+ GROUP BY t1.a;
+
+SELECT t1.a FROM t1 GROUP BY t1.a
+ HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
+
+select avg (
+ (select
+ (select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
+ from t1 as outr order by outr.a limit 1))
+from t1 as most_outer;
+
+--error 1140
+select avg (
+ (select (
+ (select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
+ from t1 as outr order by count(outr.a) limit 1)) as tt
+from t1 as most_outer;
+
+select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
+
+SET sql_mode=@save_sql_mode;
+DROP TABLE t1, t2;
+
--echo End of 5.0 tests
# Bug #21174: Index degrades sort performance and
# optimizer does not honor IGNORE INDEX.
diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test
index d1ad7ab8ffa..4ab8a982e63 100644
--- a/mysql-test/t/temp_table.test
+++ b/mysql-test/t/temp_table.test
@@ -191,3 +191,17 @@ truncate t1;
insert into t1 values (42);
select * from t1;
drop table t1;
+
+#
+# Bug #35392: Delete all statement does not execute properly after
+# few delete statements
+#
+CREATE TEMPORARY TABLE t1(a INT, b VARCHAR(20));
+INSERT INTO t1 VALUES(1, 'val1'), (2, 'val2'), (3, 'val3');
+DELETE FROM t1 WHERE a=1;
+SELECT count(*) FROM t1;
+DELETE FROM t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo End of 5.1 tests
diff --git a/sql/item.cc b/sql/item.cc
index 883c293f645..fb8b0c1de2b 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4003,9 +4003,9 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
}
if ((ret= fix_outer_field(thd, &from_field, reference)) < 0)
goto error;
- else if (!ret)
- return FALSE;
outer_fixed= TRUE;
+ if (!ret)
+ goto mark_non_agg_field;
}
else if (!from_field)
goto error;
@@ -4017,9 +4017,9 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
int ret;
if ((ret= fix_outer_field(thd, &from_field, reference)) < 0)
goto error;
- if (!ret)
- return FALSE;
outer_fixed= 1;
+ if (!ret)
+ goto mark_non_agg_field;
}
/*
@@ -4103,6 +4103,26 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
thd->lex->current_select->non_agg_fields.push_back(this);
marker= thd->lex->current_select->cur_pos_in_select_list;
}
+mark_non_agg_field:
+ if (fixed && thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
+ {
+ /*
+ Mark selects according to presence of non aggregated fields.
+ Fields from outer selects added to the aggregate function
+ outer_fields list as its unknown at the moment whether it's
+ aggregated or not.
+ */
+ if (!thd->lex->in_sum_func)
+ cached_table->select_lex->full_group_by_flag|= NON_AGG_FIELD_USED;
+ 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;
+ }
+ }
return FALSE;
error:
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 0ccadaf28da..ea16f3c3518 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1498,6 +1498,19 @@ Item_in_subselect::select_in_like_transformer(JOIN *join, Comp_creator *func)
DBUG_ENTER("Item_in_subselect::select_in_like_transformer");
+ {
+ /*
+ IN/SOME/ALL/ANY subqueries aren't support LIMIT clause. Without it
+ ORDER BY clause becomes meaningless thus we drop it here.
+ */
+ SELECT_LEX *sl= current->master_unit()->first_select();
+ for (; sl; sl= sl->next_select())
+ {
+ if (sl->join)
+ sl->join->order= 0;
+ }
+ }
+
if (changed)
{
DBUG_RETURN(RES_OK);
@@ -1532,6 +1545,7 @@ Item_in_subselect::select_in_like_transformer(JOIN *join, Comp_creator *func)
transformed= 1;
arena= thd->activate_stmt_arena_if_needed(&backup);
+
/*
Both transformers call fix_fields() only for Items created inside them,
and all that items do not make permanent changes in current item arena
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index ce9e2ad906c..264b53c780a 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -68,6 +68,7 @@ bool Item_sum::init_sum_func_check(THD *thd)
aggr_sel= NULL;
max_arg_level= -1;
max_sum_func_level= -1;
+ outer_fields.empty();
return FALSE;
}
@@ -176,6 +177,7 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
MYF(0));
return TRUE;
}
+
if (in_sum_func)
{
/*
@@ -196,6 +198,68 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
set_if_bigger(in_sum_func->max_sum_func_level, aggr_level);
set_if_bigger(in_sum_func->max_sum_func_level, max_sum_func_level);
}
+
+ /*
+ Check that non-aggregated fields and sum functions aren't mixed in the
+ same select in the ONLY_FULL_GROUP_BY mode.
+ */
+ if (outer_fields.elements)
+ {
+ Item_field *field;
+ /*
+ Here we compare the nesting level of the select to which an outer field
+ belongs to with the aggregation level of the sum function. All fields in
+ the outer_fields list are checked.
+
+ If the nesting level is equal to the aggregation level then the field is
+ aggregated by this sum function.
+ If the nesting level is less than the aggregation level then the field
+ belongs to an outer select. In this case if there is an embedding sum
+ function add current field to functions outer_fields list. If there is
+ no embedding function then the current field treated as non aggregated
+ and the select it belongs to is marked accordingly.
+ If the nesting level is greater than the aggregation level then it means
+ that this field was added by an inner sum function.
+ Consider an example:
+
+ select avg ( <-- we are here, checking outer.f1
+ select (
+ select sum(outer.f1 + inner.f1) from inner
+ ) from outer)
+ from most_outer;
+
+ In this case we check that no aggregate functions are used in the
+ select the field belongs to. If there are some then an error is
+ raised.
+ */
+ List_iterator<Item_field> of(outer_fields);
+ while ((field= of++))
+ {
+ SELECT_LEX *sel= field->cached_table->select_lex;
+ if (sel->nest_level < aggr_level)
+ {
+ if (in_sum_func)
+ {
+ /*
+ Let upper function decide whether this field is a non
+ aggregated one.
+ */
+ in_sum_func->outer_fields.push_back(field);
+ }
+ else
+ sel->full_group_by_flag|= NON_AGG_FIELD_USED;
+ }
+ if (sel->nest_level > aggr_level &&
+ (sel->full_group_by_flag & SUM_FUNC_USED) &&
+ !sel->group_list.elements)
+ {
+ my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
+ ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
+ return TRUE;
+ }
+ }
+ }
+ aggr_sel->full_group_by_flag|= SUM_FUNC_USED;
update_used_tables();
thd->lex->in_sum_func= in_sum_func;
return FALSE;
diff --git a/sql/item_sum.h b/sql/item_sum.h
index d1e6a74e85e..bee8792fbfa 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -239,6 +239,13 @@ public:
int8 max_arg_level; /* max level of unbound column references */
int8 max_sum_func_level;/* max level of aggregation for embedded functions */
bool quick_group; /* If incremental update of fields */
+ /*
+ This list is used by the check for mixing non aggregated fields and
+ sum functions in the ONLY_FULL_GROUP_BY_MODE. We save all outer fields
+ directly or indirectly used under this function it as it's unclear
+ at the moment of fixing outer field whether it's aggregated or not.
+ */
+ List<Item_field> outer_fields;
protected:
table_map used_tables_cache;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 26f253a6f8e..0791daf76d5 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1407,6 +1407,13 @@ 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_insert.cc b/sql/sql_insert.cc
index d27b9f5bca6..89038bacda3 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -701,8 +701,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
error=0;
thd_proc_info(thd, "update");
- if (duplic != DUP_ERROR || ignore)
- table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
if (duplic == DUP_REPLACE &&
(!table->triggers || !table->triggers->has_delete_triggers()))
table->file->extra(HA_EXTRA_WRITE_CAN_REPLACE);
@@ -720,8 +718,15 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
values_list.elements, and - if nothing else - to initialize
the code to make the call of end_bulk_insert() below safe.
*/
- if (lock_type != TL_WRITE_DELAYED && !thd->prelocked_mode)
- table->file->ha_start_bulk_insert(values_list.elements);
+#ifndef EMBEDDED_LIBRARY
+ if (lock_type != TL_WRITE_DELAYED)
+#endif /* EMBEDDED_LIBRARY */
+ {
+ if (duplic != DUP_ERROR || ignore)
+ table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
+ if (!thd->prelocked_mode)
+ table->file->ha_start_bulk_insert(values_list.elements);
+ }
thd->abort_on_warning= (!ignore && (thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
@@ -840,6 +845,9 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
table->file->print_error(my_errno,MYF(0));
error=1;
}
+ if (duplic != DUP_ERROR || ignore)
+ table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
+
transactional_table= table->file->has_transactions();
if ((changed= (info.copied || info.deleted || info.updated)))
@@ -932,8 +940,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
table->next_number_field=0;
thd->count_cuted_fields= CHECK_FIELD_IGNORE;
table->auto_increment_field_not_null= FALSE;
- if (duplic != DUP_ERROR || ignore)
- table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
if (duplic == DUP_REPLACE &&
(!table->triggers || !table->triggers->has_delete_triggers()))
table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE);
@@ -1267,7 +1273,12 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
select_lex->fix_prepare_information(thd, &fake_conds, &fake_conds);
select_lex->first_execution= 0;
}
- if (duplic == DUP_UPDATE || duplic == DUP_REPLACE)
+ /*
+ Only call prepare_for_posistion() if we are not performing a DELAYED
+ operation. It will instead be executed by delayed insert thread.
+ */
+ if ((duplic == DUP_UPDATE || duplic == DUP_REPLACE) &&
+ (table->reginfo.lock_type != TL_WRITE_DELAYED))
table->prepare_for_position();
DBUG_RETURN(FALSE);
}
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index b07efc62a00..449c2fccb0b 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1601,6 +1601,7 @@ 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;
}
/*
@@ -1836,8 +1837,6 @@ bool st_select_lex::test_limit()
"LIMIT & IN/ALL/ANY/SOME subquery");
return(1);
}
- // no sense in ORDER BY without LIMIT
- order_list.empty();
return(0);
}
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index b1a0d506382..443c85b4854 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -692,6 +692,16 @@ 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();
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3db0898c0ac..8aec8795aa8 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -585,37 +585,13 @@ JOIN::prepare(Item ***rref_pointer_array,
/*
Check if there are references to un-aggregated columns when computing
aggregate functions with implicit grouping (there is no GROUP BY).
- TODO: Add check of calculation of GROUP functions and fields:
- SELECT COUNT(*)+table.col1 from table1;
*/
- if (thd->variables.sql_mode & MODE_ONLY_FULL_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))
{
- if (!group_list)
- {
- uint flag=0;
- List_iterator_fast<Item> it(fields_list);
- Item *item;
- while ((item= it++))
- {
- if (item->with_sum_func)
- flag|=1;
- else if (!(flag & 2) && !item->const_during_execution())
- flag|=2;
- }
- if (having)
- {
- if (having->with_sum_func)
- flag |= 1;
- else if (!having->const_during_execution())
- flag |= 2;
- }
- if (flag == 3)
- {
- my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
- ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
- DBUG_RETURN(-1);
- }
- }
+ my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
+ ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
+ DBUG_RETURN(-1);
}
{
/* Caclulate the number of groups */
diff --git a/storage/myisam/mi_delete_all.c b/storage/myisam/mi_delete_all.c
index dea0385cbca..e2bbb04ab3c 100644
--- a/storage/myisam/mi_delete_all.c
+++ b/storage/myisam/mi_delete_all.c
@@ -53,15 +53,18 @@ int mi_delete_all_rows(MI_INFO *info)
since it was locked then there may be key blocks in the key cache
*/
flush_key_blocks(share->key_cache, share->kfile, FLUSH_IGNORE_CHANGED);
+#ifdef HAVE_MMAP
+ if (share->file_map)
+ _mi_unmap_file(info);
+#endif
if (my_chsize(info->dfile, 0, 0, MYF(MY_WME)) ||
my_chsize(share->kfile, share->base.keystart, 0, MYF(MY_WME)) )
goto err;
VOID(_mi_writeinfo(info,WRITEINFO_UPDATE_KEYFILE));
#ifdef HAVE_MMAP
- /* Resize mmaped area */
- rw_wrlock(&info->s->mmap_lock);
- mi_remap_file(info, (my_off_t)0);
- rw_unlock(&info->s->mmap_lock);
+ /* Map again */
+ if (share->file_map)
+ mi_dynmap_file(info, (my_off_t) 0);
#endif
allow_break(); /* Allow SIGHUP & SIGINT */
DBUG_RETURN(0);
View Lorry Controller Status