[jira] [Commented] (CASSANDRA-10310) avg() on int give incorrect results (returning int instead of decimal or float)
[
https://issues.apache.org/jira/browse/CASSANDRA-10310?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14743485#comment-14743485
]
Sylvain Lebresne commented on CASSANDRA-10310:
--
bq. You can also cast your columns and get the results that you want.
No you can't. We definitively don't support type casing the selections, it's
not even parsed.
> 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
> 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. |
> ++
> 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)
[jira] [Commented] (CASSANDRA-10310) avg() on int give incorrect results (returning int instead of decimal or float)
[
https://issues.apache.org/jira/browse/CASSANDRA-10310?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14743481#comment-14743481
]
Sylvain Lebresne commented on CASSANDRA-10310:
--
bq. SQL server and Oracle allow you to cast a column and get whatever value you
want.
Yes, and that's what we should support imo (if for no other reasons than
because that's a solution that doesn't break backward compatibility). In fact,
that's what I suggested in [my
comment|https://issues.apache.org/jira/browse/CASSANDRA-4914?focusedCommentId=14132148&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14132148]
and to be honest I though we supported it, but appears that fell through the
cracks.
> 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
> 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. |
> ++
> 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)
[jira] [Commented] (CASSANDRA-10310) avg() on int give incorrect results (returning int instead of decimal or float)
[
https://issues.apache.org/jira/browse/CASSANDRA-10310?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14743474#comment-14743474
]
Benjamin Lerer commented on CASSANDRA-10310:
{quote}SQL server and Oracle allow you to cast a column and get whatever value
you want.{quote}
C* does the same. You can also cast your columns and get the results that you
want. Type casting as been implemented in CASSANDRA-5226.
> 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
> 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. |
> ++
> 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)
[jira] [Commented] (CASSANDRA-10310) avg() on int give incorrect results (returning int instead of decimal or float)
[
https://issues.apache.org/jira/browse/CASSANDRA-10310?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14743444#comment-14743444
]
Jon Haddad commented on CASSANDRA-10310:
SQL server and Oracle allow you to cast a column and get whatever value you
want. This decision is a little odd to me, since we're effectively saying "you
choose an int for your column so you don't get precision", and don't offer a
way around it. I can't think of a case where you'd prefer to have a less
precise result from avg().
> 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
> 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. |
> ++
> 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)
[jira] [Commented] (CASSANDRA-10310) avg() on int give incorrect results (returning int instead of decimal or float)
[
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. |
> ++
> 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)
