CODEWITHSUNDEEP
mysql
Raffael
Raffael

Reputation: 20045

Odd behaviour of SELECT on MySQL

I observed this skipping rows for this type of expression quite a few times and always wondered whether this is a bug or if it makes sense - at least technically. The purpose of the queries is to generate sequential table content. Mostly DATEs or DATETIMEs.

t is just any table with at least 30 rows.

This version produces the result as intended:

set @date = "2012-01-01";

select
    @date := date_add(@date, interval 1 day) as d
from t
#having d < "2012-01-31"
limit 30
;

output:

'2012-01-02'
'2012-01-03'
'2012-01-04'
...
'2012-01-31'

now including the HAVING condition (so I don't have to use a number to limit how many rows I want to generate, but can simply specify an upper boudary):

set @date = "2012-01-01";

select
    @date := date_add(@date, interval 1 day) as d
from t
having d < "2012-01-31"
limit 30
;

output:

'2012-01-03'
'2012-01-05'
'2012-01-07'
...
'2012-01-29'
'2012-01-31'

But please mind - this is a WHY-question - not a HOW-question.

Upvotes: 1

Views: 806

Answers (1)

Vincent Savard
Vincent Savard

Reputation: 35907

The problem is that when you call the column d in your HAVING clause, it computes the expression @date := date_add(@date, interval 1 day) again. This means that interval 1 day is added twice to the initial @date variable, thus why you have gaps in your entries. You can verify this fact by adding an extra condition, for instance HAVING d < '2012-01-31' AND d > '2012-01-01'.

The correct query would be as follow :

SET @date = '2012-01-01';
SELECT @date := date_add(@date, interval 1 day) as d
FROM t
WHERE date_add(@date, interval 1 day) < '2012-01-31'
ORDER BY d
LIMIT 30;

Note that a HAVING clause should be used with aggregate functions, and that using LIMIT without an ORDER BY clause might give you results in an incorrect order.

Upvotes: 2

Related Questions