Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.2.8, 10.2.10
-
None
-
Windows
Description
When using Variable Assignation in a SELECT Query and using a HAVING (Filtering After Table Materialization) Wrong Results are returned:
SELECT |
`MachineUT`,
|
`Year`, |
@Date_MainStartDate := DATE(MainStartDate) |
AS `Study_Start_Date_In_Maintenance`, |
@Date_MainEndDate :=
|
CASE |
WHEN |
DATE(MainEndDate) IS NULL |
OR DATE(MainEndDate) = DATE('0000-00-00') |
OR (NOW() <= DATE(MainEndDate)) |
THEN |
DATE(NOW()) |
ELSE |
DATE(MainEndDate) |
END AS `Study_End_Date_In_Maintenance`, |
CASE |
-- CASE 0: Study Year NOT IN Maintenance |
WHEN |
`Year` NOT BETWEEN YEAR(@Date_MainStartDate) AND YEAR(@Date_MainEndDate) |
THEN |
NULL |
-- CASE 1: Maintenance Start & End Date NOT IN Current Year |
WHEN |
`Year` <> YEAR(@Date_MainStartDate) |
AND `Year` <> YEAR(@Date_MainEndDate) |
THEN |
DAYOFYEAR(CONCAT(`Year`,'-12','-31')) |
|
-- CASE 2: Maintenance Start & End Date IN Current Year |
WHEN |
`Year` = YEAR(@Date_MainStartDate) |
AND `Year` = YEAR(@Date_MainEndDate) |
THEN |
TIMESTAMPDIFF(
|
DAY, |
DATE(@Date_MainStartDate), |
DATE(@Date_MainEndDate)) + 1 |
|
-- CASE 3: Maintenance Start Date IN Current Year & Maintenance End Date NOT IN Current Year |
WHEN |
`Year` = YEAR(@Date_MainStartDate) |
AND `Year` <> YEAR(@Date_MainEndDate) |
THEN |
TIMESTAMPDIFF(
|
DAY, |
DATE(@Date_MainStartDate), |
DATE(CONCAT(`Year`,'-12','-31'))) + 1 |
|
-- CASE 4: Maintenance Start Date NOT IN Current Year & Maintenance End Date IN Current Year |
WHEN |
`Year` <> YEAR(@Date_MainStartDate) |
AND `Year` = YEAR(@Date_MainEndDate) |
THEN |
TIMESTAMPDIFF(
|
DAY, |
DATE(CONCAT(`Year`,'-01','-01')), |
DATE(@Date_MainEndDate)) + 1 |
ELSE |
NULL |
END AS `LifeDays_UT_Year` |
FROM |
failure_rates.t1_Population_Year AS A |
INNER JOIN eo_mariadb.t_machines AS B |
USING(MachineUT)
|
WHERE |
B.Enabled = 1
|
AND DATE(B.MainStartDate) <> DATE('0000-00-00') |
AND DATE(B.MainStartDate) IS NOT NULL |
HAVING |
`LifeDays_UT_Year` IS NOT NULL |
ORDER BY |
`MachineUT`,
|
`Year` |
When Using "HAVING `LifeDays_UT_Year` IS NOT NULL" Calculations are not done correctly, and when not using such clause, yes.
I knew that Variable Assignation := did not worked properly when used with GROUP BY, but did not know that also did not worked with HAVING.
My suggestion on how to avoid Incorrect Calculations:
An error shall be raised in MariaDB when using Variable Assignation in a SELECT with a GROUP BY or HAVING Clause.
Thank you,
Juan