cassandra-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sylvain Lebresne (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CASSANDRA-10310) Support type casting in selection clause
Date Fri, 18 Sep 2015 12:36:04 GMT

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

Sylvain Lebresne commented on CASSANDRA-10310:
----------------------------------------------

Just as a fyi, I actually don't think we can use type casting for this, we might have to rely
on conversion functions for that (though there is potentially other options). I don't really
have time to go into the details of why today however, so I'll update with more details next
week or something.

With that said, I generally agree with the idea of allowing as much as is reasonable, but
I also think that the exact details on what is reasonable is the least important part of this
ticket, and we should actually probably start with something conservative, since adding new
conversion/casting we've forgotten is easy, it's removing some that turns out to not be such
a good idea that is hard.

> Support type casting in selection clause
> ----------------------------------------
>
>                 Key: CASSANDRA-10310
>                 URL: https://issues.apache.org/jira/browse/CASSANDRA-10310
>             Project: Cassandra
>          Issue Type: Bug
>            Reporter: Jon Haddad
>            Assignee: Benjamin Lerer
>              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.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