MySQL Bugs: #1308: after upgrading from 4.0.12 to 4.0.15(-max), a sql query doesn't work anymore

Bug #1308	after upgrading from 4.0.12 to 4.0.15(-max), a sql query doesn't work anymore
Submitted:	16 Sep 2003 13:39	Modified:	21 Oct 2003 3:02
Reporter:	Gael Martinez	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.15 4.0.15-max	OS:	Linux (linux debian)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
Running the following query on 4.0.12 and 4.0.15, gives different results,
i can provide a dump of this database (it's 4 meg gzipped), the query pasted
below was pasted also in the difference databases to obtain the output at the end of this description.

select lfgpl_champs_resultats.M_PILOTES_ID,
 count(V_BAREMES_PILOTES_PTS)  NB_COURSES,
 if (sum(V_BAREMES_PILOTES_PTS * if(I_RESULTATS_COMPTE_PILOTES='Y',1,0))is
null,0, sum(V_BAREMES_PILOTES_PTS * if(I_RESULTATS_COMPTE_PILOTES='Y',1,0)))
POINTS,
 if(sum(V_BONUS_PILOTES_PTS * if(I_RESULTATS_COMPTE_PILOTES='Y',1,0))is
null,0,sum(V_BONUS_PILOTES_PTS * if(I_RESULTATS_COMPTE_PILOTES='Y',1,0)))
PTS_BONUS,
 if(sum(V_PENALITES_PILOTES_PTS * if(I_RESULTATS_COMPTE_PILOTES='Y',1,0)) is
null,0,sum(V_PENALITES_PILOTES_PTS *
if(I_RESULTATS_COMPTE_PILOTES='Y',1,0))) PTS_PENALITES,

 sum(V_BAREMES_PILOTES_PTS) +
 sum(V_BONUS_PILOTES_PTS) - sum(V_PENALITES_PILOTES_PTS) as TOTAL_PTS

from lfgpl_champs_baremes,
 lfgpl_champs_resultats LEFT OUTER JOIN lfgpl_champs_bonus
 on ((lfgpl_champs_resultats.M_CHAMPS_ID = lfgpl_champs_bonus.M_CHAMPS_ID)
and
     (lfgpl_champs_resultats.M_DIVISIONS_ID =
lfgpl_champs_bonus.M_DIVISIONS_ID) and
  (lfgpl_champs_resultats.M_PILOTES_ID = lfgpl_champs_bonus.M_PILOTES_ID)
and
  (lfgpl_champs_resultats.M_CIRCUIT_ID = lfgpl_champs_bonus.M_CIRCUIT_ID) )

 LEFT OUTER JOIN lfgpl_champs_penalites
 on ((lfgpl_champs_resultats.M_CHAMPS_ID =
lfgpl_champs_penalites.M_CHAMPS_ID) and
     (lfgpl_champs_resultats.M_DIVISIONS_ID =
lfgpl_champs_penalites.M_DIVISIONS_ID) and
  (lfgpl_champs_resultats.M_PILOTES_ID =
lfgpl_champs_penalites.M_PILOTES_ID) and
  (lfgpl_champs_resultats.M_CIRCUIT_ID =
lfgpl_champs_penalites.M_CIRCUIT_ID))

where (lfgpl_champs_resultats.M_CHAMPS_ID = '5') and
 (lfgpl_champs_resultats.M_CHAMPS_ID = lfgpl_champs_baremes.M_CHAMPS_ID) and
 (C_BAREMES_POS  = M_BAREMES_POS) and
 (lfgpl_champs_resultats.M_DIVISIONS_ID =
lfgpl_champs_baremes.M_DIVISIONS_ID)
group by lfgpl_champs_resultats.M_PILOTES_ID
order by  TOTAL_PTS desc, POINTS desc;

On 4.0.12 compiled locally:

+--------------+------------+--------+-----------+---------------+-----------+
| M_PILOTES_ID | NB_COURSES | POINTS | PTS_BONUS | PTS_PENALITES | TOTAL_PTS |
+--------------+------------+--------+-----------+---------------+-----------+
|         9384 |          5 |    823 |         0 |             0 |       823 |
|         7782 |          5 |    616 |         0 |             0 |       616 |

On 4.0.15 compiled locally (tested on 2 distinct servers) and 4.0.15-max downloaded from the website:

+--------------+------------+--------+-----------+---------------+-----------+
| M_PILOTES_ID | NB_COURSES | POINTS | PTS_BONUS | PTS_PENALITES | TOTAL_PTS |
+--------------+------------+--------+-----------+---------------+-----------+
|         9384 |          5 |    823 |         0 |             0 |      NULL |
|         7782 |          5 |    616 |         0 |             0 |      NULL |

How to repeat:
mysqldump gpl > gpl.txt
mysqladmin create gpl
mysql gpl < gpl.txt
mysql gpl
> select lfgpl_champs_resultats.M_PILOTES_ID,
 count(V_BAREMES_PILOTES_PTS)  NB_COURSES,
 if (sum(V_BAREMES_PILOTES_PTS * if(I_RESULTATS_COMPTE_PILOTES='Y',1,0))is
null,0, sum(V_BAREMES_PILOTES_PTS * if(I_RESULTATS_COMPTE_PILOTES='Y',1,0)))
POINTS,
 if(sum(V_BONUS_PILOTES_PTS * if(I_RESULTATS_COMPTE_PILOTES='Y',1,0))is
null,0,sum(V_BONUS_PILOTES_PTS * if(I_RESULTATS_COMPTE_PILOTES='Y',1,0)))
PTS_BONUS,
 if(sum(V_PENALITES_PILOTES_PTS * if(I_RESULTATS_COMPTE_PILOTES='Y',1,0)) is
null,0,sum(V_PENALITES_PILOTES_PTS *
if(I_RESULTATS_COMPTE_PILOTES='Y',1,0))) PTS_PENALITES,

 sum(V_BAREMES_PILOTES_PTS) +
 sum(V_BONUS_PILOTES_PTS) - sum(V_PENALITES_PILOTES_PTS) as TOTAL_PTS

from lfgpl_champs_baremes,
 lfgpl_champs_resultats LEFT OUTER JOIN lfgpl_champs_bonus
 on ((lfgpl_champs_resultats.M_CHAMPS_ID = lfgpl_champs_bonus.M_CHAMPS_ID)
and
     (lfgpl_champs_resultats.M_DIVISIONS_ID =
lfgpl_champs_bonus.M_DIVISIONS_ID) and
  (lfgpl_champs_resultats.M_PILOTES_ID = lfgpl_champs_bonus.M_PILOTES_ID)
and
  (lfgpl_champs_resultats.M_CIRCUIT_ID = lfgpl_champs_bonus.M_CIRCUIT_ID) )

 LEFT OUTER JOIN lfgpl_champs_penalites
 on ((lfgpl_champs_resultats.M_CHAMPS_ID =
lfgpl_champs_penalites.M_CHAMPS_ID) and
     (lfgpl_champs_resultats.M_DIVISIONS_ID =
lfgpl_champs_penalites.M_DIVISIONS_ID) and
  (lfgpl_champs_resultats.M_PILOTES_ID =
lfgpl_champs_penalites.M_PILOTES_ID) and
  (lfgpl_champs_resultats.M_CIRCUIT_ID =
lfgpl_champs_penalites.M_CIRCUIT_ID))

where (lfgpl_champs_resultats.M_CHAMPS_ID = '5') and
 (lfgpl_champs_resultats.M_CHAMPS_ID = lfgpl_champs_baremes.M_CHAMPS_ID) and
 (C_BAREMES_POS  = M_BAREMES_POS) and
 (lfgpl_champs_resultats.M_DIVISIONS_ID =
lfgpl_champs_baremes.M_DIVISIONS_ID)
group by lfgpl_champs_resultats.M_PILOTES_ID
order by  TOTAL_PTS desc, POINTS desc;

Suggested fix:
no idea .... :(

Please send us your dump. 
You can use either Files URL above or upload it at ftp://support.mysql.com/pub/mysql/secret with some easy recognizable name like bug-1308.gz

I uploaded the file as requested

What is the status on that issue  ?

What server charset did you use?

Using the data you submitted, I see that the first LEFT OUTER JOIN condition is impracticable:
...
 LEFT OUTER JOIN lfgpl_champs_bonus
 on ((lfgpl_champs_resultats.M_CHAMPS_ID = lfgpl_champs_bonus.M_CHAMPS_ID)
and
...
where (lfgpl_champs_resultats.M_CHAMPS_ID = '5') and 
...

because there are no records where lfgpl_champs_bonus.M_CHAMPS_ID = 5 in the lfgpl_champs_bonus table. So I got NULLs for V_BONUS_PILOTES_PTS field.

Try:
select * from lfgpl_champs_bonus where M_CHAMPS_ID = '5';
and you'll get an empty result.

That doesn't explain the fact that the query runs fine on other version of the database ...

Ramil - I repeated the behaviour, 4.0.12 and 4.0.16 produce different results.

Ok, here it is.

As Ramil correctly noticed, you use a condition that excludes all not NULL rows from lfgpl_champs_bonus table. Try your select with only

  SELECT sum(V_BONUS_PILOTES_PTS) FROM ...  <everything else as in your select above>

(and without ORDER BY, of course).
You will find that 4.0.15 returns NULLs while 4.0.12 returns 0.
Try also MIN or MAX instead of SUM - both versions will return NULL.
Thus, you can see that 4.0.15 behaves correctly, while 4.0.12 calculates SUM(NULL) as 0, instead of NULL. Checking changelog in the manual, indeed, you can see that:

Changes in release 4.0.13 (16 May 2003)
---------------------------------------
...
Bugs fixed:
...
   * `SUM()' didn't return `NULL' when there was no rows in result or
     when all values was `NULL'.
...

hopefully, this closes the issue.