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) Support type casting in selection clause
Date Mon, 09 Nov 2015 10:36:11 GMT

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

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

The patch is [here|https://github.com/apache/cassandra/compare/trunk...blerer:10310-3.0].
For the syntax I choosed to use the syntax used by most of the relational databases {{CAST(
x AS double)}}.
The implementation use internally native scalar functions to perform the conversions. An other
option would have been to create a new {{Selector}}.
The advantage of using the functions framework was that it was allowing to reuse existing
conversion functions.

The patch add support for the following casts:  
{code}
ascii -> text, varchar		
bigint -> decimal, double, float, int, tinyint, smallint, varint
tinyint -> decimal, bigint, double, float, int, smallint, varint
smallint -> decimal, bigint, double, float, int, tinyint, varint
boolean -> ascii, text, varchar
counter -> decimal, bigint, double, float, int, tinyint, smallint, varint
date -> timestamp	
decimal -> bigint, double, float, int, tinyint, smallint, varint
double -> decimal, bigint, float, int, tinyint, smallint, varint
float -> decimal, bigint, double, int, tinyint, smallint, varint
inet -> ascii, text, varchar	
int -> decimal, bigint, double, float, tinyint, smallint, varinttime 	
timestamp -> date
timeuuid -> date, timestamp	
varint -> decimal, bigint, double, float, int, tinyint, smallint
{code}

C* will ignore any unnecessary cast (like casting an int to an int).

CI:
* The unit test results are [here|http://cassci.datastax.com/view/Dev/view/blerer/job/blerer-10310-3.0-testall/3/]
* The dtest results are [here|http://cassci.datastax.com/view/Dev/view/blerer/job/blerer-10361-3.0-dtest/2/]
 

> 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: cql, 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