Uploaded image for project: 'Cassandra'
  1. Cassandra
  2. CASSANDRA-10310

Support type casting in selection clause

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Fix Version/s: 3.2
    • Component/s: None
    • Labels:

      Description

      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:

      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)

      I tried avg() with MySQL, here's the result:

      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)

      I created a new table using the above query. The result:

      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)

      It works the same way in postgres, and to my knowledge, every RDBMs.

      Broken in 2.2, 3.0.

        Attachments

        1. cassandra-2.2-10310.txt
          4 kB
          Brett Snyder
        2. cassandra-3.0-10310.txt
          4 kB
          Brett Snyder

          Activity

            People

            • Assignee:
              blerer Benjamin Lerer
              Reporter:
              rustyrazorblade Jon Haddad
              Reviewer:
              Robert Stupp
              Tester:
              Jon Haddad
            • Votes:
              1 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: