cassandra-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Benjamin Lerer (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CASSANDRA-10310) avg() on int give incorrect results (returning int instead of decimal or float)
Date Sun, 13 Sep 2015 19:11:46 GMT

    [ https://issues.apache.org/jira/browse/CASSANDRA-10310?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14742604#comment-14742604
] 

Benjamin Lerer commented on CASSANDRA-10310:
--------------------------------------------

{quote}It works the same way in postgres, and to my knowledge, every RDBMs.{quote}
Both Oracle and Microsoft SQL server return an {{int}} if the input type is an {{int}}.
*[Oracle documentation|http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions018.htm#SQLRF00609]
*[Transact-SQL documentation|https://msdn.microsoft.com/en-us/library/ms177677.aspx]

If you want to read the full discussion about aggregates return types, you can found it [here|https://issues.apache.org/jira/browse/CASSANDRA-4914?focusedCommentId=14132148&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14132148].

> avg() on int give incorrect results (returning int instead of decimal or float)
> -------------------------------------------------------------------------------
>
>                 Key: CASSANDRA-10310
>                 URL: https://issues.apache.org/jira/browse/CASSANDRA-10310
>             Project: Cassandra
>          Issue Type: Bug
>            Reporter: Jon Haddad
>              Labels: patch
>             Fix For: 3.x, 2.2.x
>
>         Attachments: cassandra-2.2-10310.txt, cassandra-3.0-10310.txt
>
>
> When selecting an avg() of int values, the type of the avg value returned is an int as
well, meaning it's rounded off to an incorrect answer.  This is both incorrect and inconsistent
with other databases.
> Example:
> {quote}
> cqlsh:test> select * from monkey where id = 1;
>  id | i | v
> ----+---+---
>   1 | 1 | 1
>   1 | 2 | 1
>   1 | 3 | 2
> (3 rows)
> cqlsh:test> select avg(v) from monkey where id = 1;
>  system.avg(v)
> ---------------
>              1
> (1 rows)
> {quote}
> I tried avg() with MySQL, here's the result:
> {quote}
> mysql> create table blah ( id int primary key, v int );
> Query OK, 0 rows affected (0.15 sec)
> mysql> insert into blah set id = 1, v = 1;
> Query OK, 1 row affected (0.02 sec)
> mysql> insert into blah set id = 1, v = 1;
> ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
> mysql> insert into blah set id = 2, v = 1;
> Query OK, 1 row affected (0.01 sec)
> mysql> insert into blah set id = 3, v = 2;
> Query OK, 1 row affected (0.01 sec)
> mysql> select avg(v) from blah;
> +--------+
> | avg(v) |
> +--------+
> | 1.3333 |
> +--------+
> 1 row in set (0.00 sec)
> {quote}
> I created a new table using the above query. The result:
> {quote}
> mysql> create table foo as select avg(v) as a from blah;
> Query OK, 1 row affected, 1 warning (0.04 sec)
> Records: 1  Duplicates: 0  Warnings: 1
> mysql> desc foo;
> +-------+---------------+------+-----+---------+-------+
> | Field | Type          | Null | Key | Default | Extra |
> +-------+---------------+------+-----+---------+-------+
> | a     | decimal(14,4) | YES  |     | NULL    |       |
> +-------+---------------+------+-----+---------+-------+
> 1 row in set (0.01 sec)
> {quote}
> It works the same way in postgres, and to my knowledge, every RDBMs.
> Broken in 2.2, 3.0.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message