Bug in Mysql-4.1 with IF ?

Hi,

How-to-repeat :

CREATE TABLE iftest (field char(1) NOT NULL DEFAULT 'b');
INSERT INTO iftest VALUES ();

mysql> SELECT IF('b',0,SUM('a')) as c;
+------+
| b |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> SELECT IF('b',0,SUM('a')) as c FROM iftest;
+------+
| b |
+------+
| 0 |
+------+
1 row in set (0.00 sec)

it should return also NULL ?

mysql> SELECT IF('b',0,SUM('a')) as c FROM iftest HAVING c='a';
+------+
| b |
+------+
| 0 |
+------+
1 row in set (0.00 sec)

it should return no result ?

Regards,
Jocelyn



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12923@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in Mysql-4.1 with IF ?

Hi!

On Nov 09, Jocelyn Fournier wrote:
> Hi,
>
> How-to-repeat :
>
> CREATE TABLE iftest (field char(1) NOT NULL DEFAULT 'b');
> INSERT INTO iftest VALUES ();
>
> mysql> SELECT IF('b',0,SUM('a')) as c;
> +------+
> | c |
> +------+
> | NULL |
> +------+
> 1 row in set (0.00 sec)
>
> mysql> SELECT IF('b',0,SUM('a')) as c FROM iftest;
> +------+
> | c |
> +------+
> | 0 |
> +------+
> 1 row in set (0.00 sec)
>
> it should return also NULL ?
>
> mysql> SELECT IF('b',0,SUM('a')) as c FROM iftest HAVING c='a';
> +------+
> | c |
> +------+
> | 0 |
> +------+
> 1 row in set (0.00 sec)
>
> it should return no result ?

No, this is expected and documented behaviour.
First, it has nothing to do with IF,

mysql> SELECT SUM('a') as c;
mysql> SELECT SUM('a') as c FROM iftest;
mysql> SELECT SUM('a') as c FROM iftest HAVING c='a';

will produce exactly the same results.

Then, according to the manual:

`SUM(expr)'
Returns the sum of `expr'. Note that if the return set has no
rows, it returns NULL!

So, SUM('a') is NULL if there were no rows, or 0 (as int('a')==0) if
there was at least one row. First query return NULL (no rows were
selected), second query returns 0 (as the table has one row), third
query returns 0, as SUM() result is a number, and in numeric context
'a'==0, so HAVING condition is true.

No bug this time :)

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12928@lists.mysql.com
To unsubscribe, e-mail