| Bug #17896 | MIN of CASE WHEN returns non-minimum value! | ||
|---|---|---|---|
| Submitted: | 3 Mar 2006 12:42 | Modified: | 27 Apr 2006 15:02 |
| Reporter: | Frank Osterberg | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.20-BK, 4.1 & 5.0 | OS: | Linux (Linux, Win32/64) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
[3 Mar 2006 12:42]
Frank Osterberg
[3 Mar 2006 13:08]
Frank Osterberg
On further observations/bugs found: The MIN value without the case that is returned does NOT exactly match the true minimum... it has some added decimal places (where the heck did they come from?!) What it looks like to me: a DOUBLE variable was used to return the result, this double was not zeroed/initilized.. when used as an assignment target the source was not converted into a double and only replaced the first 4 bytes.. leaving the rest a mix of random values... this is purely a guess though. happens with MIN and MAX when using a table with FLOAT fields.. works perfectly on a table with DOUBLE fields! The problem where MIN does not return the proper minimum value when used with CASE WHEN does not occur on all data sets but it doesnt matter if the destination field is a double or a float. IT also occures on MyISAM tables.
[3 Mar 2006 13:30]
Frank Osterberg
Well the float bug seems to be a whole different problem.. try: (using the table from first bug post, the one where val is a float type field) INSERT INTO t1 (val) values (1.1); DELERE FROM t1 WHERE val=1.1; -- nothing is deleted.. no value 1.1 is found!?! SELECT * FROM t1 WHERE val=1.1; -- nothing found! SELECT * FROM t1 WHERE val BETWEEN 1.0 AND 1.2 -- finds one with val = 1.1! but this is not that much of a problem.. just annoying.. the MIN(CASE WHEN not working is much more of a problem for me.. still can't fix it.. it apperently works when i insert a whole number less the the smallest fractional number.. i.e. after adding -1 it works again.. once i remove it it no longer works....
[3 Mar 2006 14:18]
Frank Osterberg
Hi, the original problem stille exists, but since i found an (hopefully reliable) alternative it is not as important (to me :) anymore. i found that using MIN(IF(exp,valueField,NULL)) instead seems to work.. (at least for the cases that i saw where using MIN(CASE WHEN exp THEN valueField ELSE NULL END) did not return the correct value)
[3 Mar 2006 14:19]
Frank Osterberg
Hi, the original problem stille exists, but since i found an (hopefully reliable) alternative, so it is not as important (to me :) anymore. i found that using MIN(IF(exp,valueField,NULL)) instead seems to work in those cases where i saw that using MIN(CASE WHEN exp THEN valueField ELSE NULL END) did not (work correctly).
[3 Mar 2006 15:01]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with latest 5.0.20-BK build on Linux:
mysql> CREATE TABLE `t1` (
-> `id` int(10) unsigned NOT NULL auto_increment,
-> `val` float default NULL,
-> `bin` int(10) unsigned NOT NULL default '3',
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 (val) values (4.60308),(2.90326),(3.42491),(3.70293), (3.
66227),(3.20431),(7.10566),(6.02002),(6.16832),(5.52909),(6.37811), (6.22019),(5.88964),(5.45675),(6.25241),(5.7688),(7.6711),(11.3274), (4.7363),(5.17713),(2.83313),(5.85495),(8.12902);
Query OK, 23 rows affected (0.01 sec)
Records: 23 Duplicates: 0 Warnings: 0
mysql> SELECT MIN(val), MIN(CASE WHEN 1=1 THEN val ELSE NULL END)
-> FROM t1
-> WHERE bin=3 GROUP BY bin;
+-----------------+-------------------------------------------+
| MIN(val) | MIN(CASE WHEN 1=1 THEN val ELSE NULL END) |
+-----------------+-------------------------------------------+
| 2.8331298828125 | 11.3274 |
+-----------------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT MAX(val), MAX(CASE WHEN 1=1 THEN val ELSE NULL END) FROM t1 WHERE bin=3 GROUP BY bin;
+----------------+-------------------------------------------+
| MAX(val) | MAX(CASE WHEN 1=1 THEN val ELSE NULL END) |
+----------------+-------------------------------------------+
| 11.32740020752 | 8.12902 |
+----------------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT val, CASE WHEN 1=1 THEN val ELSE NULL END FROM t1 WHERE bin=3;
+---------+--------------------------------------+
| val | CASE WHEN 1=1 THEN val ELSE NULL END |
+---------+--------------------------------------+
| 4.60308 | 4.60308 |
| 2.90326 | 2.90326 |
| 3.42491 | 3.42491 |
| 3.70293 | 3.70293 |
| 3.66227 | 3.66227 |
| 3.20431 | 3.20431 |
| 7.10566 | 7.10566 |
| 6.02002 | 6.02002 |
| 6.16832 | 6.16832 |
| 5.52909 | 5.52909 |
| 6.37811 | 6.37811 |
| 6.22019 | 6.22019 |
| 5.88964 | 5.88964 |
| 5.45675 | 5.45675 |
| 6.25241 | 6.25241 |
| 5.7688 | 5.7688 |
| 7.6711 | 7.6711 |
| 11.3274 | 11.3274 |
| 4.7363 | 4.7363 |
| 5.17713 | 5.17713 |
| 2.83313 | 2.83313 |
| 5.85495 | 5.85495 |
| 8.12902 | 8.12902 |
+---------+--------------------------------------+
23 rows in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.20 |
+-----------+
1 row in set (0.00 sec)
[6 Mar 2006 12:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/3498
[10 Apr 2006 14:39]
Magnus Blåudd
Ramil, here are my comments:
static void agg_result_type(Item_result *type, Item **items, uint nitems)
{
- uint i;
- type[0]= items[0]->result_type();
- for (i=1 ; i < nitems ; i++)
- type[0]= item_store_type(type[0], items[i]->result_type());
+ Item **item, **item_end;
Please add comment what function is doing, I assume it should be "Update result type of items in aggregated function, use the first not null type and set it for all the others"
+
+ /* Note: NULL items don't affect the result type */
+ *type= STRING_RESULT;
+ /* Skip beginning NULL items */
+ for (item= items, item_end= item + nitems; item < item_end; item++)
^^
Please add sorrounding braces for "for"-loop
+ if ((*item)->type() != Item::NULL_ITEM)
+ {
+ *type= (*item)->result_type();
+ item++;
+ break;
+ }
Add comment what this part is doing...
+ for (; item < item_end; item++)
^^
Please add sorrounding braces for "for"-loop
+ if ((*item)->type() != Item::NULL_ITEM)
+ *type= item_store_type(type[0], (*item)->result_type());
}
[21 Apr 2006 6:47]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/5251
[24 Apr 2006 7:41]
Ramil Kalimullin
fixed in 4.1.19
[27 Apr 2006 15:02]
Paul DuBois
Noted in 4.1.19 changelog. A call to <literal>MIN()</literal> with a <literal>CASE</literal> expression as its argument could return a non-minimum value. (Bug #17896)
