[ monetdb-Bugs-2892439 ] Overflow using CASE

[SourceForge.net]

Bugs item #2892439, was opened at 2009-11-05 09:04
Message generated for change (Tracker Item Submitted) made by skinkie
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2892439&group_id=56967

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL/Core
Group: SQL CVS Head
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Stefan de Konink (skinkie)
Assigned to: Niels Nes (nielsnes)
Summary: Overflow using CASE

Initial Comment:
Dataset;
http://mirror.openstreetmap.nl/knmi/KNMI.sql

Query;
select epoch(cast(date as timestamp)) * 1000, ((CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END) + (CASE WHEN station136 is NULL THEN 0 ELSE station136 END)) from rivm_co WHERE date BETWEEN '2009-1-1' AND '2009-7-1' limit 3;

[yes this was a query generator bug]

Results:
+--------------------+-------------------+
| sql_mul_epoch_date | isnull_station136 |
+====================+===================+
|      1230768000000 |            -16352 |
|      1230771600000 |            -26240 |
|      1230775200000 |            -26688 |
+--------------------+-------------------+

Bad results above; see below:

sql>select min(station136)  from rivm_co WHERE date BETWEEN '2009-1-1' AND '2009-7-1' limit 3;
+--------------+
| single_value |
+==============+
|           84 |
+--------------+
1 tuple

----------------------------------------------------------------------

You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2892439&group_id=56967

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Monetdb-bugs mailing list
Monetdb-bugs@...
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs