MDEV-19071 Wrong results when using STDDEV_SAMP() and view

6 files changed, 29 insertions, 4 deletions

diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result
index 0f3169e330f..0f80d14b603 100644
--- a/mysql-test/main/func_group.result
+++ b/mysql-test/main/func_group.result
@@ -2552,5 +2552,19 @@ DROP TABLE t1;
 #
 SET STATEMENT sql_mode=ONLY_FULL_GROUP_BY FOR EXECUTE IMMEDIATE 'ALTER TABLE mysql.time_zone_transition ORDER BY Time_zone_id, Transition_time';
 #
+# MDEV-19071 Wrong results when using STDDEV_SAMP() and view
+#
+create table t1(i int);
+insert into t1 values  (1),(2),(3),(4),(5);
+create view v1 as select stddev_samp(i),stddev_pop(i),stddev(i),std(i) from t1;
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select stddev_samp(`t1`.`i`) AS `stddev_samp(i)`,std(`t1`.`i`) AS `stddev_pop(i)`,std(`t1`.`i`) AS `stddev(i)`,std(`t1`.`i`) AS `std(i)` from `t1`	latin1	latin1_swedish_ci
+select * from v1;
+stddev_samp(i)	stddev_pop(i)	stddev(i)	std(i)
+1.5811	1.4142	1.4142	1.4142
+drop view v1;
+drop table t1;
+#
 # End of 10.3 tests
 #
diff --git a/mysql-test/main/func_group.test b/mysql-test/main/func_group.test
index 862ea453b46..6b3a15fd45e 100644
--- a/mysql-test/main/func_group.test
+++ b/mysql-test/main/func_group.test
@@ -1790,5 +1790,16 @@ DROP TABLE t1;
 SET STATEMENT sql_mode=ONLY_FULL_GROUP_BY FOR EXECUTE IMMEDIATE 'ALTER TABLE mysql.time_zone_transition ORDER BY Time_zone_id, Transition_time';
 
 --echo #
+--echo # MDEV-19071 Wrong results when using STDDEV_SAMP() and view
+--echo #
+create table t1(i int);
+insert into t1 values  (1),(2),(3),(4),(5);
+create view v1 as select stddev_samp(i),stddev_pop(i),stddev(i),std(i) from t1;
+show create view v1;
+select * from v1;
+drop view v1;
+drop table t1;
+
+--echo #
 --echo # End of 10.3 tests
 --echo #
diff --git a/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result b/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result
index b9fe877b0f2..6b7faa18da8 100644
--- a/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result
+++ b/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result
@@ -129,7 +129,7 @@ create table t1 (a int, b int generated always as (stddev_pop(a)) virtual);
 ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
 # STDDEV_SAMP()
 create table t1 (a int, b int generated always as (stddev_samp(a)) virtual);
-ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+ERROR HY000: Function or expression 'stddev_samp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
 # STDDEV()
 create table t1 (a int, b int generated always as (stddev(a)) virtual);
 ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
diff --git a/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_myisam.result b/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_myisam.result
index 23fdea42488..99921c658b3 100644
--- a/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_myisam.result
@@ -131,7 +131,7 @@ create table t1 (a int, b int generated always as (stddev_pop(a)) virtual);
 ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
 # STDDEV_SAMP()
 create table t1 (a int, b int generated always as (stddev_samp(a)) virtual);
-ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+ERROR HY000: Function or expression 'stddev_samp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
 # STDDEV()
 create table t1 (a int, b int generated always as (stddev(a)) virtual);
 ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
diff --git a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result b/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result
index 19e8efb06ad..1cf24194c5b 100644
--- a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result
+++ b/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result
@@ -191,7 +191,7 @@ create or replace table t1 (a int, b int as (stddev_pop(a)));
 ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
 # STDDEV_SAMP()
 create or replace table t1 (a int, b int as (stddev_samp(a)));
-ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+ERROR HY000: Function or expression 'stddev_samp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
 # STDDEV()
 create or replace table t1 (a int, b int as (stddev(a)));
 ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b`
diff --git a/sql/item_sum.h b/sql/item_sum.h
index 48565ece5a8..c93332b320f 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -1038,7 +1038,7 @@ class Item_sum_std :public Item_sum_variance
   enum Sumfunctype sum_func () const { return STD_FUNC; }
   double val_real();
   Item *result_item(THD *thd, Field *field);
-  const char *func_name() const { return "std("; }
+  const char *func_name() const { return sample ? "stddev_samp(" : "std("; }
   Item *copy_or_same(THD* thd);
   Item *get_copy(THD *thd)
   { return get_item_copy<Item_sum_std>(thd, this); }