When I execute the prepared statement:
select SQL_CALC_FOUND_ROWS * from entries where ( START_DATE >= ? ) limit 300, 400
where START_DATE is of type datetime, I get the same results as if I run
select SQL_CALC_FOUND_ROWS * from entries where ( START_DATE >= ? ) limit 0, 400
This means that I am shown 400 results, and not 100 in the first query! Why is that?
Extra info:
I use SQL_CALC_FOUND_ROWS because I then execute the query select FOUND_ROWS() because I need the total results count in order to apply my pagination feature so as to show 100 entries per page. One query like the above returns about 35k total results (350 pages).
This is not true for other queries in the same table. For example, the query:
select SQL_CALC_FOUND_ROWS * from entries where ( NATIONALITY = ? ) limit 100, 200
successfully returns only 100 results. This is true for the other columns as well. Only the one of type datetime seems to have the problem.
My opinion - you are mixing things in your mind and misunderstood the LIMIT syntax idea.
According to documentation:
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
because as @zerkms noticed in comments:
LIMIT [OFFSET, ] NUMBER
that means in your case:
limit 300, 400
must return max 400 rows following after 300 :-)
and if you wonder why
select SQL_CALC_FOUND_ROWS * from entries where ( NATIONALITY = ? ) limit 100, 200
successfully returns only 100 results.
my only guess is your SQL result without any LIMIT contain just 200 rows (where ( NATIONALITY = ? ))
So there is no magic, but from your question it like you are expecting that LIMIT 100,200 mean for you "return me rows BETWEEN row 100 and row 200 from result" - but it is not how LIMIT works