Fw: 4.0.10 - order by - varius sort

>
> ----- Original Message -----
> From: "Sinisa Milivojevic"
> To:
> Cc:
> Sent: Friday, February 07, 2003 1:41 PM
> Subject: Re: 4.0.10 - order by - varius sort
>
>
> > Irek writes:
> > > How-to-repeat :
> > >
> > > I don't know when, but sometimes in:
> > >
> > > select tab026.*
> > > from tab032 left join tab026 on tab032.m_field1 = tab026.m_field2
> > > group by tab026.id
> > > order by tab026.m_sort
> > >
> > > database returns rown with various sort. In all returned rows value
> m_sort
> > > = 0, but select returns for example:
> > >
> > > valueA, valueAA,0
> > > valueB, valueBB, 0
> > > valueC, valueCC, 0
> > >
> > > but later returns:
> > >
> > > valueB, valueBB, 0
> > > valueA, valueAA,0
> > > valueC, valueCC, 0
> > >
> > > Is it bug ?
> > >
> > > (I have database with this error, but it about 7000 rows (700 kB)....
I
> can
> > > send it. Char set in MySQL is latin2)
> > >
> > > Regards
> > > Irek Smaczny
> > >
> >
> > Hi!
> >
> > Yes, it should be a bug. ORDER BY on the same table, with the same
> > charset should always retun tuples in the same order.
> >
> > Please provide a full test case, including table dump and upload it to
> >
> > ftp://support.mysql.com:/pub/mysql/secret
> >
> > and let us know a filename.
> >
> > It must be a repeatable test case, i.e. a bug should pop-up always.
> >
> > --
> > __ ___ ___ ____ __
> > / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> > <___/ www.mysql.com
> >
> > Join MySQL Users Conference and Expo:
> > http://www.mysql.com/events/uc2003/
> >

I send file to /secret/. File name order_error_mysql.zip. This is a dump 2
tables and this select.

Regards
Irek Smaczny
smaczny@dst.com.pl



------------------------------------------------------------ ---------
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-thread13728@lists.mysql.com
To unsubscribe, e-mail

Re: Fw: 4.0.10 - order by - varius sort

Irek writes:
> > > > select tab026.*
> > > > from tab032 left join tab026 on tab032.m_field1 = tab026.m_field2
> > > > group by tab026.id
> > > > order by tab026.m_sort
> > > >
> > > > valueA, valueAA,0
> > > > valueB, valueBB, 0
> > > > valueC, valueCC, 0
> > > >
>
> I send file to /secret/. File name order_error_mysql.zip. This is a dump 2
> tables and this select.
>
> Regards
> Irek Smaczny
> smaczny@dst.com.pl

Hi!

I have taken a look at your test case and it is NOT a bug.

This is because you have included only m_sort in ORDER BY clause,
which means that other tuples can be returned in random order.

This is according to standard, where RDBMS optimiser may return tuples
in any order, unless ORDER BY all columns is specified. If m_osrt had
unique values for each row, then you would have get your results in
the same order as many times as you have run your query.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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-thread13732@lists.mysql.com
To unsubscribe, e-mail

Re: Fw: 4.0.10 - order by - varius sort

OK, thanks for all. I asked becouse in oldest version (4.0.4 or older) ORDER
BY clause returned rows in my test case ALWAYS (!)in the same order, but in
4.0.10 in random order.

It is not a problem to change query :-)

Regards
Irek Smaczny




------------------------------------------------------------ ---------
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-thread13743@lists.mysql.com
To unsubscribe, e-mail

Re: Fw: 4.0.10 - order by - varius sort

Irek,

Irek wrote:
> OK, thanks for all. I asked becouse in oldest version (4.0.4 or older) ORDER
> BY clause returned rows in my test case ALWAYS (!)in the same order, but in
> 4.0.10 in random order.
>
> It is not a problem to change query :-)

According to Relational model theory Tables are Unordered Sets of tuples.
Because of this Server is free to:

1. Store rows on ANY order
2. Process rows in ANY order

3. Return rows in ANY order unless ORDER BY clause is specified.

You only can affect 3. behaviour.

Note that it is ANY order (or Undefined if you wish). Server is free to decide
particular order in any moment of time. That's why it can remain always the same
or to be changed in random manner.

If you think a while you will realize this simple requirement (freedom actually)
is source of infinite posibilities for optimizations. If server is not free to
decide or change internal order it will be very restricted.


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-thread13802@lists.mysql.com
To unsubscribe, e-mail