db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5466) Add support for SQL Standard statistics functions, such as STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP
Date Mon, 10 Aug 2015 15:45:46 GMT

    [ 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)

Mime
View raw message