Mysql Question posted in
The official MySQL documentation can be found here.

In MySQL why := is executed inside of case statement even if the condition is False and how can I fix this?

This problem I am trying to solve on HackerRank. In this problem, I have to group consecutive dates. Two dates are consecutive when the difference between two dates is 1 and in this problem statement, the start date and end date are always consecutive.

My approach is to group the consecutive dates. To identify the consecutive dates I am using a variable @rn. When the current start date is equal to the previous end date then I will have the same rn otherwise I am incrementing rn. Then I wanted to group by rn and get the min(start date) and max(end date) from each group with the same rn. But before implementing this logic I was facing an issue with the @rn variable being incremented even when the when condition is false inside of the case clause in MySQL.

set @rn = 0;

with tasks as (
    select distinct Start_Date, End_Date
    from Projects
    order by Start_Date
)

select
Start_Date, End_Date,
case 
when lag(End_Date, 1) over(Order by Start_Date) != Start_Date then @rn:=@rn+1
else @rn
end as cnt
from tasks

In the above query @rn := @rn+1 is executed even when the when condition is false.

I encountered this issue which trying to solve a problem from HackeRank.

If someone can explain why @run := @rn+1 is executed even when the condition is false it would be great.

Here is the output that the above query is generating for the specified problem :

Output I got :

2015-10-01 2015-10-02 0

2015-10-02 2015-10-03 1

2015-10-03 2015-10-04 2

2015-10-04 2015-10-05 3

2015-10-11 2015-10-12 24

2015-10-12 2015-10-13 5

2015-10-15 2015-10-16 24

2015-10-17 2015-10-18 24

2015-10-19 2015-10-20 24

2015-10-21 2015-10-22 24

2015-10-25 2015-10-26 24

...

Output I am expecting

2015-10-01 2015-10-02 1

2015-10-02 2015-10-03 1

2015-10-03 2015-10-04 1

2015-10-04 2015-10-05 1

2015-10-11 2015-10-12 2

2015-10-12 2015-10-13 2

2015-10-15 2015-10-16 3

2015-10-17 2015-10-18 4

2015-10-19 2015-10-20 5

2015-10-21 2015-10-22 6

2015-10-25 2015-10-26 7

...
Tags:

2

Answers


  1. 0

    Given that you are using MySQL 8+, which makes available the window functions, there isn’t even any need to resort to using session/user variables. With the help of window functions, your query is actually straightforward to phrase:

    WITH cte AS (
    SELECT *, CASE WHEN LAG(End_Date) OVER (ORDER BY Start_Date) != Start_Date
                   THEN 1 ELSE 0 END AS cnt
    FROM tasks
)

SELECT Start_Date, End_Date, 1 + SUM(cnt) OVER (ORDER BY Start_Date) AS total
FROM cte
ORDER BY Start_Date;
    Login or Signup to reply.
  2. 0

    Prior to mysql 8 the way to do this was using a cross join

    select t.start_date,t.end_date,
        if(end_date > date_add(@dt ,interval 1 day) ,@rn:=@rn+1,@rn:=@rn) cnt,
        @dt := end_date
from t 
cross join (select @rn:=1,@dt:='2021-01-01') r
order by start_date;

    https://dbfiddle.uk/FZMrDDnb

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search