Problem with LIMIT and a DATE

Using a limit with a repeated date produces seemingly random results.
I have to order on "date desc, id desc" to get a consistent order.
Is this expected behaviour?
Am I missing something obvious?

Sam Phillips
sambeau@mac.com
UK

How-To-Repeat:

mysql> select id, date
-> from news
-> order by date desc
-> ;
+----+------------+
| id | date |
+----+------------+
| 7 | 2003-03-08 |
| 8 | 2003-02-04 |
| 2 | 2003-01-18 |
| 3 | 2003-01-18 |
| 4 | 2003-01-18 |
| 5 | 2003-01-18 |
| 6 | 2003-01-18 |
+----+------------+
7 rows in set (0.01 sec)

mysql> select id, date
-> from news
-> order by date desc
-> limit 0,5;
+----+------------+
| id | date |
+----+------------+
| 7 | 2003-03-08 |
| 8 | 2003-02-04 |
| 6 | 2003-01-18 |
| 5 | 2003-01-18 |
| 4 | 2003-01-18 |
+----+------------+
5 rows in set (0.02 sec)

mysql> select id, date
-> from news
-> order by date desc
-> limit 5,5;
+----+------------+
| id | date |
+----+------------+
| 5 | 2003-01-18 |
| 6 | 2003-01-18 |
+----+------------+
2 rows in set (0.02 sec)



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13935@lists.mysql.com
To unsubscribe, e-mail

Re: Problem with LIMIT and a DATE

At 1:02 +0000 3/11/03, Sam Phillips wrote:
>Using a limit with a repeated date produces seemingly random results.
>I have to order on "date desc, id desc" to get a consistent order.
>Is this expected behaviour?
>Am I missing something obvious?

I don't see the problem. The query results you have below show the
correct dates to be expected. If you're wondering why you don't
get the id values you seem to think you ought to get, then yes,
you have to sort on id. If you don't sort a column, you get any
legal row, in whatever order the server chooses to return it.
ORDER BY is necessary if you require a column to be sorted a
particular way.

>
>Sam Phillips
>sambeau@mac.com
>UK
>
>How-To-Repeat:
>
>mysql> select id, date
> -> from news
> -> order by date desc
> -> ;
>+----+------------+
>| id | date |
>+----+------------+
>| 7 | 2003-03-08 |
>| 8 | 2003-02-04 |
>| 2 | 2003-01-18 |
>| 3 | 2003-01-18 |
>| 4 | 2003-01-18 |
>| 5 | 2003-01-18 |
>| 6 | 2003-01-18 |
>+----+------------+
>7 rows in set (0.01 sec)
>
>mysql> select id, date
> -> from news
> -> order by date desc
> -> limit 0,5;
>+----+------------+
>| id | date |
>+----+------------+
>| 7 | 2003-03-08 |
>| 8 | 2003-02-04 |
>| 6 | 2003-01-18 |
>| 5 | 2003-01-18 |
>| 4 | 2003-01-18 |
>+----+------------+
>5 rows in set (0.02 sec)
>
>mysql> select id, date
> -> from news
> -> order by date desc
> -> limit 5,5;
>+----+------------+
>| id | date |
>+----+------------+
>| 5 | 2003-01-18 |
>| 6 | 2003-01-18 |
>+----+------------+
>2 rows in set (0.02 sec)
>
>
>
>----------------------------------------------------------- ----------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail bugs-thread13935@lists.mysql.com
>To unsubscribe, e-mail


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13938@lists.mysql.com
To unsubscribe, e-mail

Re: Problem with LIMIT and a DATE

Sam,

Sam Phillips wrote:
> Using a limit with a repeated date produces seemingly random results.
> I have to order on "date desc, id desc" to get a consistent order.
> Is this expected behaviour?

It is not only expected, but only possible behaviour. Everything else will violate
basics of Relational Model.

Tables in Relational Databases Theory are descirbed in terms of Sets - they have
no particular order. This means server is free to store and process table rows in
any given order it decides at any point of time.

The only exception is that server must return rows in query *result* according to
ORDER BY clause.

When you use query without ORDER BY clause you tell the server you don't care
about order of rows in result:

SELECT * FROM tbl;

translates as:

"Give me all rows from tbl in whatever order you like"



LIMIT is applied to result of query so:

SELECT * FROM tbl LIMIT 5;

translates as:

"Take all rows from tbl in whatever order you like and give me only 5 of them"

which is obviously the same as

"Give me *some* 5 rows from tbl"


I hope that helps

Best regards
--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13939@lists.mysql.com
To unsubscribe, e-mail