Skip to content

Commit f86c580

Browse files
author
Tor Didriksen
committed
Bug#12329653 - EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY
The query was re-written *after* we had tagged it with NON_AGG_FIELD_USED. Remove the flag before continuing. --BZR-- revision-id: [email protected] property-branch-nick: 5.0-security property-file-info: ld7:file_id67:sp1f-explain.result-20001228015633-fcck4ixyixae4yjfpahxubumufcrdc7p7:message31:Update test case for Bug#48295.4:path27:mysql-test/r/explain.resulted7:file_id69:sp1f-subselect.result-20020512204640-zgegcsgavnfd7t7eyrf7ibuqomsw7uzo7:message14:New test case.4:path29:mysql-test/r/subselect.resulted7:file_id65:sp1f-explain.test-20001228015635-wk7l25cmz54vfufovxkip3auyxz2s36e7:message31:Update test case for Bug#48295.4:path25:mysql-test/t/explain.tested7:file_id67:sp1f-subselect.test-20020512204640-lyqrayx6uwsn7zih6y7kerkenuitzbvr7:message14:New test case.4:path27:mysql-test/t/subselect.tested7:file_id60:sp1f-item.cc-19700101030959-u7hxqopwpfly4kf5ctlyk2dvrq4l3dhn7:message60:Use accessor functions for non_agg_field_used/agg_func_used.4:path11:sql/item.cced7:file_id70:sp1f-item_subselect.cc-20020512204640-qep43aqhsfrwkqmrobni6czc3fqj36oo7:message82:Remove non_agg_field_used when we rewrite query '1 < some (...)' => '1 < max(...)'4:path21:sql/item_subselect.cced7:file_id64:sp1f-item_sum.cc-19700101030959-4woo23bi3am2t2zvsddqbpxk7xbttdkm7:message60:Use accessor functions for non_agg_field_used/agg_func_used.4:path15:sql/item_sum.cced7:file_id65:sp1f-mysql_priv.h-19700101030959-4fl65tqpop5zfgxaxkqotu2fa2ree5ci7:message23:Remove unused #defines.4:path16:sql/mysql_priv.hed7:file_id63:sp1f-sql_lex.cc-19700101030959-4pizwlu5rqkti27gcwsvxkawq6bc2kph7:message33:Initialize new member variables. property-file-info: 4:path14:sql/sql_lex.cced7:file_id62:sp1f-sql_lex.h-19700101030959-sgldb2sooc7twtw5q7pgjx7qzqiaa3sn7:message97:Replace full_group_by_flag with two boolean flags, property-file-info: and itroduce accessors for manipulating them. property-file-info: 4:path13:sql/sql_lex.hed7:file_id66:sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb7:message60:Use accessor functions for non_agg_field_used/agg_func_used.4:path17:sql/sql_select.ccee testament3-sha1: e03c31c16d2a8cf738b2b946830e0e46e6b22fd8
1 parent 5520a29 commit f86c580

11 files changed

Lines changed: 110 additions & 38 deletions

File tree

‎mysql-test/r/explain.result‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -176,11 +176,12 @@ SELECT @@session.sql_mode INTO @old_sql_mode;
176176
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
177177
EXPLAIN EXTENDED SELECT 1 FROM t1
178178
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
179-
ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
180-
SHOW WARNINGS;
181-
Level Code Message
182-
Error 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
183-
Note 1003 select 1 AS `1` from `test`.`t1` where <not>(<exists>(...))
179+
id select_type table type possible_keys key key_len ref rows Extra
180+
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
181+
2 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
182+
2 SUBQUERY t system NULL NULL NULL NULL 0 const row not found
183+
Warnings:
184+
Note 1003 select 1 AS `1` from `test`.`t1` where 0
184185
SET SESSION sql_mode=@old_sql_mode;
185186
DROP TABLE t1;
186187
End of 5.0 tests.

‎mysql-test/r/subselect.result‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4528,6 +4528,32 @@ pk int_key
45284528
7 3
45294529
DROP TABLE t1,t2;
45304530
#
4531+
# Bug#12329653
4532+
# EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY
4533+
#
4534+
CREATE TABLE t1(a1 int);
4535+
INSERT INTO t1 VALUES (1),(2);
4536+
SELECT @@session.sql_mode INTO @old_sql_mode;
4537+
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
4538+
SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
4539+
1
4540+
1
4541+
1
4542+
PREPARE stmt FROM
4543+
'SELECT 1 UNION ALL
4544+
SELECT 1 FROM t1
4545+
ORDER BY
4546+
(SELECT 1 FROM t1 AS t1_0
4547+
WHERE 1 < SOME (SELECT a1 FROM t1)
4548+
)' ;
4549+
EXECUTE stmt ;
4550+
ERROR 21000: Subquery returns more than 1 row
4551+
EXECUTE stmt ;
4552+
ERROR 21000: Subquery returns more than 1 row
4553+
SET SESSION sql_mode=@old_sql_mode;
4554+
DEALLOCATE PREPARE stmt;
4555+
DROP TABLE t1;
4556+
#
45314557
# Bug #52711: Segfault when doing EXPLAIN SELECT with
45324558
# union...order by (select... where...)
45334559
#

‎mysql-test/t/explain.test‎

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
#
2-
# Test of different EXPLAIN's
2+
# Test of different EXPLAINs
33

44
--disable_warnings
55
drop table if exists t1;
@@ -157,11 +157,12 @@ CREATE TABLE t1 (f1 INT);
157157
SELECT @@session.sql_mode INTO @old_sql_mode;
158158
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
159159

160-
# EXPLAIN EXTENDED (with subselect). used to crash. should give NOTICE.
161-
--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
160+
# EXPLAIN EXTENDED (with subselect). used to crash.
161+
# This is actually a valid query for this sql_mode,
162+
# but it was transformed in such a way that it failed, see
163+
# Bug#12329653 - EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY
162164
EXPLAIN EXTENDED SELECT 1 FROM t1
163165
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
164-
SHOW WARNINGS;
165166

166167
SET SESSION sql_mode=@old_sql_mode;
167168

‎mysql-test/t/subselect.test‎

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3506,6 +3506,40 @@ ORDER BY outr.pk;
35063506

35073507
DROP TABLE t1,t2;
35083508

3509+
--echo #
3510+
--echo # Bug#12329653
3511+
--echo # EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY
3512+
--echo #
3513+
3514+
CREATE TABLE t1(a1 int);
3515+
INSERT INTO t1 VALUES (1),(2);
3516+
3517+
SELECT @@session.sql_mode INTO @old_sql_mode;
3518+
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
3519+
3520+
## First a simpler query, illustrating the transformation
3521+
## '1 < some (...)' => '1 < max(...)'
3522+
SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
3523+
3524+
## The query which made the server crash.
3525+
PREPARE stmt FROM
3526+
'SELECT 1 UNION ALL
3527+
SELECT 1 FROM t1
3528+
ORDER BY
3529+
(SELECT 1 FROM t1 AS t1_0
3530+
WHERE 1 < SOME (SELECT a1 FROM t1)
3531+
)' ;
3532+
3533+
--error ER_SUBQUERY_NO_1_ROW
3534+
EXECUTE stmt ;
3535+
--error ER_SUBQUERY_NO_1_ROW
3536+
EXECUTE stmt ;
3537+
3538+
SET SESSION sql_mode=@old_sql_mode;
3539+
3540+
DEALLOCATE PREPARE stmt;
3541+
DROP TABLE t1;
3542+
35093543
--echo #
35103544
--echo # Bug #52711: Segfault when doing EXPLAIN SELECT with
35113545
--echo # union...order by (select... where...)

‎sql/item.cc‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4080,14 +4080,14 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
40804080
aggregated or not.
40814081
*/
40824082
if (!thd->lex->in_sum_func)
4083-
cached_table->select_lex->full_group_by_flag|= NON_AGG_FIELD_USED;
4083+
cached_table->select_lex->set_non_agg_field_used(true);
40844084
else
40854085
{
40864086
if (outer_fixed)
40874087
thd->lex->in_sum_func->outer_fields.push_back(this);
40884088
else if (thd->lex->in_sum_func->nest_level !=
40894089
thd->lex->current_select->nest_level)
4090-
cached_table->select_lex->full_group_by_flag|= NON_AGG_FIELD_USED;
4090+
cached_table->select_lex->set_non_agg_field_used(true);
40914091
}
40924092
}
40934093
return FALSE;

‎sql/item_subselect.cc‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -936,6 +936,14 @@ Item_in_subselect::single_value_transformer(JOIN *join,
936936
it.replace(item);
937937
}
938938

939+
DBUG_EXECUTE("where",
940+
print_where(item, "rewrite with MIN/MAX"););
941+
if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
942+
{
943+
DBUG_ASSERT(select_lex->non_agg_field_used());
944+
select_lex->set_non_agg_field_used(false);
945+
}
946+
939947
save_allow_sum_func= thd->lex->allow_sum_func;
940948
thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
941949
/*

‎sql/item_sum.cc‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -246,10 +246,10 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
246246
in_sum_func->outer_fields.push_back(field);
247247
}
248248
else
249-
sel->full_group_by_flag|= NON_AGG_FIELD_USED;
249+
sel->set_non_agg_field_used(true);
250250
}
251251
if (sel->nest_level > aggr_level &&
252-
(sel->full_group_by_flag & SUM_FUNC_USED) &&
252+
(sel->agg_func_used()) &&
253253
!sel->group_list.elements)
254254
{
255255
my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
@@ -258,7 +258,7 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
258258
}
259259
}
260260
}
261-
aggr_sel->full_group_by_flag|= SUM_FUNC_USED;
261+
aggr_sel->set_agg_func_used(true);
262262
update_used_tables();
263263
thd->lex->in_sum_func= in_sum_func;
264264
return FALSE;

‎sql/mysql_priv.h‎

Lines changed: 0 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1086,13 +1086,6 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables,
10861086
bool allow_null_cond, int *error);
10871087
extern Item **not_found_item;
10881088

1089-
/*
1090-
A set of constants used for checking non aggregated fields and sum
1091-
functions mixture in the ONLY_FULL_GROUP_BY_MODE.
1092-
*/
1093-
#define NON_AGG_FIELD_USED 1
1094-
#define SUM_FUNC_USED 2
1095-
10961089
/*
10971090
This enumeration type is used only by the function find_item_in_list
10981091
to return the info on how an item has been resolved against a list

‎sql/sql_lex.cc‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1232,6 +1232,8 @@ void st_select_lex::init_query()
12321232
exclude_from_table_unique_test= no_wrap_view_item= FALSE;
12331233
nest_level= 0;
12341234
link_next= 0;
1235+
m_non_agg_field_used= false;
1236+
m_agg_func_used= false;
12351237
}
12361238

12371239
void st_select_lex::init_select()
@@ -1266,7 +1268,8 @@ void st_select_lex::init_select()
12661268
non_agg_fields.empty();
12671269
cond_value= having_value= Item::COND_UNDEF;
12681270
inner_refs_list.empty();
1269-
full_group_by_flag= 0;
1271+
m_non_agg_field_used= false;
1272+
m_agg_func_used= false;
12701273
}
12711274

12721275
/*

‎sql/sql_lex.h‎

Lines changed: 16 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -617,16 +617,7 @@ class st_select_lex: public st_select_lex_node
617617
joins on the right.
618618
*/
619619
List<String> *prev_join_using;
620-
/*
621-
Bitmap used in the ONLY_FULL_GROUP_BY_MODE to prevent mixture of aggregate
622-
functions and non aggregated fields when GROUP BY list is absent.
623-
Bits:
624-
0 - non aggregated fields are used in this select,
625-
defined as NON_AGG_FIELD_USED.
626-
1 - aggregate functions are used in this select,
627-
defined as SUM_FUNC_USED.
628-
*/
629-
uint8 full_group_by_flag;
620+
630621
void init_query();
631622
void init_select();
632623
st_select_lex_unit* master_unit();
@@ -714,6 +705,21 @@ class st_select_lex: public st_select_lex_node
714705
select lexes.
715706
*/
716707
void cleanup_all_joins(bool full);
708+
/*
709+
For MODE_ONLY_FULL_GROUP_BY we need to maintain two flags:
710+
- Non-aggregated fields are used in this select.
711+
- Aggregate functions are used in this select.
712+
In MODE_ONLY_FULL_GROUP_BY only one of these may be true.
713+
*/
714+
bool non_agg_field_used() const { return m_non_agg_field_used; }
715+
bool agg_func_used() const { return m_agg_func_used; }
716+
717+
void set_non_agg_field_used(bool val) { m_non_agg_field_used= val; }
718+
void set_agg_func_used(bool val) { m_agg_func_used= val; }
719+
720+
private:
721+
bool m_non_agg_field_used;
722+
bool m_agg_func_used;
717723
};
718724
typedef class st_select_lex SELECT_LEX;
719725

0 commit comments

Comments
 (0)