MySQL Bugs: #26488: adding an inner join

Bug #26488	adding an inner join
Submitted:	20 Feb 2007 5:12	Modified:	22 Feb 2007 7:59
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.24a-community-nt AND 5.0.27-community-nt	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
adding an inner join to a query should never increase the number of rows returned from the query.  i've found one that will!

How to repeat:
you'll need to get me to post you my database.  from there:

C:\>mysql -uXXXX -pXXXX
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 102 to server version: 5.0.24a-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT jobno
    -> FROM mp.jobsheet j
    -> WHERE jobno='077-033'
    -> LIMIT 10 ;
+---------+
| jobno   |
+---------+
| 077-033 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT jobno
    -> FROM mp.jobsheet j
    -> inner join hamr.pmsched hp on (hp.pmschedcode=j.pmtaskid)
    -> WHERE jobno='077-033'
    -> LIMIT 10 ;
+---------+
| jobno   |
+---------+
| 077-033 |
| 077-033 |
| 077-033 |
| 077-033 |
| 077-033 |
| 077-033 |
| 077-033 |
| 077-033 |
| 077-033 |
| 077-033 |
+---------+
10 rows in set (0.00 sec)

mysql>

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.27. In case of the same problem send complete CREATE TABLE statements and a dump of smallest set of data for these two tables that demonstrates the behaviour described.

i can reproduce the problem on 5.0.27 as well.  i've tried to put together the sql that produces this problem, but i have deadline pressure on me.  do you have some sort of MySQL proxy that records all the statements issued to a MySQL service?  my system is reasonably complex...

i'm sure you want to resolve this problem as much as i do so do you have any suggestions?  i can mail you the entire database...

Why do you think that "adding an inner join to a query should never increase the number of rows" ?

create table t1 (a int); insert t1 values (1);
create table t2 (b int); insert t2 values (1),(1);
select a from t1;
+------+
| a    |
+------+
|    1 | 
+------+
select a from t1 inner join t2 on (a=b);
+------+
| a    |
+------+
|    1 | 
|    1 | 
+------+