Bug #59929 Different STDDEV_SAMP() output (NULL<>0.0000000000) when view is used
Submitted: 3 Feb 2011 23:42 Modified: 3 Nov 2019 18:23
Reporter: Roel Van de Paar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.1,5.5,5.6.2-m5 OS:Any
Assigned to: CPU Architecture:Any

[3 Feb 2011 23:42] Roel Van de Paar
Description:
mysql> SELECT STDDEV_SAMP(`f1`) FROM t1;
+-------------------+
| STDDEV_SAMP(`f1`) |
+-------------------+
|              NULL |
+-------------------+

Versus

mysql> SELECT * FROM v1; /* v1 being the same query */
+-------------------+
| STDDEV_SAMP(`f1`) |
+-------------------+
|      0.0000000000 |
+-------------------+

How to repeat:
DROP DATABASE IF EXISTS test2; CREATE DATABASE test2; USE test2; /* Test setup */

CREATE TABLE `t1` (`f1` datetime);
INSERT INTO t1 VALUES ("1990-01-01 00:00:00");

SELECT STDDEV_SAMP(`f1`) FROM t1;   /* NULL */ 

CREATE VIEW v1 AS SELECT STDDEV_SAMP(`f1`) FROM t1;   /* 0.0000000000 */
SELECT * FROM v1;
[3 Nov 2019 18:23] Roy Lyseng
Posted by developer:
 
Fixed in 8.0