Search code examples
sqlmonetdb

min/max query with a where clause versus group by

Running MonetDB Database Server Toolkit v1.1 (Feb2013-SP6)

This query 

select rowtype, min(zdate), max(zdate) , count(*) 
from fdhista 
group by rowtype 
;

returns correct minimum and maximum dates for each rowtype.

rowtype L1  L2  L3
3   1970-12-31  2009-07-31  1664186
1   2003-02-24  2013-09-13  11649306

This query, over the same table

select min(zdate), max(zdate), count(*) from fdhista where rowtype=3;

seems to "ignore" the where clause, returning

L1  L2  L3
1970-12-31  2013-09-13  13313492

I haven't found a general sql precedent (yet) for that answer. Is this the expected response?

I was expecting this

L1  L2  L3
1970-12-31  2009-07-31  1664186

I tried similiar queries in Oracle and SQL Server and get back my expected response. Yet I find generic sql comments that support "ignoring" the where clause. Maybe this is a case of MonetDB's use of a specific SQL standard?

Solution

  • Agreed, looks like a bug, but could not reproduce in this example:

    create table fdhista (rowtype tinyint, zdate date);
insert into fdhista values (1,'2013-09-13'),(1,'1970-12-31'),(3,'2013-09-14'),(3,'1970-12-30'),(3,'1984-06-24');

    When I then run

    select rowtype, min(zdate), max(zdate) , count(*)  from fdhista  group by rowtype;

    I get 

    +---------+------------+------------+------+
| rowtype | L1         | L2         | L3   |
+=========+============+============+======+
|       1 | 1970-12-31 | 2013-09-13 |    2 |
|       3 | 1970-12-30 | 2013-09-14 |    3 |
+---------+------------+------------+------+

    And with the restriction

    select min(zdate), max(zdate), count(*) from fdhista where rowtype=3;

    I get

    +------------+------------+------+
| L1         | L2         | L3   |
+============+============+======+
| 1970-12-30 | 2013-09-14 |    3 |
+------------+------------+------+

    Everything looks fine, but I am running the latest MonetDB version here.

    So first, update to the latest MonetDB version Jan2014. Second, please report MonetDB bugs in the bugtracker at http://bugs.monetdb.org/. Please make sure to include enough information into the bug report so that it can be reproduced, I had to guess the schema and and come up with sample data in this case.