Bug #40629 Wrong result reutn in select statement math function

• Submitted: 11 Nov 2008 7:32
• Modified: 10 Feb 2018 17:03
• Reporter: Baskaran Angappan
• Status: Can't repeat
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.0.45
• OS: Linux
• CPU Architecture: Any
• Tags: Minus, select minus

[11 Nov 2008 7:32] Baskaran Angappan

Description:
select Paid_Status,Last_Payment,Valid_Days,DATEDIFF(NOW(),Last_Payment) AS UsedDays, Valid_Days - DATEDIFF(NOW(),Last_Payment) AS RemaingDays FROM studentdetails limit 10;
+-------------+---------------------+------------+----------+----------------------+
| Paid_Status | Last_Payment        | Valid_Days | UsedDays | RemaingDays          |
+-------------+---------------------+------------+----------+----------------------+
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
|           1 | 2008-10-09 12:59:50 |         30 |       33 | 18446744073709551613 |
|           1 | 2008-10-09 13:05:10 |         10 |       33 | 18446744073709551593 |
|           1 | 2008-10-09 13:05:17 |         30 |       33 | 18446744073709551613 |
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
+-------------+---------------------+------------+----------+----------------------+

look in to the above query and the result the RemaingDays column  have return  wrong result when Valid_Days coulmn  have less value compare  with UsedDays column ,at that time it will return wrong values. it only come in select query using with columns. if we give direct value it will return actual result. Kindly  resolve the problem immediatly.

How to repeat:
select Paid_Status,Last_Payment,Valid_Days,DATEDIFF(NOW(),Last_Payment) AS UsedDays, Valid_Days - DATEDIFF(NOW(),Last_Payment) AS RemaingDays FROM studentdetails limit 10;
+-------------+---------------------+------------+----------+----------------------+
| Paid_Status | Last_Payment        | Valid_Days | UsedDays | RemaingDays          |
+-------------+---------------------+------------+----------+----------------------+
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
|           1 | 2008-10-09 12:59:50 |         30 |       33 | 18446744073709551613 |
|           1 | 2008-10-09 13:05:10 |         10 |       33 | 18446744073709551593 |
|           1 | 2008-10-09 13:05:17 |         30 |       33 | 18446744073709551613 |
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
|           0 | 0000-00-00 00:00:00 |          0 |     NULL |                 NULL |
+-------------+---------------------+------------+----------+----------------------+

Suggested fix:
check the both values at the time of minus(subraction).

[11 Nov 2008 7:44] Valeriy Kravchuk

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.67. In case of the same problem, please, send the results of

show create table studentdetails\G

[12 Dec 2008 0:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

[10 Feb 2018 17:03] Roy Lyseng

Posted by developer:
 
Closing this bug since it has been suspended for 9 years.