How can I factor out repeated expressions in an SQL Query? Column aliases don't seem to be the ticket

Question

So, I've got a query that looks something like this:

SELECT id, 
    DATE_FORMAT(CONVERT_TZ(callTime,'+0:00','-7:00'),'%b %d %Y') as callDate, 
    DATE_FORMAT(CONVERT_TZ(callTime,'+0:00','-7:00'),'%H:%i') as callTimeOfDay, 
    SEC_TO_TIME(callLength) as callLength
FROM cs_calldata WHERE 
    customerCode='999999-abc-blahblahblah' AND 
    CONVERT_TZ(callTime,'+0:00','-7:00') >= '2010-04-25' AND
    CONVERT_TZ(callTime,'+0:00','-7:00') <= '2010-05-25'

If you're like me, you probably start thinking that maybe it would improve readability and possibly the performance of this query if I wasn't asking it to compute CONVERT_TZ(callTime,'+0:00','-7:00') four separate times.

So I try to create a column alias for that expression and replace further occurances with that alias:

SELECT id, 
    CONVERT_TZ(callTime,'+0:00','-7:00') as callTimeZoned,
    DATE_FORMAT(callTimeZoned,'%b %d %Y') as callDate, 
    DATE_FORMAT(callTimeZoned,'%H:%i') as callTimeOfDay, 
    SEC_TO_TIME(callLength) as callLength
FROM cs_calldata WHERE 
    customerCode='5999999-abc-blahblahblah' AND 
    callTimeZoned >= '2010-04-25' AND
    callTimeZoned <= '2010-05-25'

This is when I learned, to quote the MySQL manual:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

So, that approach would seem to be dead in the water.

How is someone writing queries with recurring expressions like this supposed to deal with it?

Energetic

Answer 1

You can define aliases in a derived table and then reference them in the outer query:

SELECT callTimeZoned, callLength,
    DATE_FORMAT(callTimeZoned,'%b %d %Y') as callDate, 
    DATE_FORMAT(callTimeZoned,'%H:%i') as callTimeOfDay 
FROM (
    SELECT
        CONVERT_TZ(callTime,'+0:00','-7:00') as callTimeZoned,
        SEC_TO_TIME(callLength) as callLength
    FROM cs_calldata
    WHERE customerCode='5999999-abc-blahblahblah'
) AS d
WHERE 
    callTimeZoned BETWEEN '2010-04-25' AND '2010-05-25'

Clinton

Two questions: 1) Generally: should I worry that this would end up selecting all the rows in cs_calldata in the inner query, and then filtering them using the outer (which would seem to be inefficient), or is this something most databases are smart enough to create better query plans for? 2) Specifically, is MySQL that smart? :) – Energetic

I wouldn't count on MySQL to combine row restrictions from the inner and outer queries, but EXPLAIN would help to confirm this one way or the other. At least the condition for customerCode is in the inner query, so that limits the number of rows that get treated by the functions in the inner query. – Clinton

1) Generally no, you don't have to worry. The query optimizer on many RDBMSes will collapse/merge everything. Using an inline view will not materialize before applying outer parts of the query. 2) Specifically MySQL, I don't know. – Interjection

Answer 2

SELECT id, 
    CONVERT_TZ(callTime,'+0:00','-7:00') as callTimeZoned,
    DATE_FORMAT(callTimeZoned,'%b %d %Y') as callDate, 
    DATE_FORMAT(callTimeZoned,'%H:%i') as callTimeOfDay, 
    SEC_TO_TIME(callLength) as callLength
FROM cs_calldata WHERE 
    customerCode='5999999-abc-blahblahblah' having
    callTimeZoned >= '2010-04-25' AND
    callTimeZoned <= '2010-05-25'

(Note the switch from AND to having on the third to last line.)

Vent