MYSQL 5.6 does not recognize the query as it should

Question

Greetings!

I’m building a query to pick all the times the value was 0 on a given day and the highest time of the previous day.

The data in my database is similar to the data I put in SQL Fiddle. Time_stamp and Value Column. So far, I have built this query to perform the desired query:

    SELECT 
    T1.Time_stamp,
    CASE
        WHEN T2.Time_Stamp THEN T2.Time_Stamp
    END Time_stamp,
    CASE
        WHEN (T2.Valor = 0) THEN T2.Time_Stamp
        WHEN (T1.Valor = 0) THEN T1.Time_Stamp
    END AS T2
FROM
    (SELECT 
        Time_Stamp,
            Valor,
            @seq1:=@seq1 + 1 AS Seq
    FROM
        test, (SELECT @seq1:=0) r
    WHERE
        CAST(time_stamp AS DATE) = '2018-11-02'

    ORDER BY Time_Stamp DESC) T1
        LEFT JOIN
    (SELECT 
        Time_Stamp, Valor, 
            @seq3:=@seq3 + 1 AS Seq
    FROM
        test, (SELECT @seq3:=0) r
    WHERE
        valor = 0.0  and CAST(time_stamp AS DATE) = '2018-11-01'
    ORDER BY TIME_STAMP DESC
    LIMIT 0,1) T2 
    ON T1.Seq = T2.Seq
WHERE
    T1.valor = 0

The reason I am writing this question is that this same query does not work in my database, but works in SQL Fiddle. I would like to know what the possible reasons are and how to get around that. I would also like to know, if possible, some more efficient alternative to achieve the desired result.

From now on, thank you.

Answer 1

I don’t think you need to put too much sub-query.

You can use UNION and split your query in two so you don’t have to reinvent the wheel

mysql> set @date = '2018-11-02';
Query OK, 0 rows affected (0.03 sec)

mysql> select * from test where valor = 0 
and (DATE(Time_Stamp) = @date) 
UNION ALL 
(SELECT * from test where valor = 0 
AND DATE(Time_Stamp) = @date - interval 1 day 
ORDER BY Time_Stamp DESC LIMIT 1);

+---------------------+-------+
| Time_Stamp          | valor |
+---------------------+-------+
| 2018-11-02 04:37:59 |     0 |
| 2018-11-02 04:07:59 |     0 |
| 2018-11-02 03:29:04 |     0 |
| 2018-11-01 23:36:40 |     0 |
+---------------------+-------+
4 rows in set (0.03 sec)