mysql join problem (bug?)

--Boundary_(ID_+Qby1XupuXRDCSlB9cc9Ow)
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT

Hi all,





I found this join problem (bug?) using mysql 3.23.53-max-nt (WinXP) AND
MySQL 4.0.5-beta (WinXP).

However, it works fine on MySQL 3.23.52-log (on Linux) and
4.0.1-alpha-nt(WinXP).





Thanks to DL Neil. He has found this:

> Cross-join works perfectly on 3.23.53-max-nt under Win2000 SP2, both

> from command line and MySQL Control Center 0.8.6-alpha.



Can anyone guess why and provide some insight please?

Could someone please confirm that it is a bug (on XP)? Or enlighten me
on writing the correct sql?





How-To-Repeat:



Step 1.



I have a table and data as shown in the script below.



CREATE TABLE test (



f1 int(11) NOT NULL default '0',



f2 int(11) NOT NULL default '0'



) TYPE=MyISAM;





Step 2.





#



# Dumping data for table `test`



#







INSERT INTO test VALUES (1, 2);



INSERT INTO test VALUES (1, 4);





Step 3.



Then I do a join with this query:







SELECT t1.*, t2.* from test as t1, test as t2 where t1.f1 = t2.f1





Step 4.





Unfortunately, I got this output.





f1 f2 f1 f2



1 2 1 2



1 2 1 2



1 4 1 4



1 4 1 4







I would expect the output like this (which I get on Linux using MySQL

3.23.52-log). I also get this using other DB (e.g. Access).







f1 f2 f1 f2



1 2 1 2



1 4 1 2



1 2 1 4



1 4 1 4





Does anyone else experience the same problem?





Thanks.



Haksun




--Boundary_(ID_+Qby1XupuXRDCSlB9cc9Ow)--

Re: mysql join problem (bug?)

Haksun Li writes:
> Hi all,
>
> I would expect the output like this (which I get on Linux using MySQL
>
> 3.23.52-log). I also get this using other DB (e.g. Access).
>
> f1 f2 f1 f2
>
> 1 2 1 2
> 1 4 1 2
> 1 2 1 4
> 1 4 1 4
>
> Does anyone else experience the same problem?
>
> Thanks.
>
>
>
> Haksun
>

Hi!

I get exactly the same output as above with 4.0.6.

But this is not relevant.

With relational databases, if you do not specify the manner in which
you wish result set to be ordered, you can get it in any order. And
any order is all right, according to all pertaining standards.


--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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-thread13186@lists.mysql.com
To unsubscribe, e-mail

RE: mysql join problem (bug?)

Haksun Li writes:
> Sinisa,
>
> Thanks for the reply.
>
> However, it is not the order.
>
> The rows are different!
>
> For example, the row "1 2 1 4" are not there when I use
> mysql 3.23.53-max-nt (on WinXP) and MySQL 4.0.5-beta (WinXP).
>
>
> Like I said, I got:
> f1 f2 f1 f2
> 1 2 1 2
> 1 2 1 2
> 1 4 1 4
> 1 4 1 4
>
>
> This is wrong. The rows "1 2 1 4" and "1 4 1 2" should be there instead!
>
>
> Haksun
>

I understand now.

But I do get rows as you describe with 4.0.6. So that bug was fixed
meanwhile.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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-thread13188@lists.mysql.com
To unsubscribe, e-mail

RE: mysql join problem (bug?)

No.... the bug is not fixed!

Are you using Win XP?

Even if the bug does not show up in the version you are using, it does
not mean the bug is gone..... (since it is not known that any action is
taken to address the problem)

Like I said, the bug does not show up when I use:
MySQL 3.23.52-log (on Linux).
4.0.1-alpha-nt(WinXP).
3.23.53-max-nt (Win2000 SP2)

So, something funny with the mysql code is there.... just that we don't
know yet...

But it may pop up unexpectedly in the future releases and cause other
unexpected problems....

Also, the same binary 3.23.53-max-nt works on Win2000 SP2 but does not
work in WinXP should raise some concerns....

It is unsafe to use a DB where you can't be sure it will always produce
the correct results.

How-To-Repeat:

Try the script on different OSs with different versions to understand
what is really going on?


Haksun

> -----Original Message-----
> From: Sinisa Milivojevic [mailto:sinisa@mysql.com]
> Sent: Monday, December 02, 2002 3:36 PM
> To: haksunli@engin.umich.edu
> Cc: bugs@lists.mysql.com
> Subject: RE: mysql join problem (bug?)
>
> Haksun Li writes:
> > Sinisa,
> >
> > Thanks for the reply.
> >
> > However, it is not the order.
> >
> > The rows are different!
> >
> > For example, the row "1 2 1 4" are not there when I use
> > mysql 3.23.53-max-nt (on WinXP) and MySQL 4.0.5-beta (WinXP).
> >
> >
> > Like I said, I got:
> > f1 f2 f1 f2
> > 1 2 1 2
> > 1 2 1 2
> > 1 4 1 4
> > 1 4 1 4
> >
> >
> > This is wrong. The rows "1 2 1 4" and "1 4 1 2" should be there
instead!
> >
> >
> > Haksun
> >
>
> I understand now.
>
> But I do get rows as you describe with 4.0.6. So that bug was fixed
> meanwhile.
>
> --
> Regards,
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic

> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ 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-thread13189@lists.mysql.com
To unsubscribe, e-mail

Re: mysql join problem (bug?)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Haksun Li wrote:
> No.... the bug is not fixed!
>
> Are you using Win XP?
>
> Even if the bug does not show up in the version you are using, it does
> not mean the bug is gone..... (since it is not known that any action is
> taken to address the problem)
>
> Like I said, the bug does not show up when I use:
> MySQL 3.23.52-log (on Linux).
> 4.0.1-alpha-nt(WinXP).
> 3.23.53-max-nt (Win2000 SP2)
>
> So, something funny with the mysql code is there.... just that we don't
> know yet...
>
> But it may pop up unexpectedly in the future releases and cause other
> unexpected problems....
>
> Also, the same binary 3.23.53-max-nt works on Win2000 SP2 but does not
> work in WinXP should raise some concerns....
>
> It is unsafe to use a DB where you can't be sure it will always produce
> the correct results.
>
> How-To-Repeat:
>
> Try the script on different OSs with different versions to understand
> what is really going on?
>
>
> Haksun
>
>
>>-----Original Message-----
>>From: Sinisa Milivojevic [mailto:sinisa@mysql.com]
>>Sent: Monday, December 02, 2002 3:36 PM
>>To: haksunli@engin.umich.edu
>>Cc: bugs@lists.mysql.com
>>Subject: RE: mysql join problem (bug?)
>>
>>Haksun Li writes:
>>
>>>Sinisa,
>>>
>>>Thanks for the reply.
>>>
>>>However, it is not the order.
>>>
>>>The rows are different!
>>>
>>>For example, the row "1 2 1 4" are not there when I use
>>>mysql 3.23.53-max-nt (on WinXP) and MySQL 4.0.5-beta (WinXP).
>>>
>>>
>>>Like I said, I got:
>>>f1 f2 f1 f2
>>>1 2 1 2
>>>1 2 1 2
>>>1 4 1 4
>>>1 4 1 4
>>>
>>>
>>>This is wrong. The rows "1 2 1 4" and "1 4 1 2" should be there

On a Windows XP SP1 box:

mysql> select version();
+----------------------+
| version() |
+----------------------+
| 4.0.5-beta-max-debug |
+----------------------+
1 row in set (0.26 sec)

mysql> CREATE TABLE test (
-> f1 int(11) NOT NULL default '0',
-> f2 int(11) NOT NULL default '0'
-> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test VALUES (1, 2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (1, 4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT t1.*, t2.* from test as t1, test as t2 where t1.f1 = t2.f1;
+----+----+----+----+
| f1 | f2 | f1 | f2 |
+----+----+----+----+
| 1 | 2 | 1 | 2 |
| 1 | 4 | 1 | 2 |
| 1 | 2 | 1 | 4 |
| 1 | 4 | 1 | 4 |
+----+----+----+----+
4 rows in set (0.00 sec)

mysql>

-Mark

- --
For technical support contracts, visit https://order.mysql.com/?ref=mmma

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE96+dztvXNTca6JD8RAlJMAKCvsdvCprFAizo3e/PcTjP6BiVAxQCf fjNA
0VLeuTmW7p5ArP9/7/9jA4o=
=g5T7
-----END PGP SIGNATURE-----


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