Bug #69685 STDDEV_SAMP() changes to STD() when used in a view

• Submitted: 7 Jul 2013 20:40
• Modified: 14 Mar 2018 21:07
• Reporter: Taylor Jones
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5.31
• OS: Linux
• CPU Architecture: Any
• Tags: aggregate, functions

[7 Jul 2013 20:40] Taylor Jones

Description:
When the STDDEV_SAMP() function is used in a view definition, it is converted to STD(). This is not immediately apparent, it only becomes visible when doing a schema export.

How to repeat:
CREATE TABLE controls (
  plate_id bigint NOT NULL,
  control_type varchar(64)  NOT NULL,
  time_marker int  NOT NULL,
  data float  NOT NULL
);

CREATE VIEW test_view AS
SELECT plate_id, control_type, time_marker, STDDEV_SAMP(data) AS ctrl 
   FROM controls 
GROUP BY plate_id, control_type, time_marker;

SHOW CREATE VIEW test_view;

[8 Jul 2013 6:21] MySQL Verification Team

Hello Taylor,

Thank you for the bug report. Verified as described.

Thanks,
Umesh

[8 Jul 2013 6:22] MySQL Verification Team

// 5.5.32

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.32-log |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE controls (
    ->   plate_id bigint NOT NULL,
    ->   control_type varchar(64)  NOT NULL,
    ->   time_marker int  NOT NULL,
    ->   data float  NOT NULL
    -> )engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW test_view AS
    -> SELECT plate_id, control_type, time_marker, STDDEV_SAMP(data) AS ctrl
    ->    FROM controls
    -> GROUP BY plate_id, control_type, time_marker;
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW CREATE VIEW test_view\G
*************************** 1. row ***************************
                View: test_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_view` AS select `controls`.`plate_id` AS `plate_id`,`controls`.`control_type` AS `control_type`,`controls`.`time_marker` AS `time_marker`,std(`controls`.`data`) AS `ctrl` from `controls` group by `controls`.`plate_id`,`controls`.`control_type`,`controls`.`time_marker`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

// 5.1.71

mysql> CREATE TABLE controls (
    ->   plate_id bigint NOT NULL,
    ->   control_type varchar(64)  NOT NULL,
    ->   time_marker int  NOT NULL,
    ->   data float  NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE VIEW test_view AS
    -> SELECT plate_id, control_type, time_marker, STDDEV_SAMP(data) AS ctrl
    ->    FROM controls
    -> GROUP BY plate_id, control_type, time_marker;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE VIEW test_view\G
*************************** 1. row ***************************
                View: test_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_view` AS select `controls`.`plate_id` AS `plate_id`,`controls`.`control_type` AS `control_type`,`controls`.`time_marker` AS `time_marker`,std(`controls`.`data`) AS `ctrl` from `controls` group by `controls`.`plate_id`,`controls`.`control_type`,`controls`.`time_marker`
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.71-log |
+------------+
1 row in set (0.00 sec)

[8 Jul 2013 9:11] Hartmut Holzgraefe

5.6.10 also affected

[14 Mar 2018 21:07] Roy Lyseng

Posted by developer:
 
Fixed as of version 5.7.22 and up.