[MDEV-14306] SELECT With Variable Assignation := and HAVING: Gives wrong results - Error Shall be Raised Created: 2017-11-07  Updated: 2017-12-16  Resolved: 2017-12-01

Status: Closed
Project: MariaDB Server
Component/s: Documentation
Affects Version/s: 10.2.8, 10.2.10
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Juan Telleria Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

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

 Comments   
Comment by Alice Sherepa [ 2017-11-27 ]

Setting a variable in one place (such as the SELECT list) and reading it in another (such as the HAVING clause) might give wrong results, it is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined. (https://mariadb.com/kb/en/library/user-defined-variables/)
there is also explanation here https://dev.mysql.com/doc/refman/5.7/en/user-variables.html
In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected:

SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

The reference to b in the HAVING clause refers to an alias for an expression in the select list that uses @aa. This does not work as expected: @aa contains the value of id from the previous selected row, not from the current row.

Comment by Juan Telleria [ 2017-12-01 ]

I see... a workaround would be that you could use column aliases within the selection part of a SELECT statement itself, avoiding to have to overwrite all the column content into another for it's use.

Simply for the Execution Order it shall be checked which are the dependant columns, and stablish an execution order.

Generated at Thu Feb 08 08:12:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.