inner join ... on problem

Hi,
I found some problem (may be bug or documentation bug?)

How-to-repeat:

CREATE TABLE table1 (
id int(11) NOT NULL auto_increment,
table2_id int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY replace_id (table2_id)
) TYPE=MyISAM;

--
-- Dumping data for table 'table1'
--

INSERT INTO table1 VALUES (8264,2506);
INSERT INTO table1 VALUES (8299,2517);
INSERT INTO table1 VALUES (8301,2518);
INSERT INTO table1 VALUES (8302,2519);
INSERT INTO table1 VALUES (8303,2520);
INSERT INTO table1 VALUES (8304,2521);
INSERT INTO table1 VALUES (8305,2522);

--
-- Table structure for table 'table2'
--

CREATE TABLE table2 (
id int(11) NOT NULL auto_increment,
PRIMARY KEY (id)
) TYPE=MyISAM;

--
-- Dumping data for table 'table2'
--

INSERT INTO table2 VALUES (2517);
INSERT INTO table2 VALUES (2518);
INSERT INTO table2 VALUES (2519);
INSERT INTO table2 VALUES (2520);
INSERT INTO table2 VALUES (2521);
INSERT INTO table2 VALUES (2522);


select * from table1 inner JOIN table2 ON table1.table2_id = table2.id where
table1.table2_id >0 order by table1.id LIMIT 0, 5;

return: 2 rows in set

But:
select * from table1 inner JOIN table2 ON table1.table2_id = table2.id where
table1.table2_id >0 LIMIT 0, 5;
select * from table1 LEFT JOIN table2 ON table1.table2_id = table2.id where
table1.table2_id >0 LIMIT 0, 5;
or
select * from table1 LEFT JOIN table2 ON table1.table2_id = table2.id LIMIT
0, 5;

return: 5 rows in set

Obviously, 5 rows - correct result.

System: Linux 2.4.19-pre6aa1 #1 SMP 18:37:14 MSD 2002 i686 unknown

Sam pathes:
/usr/local/sbin:/usr/sbin:/sbin:/bin:/sbin:/usr/bin:/usr/sbi n:/usr/local/bin:
/usr/local/sbin:/usr/bin/X11:/usr/X11R6/bin:/root/bin
GCC: gcc (GCC) 3.2.1
LIBC:
-rwxr-xr-x 1 root root 1285884 Oct 10 21:19 libc-2.2.4.so
lrwxrwxrwx 1 root root 13 Nov 10 21:14 libc.so.6 ->
libc-2.2.4.so
-rw-r--r-- 1 root root 27338282 Oct 10 20:48 libc.a
-rw-r--r-- 1 root root 178 Oct 10 20:48 libc.so

Configure:
CC=/usr/local/gcc/bin/gcc \
CFLAGS="-O4 -march=pentium3 -mcpu=pentium3 -msse -pipe"
CXX=/usr/local/gcc/bin/gcc \
CXXLDFLAGS="-Wl,--defsym -Wl,__cxa_pure_virtual=0" \
CXXFLAGS="-O4 -march=pentium3 -mcpu=pentium3 -msse -pipe -felide-constructors
-fno-exceptions -fno-rtti -DUSE_MYSYS_NEW" \
../configure --build=ix86-pc-linux-gnu \
--prefix=/usr --libexecdir=/usr/sbin \
--localstatedir=/var/lib/mysql \
--mandir=/usr/share/man --infodir=/usr/share/info \
--enable-assembler \
--with-mysqld-ldflags=-all-static \
--with-mysql-user=mysql \
--with-innodb \
--with-unix-socket-path=/var/lib/mysql/mysql.sock \
--with-extra-charsets=latin1,koi8_ru,cp1251 \
--enable-thread-safe-client

--
Best regards.
Alexander Y. Fomichev
Public PGP key: http://sysadminday.org.ru/gluk.asc

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

Re: inner join ... on problem

Hi,

FYI :
I get the same bug with MySQL-4.1 and 4.0.6 :

mysql> select * from table1 inner JOIN table2 ON table1.table2_id =
table2.id where
-> table1.table2_id >0 order by table1.id LIMIT 0, 5;
+------+-----------+------+
| id | table2_id | id |
+------+-----------+------+
| 8299 | 2517 | 2517 |
| 8301 | 2518 | 2518 |
| 8302 | 2519 | 2519 |
| 8303 | 2520 | 2520 |
+------+-----------+------+
4 rows in set (0.00 sec)

mysql> select * from table1 inner JOIN table2 ON table1.table2_id =
table2.id where
-> table1.table2_id >0 LIMIT 0, 5;
+------+-----------+------+
| id | table2_id | id |
+------+-----------+------+
| 8299 | 2517 | 2517 |
| 8301 | 2518 | 2518 |
| 8302 | 2519 | 2519 |
| 8303 | 2520 | 2520 |
| 8304 | 2521 | 2521 |
+------+-----------+------+
5 rows in set (0.00 sec)

Regards,
Jocelyn


----- Original Message -----
From: "Alexander Y. Fomichev"
To:
Cc:
Sent: Tuesday, December 03, 2002 11:30 AM
Subject: inner join ... on problem


> Hi,
> I found some problem (may be bug or documentation bug?)
>
> How-to-repeat:
>
> CREATE TABLE table1 (
> id int(11) NOT NULL auto_increment,
> table2_id int(11) NOT NULL default '0',
> PRIMARY KEY (id),
> KEY replace_id (table2_id)
> ) TYPE=MyISAM;
>
> --
> -- Dumping data for table 'table1'
> --
>
> INSERT INTO table1 VALUES (8264,2506);
> INSERT INTO table1 VALUES (8299,2517);
> INSERT INTO table1 VALUES (8301,2518);
> INSERT INTO table1 VALUES (8302,2519);
> INSERT INTO table1 VALUES (8303,2520);
> INSERT INTO table1 VALUES (8304,2521);
> INSERT INTO table1 VALUES (8305,2522);
>
> --
> -- Table structure for table 'table2'
> --
>
> CREATE TABLE table2 (
> id int(11) NOT NULL auto_increment,
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
> --
> -- Dumping data for table 'table2'
> --
>
> INSERT INTO table2 VALUES (2517);
> INSERT INTO table2 VALUES (2518);
> INSERT INTO table2 VALUES (2519);
> INSERT INTO table2 VALUES (2520);
> INSERT INTO table2 VALUES (2521);
> INSERT INTO table2 VALUES (2522);
>
>
> select * from table1 inner JOIN table2 ON table1.table2_id = table2.id
where
> table1.table2_id >0 order by table1.id LIMIT 0, 5;
>
> return: 2 rows in set
>
> But:
> select * from table1 inner JOIN table2 ON table1.table2_id = table2.id
where
> table1.table2_id >0 LIMIT 0, 5;
> select * from table1 LEFT JOIN table2 ON table1.table2_id = table2.id
where
> table1.table2_id >0 LIMIT 0, 5;
> or
> select * from table1 LEFT JOIN table2 ON table1.table2_id = table2.id
LIMIT
> 0, 5;
>
> return: 5 rows in set
>
> Obviously, 5 rows - correct result.
>
> System: Linux 2.4.19-pre6aa1 #1 SMP 18:37:14 MSD 2002 i686 unknown
>
> Sam pathes:
>
/usr/local/sbin:/usr/sbin:/sbin:/bin:/sbin:/usr/bin:/usr/sbi n:/usr/local/bin
:
> /usr/local/sbin:/usr/bin/X11:/usr/X11R6/bin:/root/bin
> GCC: gcc (GCC) 3.2.1
> LIBC:
> -rwxr-xr-x 1 root root 1285884 Oct 10 21:19 libc-2.2.4.so
> lrwxrwxrwx 1 root root 13 Nov 10 21:14 libc.so.6 ->
> libc-2.2.4.so
> -rw-r--r-- 1 root root 27338282 Oct 10 20:48 libc.a
> -rw-r--r-- 1 root root 178 Oct 10 20:48 libc.so
>
> Configure:
> CC=/usr/local/gcc/bin/gcc \
> CFLAGS="-O4 -march=pentium3 -mcpu=pentium3 -msse -pipe"
> CXX=/usr/local/gcc/bin/gcc \
> CXXLDFLAGS="-Wl,--defsym -Wl,__cxa_pure_virtual=0" \
>
CXXFLAGS="-O4 -march=pentium3 -mcpu=pentium3 -msse -pipe -felide-constructor
s
> -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW" \
> ./configure --build=ix86-pc-linux-gnu \
> --prefix=/usr --libexecdir=/usr/sbin \
> --localstatedir=/var/lib/mysql \
> --mandir=/usr/share/man --infodir=/usr/share/info \
> --enable-assembler \
> --with-mysqld-ldflags=-all-static \
> --with-mysql-user=mysql \
> --with-innodb \
> --with-unix-socket-path=/var/lib/mysql/mysql.sock \
> --with-extra-charsets=latin1,koi8_ru,cp1251 \
> --enable-thread-safe-client
>
> --
> Best regards.
> Alexander Y. Fomichev
> Public PGP key: http://sysadminday.org.ru/gluk.asc
>
> ------------------------------------------------------------ ---------
> 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-thread13193@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-thread13194@lists.mysql.com
To unsubscribe, e-mail

Re: inner join ... on problem

Alexander Y. Fomichev writes:
> Hi,
> I found some problem (may be bug or documentation bug?)
>
> How-to-repeat:
>


[skip]

> --
> Best regards.
> Alexander Y. Fomichev
> Public PGP key: http://sysadminday.org.ru/gluk.asc
>

Hi!

Thank you for your bug report, which helped us fix a bug in 4.0.

This is an interim fix and final one will come in 4.0.6:

===== sql/sql_select.cc 1.212 vs edited =====
*** /tmp/sql_select.cc-1.212-782 Tue Dec 3 13:08:22 2002
--- edited/sql/sql_select.cc Tue Dec 3 20:30:16 2002
***************
*** 923,929 ****
JOIN_TAB *end_table= &join.join_tab[join.tables];
for (; table < end_table ; table++)
{
! if (table->select_cond)
{
/* We have to sort all rows */
select_limit= HA_POS_ERROR;
--- 923,929 ----
JOIN_TAB *end_table= &join.join_tab[join.tables];
for (; table < end_table ; table++)
{
! if (table->select_cond || table->keyuse)
{
/* We have to sort all rows */
select_limit= HA_POS_ERROR;


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