Wrong results when using LIMIT
Hello All,
I have the following query which returns ONE row:
SELECT DISTINCT user.Id, UCASE(Surname), Name, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(Dob)),'%Y')+0 AS Age,0 as Expiration
FROM ( SELECT UserId,Kind FROM transaction,transactiontype
WHERE transactiontype.id=transaction.transactiontypeid
AND transactiontype.kind IN (0,2,1)
AND transaction.enddate>'2005-09-27' GROUP BY userid,kind) AS t1, user
WHERE Surname>='A' AND Surname<'['
AND PrivilegeId=1 HAVING COUNT(UserId)>2 ORDER By Surname,Name LIMIT 0,1000
I get the correct result ONLY when the row_count parameter of the LIMIT clause is less that the number of records in the 'user' table, otherwise the query returns the first record in the user table according to the primary key (which is also the first record actually entered into the table, but I'm not sure whether this matters). Omitting the LIMIT clause returns the same incorrect result.
Am I doing something wrong or might it be a bug? I'm using MySQL 4.1.12 (Linux)
Thank you,
Claudio
Edited 2 time(s). Last edit at 09/27/2005 03:22AM by Claudio Bardelle.