Skip to content

Commit 98b2ccb

Browse files
roylysengdahlerlend
roylyseng
authored and
dahlerlend
committed
Bug#32717969: Wrong result of execute prepare on 'smallint'
This problem may occur with prepared implicitly grouped SELECT statements. When the WHERE clause is determined to be always false, e.g. due to the actual parameter values, the result of some aggregate functions may be picked up from the previous execution. The reason for this is that the aggregator object for such functions is not set up, instead the no_rows_in_result() function should be called and set the null_value flag (at least when the aggregate function is nullable). After inspection, it was observed that the SUM and the GROUP_CONCAT functions missed the necessary no_rows_in_result() implementations. These have now been implemented to call the clear() function, which will set the null_value for the function result. It was also detected that the NTILE function was always made nullable, even though it can never return NULL unless the argument is NULL. This problem was also fixed. Reviewed by: Chaithra Gopala Reddy <chaithra.gopalareddy@oracle.com> Change-Id: Ied8ab405a3afa520a23dd95d106f4ba7312a6f02
1 parent 45f2f3b commit 98b2ccb

File tree

4 files changed

+118
-4
lines changed

4 files changed

+118
-4
lines changed

‎mysql-test/r/group_by.result

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4156,3 +4156,69 @@ FROM t1
41564156
)
41574157
0
41584158
DROP TABLE t1;
4159+
# Bug#32717969: Wrong result of execute prepare on 'smallint'
4160+
CREATE TABLE t1(
4161+
c1 smallint NOT NULL
4162+
);
4163+
INSERT INTO t1 VALUES(32767),(14742),(14743);
4164+
SELECT COUNT(*), SUM(c1), AVG(c1), MIN(c1), MAX(c1) FROM t1 WHERE c1 > 32767;
4165+
COUNT(*) SUM(c1) AVG(c1) MIN(c1) MAX(c1)
4166+
0 NULL NULL NULL NULL
4167+
PREPARE stmt from
4168+
'SELECT COUNT(*), SUM(c1), AVG(c1), MIN(c1), MAX(c1) FROM t1 WHERE c1 > ?';
4169+
SET @a=14742;
4170+
EXECUTE stmt USING @a;
4171+
COUNT(*) SUM(c1) AVG(c1) MIN(c1) MAX(c1)
4172+
2 47510 23755.0000 14743 32767
4173+
set @a=32767;
4174+
EXECUTE stmt USING @a;
4175+
COUNT(*) SUM(c1) AVG(c1) MIN(c1) MAX(c1)
4176+
0 NULL NULL NULL NULL
4177+
SELECT BIT_AND(c1), BIT_OR(c1), BIT_XOR(c1) FROM t1 WHERE c1 > 32767;
4178+
BIT_AND(c1) BIT_OR(c1) BIT_XOR(c1)
4179+
18446744073709551615 0 0
4180+
PREPARE stmt from
4181+
'SELECT BIT_AND(c1), BIT_OR(c1), BIT_XOR(c1) FROM t1 WHERE c1 > ?';
4182+
SET @a=14742;
4183+
EXECUTE stmt USING @a;
4184+
BIT_AND(c1) BIT_OR(c1) BIT_XOR(c1)
4185+
14743 32767 18024
4186+
set @a=32767;
4187+
EXECUTE stmt USING @a;
4188+
BIT_AND(c1) BIT_OR(c1) BIT_XOR(c1)
4189+
18446744073709551615 0 0
4190+
SELECT GROUP_CONCAT(c1), JSON_ARRAYAGG(c1), JSON_OBJECTAGG('key', c1)
4191+
FROM t1
4192+
WHERE c1 > 32767;
4193+
GROUP_CONCAT(c1) JSON_ARRAYAGG(c1) JSON_OBJECTAGG('key', c1)
4194+
NULL NULL NULL
4195+
PREPARE stmt from
4196+
"SELECT GROUP_CONCAT(c1), JSON_ARRAYAGG(c1), JSON_OBJECTAGG('key', c1)
4197+
FROM t1
4198+
WHERE c1 > ?";
4199+
SET @a=14742;
4200+
EXECUTE stmt USING @a;
4201+
GROUP_CONCAT(c1) JSON_ARRAYAGG(c1) JSON_OBJECTAGG('key', c1)
4202+
32767,14743 [32767, 14743] {"key": 14743}
4203+
set @a=32767;
4204+
EXECUTE stmt USING @a;
4205+
GROUP_CONCAT(c1) JSON_ARRAYAGG(c1) JSON_OBJECTAGG('key', c1)
4206+
NULL NULL NULL
4207+
SELECT STDDEV_POP(c1), STDDEV_SAMP(c1), VAR_POP(c1), VAR_SAMP(c1)
4208+
FROM t1
4209+
WHERE c1 > 32767;
4210+
STDDEV_POP(c1) STDDEV_SAMP(c1) VAR_POP(c1) VAR_SAMP(c1)
4211+
NULL NULL NULL NULL
4212+
PREPARE stmt from
4213+
'SELECT STDDEV_POP(c1), STDDEV_SAMP(c1), VAR_POP(c1), VAR_SAMP(c1)
4214+
FROM t1
4215+
WHERE c1 > ?';
4216+
SET @a=14742;
4217+
EXECUTE stmt USING @a;
4218+
STDDEV_POP(c1) STDDEV_SAMP(c1) VAR_POP(c1) VAR_SAMP(c1)
4219+
9012 12744.892624106333 81216144 162432288
4220+
set @a=32767;
4221+
EXECUTE stmt USING @a;
4222+
STDDEV_POP(c1) STDDEV_SAMP(c1) VAR_POP(c1) VAR_SAMP(c1)
4223+
NULL NULL NULL NULL
4224+
DROP TABLE t1;

‎mysql-test/t/group_by.test

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3135,3 +3135,47 @@ SELECT
31353135
FROM (SELECT 555 AS f2) AS c;
31363136

31373137
DROP TABLE t1;
3138+
3139+
--echo # Bug#32717969: Wrong result of execute prepare on 'smallint'
3140+
3141+
CREATE TABLE t1(
3142+
c1 smallint NOT NULL
3143+
);
3144+
INSERT INTO t1 VALUES(32767),(14742),(14743);
3145+
SELECT COUNT(*), SUM(c1), AVG(c1), MIN(c1), MAX(c1) FROM t1 WHERE c1 > 32767;
3146+
PREPARE stmt from
3147+
'SELECT COUNT(*), SUM(c1), AVG(c1), MIN(c1), MAX(c1) FROM t1 WHERE c1 > ?';
3148+
SET @a=14742;
3149+
EXECUTE stmt USING @a;
3150+
set @a=32767;
3151+
EXECUTE stmt USING @a;
3152+
SELECT BIT_AND(c1), BIT_OR(c1), BIT_XOR(c1) FROM t1 WHERE c1 > 32767;
3153+
PREPARE stmt from
3154+
'SELECT BIT_AND(c1), BIT_OR(c1), BIT_XOR(c1) FROM t1 WHERE c1 > ?';
3155+
SET @a=14742;
3156+
EXECUTE stmt USING @a;
3157+
set @a=32767;
3158+
EXECUTE stmt USING @a;
3159+
SELECT GROUP_CONCAT(c1), JSON_ARRAYAGG(c1), JSON_OBJECTAGG('key', c1)
3160+
FROM t1
3161+
WHERE c1 > 32767;
3162+
PREPARE stmt from
3163+
"SELECT GROUP_CONCAT(c1), JSON_ARRAYAGG(c1), JSON_OBJECTAGG('key', c1)
3164+
FROM t1
3165+
WHERE c1 > ?";
3166+
SET @a=14742;
3167+
EXECUTE stmt USING @a;
3168+
set @a=32767;
3169+
EXECUTE stmt USING @a;
3170+
SELECT STDDEV_POP(c1), STDDEV_SAMP(c1), VAR_POP(c1), VAR_SAMP(c1)
3171+
FROM t1
3172+
WHERE c1 > 32767;
3173+
PREPARE stmt from
3174+
'SELECT STDDEV_POP(c1), STDDEV_SAMP(c1), VAR_POP(c1), VAR_SAMP(c1)
3175+
FROM t1
3176+
WHERE c1 > ?';
3177+
SET @a=14742;
3178+
EXECUTE stmt USING @a;
3179+
set @a=32767;
3180+
EXECUTE stmt USING @a;
3181+
DROP TABLE t1;

‎sql/item_sum.cc

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1815,6 +1815,8 @@ void Item_sum_sum::clear() {
18151815
m_frame_null_count = 0;
18161816
}
18171817

1818+
void Item_sum_sum::no_rows_in_result() { clear(); }
1819+
18181820
bool Item_sum_sum::resolve_type(THD *thd) {
18191821
DBUG_TRACE;
18201822
if (param_type_is_default(thd, 0, 1, MYSQL_TYPE_DOUBLE)) return true;
@@ -4251,6 +4253,8 @@ Item *Item_func_group_concat::copy_or_same(THD *thd) {
42514253
return result;
42524254
}
42534255

4256+
void Item_func_group_concat::no_rows_in_result() { clear(); }
4257+
42544258
void Item_func_group_concat::clear() {
42554259
result.length(0);
42564260
result.copy();
@@ -4389,6 +4393,8 @@ bool Item_func_group_concat::fix_fields(THD *thd, Item **ref) {
43894393
&fields, order_array.begin()))
43904394
return true;
43914395

4396+
null_value = true;
4397+
43924398
fixed = true;
43934399

43944400
return false;
@@ -4865,7 +4871,6 @@ bool Item_nth_value::check_wf_semantics2(Window_evaluation_requirements *r) {
48654871
bool Item_ntile::fix_fields(THD *thd, Item **items) {
48664872
if (super::fix_fields(thd, items)) return true;
48674873

4868-
set_nullable(true);
48694874
return false;
48704875
}
48714876

‎sql/item_sum.h

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1020,9 +1020,9 @@ class Item_sum_sum : public Item_sum_num {
10201020
enum Item_result result_type() const override { return hybrid_type; }
10211021
bool check_wf_semantics1(THD *thd, Query_block *select,
10221022
Window_evaluation_requirements *reqs) override;
1023+
void no_rows_in_result() override;
10231024
void reset_field() override;
10241025
void update_field() override;
1025-
void no_rows_in_result() override {}
10261026
const char *func_name() const override { return "sum"; }
10271027
Item *copy_or_same(THD *thd) override;
10281028
};
@@ -1303,7 +1303,6 @@ class Item_sum_avg final : public Item_sum_sum {
13031303
Item *result_item(Field *) override {
13041304
return new Item_avg_field(hybrid_type, this);
13051305
}
1306-
void no_rows_in_result() override {}
13071306
const char *func_name() const override { return "avg"; }
13081307
Item *copy_or_same(THD *thd) override;
13091308
Field *create_tmp_field(bool group, TABLE *table) override;
@@ -2161,7 +2160,7 @@ class Item_func_group_concat final : public Item_sum {
21612160
}
21622161
String *val_str(String *str) override;
21632162
Item *copy_or_same(THD *thd) override;
2164-
void no_rows_in_result() override {}
2163+
void no_rows_in_result() override;
21652164
void print(const THD *thd, String *str,
21662165
enum_query_type query_type) const override;
21672166
bool change_context_processor(uchar *arg) override {

0 commit comments

Comments
 (0)