CODEWITHSUNDEEP
mysql
josh
josh

Reputation: 145

MySQL query, storing a bit of calculated data just for life of query

I have the following MySQL query

SELECT
    (m.speed *  TIMESTAMPDIFF(MINUTE, m.arrival, m.departure) ) AS distance_traveled,
    (m.origin_x + distance_traveled) AS new_x,
    (m.origin_y + distance_traveled) AS new_y,
    m.city_id
FROM missions AS m
WHERE
        (new_x >= 1 AND new_x <= 15)
    AND
        (new_y >= 1 AND new_y <= 15)

Is there any way to use distance_traveled as a sort of variable just for this query? I wouldn't want to calculate the same math every time it is needed in the same query.

This query currently outputs the error

Unknown column 'distance_traveled' in 'field list'

Thanks!

Upvotes: 0

Views: 45

Answers (3)

Uueerdo
Uueerdo

Reputation: 15951

You can get away with this session variable trick, but opinions on how reliable they are varies widely:

Edit: As another answer made me notice, the result fields are being used in the WHERE, it might be safe in a HAVING, but even then I would no longer suggest it just to make your query shorter.

Edit#2: Since there were so many comments, I hesitate to delete the answer; instead I will add inline comments to show what is wrong.

SELECT
    @someVar := (m.speed *  TIMESTAMPDIFF(MINUTE, m.arrival, m.departure) ) AS distance_traveled,
    (m.origin_x + @someVar) AS new_x,
    (m.origin_y + @someVar) AS new_y,
    m.city_id
FROM missions AS m
WHERE
        (new_x >= 1 AND new_x <= 15) // Referencing aliases from the SELECT
    AND                              // is not allowed; those values are not
        (new_y >= 1 AND new_y <= 15) // actually determined until after the 
                                     // WHERE is evaluated. Even if that 
                                     // were not the case, crossing clauses
                                     // with session variables (which this 
                                     // effectively does) is a recipe for 
                                     // disaster.
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Unfortunately, MySQL does not offer a solution for the from clause that is convenient, reliable, and efficient. A subquery ends up getting materialized. Variables are unreliable.

It does, however, extend the having clause for this case. So, you can replace the where with having and do:

SELECT (m.speed *  TIMESTAMPDIFF(MINUTE, m.arrival, m.departure) ) AS distance_traveled,
       (m.origin_x + (m.speed *  TIMESTAMPDIFF(MINUTE, m.arrival, m.departure) )
       ) AS new_x,
       (m.origin_y + (m.speed *  TIMESTAMPDIFF(MINUTE, m.arrival, m.departure) )
       ) AS new_y,
       m.city_id
FROM missions  m
HAVING (new_x >= 1 AND new_x <= 15) AND
       (new_y >= 1 AND new_y <= 15)

Upvotes: 1

Sebas
Sebas

Reputation: 21532

Mysql still does not implement constant table expressions, therefore we need to go old school for this one:

SELECT
    m.distance_traveled,
    m.new_x,
    m.new_y,
    m.city_id
FROM (
        SELECT 
            `missions`.*, 
            (speed *  TIMESTAMPDIFF(MINUTE, arrival, departure)) AS distance_traveled,
            (speed *  TIMESTAMPDIFF(MINUTE, arrival, departure)) + origin_x AS new_x,
            (speed *  TIMESTAMPDIFF(MINUTE, arrival, departure)) + origin_y AS new_y
        FROM `missions`
    ) m
WHERE
    m.new_x >= 1 AND m.new_x <= 15
AND
    m.new_y >= 1 AND m.new_y <= 15

Upvotes: 1

Related Questions