Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL aggregate functions avg(), sum() and median() return an error when used on a column with datatype interval second #3533

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
enhancement New feature or request SQL

Comments

@monetdb-team
Copy link

Date: 2014-08-08 16:04:05 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: -- development
CC: @njnes, @PedroTadim

Last updated: 2019-11-28 10:00:06 +0100

Comment 20017

Date: 2014-08-08 16:04:05 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:30.0) Gecko/20100101 Firefox/30.0
Build Identifier:

sql>SELECT sum(f1) FROM INTERVAL_TBL;
overflow in conversion of 1000 to bte.

sql>SELECT avg(f1) FROM INTERVAL_TBL;
types sec_interval(13,0) and double(53,0) are not equal for column 'f1'

sql>SELECT median(f1) FROM INTERVAL_TBL;
overflow in conversion of 1000 to bte.

Reproducible: Always

Steps to Reproduce:

  1. start mserver5 (Database: MonetDB v11.17.21 (Jan2014-SP3), 'demo')
  2. start mclient
  3. enter SQL:
    CREATE TABLE INTERVAL_TBL (f1 interval second);
    INSERT INTO INTERVAL_TBL (f1) VALUES (1);
    INSERT INTO INTERVAL_TBL (f1) VALUES (2);
    INSERT INTO INTERVAL_TBL (f1) VALUES (3);
    INSERT INTO INTERVAL_TBL (f1) VALUES (4);
    SELECT * FROM INTERVAL_TBL;
    SELECT count(f1) FROM INTERVAL_TBL;
    -- next queries fail
    SELECT sum(f1) FROM INTERVAL_TBL;
    SELECT avg(f1) FROM INTERVAL_TBL;
    SELECT median(f1) FROM INTERVAL_TBL;

Actual Results:

sql>select * from interval_tbl;
+------------------------------------------------------------------------------+
| f1 |
+==============================================================================+
| 1.000 |
| 2.000 |
| 3.000 |
| 4.000 |
+------------------------------------------------------------------------------+
4 tuples (0.574ms)
sql>SELECT count(f1) FROM INTERVAL_TBL;
+------+
| L1 |
+======+
| 4 |
+------+
1 tuple (1.292ms)

sql>SELECT sum(f1) FROM INTERVAL_TBL;
overflow in conversion of 1000 to bte.

sql>SELECT avg(f1) FROM INTERVAL_TBL;
types sec_interval(13,0) and double(53,0) are not equal for column 'f1'

sql>SELECT median(f1) FROM INTERVAL_TBL;
overflow in conversion of 1000 to bte.

Expected Results:

sql>SELECT sum(f1) FROM INTERVAL_TBL;
10

sql>SELECT avg(f1) FROM INTERVAL_TBL;
2.5

sql>SELECT median(f1) FROM INTERVAL_TBL;
2.5

SQL queries:
SELECT min(f1) FROM INTERVAL_TBL;
and
SELECT max(f1) FROM INTERVAL_TBL;
work okay

Comment 20025

Date: 2014-08-11 16:30:18 +0200
From: @njnes

I'll move this into a feature request, ie one monetdb sql allows for this easy insert of interval values, but no avg,sum or median is defined over it.
Instead of the current auto coercion, an proper implementation of the aggegation of these intervals is needed.

Comment 27409

Date: 2019-11-13 22:01:36 +0100
From: Martin van Dinther <<martin.van.dinther>>

In the Nov2019 release (on 13 nov 2019) the sum() and avg() now work on interval type for the test case. The median() still gives an error:

sql>SELECT sum(f1) FROM INTERVAL_TBL;
+--------+
| L2 |
+========+
| 10.000 |
+--------+
1 tuple
sql>SELECT avg(f1) FROM INTERVAL_TBL;
+--------------------------+
| L2 |
+==========================+
| 2.5 |
+--------------------------+
1 tuple
sql>SELECT median(f1) FROM INTERVAL_TBL;
overflow in conversion of 1000 to bte.

Would be nice if the change made for avg() also is implemented for median().
Is that possible?

Comment 27412

Date: 2019-11-15 16:55:23 +0100
From: MonetDB Mercurial Repository <>

Changeset 9648e02041f6 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=9648e02041f6

Changeset description:

Added missing definitions for analytical aggregates on interval types. This fixes bug #3533.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request SQL
Projects
None yet
Development

No branches or pull requests

2 participants