[
https://issues.apache.org/jira/browse/DERBY-5466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14680279#comment-14680279
]
Rick Hillegas commented on DERBY-5466:
--------------------------------------
A little polishing is needed for the bind logic. The DISTINCT and ALL keywords
may not be used with var_pop(), var_samp(), stddev_pop(), and stddev_samp()
according to the 2011 SQL Standard, section 4.16.4 (Aggregate functions):
"Neither DISTINCT nor ALL are allowed to be specified for VAR_POP, VAR_SAMP,
STDDEV_POP, or STDDEV_SAMP; redundant duplicates are not removed when computing
these functions."
When ALL is used, we return a reasonable error message:
{noformat}
select stddev_samp( all a ) from ruth.doubles;
ERROR 42X01: Syntax error: Encountered "all" at line 1, column 21.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed
directly.
Consult your DBMS server reference documentation for details of the SQL syntax
supported by your server.
{noformat}
However, the error message is misleading when the user specifies DISTINCT:
{noformat}
select stddev_samp( distinct a ) from ruth.doubles;
ERROR 42X94: DERBY AGGREGATE 'STDDEV_SAMP' does not exist.
{noformat}
> Add support for SQL Standard statistics functions, such as STDDEV_POP,
> STDDEV_SAMP, VAR_POP, VAR_SAMP
> -----------------------------------------------------------------------------------------------------
>
> Key: DERBY-5466
> URL: https://issues.apache.org/jira/browse/DERBY-5466
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.8.1.2
> Reporter: Lukas Eder
> Priority: Minor
> Labels: derby_triage10_10
> Attachments: DERBY-5466.stats.patch,
> derby-5466-01-aa-aggregatorClasses.diff, derby-5466-02-aa-bindLogic.diff,
> derby-5466-02-ab-bindLogic.diff, var_pop_formulas.txt
>
>
> Any of these RDBMS support the SQL standard statistics functions STDDEV_POP,
> STDDEV_SAMP, VAR_POP, VAR_SAMP:
> - DB2 (only STDDEV, VARIANE)
> - H2
> - HSQLDB
> - Ingres
> - MySQL
> - Oracle
> - Postgres
> - SQL Server (named STDEVP, STDEV, VARP, VAR)
> - Sybase ASE
> - Sybase SQL Anywhere
> These don't:
> - Derby
> - SQLite
> This would be a useful addition for Derby, I think.
> An even larger example list of possible statistics aggregate functions is
> listed in the Postgres documentation:
> http://www.postgresql.org/docs/9.0/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
