min/max query with a where clause versus group by

Question

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?

That looks like a bug. What do you get when you try `select * from fdhista where rowtype=3;`? — John Saunders, Feb 23 '14 at 04:43
Worse come to worst, you could try `select rowtype, min(zdate), max(zdate) , count(*) from fdhista group by rowtype having rowtype=3;` — John Saunders, Feb 23 '14 at 04:44
@JohnSaunders: Returns all 1664186 rowtype=3 rows. The HAVING clause does work. Thanks. — p99, Feb 23 '14 at 14:44

Hannes Mühleisen · Accepted Answer · 2014-02-24T00:40:47.207

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.

Note he later said that `rowtype` is `tinyint`. Maybe that's the difference? — John Saunders, Feb 23 '14 at 17:49
After using your test code from this answer in two separate tests (using integer and tinyint for rowtype), NEITHER test exhibited my "bug". However, my original table still exhibits the inconsistent behavior that prompted this question. Realizing that, my troubles stem from COPY INTO bulk loading. Maybe [this](http://bugs.monetdb.org/3436)? Updating to Jan2014 made that go away. Thanks to both Hannes and John for steering me right. — p99, Feb 24 '14 at 12:38

min/max query with a where clause versus group by

1 Answers1